如何在 PostgreSQL 中实现条件超前/滞后函数？

Postgres 的条件超前/滞后函数？

许多用户对如何使用窗口函数在 Postgres 中实现条件超前/滞后函数感到困惑。在此示例中，我们有一个包含如下数据的表：

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

我们希望将此表转换为一个报告，显示每个用户的下一个类型 B 的活动，其中上一个活动是类型 A。具体来说，我们的目标是创建一个像这样的表：

Name	Activity	Next Activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

条件窗口函数

传统上，用户可能会尝试使用 Lead() 函数来解决这个问题。但由于Postgres窗口函数的限制，并不直接支持条件函数。具体来说，FILTER 子句不能应用于 Lead() 或 lag()。

解决问题

要解决这个问题，我们必须放弃使用条件引导函数，而是使用更复杂的查询策略。一种方法是使用 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;

说明

• 子查询为每个用户选择不同的行，按时间降序排列。
• lead() 函数返回序列中的下一个活动，用于确定每个 B 类型的下一个活动用户。
• CASE 语句用于根据活动类型确定当前活动和下一个活动。

性能注意事项

对于小型数据集，上述查询应该表现得足够好。然而，对于大型数据集，可能需要使用索引或窗口函数等技术来优化查询。

以上是如何在 PostgreSQL 中实现条件超前/滞后函数？的详细内容。更多信息请关注PHP中文网其他相关文章！