挑战100天不停更，刷爆 hive sql第26天 - 场景题-访问统计

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

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

NUM: 第26天 -访问统计

先说几个关键函数
date_format()
regexp_replace()
sum() over()
如果对这几个函数都比较熟悉的话,下面内容可以不用看了,嵌套个子查询就能查出来
如果考虑到访问记录的去重问题:,可以参考另外一篇:
挑战100天不停更hive sql第24天 -累计去重

🧨刷题~~🧨

🎈表结构

userid:用户
visitdate:日期
visitcount:访问次数

🎉建表并插入数据

CREATE TABLE daily_sql.t26
(
    userId     string,
    visitDate  string,
    visitCount INT
)
    ROW format delimited FIELDS TERMINATED BY "\t";


INSERT INTO TABLE daily_sql.t26
VALUES ('u01', '2017/1/21', 5),
       ('u02', '2017/1/23', 6),
       ('u03', '2017/1/22', 8),
       ('u04', '2017/1/20', 3),
       ('u01', '2017/1/23', 6),
       ('u01', '2017/2/21', 8),
       ('u02', '2017/1/23', 6),
       ('u01', '2017/2/22', 4);

👓问题:以年为单位统计每个用户每月的累积访问次数，如下表所示

✨先看执行结果

解法:

🎨思考

🧨SQL

select
    userId
    ,month
    ,month_cnt
    ,sum(month_cnt) over(partition by userId order by month )
from (
         select userId
              , month
              , sum(visitCount) month_cnt
         from (
             select *
                    --这里不能直接转换
                    -- ,date_format(unix_timestamp(visitDate,'yyyy/MM/dd'),'yyyy/MM')
                    , date_format(regexp_replace(visitDate, '/', '-'), 'yyyy-MM') month
               from t26
             ) t1
         group by userId, t1.month) t2

关于sql的规范问题

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