如何使用窗口函数在 PostgreSQL 中查找 A 组活动之后的 B 组中的下一个活动？

PostgreSQL 中的条件超前/滞后函数

您的任务是生成一个查询，从给定表中检索用户的特定活动序列。您想要确定每个用户的 B 组下一个活动（始终在 A 组活动之后发生）。

问题定义

考虑下表：

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

解决方案

您可以通过利用 DISTINCT ON 和 CASE 语句结合 window 来解决此问题函数：

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;

解释

• 子查询为每个用户标识 A 组中的最新活动以及 B 组中的以下活动（如果有）使用 DISTINCT ON 和窗口函数 Lead() 以及 ORDER BY time DESC。
• CASE 语句处理所需输出：A 组的最新活动，以及 B 组的下一个活动（如果存在）。

条件窗口函数

而 PostgreSQL 则这样做不直接支持条件窗口函数（例如，lead(activity) FILTER (WHERE Activity LIKE 'A%')），您可以使用带有聚合函数的 FILTER 子句并将其用作窗口函数：

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

但是，这种方法对于大型数据集来说效率低下且不切实际。相反，建议针对小型和大型数据集使用上述解决方案。

以上是如何使用窗口函数在 PostgreSQL 中查找 A 组活动之后的 B 组中的下一个活动？的详细内容。更多信息请关注PHP中文网其他相关文章！