如何在PostgreSQL 8.0.2中将用户状态变更表转换为登录时段表？

需求分析 #

我们需要将用户的状态变更记录转换为登录-登出时段表：

• 连续的非离线状态（stx/stX视为离线）合并为一个登录时段
• 登录时段的起始时间为该时段第一个非离线状态的Start Time
• 登录时段的结束时间为该时段后续第一个离线状态的End Time；若没有后续离线状态，则用now()作为结束时间

实现方案 #

由于PostgreSQL 8.0.2不支持递归CTE，但支持基础窗口函数和关联子查询，我们可以通过以下步骤实现：

1. 识别登录时段的起始点

筛选出每个登录时段的起始记录：即非离线状态，且前一个状态是离线（或为该用户的第一条记录）。

2. 匹配每个起始点对应的结束时间

对每个起始点，查找后续第一个离线状态的End Time；如果没有找到，则使用now()。

完整SQL代码 #

假设原始表名为user_state_changes，字段为user_name、new_state、start_time、end_time：

SELECT
    s.user_name AS "User",
    s.session_start AS "Start Time",
    COALESCE(
        -- 查找当前起始点之后的第一个离线状态的结束时间
        (SELECT sc.end_time
         FROM user_state_changes sc
         WHERE sc.user_name = s.user_name
           AND sc.start_time > s.session_start
           AND LOWER(sc.new_state) = 'stx'
           -- 确保是第一个离线状态
           AND NOT EXISTS (
               SELECT 1
               FROM user_state_changes sc2
               WHERE sc2.user_name = sc.user_name
                 AND sc2.start_time > s.session_start
                 AND sc2.start_time < sc.start_time
                 AND LOWER(sc2.new_state) = 'stx'
           )),
        -- 没有后续离线状态则用当前时间
        NOW()
    ) AS "End Time"
FROM (
    -- 筛选登录时段的起始点
    SELECT
        user_name,
        start_time AS session_start
    FROM user_state_changes sc1
    WHERE LOWER(sc1.new_state) != 'stx'
      AND (
          -- 前一个状态是离线，或者是用户的第一条记录
          NOT EXISTS (
              SELECT 1
              FROM user_state_changes sc2
              WHERE sc2.user_name = sc1.user_name
                AND sc2.end_time = sc1.start_time
                AND LOWER(sc2.new_state) = 'stx'
          )
          OR (
              SELECT COUNT(*)
              FROM user_state_changes sc2
              WHERE sc2.user_name = sc1.user_name
                AND sc2.start_time < sc1.start_time
          ) = 0
        )
) s
ORDER BY s.user_name, s.session_start;

代码说明 #

• LOWER(new_state) = 'stx'：统一处理大小写，将stX和stx都视为离线状态
• 子查询s：筛选出所有登录时段的起始时间
• 外层关联子查询：为每个起始点匹配第一个后续离线状态的结束时间，无匹配则用now()
• 最终按用户和起始时间排序，得到预期的登录-登出时段表

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