如何将变更日志转换为每周快照记录？

你当前的关联逻辑会匹配所有早于等于周日期的变更记录，导致同一周日期对应多条历史值。要生成正确的每周快照，核心是为每个周日期获取最近的、不晚于该周日期的变更记录，以下是几种通用解决方案：

方法1：使用窗口函数（通用SQL） #

假设变更历史表为change_history，周日期表为weekly_dates，字段分别为value、change_date和weekly_date：

WITH ranked_changes AS (
    SELECT
        w.weekly_date,
        c.value,
        ROW_NUMBER() OVER (
            PARTITION BY w.weekly_date
            ORDER BY c.change_date DESC
        ) AS rn
    FROM weekly_dates w
    LEFT JOIN change_history c
        ON c.change_date <= w.weekly_date
)
SELECT weekly_date, value
FROM ranked_changes
WHERE rn = 1
ORDER BY weekly_date;

逻辑说明：

• 按周日期分组（PARTITION BY w.weekly_date）
• 每组内的变更记录按日期倒序排列并标记序号
• 筛选序号为1的记录，即每个周日期对应的最新变更值

方法2：使用关联子查询 #

适合不支持窗口函数的数据库：

SELECT
    w.weekly_date,
    (SELECT c.value
     FROM change_history c
     WHERE c.change_date <= w.weekly_date
     ORDER BY c.change_date DESC
     LIMIT 1) AS value
FROM weekly_dates w
ORDER BY w.weekly_date;

逻辑说明：对每个周日期，子查询直接找出不晚于它的最新变更值，通过LIMIT 1取倒序后的第一条记录。

方法3：使用LATERAL JOIN/OUTER APPLY #

PostgreSQL 版本 #

SELECT
    w.weekly_date,
    c.value
FROM weekly_dates w
LEFT JOIN LATERAL (
    SELECT value
    FROM change_history
    WHERE change_date <= w.weekly_date
    ORDER BY change_date DESC
    LIMIT 1
) c ON true
ORDER BY w.weekly_date;

SQL Server 版本 #

SELECT
    w.weekly_date,
    c.value
FROM weekly_dates w
OUTER APPLY (
    SELECT TOP 1 value
    FROM change_history
    WHERE change_date <= w.weekly_date
    ORDER BY change_date DESC
) c
ORDER BY w.weekly_date;

逻辑说明：为每个周日期单独执行子查询获取最新变更值，逻辑直观，大数据量下性能表现更优。

结果说明 #

执行以上任意方案后，会得到符合预期的快照结果：

• May 8 - 2000
• May 15 - 10000

若存在无变更记录的周日期（如May 1），结果中value会返回NULL，可通过COALESCE(c.value, 你的默认值)设置默认值。

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