基于PO number与schedule_code的相邻周一记录新旧标识技术问询

问题背景 #

现有数据集按日期、PO number维度存储不同schedule_code，需筛选数据中最新的两个周一的记录，对每个(PO number, schedule_code)组合标记：

• Old：上周的周一已存在该组合
• New：仅本周的周一出现该组合

输入表结构与示例数据 #

输入表（schedule_data） #

示例输入数据 #

预期输出 #

注：上周的所有组合默认标记为Old；本周的组合若上周已存在则为Old，否则为New

常见尝试误区 #

很多开发者容易踩的坑：

1. 误将"当前日期往前推两个周一"作为筛选条件，而非从现有数据中提取实际存在的最新两个周一
2. 未区分"上周记录"和"本周记录"的标记逻辑，统一用自关联判断导致结果错误

正确SQL实现方案 #

完整SQL代码 #

WITH recent_mondays AS (
    -- 提取数据中最新的两个周一
    SELECT DISTINCT record_date
    FROM schedule_data
    -- 注意：不同数据库的星期判断语法有差异，下方是通用示例，需根据数据库调整
    WHERE TO_CHAR(record_date, 'DY') = 'MON' 
    ORDER BY record_date DESC
    LIMIT 2
),
filtered_data AS (
    -- 筛选出两个周一的所有记录，并关联上周周一的日期
    SELECT 
        sd.record_date,
        sd.po_number,
        sd.schedule_code,
        MAX(CASE WHEN rm.record_date != sd.record_date THEN rm.record_date END) OVER () AS prev_monday
    FROM schedule_data sd
    JOIN recent_mondays rm ON sd.record_date = rm.record_date
)
-- 最终标记状态
SELECT 
    fd.record_date,
    fd.po_number,
    fd.schedule_code,
    CASE
        WHEN fd.record_date = fd.prev_monday THEN 'Old'
        ELSE CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM schedule_data sd
                WHERE sd.record_date = fd.prev_monday
                  AND sd.po_number = fd.po_number
                  AND sd.schedule_code = fd.schedule_code
            ) THEN 'Old'
            ELSE 'New'
        END
    END AS status
FROM filtered_data fd
ORDER BY fd.record_date DESC, fd.po_number, fd.schedule_code;

数据库适配调整 #

• MySQL：将TO_CHAR(record_date, 'DY')替换为DAYNAME(record_date) = 'Monday'，保留LIMIT 2
• SQL Server：将TO_CHAR(record_date, 'DY')替换为DATENAME(weekday, record_date) = 'Monday'，LIMIT 2替换为TOP 2
• Oracle：将LIMIT 2替换为FETCH FIRST 2 ROWS ONLY，TO_CHAR改为TO_CHAR(record_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MON'（确保星期缩写为英文）

逻辑说明 #

1. recent_mondays CTE：从数据集里提取出最新的两个周一日期，避免硬编码日期导致的兼容性问题
2. filtered_data CTE：筛选出这两个周一的所有记录，并通过窗口函数获取对应的上周周一日期
3. 最终查询：对上周的记录直接标记为Old；对本周的记录，通过EXISTS子查询判断该组合是否在上周存在，进而标记Old或New

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