Sql 识别顺序记录组

你的怀疑是正确的，这是一个缺口和岛屿问题.以下是获得所描述结果的一种方法:

WITH
  tmp (id, source_id, event_user, event_date) AS (
    VALUES
      (1, 1, 'A', NOW()),
      (2, 1, 'A', NOW() + INTERVAL '1 day'),
      (3, 1, 'B', NOW() + INTERVAL '2 day'),
      (4, 1, 'B', NOW() + INTERVAL '3 day'),
      (5, 1, 'A', NOW() + INTERVAL '4 day'),
      (6, 1, 'A', NOW() + INTERVAL '5 day'),
      (7, 1, 'A', NOW() + INTERVAL '6 day'),
      (8, 2, 'A', NOW() + INTERVAL '7 day'),
      (9, 2, 'B', NOW() + INTERVAL '8 day'),
      (10, 2, 'A', NOW() + INTERVAL '9 day'),
      (11, 2, 'B', NOW() + INTERVAL '10 day'),
      (12, 2, 'B', NOW() + INTERVAL '11 day')
  ),
  breaks AS (
    SELECT
      id,
      event_user IS DISTINCT FROM LAG(event_user) OVER (
        PARTITION BY
          source_id
        ORDER BY
          event_date,
          event_user
      ) break,
      source_id,
      event_user,
      event_date
    FROM
      tmp
  )
SELECT
  id,
  source_id,
  COUNT(break) FILTER (
    WHERE
      break
  ) OVER (
    PARTITION BY
      source_id
    ORDER BY
      event_date
  ) AS series_id,
  event_user,
  event_date
FROM
  breaks
ORDER BY
  event_date;

这种方法确定哪些行开始新系列，然后通过计数每source_id内开始的系列数来分配系列.