如何在查询中使用财日历？按财日历统计周销售额的技术问询

要实现按自定义财日历（起始于12月1日，每月月末为周五）统计每周总销售额，核心思路是先从宽表结构的财日历表中提取并生成财周的起止日期序列，再关联销售表进行分组求和。以下分步骤说明：

1. 明确财日历规则 #

根据题目描述，财日历的核心规则：

• 财年起始于12月1日，每个财月的最后一天是周五
• 财周以周五为起始日，下周四为结束日（对应示例中W1覆盖12月1日-7日，W2覆盖8日-14日，以此类推）

2. 转换财日历宽表为窄表 #

原Fiscal_calendar是宽表结构（每个Period对应多组PeriodX_StartDate/PeriodX_EndDate），我们需要先将其转换为窄表，每行对应一个财月的起止日期。

3. 生成财周序列 #

基于财月的起止日期，用递归CTE生成该财月内的所有财周（每周7天，从周五到下周四）。

4. 关联销售表统计销售额 #

将销售表的日期与财周关联，按财周分组计算总销售额。

针对SQL Server的实现代码 #

WITH FiscalMonths AS (
    -- 转换宽表为财月列表，若有更多PeriodX列，继续添加UNION ALL
    SELECT 
        Period,
        CAST(Period1_StartDate AS DATE) AS Month_Start,
        CAST(Period1_EndDate AS DATE) AS Month_End
    FROM Fiscal_calendar
    UNION ALL
    SELECT 
        Period,
        CAST(Period2_StartDate AS DATE) AS Month_Start,
        CAST(Period2_EndDate AS DATE) AS Month_End
    FROM Fiscal_calendar
),
FiscalWeeks AS (
    -- 递归生成财周序列
    SELECT 
        Period,
        1 AS Week_Num,
        Month_Start AS Week_Start,
        DATEADD(DAY, 6, Month_Start) AS Week_End
    FROM FiscalMonths
    WHERE DATEADD(DAY, 6, Month_Start) <= Month_End
    UNION ALL
    SELECT 
        fw.Period,
        fw.Week_Num + 1 AS Week_Num,
        DATEADD(DAY, 7, fw.Week_Start) AS Week_Start,
        DATEADD(DAY, 7, fw.Week_End) AS Week_End
    FROM FiscalWeeks fw
    JOIN FiscalMonths fm ON fw.Period = fm.Period AND fw.Week_End < fm.Month_End
)
-- 关联销售表统计每周销售额
SELECT 
    'W' + CAST(fw.Week_Num AS VARCHAR(2)) AS WEEK,
    ISNULL(SUM(s.Amount), 0) AS Total_Sales
FROM FiscalWeeks fw
LEFT JOIN Sales s ON CAST(s.Date AS DATE) BETWEEN fw.Week_Start AND fw.Week_End
GROUP BY fw.Period, fw.Week_Num
ORDER BY fw.Period, fw.Week_Num;

针对MySQL的实现代码 #

（注意MySQL 8.0及以上支持递归CTE，且需处理日期格式转换）

WITH FiscalMonths AS (
    -- 转换宽表为财月列表，若有更多PeriodX列，继续添加UNION ALL
    SELECT 
        Period,
        STR_TO_DATE(Period1_StartDate, '%d/%m/%Y') AS Month_Start,
        STR_TO_DATE(Period1_EndDate, '%d/%m/%Y') AS Month_End
    FROM Fiscal_calendar
    UNION ALL
    SELECT 
        Period,
        STR_TO_DATE(Period2_StartDate, '%d/%m/%Y') AS Month_Start,
        STR_TO_DATE(Period2_EndDate, '%d/%m/%Y') AS Month_End
    FROM Fiscal_calendar
),
FiscalWeeks AS (
    -- 递归生成财周序列
    SELECT 
        Period,
        1 AS Week_Num,
        Month_Start AS Week_Start,
        DATE_ADD(Month_Start, INTERVAL 6 DAY) AS Week_End
    FROM FiscalMonths
    WHERE DATE_ADD(Month_Start, INTERVAL 6 DAY) <= Month_End
    UNION ALL
    SELECT 
        fw.Period,
        fw.Week_Num + 1 AS Week_Num,
        DATE_ADD(fw.Week_Start, INTERVAL 7 DAY) AS Week_Start,
        DATE_ADD(fw.Week_End, INTERVAL 7 DAY) AS Week_End
    FROM FiscalWeeks fw
    JOIN FiscalMonths fm ON fw.Period = fm.Period AND fw.Week_End < fm.Month_End
)
-- 关联销售表统计每周销售额
SELECT 
    CONCAT('W', fw.Week_Num) AS WEEK,
    COALESCE(SUM(s.Amount), 0) AS Total_Sales
FROM FiscalWeeks fw
LEFT JOIN Sales s ON STR_TO_DATE(s.Date, '%d/%m/%Y') BETWEEN fw.Week_Start AND fw.Week_End
GROUP BY fw.Period, fw.Week_Num
ORDER BY fw.Period, fw.Week_Num;

优化：自动处理多PeriodX列 #

如果Fiscal_calendar中有大量PeriodX_StartDate/PeriodX_EndDate列，手动写UNION ALL太繁琐，可使用动态SQL自动生成财月列表（以SQL Server为例）：

DECLARE @sql NVARCHAR(MAX) = '';

-- 自动生成所有PeriodX的转换语句
SELECT @sql = @sql + 'SELECT Period, CAST(' + QUOTENAME(column_name) + ' AS DATE) AS Month_Start, CAST(' + QUOTENAME(REPLACE(column_name, '_StartDate', '_EndDate')) + ' AS DATE) AS Month_End FROM Fiscal_calendar UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Fiscal_calendar' AND COLUMN_NAME LIKE 'Period%_StartDate';

-- 移除最后多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - LEN(' UNION ALL '));

WITH FiscalMonths AS (
    EXEC sp_executesql @sql
),
FiscalWeeks AS (
    SELECT 
        Period,
        1 AS Week_Num,
        Month_Start AS Week_Start,
        DATEADD(DAY, 6, Month_Start) AS Week_End
    FROM FiscalMonths
    WHERE DATEADD(DAY, 6, Month_Start) <= Month_End
    UNION ALL
    SELECT 
        fw.Period,
        fw.Week_Num + 1 AS Week_Num,
        DATEADD(DAY, 7, fw.Week_Start) AS Week_Start,
        DATEADD(DAY, 7, fw.Week_End) AS Week_End
    FROM FiscalWeeks fw
    JOIN FiscalMonths fm ON fw.Period = fm.Period AND fw.Week_End < fm.Month_End
)
SELECT 
    'W' + CAST(fw.Week_Num AS VARCHAR(2)) AS WEEK,
    ISNULL(SUM(s.Amount), 0) AS Total_Sales
FROM FiscalWeeks fw
LEFT JOIN Sales s ON CAST(s.Date AS DATE) BETWEEN fw.Week_Start AND fw.Week_End
GROUP BY fw.Period, fw.WEEK
ORDER BY fw.Period, fw.Week_Num;

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