如何在SQL中为缺失值分配row_number()序号

原SQL的问题在于只筛选出了有数据的日期，缺失的日期不会出现在结果集中，自然无法获得row_number序号。要实现每个目标日期（哪怕无购买记录）都分配序号，核心思路是先构建完整的「ID-目标日期」组合，再关联原数据的聚合结果，最后生成row_number。

具体实现代码 #

WITH target_dates AS (
    -- 列出所有需要统计的目标日期
    SELECT current_date AS dt UNION ALL
    SELECT current_date - 1 UNION ALL
    SELECT current_date - 2 UNION ALL
    SELECT current_date - 364 UNION ALL
    SELECT current_date - 365
),
all_id_date_pairs AS (
    -- 生成每个ID对应所有目标日期的完整组合
    SELECT DISTINCT t.id, d.dt
    FROM your_table t
    CROSS JOIN target_dates d
),
purchase_summary AS (
    -- 原逻辑的购买金额聚合
    SELECT 
        id, 
        date, 
        SUM(purchase) AS purch
    FROM your_table
    WHERE 
        date IN (current_date(), current_date()-1, current_date()-2)
        OR date BETWEEN current_date()-364 AND current_date()-365
    GROUP BY id, date
)
SELECT 
    a.id,
    a.dt AS date,
    COALESCE(s.purch, NULL) AS purchase,
    -- 按ID分区、日期倒序分配序号
    ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY a.dt DESC) AS row_number
FROM all_id_date_pairs a
-- 左连接聚合结果，无数据则显示NULL
LEFT JOIN purchase_summary s 
    ON a.id = s.id AND a.dt = s.date
ORDER BY a.id, a.dt DESC;

关键步骤说明 #

• target_dates：手动枚举所有需要统计的日期，确保每个目标日期都被包含。
• all_id_date_pairs：通过CROSS JOIN将所有唯一ID和目标日期配对，生成完整的行集合，这一步是让缺失日期能出现在结果里的核心。
• purchase_summary：保留原SQL的聚合逻辑，计算有数据日期的购买金额总和。
• 最后左连接后，用ROW_NUMBER()按ID分区、日期倒序生成序号，此时缺失数据的日期也会被分配对应的序号，购买金额显示为NULL。

数据库兼容提示 #

如果使用的是MySQL 5.x及以下版本（不支持WITH子句），可以把CTE改成子查询形式，核心逻辑保持不变；不同数据库的日期函数可能略有差异（比如SQL Server用DATEADD(day, -1, GETDATE())代替current_date()-1），按需调整即可。

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