如何通过SQL查询计算出入场时间差及内外停留时长？

初始数据表 #

+----------+------------------------------+
|   Event  |           occurred           |
+----------+------------------------------+
|   Enter  |   2022-11-22 00:00:00.000    |
|   Exit   |   2022-11-22 02:00:00.000    |
|   Enter  |   2022-11-22 02:01:00.000    |
|   Exit   |   2022-11-22 05:00:00.000    |
+----------+------------------------------+

预期结果 #

+-----------------+--------------+
|   Event         |  Time Spent  |
+-----------------+--------------+
|   Inside        |   04:59:00   |
|   Outside       |   00:01:00   |
|   Total         |   05:00:00   |
+-----------------+--------------+

实现思路与SQL语句 #

核心是用窗口函数LEAD关联每条记录和下一条记录的时间，计算时间段差值后分类汇总：

MySQL版本

WITH event_with_next AS (
    SELECT
        Event,
        occurred,
        LEAD(occurred) OVER (ORDER BY occurred) AS next_occurred
    FROM your_table_name -- 替换为你的实际表名
),
duration_calculations AS (
    SELECT
        CASE 
            WHEN Event = 'Enter' THEN 'Inside'
            WHEN Event = 'Exit' THEN 'Outside'
        END AS period_type,
        TIMEDIFF(next_occurred, occurred) AS duration
    FROM event_with_next
    WHERE next_occurred IS NOT NULL -- 排除最后一条无后续的记录
)
SELECT
    period_type AS Event,
    SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS `Time Spent`
FROM duration_calculations
GROUP BY period_type
UNION ALL
SELECT
    'Total' AS Event,
    SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS `Time Spent`
FROM duration_calculations;

PostgreSQL版本

WITH event_with_next AS (
    SELECT
        Event,
        occurred,
        LEAD(occurred) OVER (ORDER BY occurred) AS next_occurred
    FROM your_table_name -- 替换为你的实际表名
),
duration_calculations AS (
    SELECT
        CASE 
            WHEN Event = 'Enter' THEN 'Inside'
            WHEN Event = 'Exit' THEN 'Outside'
        END AS period_type,
        EXTRACT(EPOCH FROM (next_occurred - occurred)) AS duration_seconds
    FROM event_with_next
    WHERE next_occurred IS NOT NULL
)
SELECT
    period_type AS Event,
    TO_CHAR(MAKE_INTERVAL(SECONDS => SUM(duration_seconds)), 'HH24:MI:SS') AS "Time Spent"
FROM duration_calculations
GROUP BY period_type
UNION ALL
SELECT
    'Total' AS Event,
    TO_CHAR(MAKE_INTERVAL(SECONDS => SUM(duration_seconds)), 'HH24:MI:SS') AS "Time Spent"
FROM duration_calculations;

逻辑说明 #

1. event_with_next 子查询：通过LEAD函数按时间顺序获取每条记录的下一条事件时间，让每条Enter对应后续的Exit，每条Exit对应后续的Enter。
2. duration_calculations 子查询：根据事件类型标记时间段（Enter对应场内，Exit对应场外），并计算当前事件到下一个事件的时长。
3. 最终汇总：先分组统计场内、场外的总时长，再通过UNION ALL添加总时长的统计结果，用时间转秒数再求和的方式避免直接计算时间字符串的误差。

内容的提问来源于stack exchange，提问作者Pa3k.m