如何将用户电子邮件历史记录行转置为 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中文网其他相关文章！