要求使用SQL统计出每个用户的累积访问次数，如下表所示
| 用户id | 月份 | 小计 | 累积 |
|---|---|---|---|
| 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 |
实现效果如图所示:
| visitDate | visitCount | |
|---|---|---|
| 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 |
数据准备:
CREATE TABLE test_sql.test1 (userId string, visitDate string, visitCount INT )
ROW format delimited FIELDS TERMINATED BY "\t";
INSERT INTO TABLE test_sql.test1 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 );需求实现:
with t as (select userid,
substring(visitDate, 1, 6) as month,
sum(visitCount) as date_count
from test1
group by userid, substring(visitDate, 1, 6))
select t.userid,
date_format(replace(concat(t.month, '/17'), '/', '-'), 'yyyy-MM'),
date_count,
sum(date_count) over (partition by userId order by userId rows between unbounded preceding and current row) from t;