sql - 条件超前/滞后函数 PostgreSQL？

Question

我有一个这样的表:

Name   activity  time

user1  A1        12:00
user1  E3        12:01
user1  A2        12:02
user2  A1        10:05
user2  A2        10:06
user2  A3        10:07
user2  M6        10:07
user2  B1        10:08
user3  A1        14:15
user3  B2        14:20
user3  D1        14:25
user3  D2        14:30

现在，我需要这样的结果:

Name   activity  next_activity

user1  A2        NULL
user2  A3        B1
user3  A1        B2

我想为每个用户检查 A 组的最后一个事件以及 B 组接下来发生的事件类型(B 组的事件总是在 A 组的事件之后发生)。其他类型的事件对我来说不感兴趣。我试过使用 lead() 函数，但没有成功。

我该如何解决我的问题？

Answer 1

你的定义:

activity from group B always takes place after activity from group A.

.. 逻辑上意味着每个用户在 1 个或多个 A 事件之后有 0 或 1 个 B 事件。序列中的 B 事件不得超过 1 个。

您可以使用单个窗口函数、DISTINCT ON 和 CASE，这应该是每 几行 最快的方法用户(另见下文):

SELECT name
     , CASE WHEN a2 LIKE 'B%' THEN a1 ELSE a2 END AS activity
     , CASE WHEN a2 LIKE 'B%' THEN a2 END AS next_activity
FROM  (
   SELECT DISTINCT ON (name)
          name
        , lead(activity) OVER (PARTITION BY name ORDER BY time DESC) AS a1
        , activity AS a2
   FROM   t
   WHERE (activity LIKE 'A%' OR activity LIKE 'B%')
   ORDER  BY name, time DESC
   ) sub;

db<> fiddle here

如果没有添加 ELSE 分支，SQL CASE 表达式默认为 NULL，所以我保持简短。

假设 time 被定义为 NOT NULL。否则，您可能想要添加 NULLS LAST。为什么？

• Sort by column ASC, but NULL values first?

(activity LIKE 'A%' OR activity LIKE 'B%') 比 activity ~ '^[AB]' 更冗长，但在旧版本中通常更快Postgres 的版本。关于模式匹配:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

条件窗函数？

这实际上是可能的。您可以将聚合 FILTER 子句与窗口函数的 OVER 子句结合使用。 但是:

1. FILTER 子句本身只能处理当前行中的值。

2. 更重要的是，FILTER 不是为像 lead() 或 lag() 这样的纯正函数实现的(直到 Postgres 13) - 仅适用于 aggregate functions .

如果你尝试:

lead(activity) FILTER (WHERE activity LIKE 'A%') OVER () AS activity

Postgres 会告诉你:

FILTER is not implemented for non-aggregate window functions

关于FILTER:

• Aggregate columns with additional (distinct) filters
• Referencing current row in FILTER clause of window function

性能

对于少数 用户，每个用户很少 行，几乎任何查询速度很快，即使没有索引也是如此。

对于许多 用户和每个用户少数 行，上面的第一个查询应该是最快的。见:

• Select first row in each GROUP BY group?

对于每个用户许多 行，有(可能很多)更快的技术，具体取决于您的设置细节。见:

• Optimize GROUP BY query to retrieve latest row per user