如何在 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中文網其他相關文章！