假设hive表 data.table 字段结构如下:
CREATE TABLE IF NOT EXISTS `$target.table`
(
id string COMMENT '用户ID',
pv_cnt bigint COMMENT '浏览次数'
) COMMENT ''
PARTITIONED BY (dt string COMMENT '日期')
STORED AS ORC;
现在需要统计的变量如下:
- date_sum:2019-11-03~2019-11-10 每天的总浏览次数;
- cumulative_sum:从 2019-11-03 开始每天累加的总浏览次数。
正确的统计sql语句如下:
select dt,
sum(pv_cnt) as date_sum,
sum(sum(pv_cnt)) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
from data.table
where dt between '20191103' and '20191110'
group by dt
查询结构如下,可以看到在date_sum
统计的每天总浏览量的基础上(哈哈,巧了,每天都一样),cumulative_sum
统计出了每天自 2019-11-03开始累加的总浏览量,例如 2019-11-07 对应的cumulative_sum
数值就是 2019-11-03~2019-11-07这一功共5天的浏览量数据总和。
如何理解上面的统计累加量的sql代码呢?
sum(sum(pv_cnt)) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
首先累加的对象是每天的总浏览量,也就是sum(pv_cnt),然后再累加每天的总浏览量的和,所以是sum(sum(pv_cnt))
;
但是需要现在限定从那天累加到哪天,这个即是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
的作用:
- UNBOUNDED PRECEDING 表示之前最开始的日期(我们已经使用order by对dt进行从小到大排序了);
- CURRENT表示到当前的日期,也就是截止累加到哪天就是哪天对应的日期。