Data acquisition of any adjacent time period in hive

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.