按指定月度日期范围计算Amount列平均值的SQL问题

核心逻辑拆解 #

需要按月度生成独立统计区间：第一个月从固定起始日2022-01-03到当月指定截止日，后续月份从当月1号到当月指定截止日，再分别计算每个区间内的Amount平均值。

实现代码（SQL Server） #

方式1：内联截止日逻辑（无需创建函数）

WITH MonthlyPeriods AS (
    -- 生成2022-01至今的所有统计月份，可自行调整结束范围
    SELECT 
        DATEFROMPARTS(YEAR(StartOfMonth), MONTH(StartOfMonth), 1) AS MonthStart,
        -- 按给定逻辑计算当月截止日
        DATEADD(DAY, 
            CASE DATENAME(WEEKDAY, EOMONTH(StartOfMonth))
                WHEN 'Sunday' THEN -6
                WHEN 'Saturday' THEN -5
                ELSE -7
            END, 
            DATEDIFF(DAY, 0, EOMONTH(StartOfMonth))) AS MonthlyCutoff
    FROM (
        SELECT DATEADD(MONTH, n, '2022-01-01') AS StartOfMonth
        FROM (
            SELECT TOP (DATEDIFF(MONTH, '2022-01-01', GETDATE()) + 1) 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
            FROM sys.all_columns
        ) AS Numbers
    ) AS Months
)
SELECT
    FORMAT(mp.MonthStart, 'yyyy-MM') AS 统计月份,
    -- 无数据时返回0，可根据需求改为NULL
    ISNULL(AVG(t.Amount), 0) AS 平均金额
FROM MonthlyPeriods mp
LEFT JOIN YourTable t 
    -- 第一个月单独使用固定起始日，其他月份用当月1号
    ON CAST(t.Date AS DATE) >= CASE WHEN mp.MonthStart = '2022-01-01' THEN '2022-01-03' ELSE mp.MonthStart END
    AND CAST(t.Date AS DATE) <= mp.MonthlyCutoff
GROUP BY mp.MonthStart
ORDER BY mp.MonthStart;

方式2：封装截止日为函数（复用性更高）

先创建函数：

CREATE FUNCTION dbo.fn_GetMonthlyCutoff (@MonthEnd DATE)
RETURNS DATE
AS
BEGIN
    RETURN DATEADD(DAY, 
        CASE DATENAME(WEEKDAY, @MonthEnd)
            WHEN 'Sunday' THEN -6
            WHEN 'Saturday' THEN -5
            ELSE -7
        END, 
        DATEDIFF(DAY, 0, @MonthEnd))
END

再执行统计：

WITH MonthlyPeriods AS (
    SELECT 
        DATEFROMPARTS(YEAR(StartOfMonth), MONTH(StartOfMonth), 1) AS MonthStart,
        dbo.fn_GetMonthlyCutoff(EOMONTH(StartOfMonth)) AS MonthlyCutoff
    FROM (
        SELECT DATEADD(MONTH, n, '2022-01-01') AS StartOfMonth
        FROM (
            SELECT TOP (DATEDIFF(MONTH, '2022-01-01', GETDATE()) + 1) 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
            FROM sys.all_columns
        ) AS Numbers
    ) AS Months
)
SELECT
    FORMAT(mp.MonthStart, 'yyyy-MM') AS 统计月份,
    ISNULL(AVG(t.Amount), 0) AS 平均金额
FROM MonthlyPeriods mp
LEFT JOIN YourTable t 
    ON CAST(t.Date AS DATE) >= CASE WHEN mp.MonthStart = '2022-01-01' THEN '2022-01-03' ELSE mp.MonthStart END
    AND CAST(t.Date AS DATE) <= mp.MonthlyCutoff
GROUP BY mp.MonthStart
ORDER BY mp.MonthStart;

原问题分析 #

1. 缺少分组逻辑：原语句未按月度分组，只能返回整体平均值，无法区分各月数据；
2. 日期范围错误：未针对第一个月单独设置起始日，且未动态生成每个月的截止日；
3. 时间部分隐患：若Date字段包含时间，直接用BETWEEN可能遗漏截止日当天的部分数据，需转换为DATE类型后再比较。

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