将一个结果与以前的使用日期进行比较时，获得增加或减少的百分比

Question

这是我的查询：

select 
country,
channel,
month,
sum(revenue)
from a 
group by 1,2,3

那么，有可能得到当月收入与上月收入之间的差异(%(吗？

提前感谢

Answer 1

您可以将lag()与聚合一起使用：

select country, channel, month,
sum(revenue) as revenue,
(-1 + sum(revenue) / lag(sum(revenue)) over (partition by country, channel order by month)
) as change_ratio
from a
group by 1, 2, 3

Answer 2

这里有一种方法：

select * 
,((revenue/ LAG(revenue,1,0) over (country,channel order by month)) -1)* 100.0 perc
from (
select 
country,
channel,
month,
sum(revenue) revenue
from a 
group by 1,2,3
) t