SQL实现：获取不一致时间线中特定引用的最新已知值

• 从stats表获取记录，若特定日期与ref无数据，则使用该ref的最新已知值
• 生成从最早统计日期到当前日期的完整时间线
• 按日期汇总views（缺失数据用最新值填充）

现有表结构 #

stats表 #

| ref | date     | views |
| --- | -------- | ----- |
| 1   |2022-01-01|1      |
| 2   |2022-01-01|1      |
| 1   |2022-01-02|2      |
| 2   |2022-01-02|1      |
| 1   |2022-01-03|2      |
| 1   |2022-01-04|3      |
| 2   |2022-01-04|3      |

posts表 #

| id  | title     |
| --- | --------- |
| 1   |title no. 1|
| 2   |title no. 2|

原递归CTE SQL（存在逻辑问题） #

WITH RECURSIVE timeline (
    date
) AS (
    SELECT
        MIN(date)
    FROM
        stats
UNION ALL
SELECT
    DATE_ADD(date, INTERVAL 1 day)
FROM
    timeline
WHERE (timeline.date < CURRENT_DATE),

posts_days AS (
    SELECT timeline.date, posts.id
    FROM
        posts
    CROSS JOIN timeline
),

view_stats AS (
    SELECT posts_days.date, posts_days.id, stats.views
    FROM
        posts_days
    LEFT JOIN stats ON  (stats.ref = posts_days.id and stats.date = posts_days.date)
)

SELECT
    view_stats.date, SUM(view_stats.views) AS views
--  ,SUM(prev_stats.views) AS prev_views, 
FROM view_stats
LEFT JOIN (
        SELECT id, (view_stats.views) as views, date FROM view_stats GROUP BY id, date
    ) as prev_stats on prev_stats.date = (
      SELECT date FROM view_stats s1 
        WHERE s1.date < view_stats.date and s1.id = view_stats.id ORDER BY date desc limit 1
    ) and prev_stats.id = view_stats.id
GROUP BY date
ORDER BY date

修正后的SQL方案 #

原SQL的核心问题是缺失值填充逻辑混乱，没有正确为每个ref匹配对应日期的最新已知views值。以下提供两种可行的修正方案：

方案1：子查询匹配最新值（兼容多数MySQL版本） #

WITH RECURSIVE timeline AS (
    -- 生成从stats最早日期到当前的完整日期序列
    SELECT MIN(date) AS date FROM stats
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM timeline
    WHERE date < CURRENT_DATE
),
-- 生成所有帖子ID与时间线日期的组合
posts_timeline AS (
    SELECT p.id AS ref, t.date
    FROM posts p
    CROSS JOIN timeline t
),
-- 为每个ref+日期组合填充最新已知views值
filled_stats AS (
    SELECT 
        pt.ref,
        pt.date,
        -- 获取当前日期及之前该ref的最新views记录
        (SELECT s.views 
         FROM stats s 
         WHERE s.ref = pt.ref AND s.date <= pt.date 
         ORDER BY s.date DESC 
         LIMIT 1) AS views
    FROM posts_timeline pt
)
-- 按日期汇总总views
SELECT 
    date,
    SUM(views) AS total_views
FROM filled_stats
GROUP BY date
ORDER BY date;

方案2：窗口函数优化（MySQL 8.0+适用） #

如果使用MySQL 8.0及以上版本，推荐用窗口函数提升性能：

WITH RECURSIVE timeline AS (
    SELECT MIN(date) AS date FROM stats
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM timeline
    WHERE date < CURRENT_DATE
),
-- 合并帖子、时间线与原始stats数据
combined AS (
    SELECT 
        p.id AS ref,
        t.date,
        s.views
    FROM posts p
    CROSS JOIN timeline t
    LEFT JOIN stats s ON s.ref = p.id AND s.date = t.date
),
-- 用窗口函数向前填充缺失的views值
filled_stats AS (
    SELECT 
        ref,
        date,
        -- 按ref分组，取到当前行为止的最后一个非空views值
        LAST_VALUE(views IGNORE NULLS) OVER (
            PARTITION BY ref 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS views
    FROM combined
)
SELECT 
    date,
    SUM(views) AS total_views
FROM filled_stats
GROUP BY date
ORDER BY date;

修正说明 #

• 递归CTEtimeline保持不变，负责生成完整日期序列
• posts_timeline确保每个帖子在时间线的每一天都有对应的记录
• 两种方案都实现了"缺失日期用最新已知值填充"的核心需求，窗口函数版本在大数据量下性能更优

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