SQL实现按每15分钟区间统计在场人员数量的查询方法

适用环境：Microsoft SQL Server（SQL Server Management Studio 管理工具），基于Present表实现

实现逻辑 #

• 根据传入的自定义起止时间参数，生成时间范围内所有按15分钟切分的连续时间区间
• 在场判定规则：人员BeginDate（入场时间）早于统计区间结束时间，且Exitdate（离场时间）晚于统计区间开始时间，即人员在场时段和统计区间存在时间重叠即判定为在场，完全匹配需求中18:00-18:15区间返回2个在场ID的判定标准
• 按时间区间分组聚合，统计每个区间的在场人员ID数量

可直接运行的SQL代码 #

-- 自定义统计起止时间参数，可根据需求修改
DECLARE @parameter1 DATETIME = '2022-06-13 09:00';
DECLARE @parameter2 DATETIME = '2022-06-13 18:30';

-- 递归生成指定范围内所有15分钟间隔的时间区间
WITH TimeIntervals AS (
    SELECT 
        @parameter1 AS IntervalStart,
        DATEADD(MINUTE, 15, @parameter1) AS IntervalEnd
    UNION ALL
    SELECT 
        DATEADD(MINUTE, 15, IntervalStart),
        DATEADD(MINUTE, 15, IntervalEnd)
    FROM TimeIntervals
    WHERE IntervalEnd < @parameter2
)
-- 关联Present表统计每个区间的在场人数
SELECT 
    ti.IntervalStart AS [Time],
    COUNT(p.IdNum) AS Num_Of_ID_Present
FROM TimeIntervals ti
LEFT JOIN Present p
    ON p.BeginDate < ti.IntervalEnd
    AND p.Exitdate > ti.IntervalStart
GROUP BY ti.IntervalStart
ORDER BY ti.IntervalStart
-- 取消递归层数限制，支持大跨度时间范围统计
OPTION (MAXRECURSION 0);

使用说明 #

• 修改@parameter1、@parameter2的赋值即可调整统计的时间范围，支持跨天统计
• 结果会返回参数覆盖范围内所有15分钟节点的统计值，哪怕某区间无在场人员也会显示对应时间点、在场人数为0，不会出现时间断档
• 基于提供的样例数据验证，关键节点返回结果和预期一致：
  • 2022-06-13 10:15 区间：4个ID均和区间存在时间重叠，返回4
  • 2022-06-13 18:00 区间：IdNum为123、389的人员在场，返回2
  • 2022-06-13 18:15 区间：仅IdNum为123的人员在场，返回1

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