PostgreSQL でグループ A の最後のアクティビティの後にグループ B から後続のアクティビティを見つけるにはどうすればよいですか?

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

さまざまなユーザーのアクティビティとタイムスタンプを含むテーブルがあります。各ユーザーのグループ A からの最後のアクティビティを特定し、グループ B からの後続のアクティビティ (存在する場合) を決定したいとします。

リード関数の使用

最初に、この問題を解決するために lead() 関数を使用しようとしました。ただし、lead() 関数は、条件に関係なく、単に順番に次の値を取得するだけなので、このタスクには単独では十分ではありません。

条件付きウィンドウ関数 (FILTER)

ウィンドウ関数に条件を効果的に適用するために、PostgreSQL には FILTER 句が用意されています。残念ながら、この句は現在、lead() や lag() などの真のウィンドウ関数には実装されていません。集計関数でのみ使用できるためです。

DISTINCT ON を使用した代替解決策

FILTER を使用せずにこの課題に対処するには、DISTINCT ON ステートメントと CASE ステートメントを組み合わせて使用​​できます。 subquery:

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;

このアプローチでは、DISTINCT ON 句を利用して行をユーザーごとにグループ化し、ユーザーごとにグループ A から最後に条件を満たすアクティビティを選択します。 CASE ステートメントは、指定された条件に基づいて適切なアクティビティとその後のアクティビティを決定するために使用されます。

大規模なデータセットに対する最適なソリューション

多数の行が含まれるケースの場合ユーザーごとに、代替技術の方が効率的である可能性があります。これらの手法には通常、一時テーブルを作成するか、高度なインデックス作成戦略を使用することが含まれます。ただし、最適なアプローチは、データとワークロードの特定の特性によって異なる場合があります。

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