按30分钟间隔统计各位置人员数量的SQL查询需求

问题分析 #

你当前的查询仅将每条记录的进入/离开时间各自映射到对应30分钟区间后分组，只能得到单条记录对应的起始、结束区间，无法覆盖顾客停留期间的所有30分钟区间，也无法统计每个区间内的实际人数。要实现期望结果，需先生成覆盖数据时间范围的所有30分钟区间，再匹配顾客停留时间段与这些区间的重叠关系，最后统计人数。

完整SQL代码 #

-- 生成覆盖所有需要的30分钟时间区间
WITH time_intervals AS (
    SELECT 
        -- 从最早进入时间向下取整到最近的30分钟区间作为起始
        DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, min_entered) / 30) * 30, 0) AS interval_start,
        DATEADD(MINUTE, 30, DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, min_entered) / 30) * 30, 0)) AS interval_end
    FROM (
        SELECT MIN(Entered) AS min_entered FROM main -- 替换为你的实际数据源（如main CTE或表名）
    ) t
    UNION ALL
    SELECT 
        DATEADD(MINUTE, 30, interval_start),
        DATEADD(MINUTE, 30, interval_end)
    FROM time_intervals
    WHERE interval_end <= (SELECT DATEADD(MINUTE, 30, MAX(Left)) FROM main) -- 覆盖到最晚离开时间的下一个30分钟区间
),
-- 关联原始数据，筛选出与区间重叠的记录
overlapping_records AS (
    SELECT 
        t.Loc,
        t.LocID,
        ti.interval_start AS 时间段起始,
        ti.interval_end AS 时间段结束
    FROM time_intervals ti
    JOIN main t
        ON t.Entered < ti.interval_end -- 顾客进入时间早于区间结束
        AND t.Left > ti.interval_start -- 顾客离开时间晚于区间起始
)
-- 分组统计人数
SELECT 
    Loc AS 位置(Loc),
    LocID AS 位置ID(LocID),
    时间段起始,
    时间段结束,
    COUNT(*) AS 人数(Count)
FROM overlapping_records
GROUP BY Loc, LocID, 时间段起始, 时间段结束
ORDER BY Loc, 时间段起始;

代码说明 #

1. time_intervals CTE：递归生成所有覆盖数据时间范围的30分钟区间，从最早进入时间开始，直到最晚离开时间的下一个30分钟区间，确保不遗漏任何可能有顾客停留的时间段。
2. overlapping_records CTE：将时间区间与原始数据关联，通过重叠条件判断顾客是否在当前30分钟区间内有停留，只要停留时间段与区间有交集，就计入该区间。
3. 最终统计：按位置、位置ID和时间区间分组，统计每组内的记录数，即为该区间内的实时人数。

注意事项 #

• 若你的数据源不是main CTE，替换代码中的main为实际表名即可。
• 若使用非SQL Server数据库（如MySQL、PostgreSQL），需调整时间函数，但核心逻辑（生成区间→匹配重叠→统计人数）保持一致。

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