T-SQL按星期几分组统计平均事件数实现方法

需求描述 #

• 存储事件的数据库表包含事件ID、事件发生日期字段，需统计周一至周日各星期几对应的平均事件数量
• 支持跨多月统计场景，单日可存在多条事件记录
• 逻辑要求：先按自然日分组统计每日事件总量，再按星期维度聚合计算平均值，无需手动计算日期区间内对应星期的天数，仅用T-SQL实现

样例参考 #

基础样例表TABLE_1数据如下：

对应预期输出：

Wed: 2
Thu: 1
Fri: 1

原有语句问题 #

之前尝试编写的查询存在语法错误，且分组逻辑不满足两层聚合的要求：仅按日期的「日」部分分组，没有先完成自然日粒度的事件数统计，也无法实现星期维度的二次聚合，原语句如下：

SELECT COUNT(DATEPART(DD, EVENT_DATE) AS 'Event Total', AVG(COUNT(DATEPART(DD, EVENT_DATE)) AS 'Average'
FROM TABLE_1
GROUP BY DATEPART(DD, EVENT_DATE)

可直接运行的实现代码 #

通过CTE做两层分组即可实现需求，代码如下：

-- 如需将周一设为每周第一天，取消下一行注释
-- SET DATEFIRST 1;

WITH DailyStats AS (
    SELECT
        CAST(EVENT_DATE AS DATE) AS EventDay,
        COUNT(*) AS DayEventTotal
    FROM TABLE_1
    -- 可按需添加时间范围筛选条件
    -- WHERE EVENT_DATE >= '2021-06-01' AND EVENT_DATE < '2021-07-01'
    GROUP BY CAST(EVENT_DATE AS DATE)
)
SELECT
    CONCAT(DATENAME(WEEKDAY, EventDay), ': ', AvgEventCount) AS Result
FROM (
    SELECT
        EventDay,
        AVG(CAST(DayEventTotal AS FLOAT)) OVER (PARTITION BY DATEPART(WEEKDAY, EventDay)) AS AvgEventCount,
        DATEPART(WEEKDAY, EventDay) AS WeekDaySort
    FROM DailyStats
) t
GROUP BY DATENAME(WEEKDAY, EventDay), AvgEventCount, WeekDaySort
ORDER BY WeekDaySort

如果不需要和样例完全一致的Wed: 2格式输出，可简化第二层查询，直接返回星期名称和平均值两列：

WITH DailyStats AS (
    SELECT
        CAST(EVENT_DATE AS DATE) AS EventDay,
        COUNT(*) AS DayEventTotal
    FROM TABLE_1
    GROUP BY CAST(EVENT_DATE AS DATE)
)
SELECT
    DATENAME(WEEKDAY, EventDay) AS WeekDay,
    AVG(CAST(DayEventTotal AS FLOAT)) AS AvgEventCount
FROM DailyStats
GROUP BY DATENAME(WEEKDAY, EventDay), DATEPART(WEEKDAY, EventDay)
ORDER BY DATEPART(WEEKDAY, EventDay)

逻辑说明 #

• 第一层CTEDailyStats先按自然日聚合，统计每个自然日的总事件数，解决单日多条事件记录的问题
• 第二层按星期维度分组，直接对每日事件数求平均值，AVG函数会自动按分组内的自然日数量计算，无需手动统计区间内对应星期几的总天数
• 对计数值做FLOAT类型转换，避免整数除法导致平均值精度丢失
• 可通过SET DATEFIRST 1调整每周起始日为周一，适配国内常用的星期排序规则

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