SQL之累积计算问题"/>

SQL之累积计算问题

0. 需求

 我们有如下的用户访问数据userId  visitDate   visitCountu01     2017/1/21        5u02     2017/1/23        6u03     2017/1/22        8u04     2017/1/20        3u01     2017/1/23        6u01     2017/2/21        8U02     2017/1/23        6U01     2017/2/22        4要求使用SQL统计出每个用户的累积访问次数，如下表所示：用户id    月份    小计  累积u01     2017-01  11    11u01     2017-02  12    23u02     2017-01  12    12u03     2017-01  8     8u04     2017-01  3     3

 1.实现

      窗口函数典型案例

      (1) 数据准备

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 

    （2）创建hive表

CREATE TABLE dan_test.sqltest1 ( userId string, visitDate string,visitCount string )
ROW format delimited FIELDS TERMINATED BY "\t";load data local inpath "/home/centos/dan_test/sqltest1.txt" into table sqltest1;

   （3）需求实现

     ① 将时间进行格式化，转换成题目要求的时间

    采用date_format函数+regexp_replace函数

select date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') from sqltest1hive> select date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') from sqltest1;
OK
2017-01
2017-01
2017-01
2017-01
2017-01
2017-02
2017-01
2017-02
Time taken: 0.15 seconds, Fetched: 8 row(s)

    转换日期作为子表

SELECT userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,visitcount
FROM sqltest1

  ② 分析函数+窗口函数实现

    注意求用户每月汇总额的时候注意去重，如不去重则是如下结果。为什么需要去重？因为窗口函数作用的是每条记录，在每条记录的后面都会形成结果，其分析函数，分析的范围是over()函数中所指定的，这一点一定要弄明白

select t.userid as userid  ,t.visitmonth as visitmonth,sum(t.visitcount) over(partition BY userid,visitmonth) as sum_per_monthfrom(SELECT userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,visitcountFROM sqltest1)t

     本题最终的结果如下：

select s.userid,s.visitmonth,s.sum_per_month,sum(s.sum_per_month) over(partition BY s.userid order by s.visitmonth) as total_visitcount
from
(select distinct t.userid as userid  ,t.visitmonth as visitmonth,sum(t.visitcount) over(partition BY userid,visitmonth) as sum_per_monthfrom(SELECT userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,visitcountFROM sqltest1)t
)s

2 小 结

   本题主要的使用知识点归纳如下：

•   （1）regexp_replace函数的使用
•   （2）date_format函数的使用
•   （3）窗口函数与分析函数的结合使用。当然本题也可以不用窗口函数分析，也可以使用group by完成，读者自行尝试