hiveSQL面试题二：分组问题

题目

如下电商公司用户访问数据 table1 

id                  ts(秒)

1001         17523641234

1001         17523641256

1002         17523641278

1001         17523641334

1002         17523641434

1001         17523641534

1001         17523641544

1002         17523641634

1001         17523641638

1001         17523641654

某个用户连续的访问记录如果时间间隔小于60秒则分为同一组，结果为： 

id                 ts(秒)                  group

1001          17523641234       1

1001          17523641256       1

1001          17523641334       2

1001          17523641534       3

1001          17523641544       3

1001          17523641638       4

1001          17523641654       4

1002          17523641278       1

1002          17523641434       2

1002          17523641654       3

 题解

       SELECT 
       id ,
       ts ,
       SUM(IF(diffts>60,1,0)) OVER (PARTITION BY id ORDER BY ts ) AS groupid 
       -- 相差小于60的为同一组,大于60的组id+1
       -- 这里sum开窗函数没有指定窗口，但制定了排序默认 从最开始到当前行 rows between unbounded preceding and current row
       FROM (
			SELECT 
			 id ,
			 ts ,
			 ts - lagts AS diffts  
			FROM (
			   SELECT 
			    id ,
			    ts ,
			    lag(ts,1,0) OVER (PARTITION BY id ,order BY ts ) lagts --下移一位
			   FROM 
			     table1 
			   ) a 
		 ) b