SQL实现给定时间段内每周各天日均预订量的问题排查

问题分析 #

你之前的两段SQL都没达到目标：

• 第一段SQL：子查询按星期几分组得到各天的总预订数，外层AVG把这些总数再平均，得到的是所有星期几总预订数的平均值，并非各天的日均。
• 第二段SQL：子查询已经按星期几得到总预订数，外层GROUP BY Days后AVG单个值，结果还是总预订数，没有计算日均。

正确解决方案 #

以下两种方法都能实现按星期几计算日均预订量，可根据需求选择：

方法一：先统计每日预订数，再按星期几求平均

SELECT
    DATEPART(weekday, UpdatedDate) AS DayOfWeek,
    AVG(DailyBookings) AS AvgDailyBookings
FROM (
    SELECT
        CAST(UpdatedDate AS DATE) AS BookingDate, -- 提取纯日期部分，按天分组
        DATEPART(weekday, UpdatedDate) AS DayOfWeek,
        COUNT(*) AS DailyBookings
    FROM Booking
    WHERE
        Status = 'Confirmed'
        -- 在此添加你的时间段过滤条件，例如：UpdatedDate BETWEEN '2024-01-01' AND '2024-06-30'
    GROUP BY CAST(UpdatedDate AS DATE), DATEPART(weekday, UpdatedDate)
) AS DailyCounts
GROUP BY DayOfWeek
ORDER BY DayOfWeek;

逻辑说明：子查询先按具体日期分组，统计每天的预订数并标记星期几；外层按星期几聚合，对每天的预订数求平均，得到该星期几的日均预订量。

方法二：总预订数除以对应星期几的总天数

SELECT
    DayOfWeek,
    TotalBookings * 1.0 / DayCount AS AvgDailyBookings
FROM (
    SELECT
        DATEPART(weekday, UpdatedDate) AS DayOfWeek,
        COUNT(*) AS TotalBookings,
        COUNT(DISTINCT CAST(UpdatedDate AS DATE)) AS DayCount -- 统计该星期几在时间段内的总天数
    FROM Booking
    WHERE
        Status = 'Confirmed'
        -- 在此添加你的时间段过滤条件
    GROUP BY DATEPART(weekday, UpdatedDate)
) AS Summary
ORDER BY DayOfWeek;

逻辑说明：子查询统计每个星期几的总预订数，以及该星期几在时间段内出现的独立日期数量；用总预订数除以天数（乘以1.0避免整数除法），得到日均预订量。

补充提示 #

• DATEPART(weekday, ...)的返回值依赖SQL Server的SET DATEFIRST设置（默认周日为1，周一为2），如果需要更清晰的结果，可以用DATENAME(weekday, UpdatedDate)返回星期名称（如Monday）。
• 必须添加时间段过滤条件，否则会计算全量历史数据的日均。

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