MS SQL Server 2019按区域统计月度客人数与入住晚数

需求说明 #

• 按定价区域（Zone）、年份维度，统计两项核心指标：
  1. 每月入住晚数：入住晚数按次日归属对应月份（例：9月30日至10月1日的晚数计入10月）
  2. 每月客人数：客人入住跨月时，需计入其入住覆盖的所有月份

示例数据集 #

ID  From                     To                       Zone
1   2023-10-01 00:00:00.000 2023-10-06 00:00:00.000  1
2   2023-10-03 00:00:00.000 2023-10-05 00:00:00.000  1
3   2023-09-30 00:00:00.000 2023-10-01 00:00:00.000  2
4   2023-04-14 00:00:00.000 2023-04-20 00:00:00.000  2
5   2023-09-25 00:00:00.000 2023-10-11 00:00:00.000  3
6   2023-08-31 00:00:00.000 2023-09-01 00:00:00.000  1

已实现结果（仅入住晚数） #

Year    Zone    M1  M2  M3  M4  M5  M6  M7  M8  M9  M10 M11 M12
2023    1       0   0   0   0   0   0   0   0   1   7   0   0
2023    2       0   0   0   6   0   0   0   0   0   1   0   0
2023    3       0   0   0   0   0   0   0   0   5   11  0   0

期望结果（含晚数与客人数） #

Year    Zone    M1  M2  M3  M4  M5  M6  M7  M8  M9  M10 M11 M12 G1  G2  G3  G4  G5  G6  G7  G8  G9  G10 G11 G12
2023    1       0   0   0   0   0   0   0   0   1   7   0   0   0   0   0   0   0   0   0   0   1   2   0   0
2023    2       0   0   0   6   0   0   0   0   0   1   0   0   0   0   0   1   0   0   0   0   0   1   0   0
2023    3       0   0   0   0   0   0   0   0   5   11  0   0   0   0   0   0   0   0   0   0   1   1   0   0

当前代码（仅统计晚数） #

DECLARE @Nights TABLE 
    ([ID] int,[From] datetime, [To] datetime, [Zone] INT)
;

INSERT INTO @Nights
    ([ID], [From], [To], [Zone])
VALUES
    (1, '2023-10-01', '2023-10-06', 1),
    (2, '2023-10-03', '2023-10-05', 1),
    (3, '2023-09-30', '2023-10-01', 2),
    (4, '2023-04-14', '2023-04-20', 2),
    (5, '2023-09-25', '2023-10-11', 3),
    (6, '2023-08-31', '2023-09-01', 1)
;

WITH cteTally as (
    SELECT  MAX(DATEDIFF(day,[From],[To])) - 1 as Tally
    FROM
       @Nights

    UNION ALL

    SELECT Tally - 1
    FROM
       cteTally
    WHERE
       Tally - 1 >= 0
)
, cteDates AS (
    SELECT 
       DATEADD(day,c.Tally+1,t.[From]) as date
       ,YEAR(DATEADD(day,c.Tally+1,t.[From])) as [Year]
       ,'M'+CAST(DATEPART(month,DATEADD(day,c.Tally+1,t.[From])) as varchar(2)) as [Month],t.Zone
--     ,case when c.Tally=0 then 1 else case when DATEPART(DAY,DATEADD(day,c.Tally+1,t.[From]))=1 then 1 else 0 end end as Guests,t.[ID]
    FROM
       @Nights t
       INNER JOIN cteTally c
       ON DATEDIFF(day,[From],[To]) - 1 >= c.Tally
       
)

SELECT *
FROM
    cteDates
PIVOT (
       COUNT([date])
       for [Month] IN ([M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12])
    ) p 
order by [Year],[Zone]
option (maxrecursion 0)

解决方案代码 #

DECLARE @Nights TABLE 
    ([ID] int,[From] datetime, [To] datetime, [Zone] INT)
;

INSERT INTO @Nights
    ([ID], [From], [To], [Zone])
VALUES
    (1, '2023-10-01', '2023-10-06', 1),
    (2, '2023-10-03', '2023-10-05', 1),
    (3, '2023-09-30', '2023-10-01', 2),
    (4, '2023-04-14', '2023-04-20', 2),
    (5, '2023-09-25', '2023-10-11', 3),
    (6, '2023-08-31', '2023-09-01', 1)
;

-- 生成数字序列，用于扩展日期范围
WITH cteTally AS (
    SELECT 0 AS Tally
    UNION ALL
    SELECT Tally + 1
    FROM cteTally
    WHERE Tally + 1 <= (SELECT MAX(DATEDIFF(day, [From], [To])) FROM @Nights)
),
-- 统计每月入住晚数
cteNights AS (
    SELECT
        YEAR(DATEADD(day, c.Tally + 1, t.[From])) AS [Year],
        t.Zone,
        'M' + CAST(DATEPART(month, DATEADD(day, c.Tally + 1, t.[From])) AS VARCHAR(2)) AS MonthCol,
        1 AS NightCount
    FROM @Nights t
    INNER JOIN cteTally c ON c.Tally < DATEDIFF(day, t.[From], t.[To])
),
pivotedNights AS (
    SELECT *
    FROM cteNights
    PIVOT (
        SUM(NightCount)
        FOR MonthCol IN ([M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12])
    ) p
),
-- 统计每月客人数
cteGuests AS (
    SELECT DISTINCT
        YEAR(d.CoveredMonth) AS [Year],
        t.Zone,
        'G' + CAST(DATEPART(month, d.CoveredMonth) AS VARCHAR(2)) AS MonthCol,
        1 AS GuestCount
    FROM @Nights t
    CROSS APPLY (
        -- 生成客人入住覆盖的所有月份
        SELECT DATEADD(month, n, DATEFROMPARTS(YEAR(t.[From]), MONTH(t.[From]), 1)) AS CoveredMonth
        FROM cteTally n
        WHERE DATEADD(month, n, DATEFROMPARTS(YEAR(t.[From]), MONTH(t.[From]), 1)) <= EOMONTH(t.[To])
    ) d
),
pivotedGuests AS (
    SELECT *
    FROM cteGuests
    PIVOT (
        SUM(GuestCount)
        FOR MonthCol IN ([G1],[G2],[G3],[G4],[G5],[G6],[G7],[G8],[G9],[G10],[G11],[G12])
    ) p
)
-- 合并晚数与客人数结果
SELECT
    COALESCE(n.[Year], g.[Year]) AS [Year],
    COALESCE(n.Zone, g.Zone) AS Zone,
    ISNULL(n.M1, 0) AS M1, ISNULL(n.M2, 0) AS M2, ISNULL(n.M3, 0) AS M3,
    ISNULL(n.M4, 0) AS M4, ISNULL(n.M5, 0) AS M5, ISNULL(n.M6, 0) AS M6,
    ISNULL(n.M7, 0) AS M7, ISNULL(n.M8, 0) AS M8, ISNULL(n.M9, 0) AS M9,
    ISNULL(n.M10, 0) AS M10, ISNULL(n.M11, 0) AS M11, ISNULL(n.M12, 0) AS M12,
    ISNULL(g.G1, 0) AS G1, ISNULL(g.G2, 0) AS G2, ISNULL(g.G3, 0) AS G3,
    ISNULL(g.G4, 0) AS G4, ISNULL(g.G5, 0) AS G5, ISNULL(g.G6, 0) AS G6,
    ISNULL(g.G7, 0) AS G7, ISNULL(g.G8, 0) AS G8, ISNULL(g.G9, 0) AS G9,
    ISNULL(g.G10, 0) AS G10, ISNULL(g.G11, 0) AS G11, ISNULL(g.G12, 0) AS G12
FROM pivotedNights n
FULL JOIN pivotedGuests g ON n.[Year] = g.[Year] AND n.Zone = g.Zone
ORDER BY [Year], Zone
OPTION (MAXRECURSION 0);

关键说明 #

1. 拆分统计维度：将入住晚数和客人数分开统计，避免PIVOT同时处理多聚合逻辑的复杂度
2. 晚数统计：通过数字序列扩展每晚的日期，按次日归属月份累加晚数，符合规则要求
3. 客人数统计：生成客人入住覆盖的所有月份，去重后统计每个月的客人数，确保跨月客人被计入所有覆盖月份
4. 结果合并：通过FULL JOIN按年份和区域合并两个统计结果，用ISNULL补全缺失的0值，保证结果格式统一

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