如何不使用辅助表/列，用内置函数实现左连接列值覆盖前1后2行？

• 核心需求：将右表连接后新增的flag列值，复制到对应匹配行的前1行和后2行（按同一contractid下的yearmonth顺序）
• 原方案局限：由于yearmonth是字符串格式（如202301），直接做数值加减会出现无效值（如202301-1=202300），因此原方案通过创建辅助表生成连续的yearmonth_id来关联，但现在需要无需辅助表/列的替代实现方式

数据示例代码 #

drop table if exists public.left_table;
create table public.left_table (contractid integer, yearmonth varchar(6), desired boolean);
insert into public.left_table (contractid, yearmonth, desired)
values
    (1, '202201', false), (1, '202202', true), (1, '202203', true), (1, '202204', true),
    (1, '202205', true), (1, '202206', false), (1, '202207', false),
    (2, '202210', false), (2, '202211', false), (2, '202212', true), (2, '202301', true),
    (2, '202302', true), (2, '202303', true), (2, '202304', false);
select * from left_table;

drop table if exists public.right_table;
create table public.right_table (contractid integer, yearmonth varchar(6), flag boolean);
insert into public.right_table (contractid, yearmonth, flag)
values
    (1, '202203', true),
    (2, '202301', true);
select * from right_table;

原解决方案代码 #

drop table if exists yearmonth_ids;
create table yearmonth_ids as
select row_number() over() yearmonth_id, to_char(x, 'YYYYMM') yearmonth
from generate_series('2018-01-01', current_date, '1 month') x;

select * from yearmonth_ids;

select 
    lt.*,
    coalesce(rt.flag, false) flag
from 
    (select l.*, y.yearmonth_id from left_table l left join yearmonth_ids y on l.yearmonth = y.yearmonth) lt
left join 
    (select r.*, y.yearmonth_id from right_table r left join yearmonth_ids y on r.yearmonth = y.yearmonth) rt
on lt.contractid = rt.contractid and lt.yearmonth_id between rt.yearmonth_id-1 and rt.yearmonth_id+2
order by lt.contractid, lt.yearmonth;

替代方案（无需辅助表） #

实现思路 #

将字符串格式的yearmonth转换为日期类型，利用PostgreSQL的日期区间计算能力，直接判断左表月份是否落在右表对应月份的前1个月至后2个月范围内，无需依赖辅助表生成连续ID。

代码实现 #

select
    lt.contractid,
    lt.yearmonth,
    lt.desired,
    coalesce(
        max(rt.flag) over (
            partition by lt.contractid
            order by to_date(lt.yearmonth, 'YYYYMM')
            range between interval '1 month' preceding and interval '2 months' following
        ),
        false
    ) as flag
from public.left_table lt
left join public.right_table rt
    on lt.contractid = rt.contractid
    and to_date(lt.yearmonth, 'YYYYMM') between 
        to_date(rt.yearmonth, 'YYYYMM') - interval '1 month'
        and to_date(rt.yearmonth, 'YYYYMM') + interval '2 months'
order by lt.contractid, lt.yearmonth;

逻辑说明 #

1. 日期转换：通过to_date(yearmonth, 'YYYYMM')将字符串月份转为标准日期，解决直接数值加减的无效值问题
2. 区间关联：左表与右表按contractid关联，同时判断左表日期是否在右表日期的前1个月到后2个月区间内
3. 窗口函数填充：用max(rt.flag) over (...)窗口函数，将符合区间条件的行统一标记为true，最后用coalesce将未匹配的行设为false

内容的提问来源于stack exchange，提问作者kzmlbyrk