SQL Server中基于自定义日期范围的ID月度Rate平均值计算

核心思路 #

• 先确定每年的首个工作日作为统计起始点
• 为每个统计月份计算对应的截止日期（下月1日前5个工作日）
• 将每条数据匹配到对应的统计月份
• 按ID、统计年、统计月分组计算Rate平均值

完整SQL实现（以MySQL为例） #

WITH yearly_params AS (
    SELECT 
        YEAR(Date) AS stat_year,
        -- 计算当年首个工作日：若1月1日是周末则向后推至首个工作日
        CASE
            WHEN DAYOFWEEK(CONCAT(YEAR(Date), '-01-01')) IN (1,7) THEN -- 1=周日，7=周六
                DATE_ADD(CONCAT(YEAR(Date), '-01-01'), INTERVAL (8 - DAYOFWEEK(CONCAT(YEAR(Date), '-01-01'))) DAY)
            ELSE
                CONCAT(YEAR(Date), '-01-01')
        END AS first_workday
    FROM your_table
    GROUP BY YEAR(Date)
),
monthly_ranges AS (
    SELECT
        y.stat_year,
        m.month_num,
        y.first_workday,
        -- 计算当前统计月份的截止日期：下月1日往前推5个工作日
        DATE_SUB(
            DATE_ADD(CONCAT(y.stat_year, '-', m.month_num + 1, '-01'), INTERVAL 0 DAY),
            INTERVAL 5 WORKDAY
        ) AS period_end
    FROM yearly_params y
    -- 生成1-12月的月份列表
    CROSS JOIN (
        SELECT 1 AS month_num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
        UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ) m
    -- 过滤掉跨年的截止日期（仅保留当年内的统计周期）
    HAVING period_end <= CONCAT(y.stat_year, '-12-31')
),
data_mapping AS (
    SELECT
        t.ID,
        t.Rate,
        m.month_num AS stat_month,
        m.stat_year
    FROM your_table t
    JOIN monthly_ranges m ON 
        YEAR(t.Date) = m.stat_year
        AND t.Date >= m.first_workday
        AND t.Date <= m.period_end
)
SELECT
    ID,
    stat_year,
    stat_month,
    ROUND(AVG(Rate), 3) AS avg_rate -- 保留3位小数，与示例数据格式一致
FROM data_mapping
GROUP BY ID, stat_year, stat_month
ORDER BY ID, stat_year, stat_month;

关键细节说明 #

1. 数据库适配调整

   • SQL Server：替换DAYOFWEEK为DATEPART(WEEKDAY, ...)（注意SQL Server中周日是1，周一到周六是2-7），DATE_ADD/DATE_SUB替换为DATEADD，WORKDAY函数需确保数据库版本支持（或用自定义函数实现工作日计算）
   • Oracle：用TRUNC(CONCAT(stat_year, '-01-01'), 'IW')获取当年首个工作日，NEXT_DAY结合偏移计算截止日期

2. 统计周期逻辑验证

   • 比如2022年首个工作日是1月3日，6月的统计截止日期是7月1日前5个工作日（即6月25日），符合需求描述
   • 若某条数据的Date早于当年首个工作日，会被自动过滤（可根据需求调整是否包含）

3. 原查询问题

   • 原查询按自然月分组，未考虑自定义的起始/截止规则，导致统计范围与需求不符

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