如何改写SQL查询获取当前及4周前的统计数据？

方案一：基于行号的条件聚合 #

适合数据为每周固定一条统计记录的场景，通过给排序后的记录标记行号，直接提取目标行数值：

SELECT
    MAX(CASE WHEN rn = 1 THEN TotalN END) AS TotalN,
    MAX(CASE WHEN rn = 5 THEN TotalN END) AS Trailing4WkTotalN,
    MAX(CASE WHEN rn = 1 THEN AvgMDays END) AS AvgMDays,
    MAX(CASE WHEN rn = 5 THEN AvgMDays END) AS Trailing4WkAvgMDays
FROM (
    -- 按日期倒序生成带行号的周统计数据
    SELECT
        QueryRun,
        COUNT(*) AS TotalN,
        AVG(MDays) AS AvgMDays,
        ROW_NUMBER() OVER (ORDER BY QueryRun DESC) AS rn
    FROM table_name
    WHERE YN = 1 AND HF = 0
    GROUP BY QueryRun
) t
-- 仅保留当前（行号1）和4周前（行号5）的记录
WHERE rn IN (1, 5);

说明：

• 子查询用ROW_NUMBER()按QueryRun降序标记行号，最新统计记录行号为1，4周前的记录对应行号为5（匹配你提供的示例数据间隔）。
• 外层通过CASE表达式将不同行号的统计值映射为目标列，MAX聚合用于过滤NULL值，确保只保留有效数值。

方案二：基于日期关联的自连接 #

如果数据存在周统计缺失的情况，这种方法更灵活，通过日期计算匹配4周前的最近一条记录：

以SQL Server为例（不同数据库日期函数需调整）：

WITH weekly_stats AS (
    -- 预生成所有周统计数据
    SELECT
        QueryRun,
        COUNT(*) AS TotalN,
        AVG(MDays) AS AvgMDays
    FROM table_name
    WHERE YN = 1 AND HF = 0
    GROUP BY QueryRun
)
SELECT
    curr.TotalN,
    trail.TotalN AS Trailing4WkTotalN,
    curr.AvgMDays,
    trail.AvgMDays AS Trailing4WkAvgMDays
FROM weekly_stats curr
-- 关联4周前及更早的最近一条统计记录
LEFT JOIN weekly_stats trail
    ON trail.QueryRun = (
        SELECT MAX(QueryRun)
        FROM weekly_stats
        WHERE QueryRun <= DATEADD(WEEK, -4, curr.QueryRun)
    )
-- 仅取最新的统计记录
WHERE curr.QueryRun = (SELECT MAX(QueryRun) FROM weekly_stats);

不同数据库日期函数适配：

• MySQL：将DATEADD(WEEK, -4, curr.QueryRun)替换为DATE_SUB(curr.QueryRun, INTERVAL 4 WEEK)
• PostgreSQL：使用curr.QueryRun - INTERVAL '4 weeks'

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