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

このテーブルを、前のアクティビティがタイプ A だった各ユーザーのタイプ B の次のアクティビティを示すレポートに変換したいと考えています。具体的には、次のようなテーブルを作成することを目指しています。

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

条件ウィンドウ関数

従来、ユーザーはこの問題を解決するために lead() 関数の使用を試みる場合があります。ただし、Postgres のウィンドウ関数の制限により、条件関数は直接サポートされていません。具体的には、FILTER 句を lead() または lag() に適用することはできません。

問題の解決

この問題を解決するには、使用するという概念を放棄する必要があります。条件付きリード関数を使用し、代わりにより複雑なクエリ戦略を使用します。 1 つのアプローチは、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 の次のアクティビティを決定するために使用されます。 user.
• CASE ステートメントは、アクティビティの種類に基づいてアクティビティと次のアクティビティを決定するために使用されます。

パフォーマンスに関する考慮事項

データセットが小さい場合、上記のクエリは十分に良好に実行されるはずです。ただし、大規模なデータセットの場合は、インデックス付けやウィンドウ関数などの手法を使用してクエリを最適化する必要がある場合があります。

以上がPostgreSQL で条件付きリード/ラグ関数を実装するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。