按日期分组生成时间序列：预算表与年度序列表数据处理需求

先明确你的两张表结构，方便后续理解：

1. daily_budgets表结构 #

这张表存储了不同时间段的每日预算，数据如下：

注意：这里日期是DD/MM/YY格式，后续SQL里需要转换成数据库能识别的日期类型，避免匹配错误。

2. year_2018表 #

假设这张表的核心字段是date（每日日期）和你需要汇总的数值字段（比如actual_spend，如果字段名不同，直接替换成自己的即可）。

核心需求拆解 #

你需要完成两个核心动作：

• 对year_2018按日期分组，汇总指定数值
• 匹配该日期对应的每日预算（同一日期可能存在多个生效预算，这里默认按当日所有生效预算的总和处理，你可以根据需求调整逻辑）

方案一：支持GENERATE_SERIES的数据库（PostgreSQL等） #

用GENERATE_SERIES把预算表的时间段拆成单日记录，再和时间序列表关联汇总：

-- 先把预算表的时间段拆成每日，计算每日总预算
WITH daily_budget_details AS (
    SELECT 
        DATE(d.date) AS budget_day,
        SUM(db.daily_budget) AS total_daily_budget
    FROM daily_budgets db
    -- 生成start_date到end_date之间的每一天
    GENERATE_SERIES(
        TO_DATE(db.start_date, 'DD/MM/YY'),
        TO_DATE(db.end_date, 'DD/MM/YY'),
        INTERVAL '1 day'
    ) AS d(date)
    GROUP BY budget_day
)
-- 关联时间序列表，按日期汇总
SELECT 
    y.date,
    -- 替换成你需要汇总的字段和聚合函数，比如SUM/COUNT/AVG
    SUM(y.actual_spend) AS total_actual,
    -- 没有预算的日期显示0，也可以改成NULL
    COALESCE(dbd.total_daily_budget, 0) AS total_budget
FROM year_2018 y
LEFT JOIN daily_budget_details dbd ON y.date = dbd.budget_day
GROUP BY y.date, dbd.total_daily_budget
ORDER BY y.date;

方案二：不支持GENERATE_SERIES的数据库（MySQL等） #

用递归CTE生成日期范围，再关联预算表：

WITH RECURSIVE date_list AS (
    -- 取预算表的最早开始日期
    SELECT MIN(STR_TO_DATE(start_date, '%d/%m/%y')) AS date
    FROM daily_budgets
    UNION ALL
    -- 递归生成后续日期
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_list
    WHERE date < (SELECT MAX(STR_TO_DATE(end_date, '%d/%m/%y')) FROM daily_budgets)
),
daily_budget_details AS (
    SELECT 
        dl.date AS budget_day,
        SUM(db.daily_budget) AS total_daily_budget
    FROM date_list dl
    JOIN daily_budgets db 
        ON dl.date BETWEEN STR_TO_DATE(db.start_date, '%d/%m/%y') AND STR_TO_DATE(db.end_date, '%d/%m/%y')
    GROUP BY dl.date
)
-- 关联时间序列表汇总
SELECT 
    y.date,
    SUM(y.actual_spend) AS total_actual,
    COALESCE(dbd.total_daily_budget, 0) AS total_budget
FROM year_2018 y
LEFT JOIN daily_budget_details dbd ON y.date = dbd.budget_day
GROUP BY y.date, dbd.total_daily_budget
ORDER BY y.date;

自定义调整点 #

1. 聚合函数：如果你的需求不是求和，而是计数、平均值，把SUM(y.actual_spend)改成COUNT(y.id)或者AVG(y.value)即可。
2. 预算逻辑：如果同一日期只需要取最高预算，把SUM(db.daily_budget)改成MAX(db.daily_budget)。
3. 日期格式：如果你的数据库日期格式不同，调整TO_DATE或STR_TO_DATE里的格式符。

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