SQL Server按周日结束的月内周分组统计营收技术问询

针对你需要按country分组，同时将bill_date按每月内以周日为结束的自定义周分组统计收入的需求，以下是适配SQL Server 2018的实现方案：

核心逻辑 #

1. 确定每个日期所在月份的第一天和最后一天
2. 计算当月第一个周日，划分第一周（月初至首个周日）
3. 对第一个周日之后的日期，按「周一至周日」划分常规周
4. 月末剩余天数统一归为最后一周

完整SQL代码 #

WITH DateGroups AS (
    SELECT
        -- 若bill_date含时间部分，先转为纯日期
        CAST(bill_date AS DATE) AS bill_date,
        country,
        revenues,
        DATEFROMPARTS(YEAR(bill_date), MONTH(bill_date), 1) AS first_day_of_month,
        EOMONTH(bill_date) AS last_day_of_month
    FROM your_table_name -- 替换为实际表名
),
WeekCalculations AS (
    SELECT
        bill_date,
        country,
        revenues,
        first_day_of_month,
        last_day_of_month,
        -- 计算当月第一个周日（兼容DATEFIRST设置，避免环境差异）
        DATEADD(day, 
            (7 - (DATEPART(weekday, first_day_of_month) + @@DATEFIRST - 1) % 7) % 7, 
            first_day_of_month
        ) AS first_sunday_of_month
    FROM DateGroups
),
MonthWeeks AS (
    SELECT
        bill_date,
        country,
        revenues,
        first_day_of_month,
        first_sunday_of_month,
        last_day_of_month,
        -- 计算月内自定义周数
        CASE
            WHEN bill_date <= first_sunday_of_month THEN 1
            ELSE 
                DATEDIFF(day, DATEADD(day, 1, first_sunday_of_month), bill_date) / 7 + 2
        END AS month_week_number
    FROM WeekCalculations
)
SELECT
    country,
    YEAR(first_day_of_month) AS year,
    MONTH(first_day_of_month) AS month,
    month_week_number AS week_in_month,
    -- 可选：显示该周的日期范围，便于验证
    CASE month_week_number
        WHEN 1 THEN CONVERT(VARCHAR, first_day_of_month, 23) + ' - ' + CONVERT(VARCHAR, first_sunday_of_month, 23)
        WHEN (SELECT MAX(month_week_number) FROM MonthWeeks mw WHERE mw.country = MonthWeeks.country AND mw.year = MonthWeeks.year AND mw.month = MonthWeeks.month) 
             THEN CONVERT(VARCHAR, DATEADD(day, 1, DATEADD(week, month_week_number - 2, first_sunday_of_month)), 23) + ' - ' + CONVERT(VARCHAR, last_day_of_month, 23)
        ELSE CONVERT(VARCHAR, DATEADD(day, 1, DATEADD(week, month_week_number - 2, first_sunday_of_month)), 23) + ' - ' + CONVERT(VARCHAR, DATEADD(week, month_week_number - 1, first_sunday_of_month), 23)
    END AS week_date_range,
    SUM(revenues) AS total_revenues
FROM MonthWeeks
GROUP BY
    country,
    YEAR(first_day_of_month),
    MONTH(first_day_of_month),
    month_week_number,
    first_day_of_month,
    first_sunday_of_month,
    last_day_of_month
ORDER BY
    country,
    year,
    month,
    week_in_month;

关键细节说明 #

• 兼容DATEFIRST设置：通过@@DATEFIRST调整DATEPART(weekday)的计算结果，避免不同环境下周日的编号差异导致分组错误。
• 周数计算：
  • 第一周：从当月1号到首个周日，统一标记为第1周
  • 常规周：从首个周日的次日（周一）开始，每7天为一周，周数依次递增
  • 最后一周：月末剩余天数自动归入当月最后一周，无需额外判断
• 日期格式化：使用CONVERT(VARCHAR, date, 23)输出YYYY-MM-DD格式的日期范围，便于查看。

注意事项 #

1. 替换代码中的your_table_name为你的实际表名。
2. 若bill_date字段包含时间部分，必须通过CAST(bill_date AS DATE)转为纯日期，否则会影响分组逻辑。
3. 测试2023年11月数据时，会自动生成分组：1-5日（第1周）、6-12日（第2周）、13-19日（第3周）、20-26日（第4周）、27-30日（第5周），完全符合你的需求。

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