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()函数,但它没有用.
我怎么能解决我的问题？
解决方法
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
