求SQL查询：用户去重在线时长及无用户在线时段统计

核心思路：合并重叠/连续会话 #

先按用户分组，将重叠或连续的会话合并成单一时间段，再计算总时长，避免重复统计重叠时间。

优化后的SQL示例（以PostgreSQL为例） #

假设会话表名为user_sessions，字段为user_id, login_ts, logout_ts：

WITH merged_sessions AS (
    SELECT 
        user_id,
        login_ts,
        -- 合并重叠会话：取当前会话及之前所有未结束会话的最晚 logout_ts
        MAX(logout_ts) OVER (
            PARTITION BY user_id 
            ORDER BY login_ts 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS merged_logout_ts
    FROM user_sessions
    -- 提前过滤观测时段，减少计算量
    WHERE login_ts < '2023-05-02 00:00:00' 
      AND logout_ts > '2023-05-01 00:00:00'
),
-- 去重合并后的会话（只保留每个连续块的起始记录）
unique_sessions AS (
    SELECT 
        user_id,
        login_ts,
        merged_logout_ts,
        -- 标记是否为新的会话块（当前login_ts > 上一个块的merged_logout_ts）
        CASE 
            WHEN LAG(merged_logout_ts) OVER (PARTITION BY user_id ORDER BY login_ts) >= login_ts 
            THEN 0 
            ELSE 1 
        END AS is_new_block
    FROM merged_sessions
),
session_blocks AS (
    SELECT 
        user_id,
        MIN(login_ts) AS block_start,
        MAX(merged_logout_ts) AS block_end
    FROM unique_sessions
    GROUP BY user_id, SUM(is_new_block) OVER (PARTITION BY user_id ORDER BY login_ts)
)
SELECT 
    user_id,
    -- 计算总时长，注意截断到观测时段边界
    SUM(
        EXTRACT(EPOCH FROM (
            LEAST(block_end, '2023-05-02 00:00:00') - 
            GREATEST(block_start, '2023-05-01 00:00:00')
        )) / 3600
    ) AS actual_online_hours
FROM session_blocks
GROUP BY user_id;

优化建议 #

• 索引优化：给user_id, login_ts, logout_ts建立联合索引，加速分区和排序操作：CREATE INDEX idx_user_session ON user_sessions(user_id, login_ts, logout_ts);
• 提前过滤：在CTE第一步就过滤掉观测时段外的会话，减少后续计算的数据量
• 避免全表扫描：如果表数据量大，优先用观测时段的时间范围限制，配合时间索引

核心思路：找出全局在线块的间隙 #

1. 合并所有用户的会话，得到整个观测时段内的连续在线时间段（全局无重叠）
2. 计算观测时段起始到第一个在线块、在线块之间、最后一个在线块到观测时段结束的间隙
3. 筛选出时长大于0的间隙，计算时长

实现SQL示例（以PostgreSQL为例） #

-- 定义观测时段
WITH observation_window AS (
    SELECT 
        '2023-05-01 00:00:00' AS window_start,
        '2023-05-02 00:00:00' AS window_end
),
-- 合并所有用户的重叠会话，得到全局在线块
global_merged_sessions AS (
    SELECT 
        login_ts,
        MAX(logout_ts) OVER (
            ORDER BY login_ts 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS merged_logout_ts
    FROM user_sessions, observation_window ow
    WHERE login_ts < ow.window_end 
      AND logout_ts > ow.window_start
),
global_session_blocks AS (
    SELECT 
        MIN(login_ts) AS block_start,
        MAX(merged_logout_ts) AS block_end
    FROM (
        SELECT 
            login_ts,
            merged_logout_ts,
            SUM(CASE WHEN LAG(merged_logout_ts) OVER (ORDER BY login_ts) >= login_ts THEN 0 ELSE 1 END) OVER (ORDER BY login_ts) AS block_id
        FROM global_merged_sessions
    ) sub
    GROUP BY block_id
),
-- 生成间隙时段
gap_periods AS (
    SELECT 
        -- 第一个间隙：观测起始到第一个在线块开始
        ow.window_start AS gap_start,
        MIN(gsb.block_start) AS gap_end
    FROM observation_window ow, global_session_blocks gsb
    UNION ALL
    -- 中间间隙：上一个在线块结束到下一个在线块开始
    SELECT 
        gsb_prev.block_end AS gap_start,
        gsb_curr.block_start AS gap_end
    FROM global_session_blocks gsb_prev
    JOIN global_session_blocks gsb_curr 
        ON gsb_prev.block_end < gsb_curr.block_start
    WHERE NOT EXISTS (
        SELECT 1 FROM global_session_blocks gsb_mid
        WHERE gsb_mid.block_start > gsb_prev.block_end 
          AND gsb_mid.block_end < gsb_curr.block_start
    )
    UNION ALL
    -- 最后一个间隙：最后一个在线块结束到观测结束
    SELECT 
        MAX(gsb.block_end) AS gap_start,
        ow.window_end AS gap_end
    FROM observation_window ow, global_session_blocks gsb
)
-- 筛选有效间隙（时长>0）并计算时长
SELECT 
    gap_start,
    gap_end,
    EXTRACT(EPOCH FROM (gap_end - gap_start)) / 3600 AS gap_hours
FROM gap_periods, observation_window ow
WHERE gap_start < gap_end
  AND gap_start >= ow.window_start
  AND gap_end <= ow.window_end
ORDER BY gap_start;

关键说明 #

• 如果观测时段内没有任何用户会话，SQL会直接输出整个观测时段作为间隙
• 用UNION ALL拼接三类间隙，确保覆盖所有无在线的情况
• 注意截断在线块到观测时段边界，避免超出范围的时间被计算

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