挑战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_ageFROM (-- 3,通过用户和flag分组,并判断count() >=2 ,求出连续两天的用户SELECT user_id, max(age) ageFROM (--2,date_sub 和排名相减,求得flagSELECT user_id, age--求得相同的日期用于下一个子查询分组, date_sub(dt, rn) flagFROM (-- 1, 根据开窗函数row_number()取出排行,并通过用户和时间分组SELECT user_id, dt, max(age)                                             age, row_number() OVER (PARTITION BY user_id ORDER BY dt) rnFROM t27GROUP BY dt, user_id) t1) t2GROUP BY user_id, flagHAVING count(*) >= 2) t3GROUP 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) ageFROM daily_sql.t27GROUP BY user_id) t5;

关于sql的规范问题

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！