☰

ユーザーごとの最新行を列に転置する 質問する

tag-icon • tag-icon sql database postgresql pivot-table transpose
ユーザーごとの最新行を列に転置する 質問する

次の表には、ユーザーごとに複数の電子メール アドレスが記載されています。

テーブルサンプル

作成日に基づいて「最新の」3 つの電子メール アドレスを取得するには、ユーザー クエリでこれを列にフラット化する必要があります。予想される出力は次のようになります。

ユーザー名 ユーザーID メール1 メール2 メール3
メアリー 123 [メールアドレス] [メールアドレス] [メールアドレス]
ジョー 345 [メールアドレス] [ヌル] [ヌル]

ベストアンサー1

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

最初のパラメータにはドル引用符を使用しましたが、特別な意味はありません。これは、クエリ文字列内の単一引用符をエスケープするのに便利なだけであり、よくあるケースです。

詳細な説明と手順:

特に、「追加の列」の場合:

の特別な困難ここにあります:

  • キー値が不足しています。
    → 次のように置き換えますrow_number()サブクエリ内。

  • 電子メールの数は異なります。
    → 外側では最大 3 つに制限しSELECT、2 つのパラメータを使用してcrosstab()、可能なキーのリストを提供します。

注意を払うNULLS LASTの中にORDER BY。

おすすめ記事