如何通过SQL查询生成日期区间内数据填充的连续行？

原数据表数据 #

id     type     date      price
1       A    2022-11-09    100
2       A    2022-11-12    200

需求说明 #

需要获取2022年11月9日至15日期间，type为A的每日价格，缺失日期的价格沿用最近一次的已知价格，期望结果如下：

type     date      price
   A    2022-11-09    100
   A    2022-11-10    100
   A    2022-11-11    100
   A    2022-11-12    200
   A    2022-11-13    200
   A    2022-11-14    200
   A    2022-11-15    200

实现方案 #

完全可以通过SQL查询实现，核心思路是：先生成目标日期区间内的所有连续日期，再与原数据表关联，通过窗口函数填充缺失的价格值。以下是主流数据库的具体实现代码：

1. PostgreSQL 实现

利用generate_series生成日期序列，结合LAST_VALUE窗口函数填充缺失价格：

WITH date_range AS (
    SELECT generate_series('2022-11-09'::DATE, '2022-11-15'::DATE, '1 day'::INTERVAL) AS date
),
type_dates AS (
    SELECT 'A' AS type, dr.date::DATE
    FROM date_range dr
),
price_data AS (
    SELECT td.type, td.date, t.price
    FROM type_dates td
    LEFT JOIN your_table t ON td.type = t.type AND td.date = t.date
)
SELECT 
    type,
    date,
    LAST_VALUE(price IGNORE NULLS) OVER (PARTITION BY type ORDER BY date) AS price
FROM price_data
ORDER BY date;

2. MySQL 8.0+ 实现

用递归CTE生成日期序列，再通过窗口函数填充：

WITH RECURSIVE date_range AS (
    SELECT '2022-11-09' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2022-11-15'
),
type_dates AS (
    SELECT 'A' AS type, date
    FROM date_range
),
price_data AS (
    SELECT td.type, td.date, t.price
    FROM type_dates td
    LEFT JOIN your_table t ON td.type = t.type AND td.date = t.date
)
SELECT 
    type,
    date,
    LAST_VALUE(price) OVER (
        PARTITION BY type 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS price
FROM price_data
ORDER BY date;

注：MySQL的LAST_VALUE默认不忽略NULL，这里通过窗口范围控制确保取到最近的非空值

3. SQL Server 实现

同样用递归CTE生成日期，结合LAST_VALUE：

WITH date_range AS (
    SELECT CAST('2022-11-09' AS DATE) AS date
    UNION ALL
    SELECT DATEADD(DAY, 1, date)
    FROM date_range
    WHERE date < '2022-11-15'
),
type_dates AS (
    SELECT 'A' AS type, date
    FROM date_range
),
price_data AS (
    SELECT td.type, td.date, t.price
    FROM type_dates td
    LEFT JOIN your_table t ON td.type = t.type AND td.date = t.date
)
SELECT 
    type,
    date,
    LAST_VALUE(price IGNORE NULLS) OVER (PARTITION BY type ORDER BY date) AS price
FROM price_data
ORDER BY date
OPTION (MAXRECURSION 0);

说明 #

• 替换代码中的your_table为实际数据表名
• 如果需要支持多类型，只需调整type_dates部分，加入所有需要的type值即可

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