如何在 PostgreSQL 中有效提取每个用户最后一个'A”和后续'B”活动？

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

在 PostgreSQL 表中，活动分为 A 和 B 类型，这样 B 活动始终跟随 A 活动，用户寻求为每个用户提取最后一个 A 活动和后续 B 活动的解决方案。虽然 Lead() 函数最初看起来是一种很有前途的方法，但事实证明它是无效的。

条件窗口函数

不幸的是，PostgreSQL 目前不支持条件窗口函数。 FILTER 子句可以为窗口函数提供条件过滤，但仅适用于聚合函数。

逻辑含义和解决方案

关键的见解在于问题陈述的逻辑含义：每个用户在一个或多个 A 活动之后最多有一个 B 活动。这建议使用带有 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;

性能注意事项

对于少量用户和活动，上面的查询可能会在没有一个索引。然而，随着行数和用户数量的增加，可能需要替代技术来优化性能。

潜在的优化

对于大量数据，请考虑使用更量身定制的方法：

• 如果时间允许NULL值，请将NULLS LAST添加到ORDER BY子句中。
• 使用模式匹配表达式 Activity ~ '^[AB]' 而不是 Activity LIKE 'A%' OR Activity LIKE 'B%'。
• 探索选择每组中第一行的技术，例如本文中描述的技术: [选择每个GROUP BY中的第一行group?](https://stackoverflow.com/questions/18923181/select-first-row-in-each-group-by-group)
• 研究优化 GROUP BY 查询的更高级技术，尤其是当处理每个用户的大量行：[优化 GROUP BY 查询以检索每个用户的最新行用户](https://dba.stackexchange.com/questions/55252/optimize-group-by-query-to-retrieve-latest-row-per-user)

以上是如何在 PostgreSQL 中有效提取每个用户最后一个'A”和后续'B”活动？的详细内容。更多信息请关注PHP中文网其他相关文章！