面筋-数仓SQL

1.连续登录

描述：连续登录3天的用户

2.停留时长统计

描述：超过2min算不在线,统计停留时长

uid	tm
u1	10:00:05
u1	10:00:10
u1	10:00:15
u1	10:20:00
u2	10:31:00

解:分组排序,表自关联,交叉错位相减

SELECT a.uid, a.start_tm, a.start_tms, a.rank,
       (end_tms-start_tms) AS stop_tms,
       CASE (end_tms-start_tm) > 120000 THEN 1 ELSE 0 AS morn_2mins_flag
FROM
  (SELECT UID, tm AS start_tm, unix_timestamp AS start_tms,
          row_number() over(PARTITION BY UID ORDER BY unix_timestamp) AS rank
   FROM tmp.data) a
LEFT JOIN
  (SELECT UID, tm, tms, rank
   FROM
     (SELECT UID, tm AS end_tm, unix_timestamp AS end_tms,
             row_number() over(PARTITION BY UID ORDER BY unix_timestamp) AS rank
      FROM tmp.data) tmp
   WHERE tmp.rank > 1 ) b 
ON a.uid = b.uid AND a.rank = (b.rank - 1)

3.计算累计时长与总时长及其排名

数据格式如下:

uid	play_tm
u1	5
u2	3
u3	10
u4	15
u5	7

求:计算累计时长与总时长及其排名,类似以下

uid	play_tm	rank	inc_tm	sum_tm
u4	15	1	15	40
u3	10	2	25	40
u5	7	3	32	40
u1	5	4	37	40
u2	3	5	40	40

SELECT UID,
       play_tm,
       rank ,
       sum(play_tm) over(PARTITION BY 1 ORDER BY rank ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS inc_tm
FROM
  (SELECT UID,
          play_tm,
          row_number() over(PARTITION BY 1 ORDER BY play_tm DESC) AS rank
   FROM hw_tmpdb.tmp_play_time) a