补全缺失日期并分配会计期间的SQL查询方案咨询

问题背景 #

公司会计软件的会计期间表仅记录每个期间的End Date，需要编写查询补全所有缺失日期，并为新生成的日期准确分配对应的Fiscal Year和Fiscal Period。

现有表（假设表名为fiscal_periods）的最后三条数据：

期望返回结果：

实现方案 #

核心思路：先生成所有需要的日期范围，再通过区间匹配为每个日期分配对应的财年和财期。具体步骤：

• 生成从最早期间结束日期到最晚期间结束日期之间的所有连续日期；
• 将生成的日期与原表关联，找到该日期所属的财期（即大于上一个期间的结束日期，小于等于当前期间的结束日期）。

1. SQL Server 实现 #

WITH date_range AS (
    -- 生成连续日期序列
    SELECT DATEADD(day, seq.num, MIN(fp.[End Date])) AS calendar_date
    FROM fiscal_periods fp
    CROSS JOIN (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS num
        FROM sys.objects o1
        CROSS JOIN sys.objects o2
    ) seq
    WHERE DATEADD(day, seq.num, MIN(fp.[End Date])) <= MAX(fp.[End Date])
),
periods_with_start AS (
    -- 计算每个财期的开始日期（上一个财期结束日期+1）
    SELECT 
        [End Date],
        [Fiscal Year],
        [Fiscal Period],
        DATEADD(day, 1, LAG([End Date]) OVER (ORDER BY [End Date])) AS period_start
    FROM fiscal_periods
)
SELECT 
    dr.calendar_date AS [End Date],
    p.[Fiscal Year],
    p.[Fiscal Period]
FROM date_range dr
LEFT JOIN periods_with_start p 
    ON dr.calendar_date > p.period_start 
    AND dr.calendar_date <= p.[End Date]
-- 处理第一个期间的开始日期（无前置期间时，从最早日期开始）
UNION ALL
SELECT 
    fp.[End Date],
    fp.[Fiscal Year],
    fp.[Fiscal Period]
FROM fiscal_periods fp
WHERE fp.[End Date] = (SELECT MIN([End Date]) FROM fiscal_periods)
ORDER BY [End Date];

2. MySQL 实现 #

WITH RECURSIVE date_range AS (
    -- 递归生成连续日期
    SELECT MIN(`End Date`) AS calendar_date
    FROM fiscal_periods
    UNION ALL
    SELECT DATE_ADD(calendar_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE calendar_date < (SELECT MAX(`End Date`) FROM fiscal_periods)
),
periods_with_start AS (
    -- 计算每个财期的开始日期
    SELECT 
        `End Date`,
        `Fiscal Year`,
        `Fiscal Period`,
        DATE_ADD(LAG(`End Date`) OVER (ORDER BY `End Date`), INTERVAL 1 DAY) AS period_start
    FROM fiscal_periods
)
SELECT 
    dr.calendar_date AS `End Date`,
    p.`Fiscal Year`,
    p.`Fiscal Period`
FROM date_range dr
LEFT JOIN periods_with_start p 
    ON dr.calendar_date > p.period_start 
    AND dr.calendar_date <= p.`End Date`
-- 补全第一个期间的记录
UNION ALL
SELECT 
    fp.`End Date`,
    fp.`Fiscal Year`,
    fp.`Fiscal Period`
FROM fiscal_periods fp
WHERE fp.`End Date` = (SELECT MIN(`End Date`) FROM fiscal_periods)
ORDER BY `End Date`;

3. PostgreSQL 实现 #

WITH date_range AS (
    -- 生成连续日期序列
    SELECT generate_series(
        MIN("End Date"), 
        MAX("End Date"), 
        '1 day'::interval
    )::date AS calendar_date
    FROM fiscal_periods
),
periods_with_start AS (
    -- 计算每个财期的开始日期
    SELECT 
        "End Date",
        "Fiscal Year",
        "Fiscal Period",
        LAG("End Date") OVER (ORDER BY "End Date") + INTERVAL '1 day' AS period_start
    FROM fiscal_periods
)
SELECT 
    dr.calendar_date AS "End Date",
    p."Fiscal Year",
    p."Fiscal Period"
FROM date_range dr
LEFT JOIN periods_with_start p 
    ON dr.calendar_date > p.period_start::date 
    AND dr.calendar_date <= p."End Date"
ORDER BY "End Date";

关键说明 #

• 生成日期序列时，需确保覆盖原表中最早到最晚的End Date范围；
• 通过LAG()窗口函数获取上一个期间的结束日期，加1天得到当前期间的开始日期；
• 关联时使用dr.calendar_date > p.period_start AND dr.calendar_date <= p.[End Date]来匹配日期所属的财期；
• 部分数据库（如SQL Server）可能需要额外处理第一个期间的匹配，因为第一个期间没有前置period_start，直接保留原表的第一条记录即可。

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