sql - 如何找到上一年的变化百分比

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+

标签： sqlpostgresql

Answer 1

您可以使用窗口函数：

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