如何將使用者電子郵件歷史記錄行轉置為 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) 將結果限制為每位使用者最近的三個電子郵件地址。
• crosstab() 的第二個參數 ($$VALUES (1),(2),(3)$$) 指定新欄位的潛在值（代表電子郵件地址排名）。

應對潛在挑戰

原始表缺乏每個電子郵件地址的不同鍵，因此需要使用 row_number() 來產生代理鍵。 此外，每個使用者的電子郵件數量可變，需要將外部 SELECT 語句中的輸出限制為三列，並在第二個 crosstab() 參數中定義可能的鍵值範圍。

這種方法有效地將每個使用者的最新電子郵件地址轉換為單獨的列，從而呈現出更清晰、更有條理的使用者電子郵件歷史記錄視圖。

以上是如何將使用者電子郵件歷史記錄行轉置為 PostgreSQL 中的欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！