基于日期关联两表：匹配无对应年份销售记录的方案问询

需要基于setcode字段关联sale表与dict表，dict表仅包含2022年和2023年的条目，但sale表存在2024年的销售记录无法匹配，且不能向dict表添加新行。要求实现关联后，2024年的销售记录匹配对应setcode的2023年dict条目，得到如下结果：

a.setcode   a.datum      b.setcode    b.year  
S201        2022-05-05   S201         2022 
S201        2023-04-04   S201         2023 
S201        2024-06-06   S201         2023

现有表结构及数据：

create table dict (setcode string, year numeric);
create table sale (setcode string, datum date);

insert into dict
values ('S201',2022), ('S202',2022),('S201',2023), ('S202',2023);

insert into sale
values ('S201', '2022-05-05'), ('S201', '2023-04-04'), ('S201', '2024-06-06');

当前使用的查询只能匹配年份完全相同的记录，2024年数据无法关联：

select * from  sale a
  join dict b
 on cast(FORMAT_DATE("%Y",a.datum) as integer)=b.year
and  a.setcode=b.setcode
where cast(FORMAT_DATE("%Y",a.datum) as integer) >= 2022;

核心思路是：给每条销售记录，找到对应setcode下不大于销售年份的最大dict年份，这样2024年的记录会自动匹配到2023年的条目，2022、2023年的记录也能匹配到对应年份的条目。

方案1：子查询直接关联 #

SELECT 
    a.setcode, 
    a.datum, 
    b.setcode AS b_setcode, 
    b.year AS b_year
FROM sale a
JOIN dict b 
    ON a.setcode = b.setcode
    AND b.year = (
        -- 找到当前setcode下不大于销售年份的最大dict年份
        SELECT MAX(year) 
        FROM dict 
        WHERE setcode = a.setcode 
          AND year <= EXTRACT(YEAR FROM a.datum)
    )
WHERE EXTRACT(YEAR FROM a.datum) >= 2022;

方案2：窗口函数筛选最优匹配 #

先给每个销售记录的候选dict条目按年份倒序排名，再取排名第一的条目：

WITH ranked_dict AS (
    SELECT 
        a.setcode,
        a.datum,
        b.setcode AS b_setcode,
        b.year AS b_year,
        -- 按年份倒序排名，最大年份排第一
        ROW_NUMBER() OVER (
            PARTITION BY a.setcode, a.datum 
            ORDER BY b.year DESC
        ) AS rn
    FROM sale a
    JOIN dict b 
        ON a.setcode = b.setcode
        AND b.year <= EXTRACT(YEAR FROM a.datum)
    WHERE EXTRACT(YEAR FROM a.datum) >= 2022
)
SELECT setcode, datum, b_setcode, b_year
FROM ranked_dict
WHERE rn = 1;

补充说明 #

• 用EXTRACT(YEAR FROM a.datum)提取年份比FORMAT_DATE更简洁高效，避免类型转换的麻烦。
• 两种方案都能实现需求，可根据实际数据库的性能偏好选择（子查询适合数据量不大的场景，窗口函数在大数据量下可能更稳定）。

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