运行超过日期的总计PRESTO SQL [英] Running total sum over date presto SQL
本文介绍了运行超过日期的总计PRESTO SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Date | T | S
1/2/19 | 2 | 5
2/1/19 | 5 | 1
3/1/19 | 1 | 1
我想要
Date | T | S | cum_T | cum_S
1/2/19 | 2 | 5 | 2 | 5
2/1/19 | 5 | 1 | 7 | 6
3/1/19 | 1 | 1 | 8 | 7
但是,当我使用Presto SQL运行下面的查询时,我收到一条意外的错误消息,告诉我要将列T和S放入查询的GROUP BY部分。
这是预期的吗?当我从查询中删除GROUP BY时,它运行时没有错误,但生成了重复的日期行。+
select
date_trunc('day',tb1.date),
sum(tb1.S) over (partition by date_trunc('day',tb1.date) order by date_trunc('day',tb1.date) rows unbounded preceding ) as cum_S,
sum(tb1.T) over (partition by date_trunc('day',tb1.date) order by date_trunc('day',tb1.date) rows unbounded preceding) as cum_T
from esi_dpd_bi_esds_prst.points_tb1_use_dedup_18months_vw tb1
where
tb1.reason_id not in (45,264,418,983,990,997,999,1574)
and tb1.group_id not in (22)
and tb1.point_status not in (3)
and tb1.date between cast(DATE '2019-01-01' as date) and cast( DATE '2019-01-03' as date)
group by
1
order by date_trunc('day',tb1.date) desc
错误如下:
Error: line 3:1: '"sum"(tb1.S) OVER (PARTITION BY "date_trunc"('day', tb1.tb1) ORDER BY "date_trunc"('day', tb1.tb1) ASC ROWS UNBOUNDED PRECEDING)' must be an aggregate expression or appear in GROUP BY clause.
推荐答案
您有一个聚合查询,并且希望将聚合与窗口函数混合使用。正确的语法是:
select date_trunc('day', tb1.date),
sum(tbl1.S) as S,
sum(tbl1.T) as T,
sum(sum(tb1.S)) over (order by date_trunc('day', tb1.date) rows unbounded preceding ) as cum_S,
sum(sum(tb1.T)) over (order by date_trunc('day', tb1.date) rows unbounded preceding) as cum_T
from esi_dpd_bi_esds_prst.points_tb1_use_dedup_18months_vw tb1
where tb1.reason_id not in (45, 264, 418, 983, 990, 997, 999, 1574) and
tb1.group_id not in (22) and
tb1.point_status not in (3) and
tb1.date between cast(DATE '2019-01-01' as date) and cast( DATE '2019-01-03' as date)
group by 1
order by date_trunc('day', tb1.date) desc ;
也就是说,窗口函数在聚合之后运行,需要处理聚合值。
这篇关于运行超过日期的总计PRESTO SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文