sql - lag on a lag, PostgreSQL (获取本月前 2 个月的值)

Question

表格1

customer_id         month          subscription_price
1               April 2020         49.0
2               February 2020      19.0
3               August 2019        20.0
4               February 2021      39.0
5               April 2020         19.0
6               June 2018          59.0
7               January 2020       15.0

上表按月包含每个客户 ID 的订阅，其中 1 位客户订阅一年将有 12 行。

下面的查询

    SELECT customer_id as c_id,month
         ,lag(subscription_price) over (partition by customer_id order by month asc) as before
         ,subscription_price as current
         ,lead(subscription_price) over (partition by customer_id order by month asc) as after
    FROM schema.table st

返回 table_2

     c_id     month          before  current after
0       1   April    2020   NaN     28.0    28.0
1       1   August   2020   28.0    28.0    28.0
2       1   December 2020   28.0    28.0    28.0
3       1   February 2020   28.0    28.0    28.0
4       1   February 2021   28.0    28.0    28.0
... ... ... ... ... ...
1000    50  June     2020   71.2    32.0    71.2
1001    50  March    2020   32.0    71.2    32.0

基于 table_2，我想创建一个新列，指定：

case when before is null 
and before_2 is not null 
and current is not null 
then 'reactivation'
end as status

我的问题是：我怎样才能得出本月前两个月的订阅价格？（名为 before_2 的列）

lag(lag(subscription_price) over (partition by customer_id order by month asc)) as before_2

返回Programming Error。

标签： sqlpostgresql

Answer 1

LAG并且LEAD都采用额外的参数 - 你关心的是第二个参数，offset：

SELECT customer_id as c_id,month
     ,lag(subscription_price, 2) over (partition by customer_id order by month asc) as before
     ,subscription_price as current
     ,lead(subscription_price, 2) over (partition by customer_id order by month asc) as after
FROM schema.table st

只需添加 2 的偏移量，如上所示，您应该会很好。