hive 输出一段时间内每天的连续留存

阿冰你的锅呢

于 2023-03-27 08:55:53 发布

阅读量185

点赞数

文章标签： hive 用户运营

本文链接：https://blog.csdn.net/qq_42519523/article/details/129788180

版权

连续留存：某日登录的用户，在之后连续几天是否活跃，次日留存就是用户注册后第二天仍然活跃，第三日留存就是第二天第三天是否连续登录。

通过用户登陆表join登陆表处理，联表后输出结果，尝试过不联表处理，但是在处理date的时候不group by 就会出现列名不存在的提示，group by后又会出现按日期分组无法统计对一个日期后的记录进行处理。

join后输出结果第一列ID，第二列登录时间，第三列后续登录时间，然后在对后续时间进行判断。

 WHEN b.riqi2 = date_add(a.riqi,1) THEN 1
                  WHEN b.riqi2 = date_add(a.riqi,2) THEN 10
                  WHEN b.riqi2 = date_add(a.riqi,3) THEN 100
                  WHEN b.riqi2 = date_add(a.riqi,4) THEN 1000
                  WHEN b.riqi2 = date_add(a.riqi,5) THEN 10000
                  WHEN b.riqi2 = date_add(a.riqi,6) THEN 100000

这里相当于将玩家一个日期后的登录记录转换为二进制表示。111111表示后面连续6天都登陆，

完整语句如下：

SELECT c.date1,
       count(*),
            count(CASE
                      WHEN pmod(c.panduan,10) = 1 THEN role_id
                      ELSE NULL
                  END) AS shouri,
            count(CASE
                      WHEN pmod(c.panduan,100) = 11 THEN role_id
                      ELSE NULL
                  END) AS yiri,
            count(CASE
                      WHEN pmod(c.panduan,1000) = 111 THEN role_id
                      ELSE NULL
                  END) AS erri,
            count(CASE
                      WHEN pmod(c.panduan,10000) = 1111 THEN role_id
                      ELSE NULL
                  END) AS sanri,
            count(CASE
                      WHEN pmod(c.panduan,100000) = 11111 THEN role_id
                      ELSE NULL
                  END) AS siri
FROM
  (SELECT a.id,
          a.riqi AS date1,
          sum(CASE
                  WHEN b.riqi2 = date_add(a.riqi,1) THEN 1
                  WHEN b.riqi2 = date_add(a.riqi,2) THEN 10
                  WHEN b.riqi2 = date_add(a.riqi,3) THEN 100
                  WHEN b.riqi2 = date_add(a.riqi,4) THEN 1000
                  WHEN b.riqi2 = date_add(a.riqi,5) THEN 10000
                  WHEN b.riqi2 = date_add(a.riqi,6) THEN 100000
                  ELSE 0
              END) AS panduan
   FROM
     (SELECT id,
             from_unixtime(unix_timestamp(`date`,'yyyymmdd'),'yyyy-mm-dd') AS riqi
      FROM denglu
      WHERE `date` BETWEEN '20230309' AND '20230323'
      GROUP BY role_id,
               `date`)a
   LEFT JOIN
     (SELECT id
             from_unixtime(unix_timestamp(`date`,'yyyymmdd'),'yyyy-mm-dd') AS riqi2
      FROM denglu
      WHERE `date` BETWEEN '20230309' AND '20230323'
      GROUP BY role_id,
               `date`)b ON a.id = b.id
   GROUP BY id,
            riqi)c
GROUP BY date1

技术有限，比较小白，如果有更好的方法，欢迎指点。