ユーザーの電子メール履歴の行を PostgreSQL の列に転置するにはどうすればよいですか?

ユーザーのメール履歴の再構築: PostgreSQL の行から列へ

各ユーザーの複数の電子メール アドレスを格納するテーブルを想像してください。 この記事では、この行ベースのデータを列形式に効率的に変換する方法、特に最新の電子メール アドレスを個別の列として配置する方法を説明します。 これは、PostgreSQL の tablefunc モジュールと強力な crosstab() 関数を使用して実現します。

crosstab() ソリューション

crosstab() モジュール内の tablefunc 関数は、ユーザーごとに行を列に転置するのに最適です。次のクエリはその応用例を示しています:

<code class="language-sql">SELECT *
FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn <= 3
   $$,
   $$VALUES (1),(2),(3)$$
) AS ct (user_id INT, user_name TEXT, email_1 TEXT, email_2 TEXT, email_3 TEXT);</code>

クエリコンポーネントを理解する

• crosstab() の最初の引数はサブクエリです。このサブクエリは、ユーザー ID、ユーザー名、行番号 (rn)、および電子メール アドレスを取得します。
• row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) は、作成日順 (最新のものから順) に、各ユーザーの各電子メール アドレスに一意のランク (rn) を割り当てます。
• WHERE 句 (WHERE rn <= 3) は、結果をユーザーあたりの最新の 3 つの電子メール アドレスに制限します。
• crosstab() の 2 番目の引数 ($$VALUES (1),(2),(3)$$) は、新しい列 (電子メール アドレスのランクを表す) の潜在的な値を指定します。

潜在的な課題への対処

元のテーブルには電子メール アドレスごとに個別のキーがないため、row_number() を使用して代理キーを生成する必要がありました。 さらに、ユーザーごとの電子メールの数が可変であるため、外側の SELECT ステートメントで出力を 3 列に制限し、2 番目の crosstab() パラメーターで可能なキー値の範囲を定義する必要がありました。

このアプローチでは、ユーザーごとの最新の電子メール アドレスが効果的に個別の列に変換され、ユーザーの電子メール履歴がより明確に整理されたビューが表示されます。

以上がユーザーの電子メール履歴の行を PostgreSQL の列に転置するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。