Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Data acquisition of any adjacent time period in hive

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

Shulou(Shulou.com)06/03 Report--

Obtaining adjacent period data through sql statement is not better than through other programming languages, because there is no for loop in sql, so it is necessary to add a secondary table data during implementation. Here, a record is made for this method.

Background: obtain the number of platform users entering and leaving Guizhou Province in 2017 (divided into inbound and outbound times).

Handling method:

1. Obtain the users who appeared in Guizhou that year according to the location data.

two。 Get all the location data of these users and sort them according to the location time

As above, from left to right, the user id, location time, location province id, sort number, whether it is located in Guizhou or not

3. Here, it is considered to judge whether a user is entering or leaving the port according to the if_loc field, but it cannot be fully realized by relying on this a table alone, so consider adding a b table. The data of the b table comes from the a table, but there will be some differences between the two tables for the data corresponding to rn values.

Where b.rn_b = a.rn-1

4. Associate the two tables to obtain the entry and departure tags port_flag

Select a. Bid. A. Promoadtime.provincewriting. B. If written locales b-a.if_loc port_flagfrom ainner join b on (a.bid = b.bid and a.rn = b.rn_b)

Entry when port_flag = 1, departure when port_flag =-1, and continuous positioning within the harbor when port_flag = 0.

Entry and departure involve the positioning data of two periods of time, in this way, other data of the two periods can also be obtained.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report