挑战100天不停更hive sql第27天 -活跃用户统计

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

NUM: 第27天 -活跃用户统计

今天是关于活跃用户的统计问题, 所有用户及连续登陆两天的活跃用户,所有用户的活跃度比较简单,那连续两天活跃的用户要怎么求呢 ?
这道题的核心在于: 日期减去行号 -->在分组-->判断是否>=2,那么就是活跃的用户,这个思想特别重要,之前写过的还有关于求绝对值去算最接近的, 这个: 挑战100天不停更hive sql第23天 - 非等值连接-最近匹配,大家可以放在一起去思考!!
把要用到的贴出来,可以先思考
decimal() (下面会详细说明和round()的区别)
date_sub()
row_number()
为了代码规范, 今天的sql把关键字全部换为大写了, 虽然看着还不太习惯, 但是规范很重要~~

🧨那就不废话了, 刷题~~🧨

🎈表结构

🎉建表并插入数据

CREATE TABLE t27
(
    dt      string,
    user_id string,
    age     int
)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-11', 'test_1', 23);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-11', 'test_2', 19);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-11', 'test_3', 39);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-11', 'test_1', 23);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-11', 'test_3', 39);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-11', 'test_1', 23);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-12', 'test_2', 19);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-13', 'test_1', 23);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-15', 'test_2', 19);
INSERT INTO TABLE daily_sql.t27
VALUES ('2019-02-16', 'test_2', 19);

👓问题:

✨先看执行结果

解法:

🎨思考

1. 根据开窗函数row_number()取出排行,并通过用户和时间分组
2. 使用date_sub()求得flag
3. 通过用户和flag分组,并判断count() >=2 ,求出连续两天的用户
4. 通过用户分组求得连续两天的访问用户及平均年龄
   1. 这里为什么要用decimal (m,n)呢? 而不使用round()函数呢?
      decimal是数据类型 decimal(m,n)表示数字总长度为m位，小数位为n位，那么整数位就只有m-n位,一般结合cast()使用
      round(m,n)为函数,用法是是四舍五入, 小数点后面保留n位,如果m是整数则直接显示小数点后面的0
5. union all 所有用户
   

🧨SQL

-- 5,union all 所有用户
SELECT total_user_cnt
     , total_user_avg_age
     , two_days_cnt
     , avg_age
FROM (
         -- 4,通过用户分组求得连续两天的访问用户及平均年龄
         SELECT 0                                             total_user_cnt
              , 0                                             total_user_avg_age
              , count(*)                                   AS two_days_cnt
              -- decimal (m,n)表示数字总长度为m位，小数位为n位，那么整数位就只有m-n位
              , cast(sum(age) / count(*) AS decimal(5, 2)) AS avg_age
         FROM (
                  -- 3,通过用户和flag分组,并判断count() >=2 ,求出连续两天的用户
                  SELECT user_id
                       , max(age) age
                  FROM (
                           --2,date_sub 和排名相减,求得flag
                           SELECT user_id
                                , age
                                --求得相同的日期用于下一个子查询分组
                                , date_sub(dt, rn) flag
                           FROM (
                                    -- 1, 根据开窗函数row_number()取出排行,并通过用户和时间分组
                                    SELECT user_id
                                         , dt
                                         , max(age)                                             age
                                         , row_number() OVER (PARTITION BY user_id ORDER BY dt) rn
                                    FROM t27
                                    GROUP BY dt, user_id
                                ) t1
                       ) t2
                  GROUP BY user_id, flag
                  HAVING count(*) >= 2
              ) t3
         GROUP BY user_id
     ) t4
UNION ALL
SELECT count(*)                                   total_user_cnt
     , cast(sum(age) / count(*) AS decimal(5, 2)) total_user_avg_age
     , 0                                          two_days_cnt
     , 0                                          avg_age
FROM (
         SELECT user_id,
                max(age) age
         FROM daily_sql.t27
         GROUP BY user_id
     ) t5;

关于sql的规范问题

1. 尽量把逗号放在前面,为什么?
   1. 方便排查,不会遗漏逗号
   2. 方便注释,可以单行直接注释,不用再改逗号
   3. 排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
   4. 特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快
2. 使用关键字大写,养成好习惯