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完成,读者自行尝试
更多推荐
SQL之累积计算问题
发布评论