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

行データを列に変換: ユーザーの最新の電子メール アドレスを表示します

質問:

テーブルにはユーザーごとに複数の電子メール アドレスがあり、作成日に基づいて各ユーザーの最新の 3 つの電子メール アドレスが表示されるように列に平坦化したいと考えています。

期待される出力:

user_name	user_id	email1	email2	email3
Mary	123	[email protected]	[email protected]	[email protected]
Joe	345	[email protected]	NULL	NULL

解決策:

これを行うには、PostgreSQL の tablefunc モジュールのcrosstab() 関数を利用できます。

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
    ORDER BY user_id, rn
    $$,
    'VALUES (1),(2),(3)'
) AS ct (user_id integer, user_name text, email1 text, email2 text, email3 text);

説明:

• サブクエリは、各ユーザーのユーザー ID、ユーザー名、電子メール アドレス、および序数を取得します。 row_number() 関数は、各ユーザーのメールボックスの順序を決定するために使用されます。NULLS LAST により、NULL 値が最新の値とみなされます。
• crosstab() 関数は、行番号をキーとして使用して、メールボックスを列に転置します。 crosstab() の 2 番目のパラメータ「VALUES (1),(2),(3)」は、作成する 3 つの列を指定します。
• 最後の SELECT ステートメントは転置されたデータを返します。各ユーザーの最新の 3 つのメールボックスが email1、email2、および email3 列に表示されます。

注:

• 各ユーザーが異なる数のメールボックスを持っている場合は、「VALUES (1),(2),(3)」をすべての可能なキー値のリストに置き換えることができます。
• NULL 値が最新の値として扱われるように、ORDER BY 句で NULLS LAST を使用することに注意してください。

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