PostgreSQL数据库START/END动作配对SQL查询实现求助

根据你的需求，已知START和END动作交替出现、无重叠，这里提供两种高效的SQL实现方案：

方案一：使用LEAD窗口函数（简洁高效） #

适合仅按时间顺序配对的场景，利用LEAD()函数直接获取每个START之后的第一个END时间戳：

-- PostgreSQL 13+ 可直接用QUALIFY过滤
SELECT
  action_timestamp AS start_time,
  LEAD(action_timestamp) OVER (ORDER BY action_timestamp) AS end_time
FROM action_log
WHERE action_type IN ('START', 'END')
QUALIFY action_type = 'START';

如果你的PostgreSQL版本低于13，改用子查询过滤：

SELECT start_time, end_time
FROM (
  SELECT
    action_timestamp AS start_time,
    LEAD(action_timestamp) OVER (ORDER BY action_timestamp) AS end_time,
    action_type
  FROM action_log
  WHERE action_type IN ('START', 'END')
) filtered
WHERE action_type = 'START';

方案二：分组配对法（支持多维度扩展） #

如果后续需要按其他维度（比如用户ID）分组配对START/END，这种方法更灵活：

WITH filtered_actions AS (
  SELECT
    action_timestamp,
    action_type,
    -- 统计当前行及之前的START数量，作为配对组ID
    SUM(CASE WHEN action_type = 'START' THEN 1 ELSE 0 END) 
      OVER (ORDER BY action_timestamp) AS group_id
    -- 若需按用户分组，添加PARTITION BY user_id到OVER子句中
  FROM action_log
  WHERE action_type IN ('START', 'END')
)
SELECT
  MAX(CASE WHEN action_type = 'START' THEN action_timestamp END) AS start_time,
  MAX(CASE WHEN action_type = 'END' THEN action_timestamp END) AS end_time
FROM filtered_actions
GROUP BY group_id;

说明 #

• 请将上述SQL中的action_log替换为你的实际表名，action_timestamp、action_type替换为表中对应的时间戳字段和动作类型字段。
• 两种方案均基于你提供的「无重叠、无连续同类型动作」前提，确保配对逻辑准确。

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