PostgreSQL でユーザーごとの最後の「A」アクティビティと後続の「B」アクティビティを効率的に抽出する方法

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

アクティビティがタイプ A と B にグループ化されている PostgreSQL テーブルでは、B アクティビティが常に A アクティビティに続くように、ユーザーは各ユーザーの最後の A アクティビティと後続の B アクティビティを抽出するソリューション。 lead() 関数は当初有望なアプローチのように見えましたが、効果がないことが判明しました。

条件付きウィンドウ関数

残念ながら、PostgreSQL は現在、条件付きウィンドウ関数をサポートしていません。ウィンドウ関数に条件付きフィルタリングを提供できる FILTER 句は、集計関数でのみ使用できます。

論理的な意味と解決策

重要な洞察は、問題ステートメントの論理的な意味にあります。各ユーザーでは、1 つ以上の A アクティビティの後には、多くても 1 つの 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 に追加します
• アクティビティ LIKE 'A%' OR アクティビティ LIKE 'B%' の代わりに、パターン マッチング式 activity ~ '^[AB]' を使用します。
• 最初の行を選択するためのテクニックを調べるこの記事で説明されているように、各グループ内で次のようにします。 [各 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 中国語 Web サイトの他の関連記事を参照してください。