SQL实现按周重置的每日累计酒店超住人数统计

需求说明 #

需要基于酒店客人超住记录，生成每日滚动累计超住人数，且累计统计每周自动重置。同时要考虑客人退房（EndStayDate有值）时的人数减少。已拥有包含WeekStartDate、WeekEndDate、DayOfWeek等字段的日历维度表Calendar_tbl。

示例数据集 #

注：EndStayDate为NULL表示客人仍在店。

当前问题 #

现有查询仅统计了每日新增的超住人数，未实现滚动累计，也未考虑退房的人数减少，结果不符合预期：

预期结果 #

实现方案 #

核心思路是：先计算每日超住人数的净变动量（入住+1，退房-1），再用窗口函数按周分区做滚动累计，每周重置统计。

完整SQL代码 #

WITH GuestStayChanges AS (
    -- 生成入住记录（+1）和退房记录（-1）
    SELECT 
        UserID,
        PlannedStayDate AS ChangeDate,
        1 AS ChangeAmount
    FROM X
    UNION ALL
    SELECT 
        UserID,
        -- 处理未退房的客人：用日历表最大日期代替NULL，确保统计到当前
        ISNULL(EndStayDate, (SELECT MAX(Date) FROM Calendar_tbl)) AS ChangeDate,
        -1 AS ChangeAmount
    FROM X
    WHERE EndStayDate IS NOT NULL OR EXISTS (SELECT 1 FROM Calendar_tbl)
),
DailyNetChanges AS (
    -- 关联日历表，计算每日净变动人数
    SELECT 
        Cal.Date,
        Cal.DayOfWeek,
        Cal.WeekStartDate,
        ISNULL(SUM(gsc.ChangeAmount), 0) AS DailyNetChange
    FROM Calendar_tbl Cal
    LEFT JOIN GuestStayChanges gsc ON Cal.Date = gsc.ChangeDate
    -- 限定统计的日期范围，可根据需求调整
    WHERE Cal.Date BETWEEN (SELECT MIN(PlannedStayDate) FROM X) AND (SELECT MAX(ISNULL(EndStayDate, Date)) FROM X, Calendar_tbl)
    GROUP BY Cal.Date, Cal.DayOfWeek, Cal.WeekStartDate
)
-- 按周滚动累计计算每日超住总人数
SELECT 
    DayOfWeek,
    SUM(DailyNetChange) OVER (
        PARTITION BY WeekStartDate 
        ORDER BY Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS TotalOverstayedDays
FROM DailyNetChanges
ORDER BY Date;

代码关键点解释 #

1. GuestStayChanges CTE：

   • 将每个客人拆分为两条记录：入住日+1，退房日-1
   • 对未退房的客人，用日历表的最大日期替代EndStayDate的NULL，确保统计截止到当前日历的最后一天

2. DailyNetChanges CTE：

   • 关联日历表，按日期分组计算每日的净变动人数（新增入住数减去退房数）
   • 过滤统计日期范围，避免无效日期的干扰

3. 滚动累计计算：

   • 使用SUM() OVER()窗口函数，按WeekStartDate分区（实现每周重置）
   • ORDER BY Date确保按日期顺序累计
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从本周第一天累计到当前日

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