条件超前/滞后功能PostgreSQL？

CREATE TEMP TABLE t (name text,activity text,time time);
INSERT INTO t values
 ('user1','A1','12:00'),('user1','E3','12:01'),'A2','12:02'),('user2','10:05'),'10:06'),'A3','10:07'),'M6','B1','10:08'),('user3','14:15'),'B2','14:20'),'D1','14:25'),'D2','14:30');

你的定义：

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

..逻辑上暗示在一个或多个A活动之后,每个用户有0或1个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;

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

还假设时间定义为NOT NULL.否则,您可能想要添加NULLS LAST.为什么？

> Select first row in each GROUP BY group?

(活动类似’A％’或活动类似’B％’)比活动更详细?’^ [AB]’,但在旧版本的Postgres中通常更快.关于模式匹配：

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

条件窗口函数？

这实际上是可能的.您可以将聚合FILTER子句与窗口函数的OVER子句组合在一起.然而：

> FILTER子句本身只能使用当前行的值.
>更重要的是,FILTER没有在Postgres 9.6(尚未)中实现纯粹的窗口函数,如lead()或lag() – 仅适用于aggregate functions.

如果你试试：

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

Postgres会告诉你：

06003

关于FILTER：

> How can I simplify this game statistics query?
> Referencing current row in FILTER clause of window function

性能

(对于每个用户只有少量行的少数用户,几乎任何查询都很快,即使没有索引也是如此.)

对于许多用户和每个用户几行,上面的第一个查询应该是最快的.有关索引和性能,请参阅上面的linked answer.

对于每个用户的许多行,有(可能更多)更快的技术,具体取决于您的设置的其他详细信息：

> Optimize GROUP BY query to retrieve latest record per user