如何用SQL生成每日重复的价格趋势数据（直至价格变动）

原始数据 #

+------+-------------+
| price | date       |
+-------+------------+
|    7  | 2023-01-04 |
|    9  | 2023-02-14 |
|    1  | 2023-04-12 |
|    3  | 2023-07-23 |
+-------+------------+

需求 #

从每个价格对应的起始日期开始，每日重复该价格值，直至下一次价格变动的前一天；最后一条价格数据需持续生成到当前日期，示例输出如下：

+------+-------------+
| price | date       |
+-------+------------+
|    7  | 2023-01-04 |
|    7  | 2023-01-05 |
|    7  | 2023-01-06 |
[...]
|    7  | 2023-02-13 |
|    9  | 2023-02-14 |
|    9  | 2023-02-15 |
[...]
|    3  | 2023-08-25 |
+-------+------------+

背景 #

使用Metabase制作商品价格趋势图，需要连续的日度时间序列数据；此前已通过递归CTE实现按月重复数据的功能，但无法满足当前日度需求。

解决方案 #

利用窗口函数LEAD()获取每个价格的下一次变动日期，再结合递归CTE生成每日连续数据：

完整SQL代码 #

WITH price_ranges AS (
    -- 为每个价格行计算有效结束日期：下一次价格变动的前一天，最后一行用当前日期
    SELECT
        price,
        date AS start_date,
        COALESCE(LEAD(date) OVER (ORDER BY date) - INTERVAL 1 DAY, CURRENT_DATE) AS end_date
    FROM
        your_table_name -- 替换为你的实际表名
),
recursive_dates AS (
    -- 递归起始：每个价格的起始日期
    SELECT
        price,
        start_date AS date
    FROM
        price_ranges
    UNION ALL
    -- 递归生成后续日期，直到达到该价格的结束日期
    SELECT
        rd.price,
        rd.date + INTERVAL 1 DAY
    FROM
        recursive_dates rd
    JOIN
        price_ranges pr ON rd.price = pr.price AND rd.date >= pr.start_date AND rd.date < pr.end_date
)
-- 最终结果按日期排序
SELECT
    price,
    date
FROM
    recursive_dates
ORDER BY
    date;

代码说明 #

• price_ranges CTE：
  • 用LEAD(date) OVER (ORDER BY date)获取当前价格的下一次变动日期，减去1天得到当前价格的最后有效日期
  • 用COALESCE处理最后一行数据，将其结束日期设为CURRENT_DATE，确保数据持续到当前日期
• recursive_dates CTE：
  • 初始查询获取所有价格的起始日期
  • 递归部分每天累加1天，直到日期小于对应价格的结束日期
• 最终查询按日期排序，得到连续的日度价格序列

内容的提问来源于stack exchange，提问作者William Brochensque junior