面筋-数仓SQL

1.连续登录

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

2.停留时长统计

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

uidtm
u110:00:05
u110:00:10
u110:00:15
u110:20:00
u210: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.计算累计时长与总时长及其排名

数据格式如下:

uidplay_tm
u15
u23
u310
u415
u57

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

uidplay_tmrankinc_tmsum_tm
u41511540
u31022540
u5733240
u1543740
u2354040
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
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值