如何利用SQL逻辑创建基于时间区间的每日数据视图？

要实现从最早记录日期到今日的每日数据填充，核心思路是先生成连续日期序列，再为每个日期匹配对应的生效价格区间。以下是不同主流数据库的实现方案：

通用逻辑说明 #

1. 生成连续日期范围：从原表的最早记录日期到当前日期，生成每一天的日期序列。
2. 确定价格生效区间：为每条价格记录标记生效起始日期和结束日期（结束日期为下一条价格记录的前一天，最后一条价格的结束日期为今日）。
3. 关联匹配：将日期序列与价格生效区间关联，得到每日对应的价格。

PostgreSQL 实现 #

假设原表名为 price_history，字段为 id, price, date：

WITH date_range AS (
    -- 生成从最早日期到今日的连续日期序列
    SELECT generate_series(
        (SELECT MIN(date) FROM price_history),
        CURRENT_DATE,
        '1 day'::interval
    ) AS day
),
price_periods AS (
    -- 计算每条价格的生效区间
    SELECT
        id,
        price,
        date AS start_date,
        -- 下一条价格的前一天为当前价格的结束日期，无下一条则到今日
        COALESCE(LEAD(date) OVER (PARTITION BY id ORDER BY date) - INTERVAL '1 day', CURRENT_DATE) AS end_date
    FROM price_history
)
-- 关联日期序列和价格区间，得到每日数据
SELECT
    pp.id,
    dr.day AS date,
    pp.price
FROM date_range dr
JOIN price_periods pp ON dr.day BETWEEN pp.start_date AND pp.end_date
ORDER BY pp.id, dr.day;

MySQL 8.0+ 实现 #

WITH RECURSIVE date_range AS (
    -- 递归生成连续日期序列
    SELECT MIN(date) AS day FROM price_history
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM date_range WHERE day < CURRENT_DATE
),
price_periods AS (
    SELECT
        id,
        price,
        date AS start_date,
        COALESCE(DATE_SUB(LEAD(date) OVER (PARTITION BY id ORDER BY date), INTERVAL 1 DAY), CURRENT_DATE) AS end_date
    FROM price_history
)
SELECT
    pp.id,
    dr.day AS date,
    pp.price
FROM date_range dr
JOIN price_periods pp ON dr.day BETWEEN pp.start_date AND pp.end_date
ORDER BY pp.id, dr.day;

SQL Server 实现 #

WITH date_range AS (
    -- 递归生成连续日期序列
    SELECT MIN(date) AS day FROM price_history
    UNION ALL
    SELECT DATEADD(DAY, 1, day) FROM date_range WHERE day < CAST(GETDATE() AS DATE)
),
price_periods AS (
    SELECT
        id,
        price,
        date AS start_date,
        COALESCE(DATEADD(DAY, -1, LEAD(date) OVER (PARTITION BY id ORDER BY date)), CAST(GETDATE() AS DATE)) AS end_date
    FROM price_history
)
SELECT
    pp.id,
    dr.day AS date,
    pp.price
FROM date_range dr
JOIN price_periods pp ON dr.day BETWEEN pp.start_date AND pp.end_date
ORDER BY pp.id, dr.day;

为什么 LAG() 无法实现预期效果 #

LAG() 函数只能获取当前记录的上一条数据，无法直接构建价格的生效区间。我们需要的是将每条价格的覆盖范围明确标记出来，再和连续日期做关联，LEAD() 函数正好能帮我们获取下一条价格的起始日期，从而确定当前价格的结束范围。

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