如何计算当前行与上个月最后一行的差值?

Question

update_time         |   net_value 
----------------------------+---------------------------
2021-03-03 00:33:00.13055  |    0.6
2021-03-02 14:23:18.173233 |    0.5
2021-03-01 01:31:00.146775 |    0.3
2021-02-28 00:33:00.13055  |    0.2

我有一个如上所述的postgres表net_value，每天一行。

如何计算当前行与上个月最后一行的差值?输出如下所示:

update_time         |   monthly_diff 
----------------------------+---------------------------
2021-03-03 00:33:00.13055  |    0.4 // 0.6 - 0.2
2021-03-02 14:23:18.173233 |    0.3 // 0.5 - 0.2
2021-03-01 01:31:00.146775 |    0.1 // 0.3 - 0.2
2021-02-28 00:33:00.13055  |    0.2 // 0.2 - 0

谢谢!

Answer 1

嗯…下面是一个带有join的方法:

select t.*,
(value - prev_eom_net_value) 
from t join
(select yyyymm, eom_net_value,
lag(eom_net_value) over (order by yyyymm) as prev_eom_net_value
from (select date_trunc('month', update_time) as yyyymm,
(array_agg(value order by update_time desc))[1] as eom_net_value
from t
group by yyyymm
) t
) x
on x.yyyymm = date_trunc('month', update_time);

我还认为使用窗口函数和窗框的方法也应该起作用:

select t.*,
(net_value -
max(net_value) over (partition by date_trunc('month', update_time)
order by date_trunc('month', update_time)
range between '1 month' preceding and '1 month' preceding
)
)
from (select t.*,
first_value(net_value) over (partition by date_trunc('month', update_time) order by update_time desc) as eom_net_value
from t
) t;