简单HQL练习-统计用户累计访问次数

题目：
统计用户累计访问次数与当月访问次数

输出格式：
u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

数据准备：

lx1.csv
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

建表：

hive> create table action(
    > userId string,
    > visitDate string,
    > visitCount int)
    > row format delimited fields terminated by '\t';

导入数据:

hive> load data local inpath '/usr/hdk/data/lx1.csv' into table action;

分析：需要输出四个字段：UID，访问日期，当月访问次数，累计访问次数
1.先将访问日期格式化输出,可以使用date_format函数

select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from
    action;

输出结果：
u01	2017-01	5
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3
u01	2017-01	6
u01	2017-02	8
u02	2017-01	6
u01	2017-02	4

然后UID，访问日期都有了，第三个是当月访问次数，只需要按照UID和访问日期分组聚合即可。
2. 对UID和访问日期分组求和

select
    userId,
    mn,
    sum(visitCount)
from
    (
    select
        userId,
        date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
        visitCount
    from
        action
    ) t1
group by
    userId,
    mn
;

访问日期：
u01	2017-01	11
u01	2017-02	12
u02	2017-01	12
u03	2017-01	8
u04	2017-01	3

第四个字段是这道题的核心考点：如何进行累加，肯定使用窗口函数。
3. 按照UID和时间对访问次数做累加

select
    uId,
    mn,
    sum_uv,
    sum(sum_uv) over(partition by uId order by mn)
from
    (
    select
        userId as uId,
        mn,
        sum(visitCount) as sum_uv
    from
        (
        select
            userId,
            date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
            visitCount
        from
            action
        ) t1
    group by
        userId,
        mn
    ) t2
;

输出结果：
u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

小结：
1.不要只想着把sql全部写在一条语句中。
2.细化问题