基于时间的SQL聚合：计算人员在区域的停留时长

核心思路 #

通过窗口函数标记连续停留区间，分组计算每个区间的时长后按区域汇总，完全匹配给定的计算规则：

• 同一人员同一区域内，记录间隔<10分钟视为连续停留，时长取首尾时间差
• 单条记录按5分钟计算

完整SQL查询 #

WITH ranked_data AS (
    SELECT
        date,
        node_address,
        areaName,
        -- 标记新停留区间：首次记录 或 与上一条间隔≥10分钟
        CASE
            WHEN LAG(date) OVER (PARTITION BY node_address, areaName ORDER BY date) IS NULL
                OR DATETIME_DIFF(date, LAG(date) OVER (PARTITION BY node_address, areaName ORDER BY date), MINUTE) >= 10
            THEN 1
            ELSE 0
        END AS is_new_session
    FROM your_table_name -- 替换为你的表名
),
session_groups AS (
    SELECT
        date,
        node_address,
        areaName,
        -- 累计生成每个停留区间的唯一ID
        SUM(is_new_session) OVER (PARTITION BY node_address, areaName ORDER BY date) AS session_id
    FROM ranked_data
),
session_durations AS (
    SELECT
        areaName,
        node_address,
        session_id,
        -- 计算单个区间时长：单条记录算5分钟，连续记录取首尾时间差
        CASE
            WHEN COUNT(*) = 1 THEN 5
            ELSE DATETIME_DIFF(MAX(date), MIN(date), MINUTE)
        END AS session_minutes
    FROM session_groups
    GROUP BY areaName, node_address, session_id
)
SELECT
    areaName,
    CONCAT(SUM(session_minutes), ' min') AS time
FROM session_durations
GROUP BY areaName
ORDER BY areaName;

步骤说明 #

1. ranked_data：按node_address（人员）和areaName（区域）分区，用LAG函数判断当前记录是否开启新的停留区间，标记为is_new_session。
2. session_groups：对is_new_session做累计求和，为每个连续停留的记录组生成唯一session_id，确保同一段连续停留的记录归属同一组。
3. session_durations：按区域、人员、session_id分组，计算每个停留区间的时长：单条记录按5分钟计算，连续记录取区间内最晚时间与最早时间的分钟差。
4. 最终汇总：按区域求和所有停留区间的时长，格式化为X min的结果样式。

数据库适配说明 #

如果使用非BigQuery数据库，替换DATETIME_DIFF为对应函数：

• MySQL：TIMESTAMPDIFF(MINUTE, LAG(date), date)
• PostgreSQL：EXTRACT(MINUTE FROM (date - LAG(date)))

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