Sql 获取上个月和上一年的值

如何使用基于连接的基本逻辑来执行此操作，我还包括了"当前合计月份"，只是为了判断这些数字是否有意义:

WITH my_table(USER, ID_REW, POINTS, POC, _DATE_) as (
    SELECT * FROM VALUES
        (1, 1635798105611, 10, 1, '2021-01-01'::date),
        (1, 1635798105611, 20, 1, '2021-01-25'::date),
        (1, 1635798105611, 10, 1, '2021-02-05'::date),
        (1, 1635798105611, 15, 1, '2021-02-20'::date),
        (2, 1641209778245, 50, 2, '2021-02-03'::date),
        (2, 1654058198065, 60, 2, '2021-05-15'::date),
        (2, 1654065920033, 15, 2, '2022-02-25'::date),
        (2, 1654051152555, 10, 4, '2022-02-26'::date),
        (2, 1654051152555, 5, 4, '2023-02-15'::date),
        (2, 1635798105611, 25, 4, '2023-02-25'::date)
), enriched as (
    select *
        ,date_trunc(month, _date_) as sum_month
    from my_table
), summed as (
    select 
        user 
        ,id_rew
        ,poc
        ,sum_month
        ,dateadd('month', 1, sum_month) as next_month
        ,dateadd('year', 1, sum_month) as next_year
        ,sum(points) as s_points
    from enriched
    group by 1,2,3,4
)
select a.*
    ,b.s_points as current_months_points
    ,c.s_points as prev_months_points
    ,d.s_points as prev_year_points  
from enriched as a
join summed as b 
    on a.user = b.user and a.id_rew = b.id_rew and a.poc = b.poc and a.sum_month = b.sum_month
left join summed as c
    on a.user = c.user and a.id_rew = c.id_rew and a.poc = c.poc and a.sum_month = c.next_month
left join summed as d
    on a.user = d.user and a.id_rew = d.id_rew and a.poc = d.poc and a.sum_month = d.next_year
order by 1,2,4,5
    

执行情况:

但如果它在我的数据库上，我会将前一个的联接重新组织到CTE中，以降低所有成本:

WITH my_table(USER, ID_REW, POINTS, POC, _DATE_) as (
    SELECT * FROM VALUES
        (1, 1635798105611, 10, 1, '2021-01-01'::date),
        (1, 1635798105611, 20, 1, '2021-01-25'::date),
        (1, 1635798105611, 10, 1, '2021-02-05'::date),
        (1, 1635798105611, 15, 1, '2021-02-20'::date),
        (2, 1641209778245, 50, 2, '2021-02-03'::date),
        (2, 1654058198065, 60, 2, '2021-05-15'::date),
        (2, 1654065920033, 15, 2, '2022-02-25'::date),
        (2, 1654051152555, 10, 4, '2022-02-26'::date),
        (2, 1654051152555, 5, 4, '2023-02-15'::date),
        (2, 1635798105611, 25, 4, '2023-02-25'::date)
), enriched as (
    select *
        ,date_trunc(month, _date_) as sum_month
    from my_table
), summed as (
    select 
        user 
        ,id_rew
        ,poc
        ,sum_month
        ,dateadd('month', 1, sum_month) as next_month
        ,dateadd('year', 1, sum_month) as next_year
        ,sum(points) as s_points
    from enriched
    group by 1,2,3,4
), gather as (
    select 
        a.* exclude(next_month, next_year, s_points)
        ,b.s_points as prev_months_points
        ,c.s_points as prev_year_points  
    from summed as a
    left join summed as b 
        on a.user = b.user 
            and a.id_rew = b.id_rew 
            and a.poc = b.poc 
            and a.sum_month = b.next_month
    left join summed as c
        on a.user = c.user 
            and a.id_rew = c.id_rew 
            and a.poc = c.poc 
            and a.sum_month = c.next_year
)
select a.* exclude(sum_month)
    ,b.prev_months_points
    ,b.prev_year_points
from enriched as a
join gather as b 
    on a.user = b.user and a.id_rew = b.id_rew and a.poc = b.poc and a.sum_month = b.sum_month

order by 1,2,4,5