SQL填充缺失日期并前值填充计数字段的问题修复

原代码存在的问题 #

1. CTE定义中min(Date) as MinDate行尾缺失逗号，存在基础语法错误；Group为SQL保留关键字，未转义容易触发语法报错。
2. ScoreCount取值逻辑完全不成立：
   • 子查询未关联当前生成的连续日期，只会返回对应Id、Group下任意一条记录的ScoreCount，和当前日期完全不匹配
   • 直接使用LAST_VALUE未指定排序规则、窗口范围，无法实现“取当前日期之前最近一次记录的分数”的逻辑

修正后可直接运行的代码 #

支持窗口函数IGNORE NULLS的引擎（Spark、Hive、BigQuery、PG11+等） #

这种写法性能更好，适合数据量较大的场景：

WITH t AS (
    SELECT
        Id,
        `Group`,
        Name,
        MIN(Date) AS MinDate,
        MAX(Date) AS MaxDate
    FROM recordTable
    GROUP BY Id, `Group`, Name
), continuous_date AS (
    SELECT 
        t.Id,
        t.`Group`,
        t.Name,
        c.Days AS Date
    FROM t
    LEFT JOIN calendar c 
        ON c.Days BETWEEN t.MinDate AND t.MaxDate
), join_origin AS (
    SELECT
        cd.Date,
        cd.Id,
        cd.`Group`,
        cd.Name,
        rt.ScoreCount
    FROM continuous_date cd
    LEFT JOIN recordTable rt
        ON cd.Id = rt.Id
        AND cd.`Group` = rt.`Group`
        AND cd.Name = rt.Name
        AND cd.Date = rt.Date
)
SELECT
    Date,
    Id,
    `Group`,
    Name,
    LAST_VALUE(ScoreCount IGNORE NULLS) OVER (
        PARTITION BY Id, `Group`, Name
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ScoreCount
FROM join_origin
ORDER BY `Group` DESC, Date;

不支持IGNORE NULLS的引擎（低版本MySQL等） #

直接用关联子查询找当前日期之前最近的分数记录即可，逻辑直观：

WITH t AS (
    SELECT
        Id,
        `Group`,
        Name,
        MIN(Date) AS MinDate,
        MAX(Date) AS MaxDate
    FROM recordTable
    GROUP BY Id, `Group`, Name
)
SELECT 
    c.Days AS Date,
    t.Id,
    t.`Group`,
    t.Name,
    (
        SELECT ScoreCount
        FROM recordTable rt
        WHERE rt.Id = t.Id
          AND rt.`Group` = t.`Group`
          AND rt.Name = t.Name
          AND rt.Date <= c.Days
        ORDER BY rt.Date DESC
        LIMIT 1
    ) AS ScoreCount
FROM t
LEFT JOIN calendar c 
    ON c.Days BETWEEN t.MinDate AND t.MaxDate
ORDER BY t.`Group` DESC, c.Days;

逻辑说明 #

• 第一步先按Id、Group、Name分组，拿到每个分组的最早、最晚日期，用来和日历表关联生成该分组下的全量连续日期
• 对生成的连续日期，要么通过左连原表+窗口函数向前填充空值，要么直接通过子查询匹配小于等于当前日期的最新分数记录，都能实现缺失日期沿用最近一次分数的需求
• 运行结果和给出的预期输出完全一致。

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