如何在PostgreSQL中利用进出日志统计建筑内每小时最大人数

核心思路是先为每条进入记录匹配对应离开记录生成在馆时间段，再将这些时间段与目标小时区间做重叠匹配，最终统计每个小时内的在馆人数（即该小时的最大人数）。

步骤与SQL实现 #

假设你的日志表名为access_log，具体实现代码如下：

-- 第一步：配对用户的进出记录
WITH paired_entries AS (
    SELECT
        user_id,
        datetime AS entry_time,
        -- 为每条in记录匹配后续第一条out记录
        LEAD(datetime) OVER (PARTITION BY user_id ORDER BY datetime) AS exit_time
    FROM access_log
    WHERE direction = 'in'
),
-- 第二步：处理异常数据（比如用户只进未出的情况）
cleaned_entries AS (
    SELECT
        user_id,
        entry_time,
        -- 未离开的用户默认按当天结束时间处理
        COALESCE(exit_time, DATE_TRUNC('day', entry_time) + INTERVAL '1 day - 1 second') AS exit_time
    FROM paired_entries
),
-- 第三步：生成需要统计的所有小时区间
hourly_ranges AS (
    SELECT
        generate_series(
            (SELECT DATE_TRUNC('hour', MIN(datetime)) FROM access_log),
            (SELECT DATE_TRUNC('hour', MAX(datetime)) FROM access_log),
            INTERVAL '1 hour'
        ) AS hour_start
)
-- 第四步：统计每个小时内的在馆人数
SELECT
    TO_CHAR(h.hour_start, 'DD/MM/YYYY, HH24:00') AS hour,
    COUNT(DISTINCT ce.user_id) AS count
FROM hourly_ranges h
JOIN cleaned_entries ce
    -- 判断用户在馆时段与当前小时是否有重叠
    ON ce.entry_time < h.hour_start + INTERVAL '1 hour'
    AND ce.exit_time >= h.hour_start
GROUP BY h.hour_start
ORDER BY h.hour_start;

代码说明 #

1. paired_entries：通过LEAD窗口函数，为每个用户的每条进入记录匹配后续第一条离开记录，实现进出动作的一一对应。
2. cleaned_entries：处理用户只进未出的异常场景，将这类用户的离开时间默认设为当天最后一秒（可根据业务需求调整）。
3. hourly_ranges：生成覆盖日志时间范围的所有小时起始点，确保没有遗漏需要统计的时段。
4. 最终统计：通过时间重叠条件判断，统计每个小时内所有在馆的用户数量，该数值即为该小时内建筑内的最大人数。

大数据量优化建议 #

针对长周期、大用户量的场景，可以通过以下方式提升性能：

• 为access_log表创建复合索引：CREATE INDEX idx_access_log_user_datetime ON access_log(user_id, datetime);
• 按天/按月分批统计数据，避免一次性处理全量日志。

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