如何查找与上一年相比的变化百分比

Question

采购表

create table purchase ( 
id integer primary key, 
purchasedate date not null, 
purchasesum numeric(12,2) not null )

包含购买金额。如何查找与上一年相比的变化百分比：

select
extract( year from purchasedate ),
sum( purchasesum),
( sum( purchasesum) - previous row sum( previous purchasesum ) ) /
previous row sum( previous purchasesum ) * 100
from purchase
group by 1
order by 1

结果应该是

2014     1200
2015     1320    +10%
2016     14784   +12%
2017     1774.08 +20%
2018     1596.67 -10% 
2019     2075.67 +30%

使用 Postgresql 9.1+

Answer 1

GMB的解决方案很好。 一种更短的表达方式是：

select extract(year from purchasedate),
sum(purchasesum),
(sum(purchasesum) * 100 /
lag(sum(purchasesum)) over (order by min(purchasedate)
) - 100 
from purchase
group by 1
order by 1;

请注意，这两种解决方案都假定年份中没有间隔。

如果这是可能的，那么range窗口框架可以解决问题：

select extract(year from purchasedate),
sum(purchasesum),
(sum(purchasesum) * 100 /
sum(sum(purchasesum)) over (order by extract(year from purchasedate)
range between interval 1 preceding and 1 preceding)
) - 100 
from purchase
group by 1
order by 1;

Answer 2

您可以使用窗口函数：

select
extract(year from purchasedate),
sum(purchasesum),
(
sum(purchasesum) 
- lag(sum(purchasesum)) over(order by extract(year from purchasedate))
) 
/ lag(sum(purchasesum)) over(order by extract(year from purchasedate)) 
* 100 percent_increase
from purchase
group by 1
order by 1