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