sql - 检索连续行之间具有最小时间间隔的 ID

Question

我在 Postgres 9.3 中有以下 event 表:

CREATE TABLE event (
  event_id    integer PRIMARY KEY,
  user_id     integer,
  event_type  varchar,
  event_time  timestamptz
);

我的目标 是检索所有 user_id 在他们的任何事件之间(或他们的最后一个事件和当前时间之间)至少有 30 天的间隔.另一个复杂的问题是，我只希望具有这些差距之一的用户在执行特定 event_type 'convert' 之后出现。如何轻松做到这一点？

event 表中的一些示例数据可能如下所示:

INSERT INTO event (event_id, user_id, event_type, event_time)
VALUES
(10, 1, 'signIn',  '2015-05-05 00:11'),
(11, 1, 'browse',  '2015-05-05 00:12'),  -- no 'convert' event

(20, 2, 'signIn',  '2015-06-07 02:35'),
(21, 2, 'browse',  '2015-06-07 02:35'),
(22, 2, 'convert', '2015-06-07 02:36'),  -- only 'convert' event
(23, 2, 'signIn',  '2015-08-10 11:00'),  -- gap of >= 30 days
(24, 2, 'signIn',  '2015-08-11 11:00'),

(30, 3, 'convert', '2015-08-07 02:36'),  -- starting with 1st 'convert' event
(31, 3, 'signIn',  '2015-08-07 02:36'),
(32, 3, 'convert', '2015-08-08 02:36'),
(33, 3, 'signIn',  '2015-08-12 11:00'),  -- all gaps below 30 days
(33, 3, 'browse',  '2015-08-12 11:00'),  -- gap until today (2015-08-20) too small

(40, 4, 'convert', '2015-05-07 02:36'),
(41, 4, 'signIn',  '2015-05-12 11:00');  -- gap until today (2015-08-20) >= 30 days

预期结果:

user_id
--------
2
4

Answer 1

一种方法:

SELECT user_id
FROM  (
   SELECT user_id
        , lead(e.event_time, 1, now()) OVER (PARTITION BY e.user_id ORDER BY e.event_time)
          - event_time AS gap
   FROM  (  -- only users with 'convert' event
      SELECT user_id, min(event_time) AS first_time
      FROM   event
      WHERE  event_type = 'convert'
      GROUP  BY 1
      ) e1
   JOIN   event e USING (user_id)
   WHERE  e.event_time >= e1.first_time
   ) sub
WHERE  gap >= interval '30 days'
GROUP  BY 1;

window function lead()如果没有“下一行”，则允许包含默认值，这可以方便地满足“或在他们的最后一个事件和当前时间之间”的额外要求。

索引

如果你的表很大，你至少应该在 (user_id, event_time) 上有一个索引:

CREATE INDEX event_user_time_idx ON event(user_id, event_time);

如果您经常这样做并且 event_type 'convert' 很少见，请添加另一个部分索引:

CREATE INDEX event_user_time_convert_idx ON event(user_id, event_time)
WHERE  event_type = 'convert';

对于每个用户许多事件

并且仅当 30 天的间隔很常见(并非罕见情况)时。
索引变得更加重要。
试试这个 recursive CTE为了更好的性能:

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT DISTINCT ON (user_id)
          user_id, event_time, interval '0 days' AS gap
   FROM   event
   WHERE  event_type = 'convert'
   ORDER  BY user_id, event_time
   )

   UNION ALL
   SELECT c.user_id, e.event_time, COALESCE(e.event_time, now()) - c.event_time
   FROM   cte c
   LEFT   JOIN LATERAL (
      SELECT e.event_time
      FROM   event e
      WHERE  e.user_id = c.user_id
      AND    e.event_time > c.event_time
      ORDER  BY e.event_time
      LIMIT  1     -- the next later event
      ) e ON true  -- add 1 row after last to consider gap till "now"
   WHERE  c.event_time IS NOT NULL
   AND    c.gap < interval '30 days'
   )
SELECT * FROM cte
WHERE  gap >= interval '30 days';

它的开销要大得多，但可以在第一个足够大的间隔处停止(对于每个用户)。如果那应该是最后一个事件现在之间的间隔，则结果中的event_time 为NULL。

新SQL Fiddle用更多揭示性的测试数据来证明这两个查询。

这些相关答案中的详细解释:

• Optimize GROUP BY query to retrieve latest record per user
• Select first row in each GROUP BY group?