PostgreSQL でウィンドウ関数を使用して、グループ A のアクティビティに続くグループ B から次のアクティビティを見つけるにはどうすればよいですか?

PostgreSQL の条件付きリード/ラグ関数

あなたのタスクは、指定されたテーブルからユーザーの特定のアクティビティ シーケンスを取得するクエリを生成することです。各ユーザーのグループ B から次のアクティビティ (常にグループ A のアクティビティの後に発生するアクティビティ) を決定したいとします。

問題の定義

次の表を検討してください。

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

このテーブルに必要な出力は:

Name	activity	next_activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

解決策

この問題は、DISTINCT ON ステートメントと CASE ステートメントを window と組み合わせて利用することで解決できます。関数:

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;

説明

• サブクエリは、各ユーザーのグループ A からの最新のアクティビティと、グループ B からの次のアクティビティ (存在する場合) を識別します。 DISTINCT ON およびウィンドウ関数 lead() を ORDER BY 時刻 DESC とともに使用します。
• CASE ステートメント必要な出力を処理します: グループ A からの最新のアクティビティと、グループ B からの次のアクティビティ (存在する場合)。

条件付きウィンドウ関数

while PostgreSQL は条件付きウィンドウ関数 (たとえば、lead(activity) FILTER (WHERE activity LIKE 'A%')) を直接サポートしていません。 FILTER 句を集計関数とともに使用し、それらをウィンドウ関数として使用します:

lead(activity) FILTER (WHERE activity LIKE 'A%') OVER () AS activity

ただし、このアプローチは非効率的であり、大規模なデータセットでは非現実的です。代わりに、上記のソリューションは、小規模なデータセットと大規模なデータセットの両方に推奨されます。

以上がPostgreSQL でウィンドウ関数を使用して、グループ A のアクティビティに続くグループ B から次のアクティビティを見つけるにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。