How to calculate the time of a certain field with lead next_time

This article mainly explains "how to use lead next_time to calculate a certain field time", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to use lead next_time to calculate a certain field time" bar!

LEAD (col,n,DEFAULT) is used to count the value of the nth line down in the window

Parameter 1 is the column name, parameter 2 is the next nth line (optional, default is 1), and parameter 3 is the default value (when the next nth behavior NULL, take the default value, if not specified, it is NULL)

Scene

problem

User Peter is browsing the web, at a certain moment, Peter clicks into a page, and after a period of time, Peter enters another page again and again, so repeatedly, how to count the stay time of Peter in a particular web page, or how to calculate the total stay time of users of a web page?

Data preparation

Now the user's behavior has been collected, and the processing is converted to the hive data table. The table structure is as follows:

Create table test.user_log (

Userid string

Time string

Url string

) row format delimited fields terminated by','

Record data:

+-+-+

| | user_log.userid | user_log.time | user_log.url | |

+-+-+

| | Peter | 2015-10-12 01:10:00 | url1 |

| | Peter | 2015-10-12 01:15:10 | url2 |

| | Peter | 2015-10-12 01:16:40 | url3 |

| | Peter | 2015-10-12 02:13:00 | url4 |

| | Peter | 2015-10-12 03:14:30 | url5 |

| | Marry | 2015-11-12 01:10:00 | url1 |

| | Marry | 2015-11-12 01:15:10 | url2 |

| | Marry | 2015-11-12 01:16:40 | url3 |

| | Marry | 2015-11-12 02:13:00 | url4 |

| | Marry | 2015-11-12 03:14:30 | url5 |

+-+-+

Thank you for your reading, the above is the content of "how to use lead next_time to calculate a certain field time". After the study of this article, I believe you have a deeper understanding of how to use lead next_time to calculate a certain field time, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!