Postgres - 将行转置为列 [英] Postgres - Transpose Rows to Columns

问题描述

我有下表，其中列出了每个用户的多个电子邮件地址.

I have the following table, which gives multiple email addresses for each user.

我需要将其展平为用户查询中的列.根据创建日期向我提供最新"的 3 个电子邮件地址.

I need to flatten this out to columns on a user query. To give me the "newest" 3 email addresses based on the creation date.

user.name | user.id | email1          | email2           | email3**

Mary      | 123     | [e-mail]  | [e-mail] | [e-mail]

Joe       | 345     | [e-mail]   | [NULL]           | [NULL]

推荐答案

Use crosstab() 来自 tablefunc 模块.

Use crosstab() from the tablefunc module.

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);

我对第一个参数使用了美元引用，没有特殊含义.在查询字符串中转义单引号很方便，这是一个常见的情况:

I used dollar-quoting for the first parameter, which has no special meaning. It's just convenient to escape single quotes in the query string, which is a common case:

• 在 PostgreSQL 中插入带单引号的文本

详细说明和说明:

• PostgreSQL 交叉表查询

特别是对于额外的列":

And in particular, for "extra columns":

• 使用 Tablefunc 在多列上透视

这里的特殊困难是:

• 缺少键名.
  -->我们用 row_number() 代替子查询.

• The lack of key names.
  --> We substitute with row_number() in a subquery.

不同数量的电子邮件.
-->我们限制为最大值.外部 SELECT
中的三个并使用带有两个参数的 crosstab()，提供可能的键列表.

The varying number of emails.
--> We limit to a max. of three in the outer SELECT
and use crosstab() with two parameters, providing a list of possible keys.

注意ORDER BY中的NULLS LAST.

这篇关于Postgres - 将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！