时间戳

首页 > 解决方案 > 在 Postgres 中使用列名进行透视

sql - 在 Postgres 中使用列名进行透视

问题描述

我有下表tbl：

column1 | column2  | column 3
-----------------------------------
1       | 'value1' |  3
2       | 'value2' |  4

如何使用列名进行“旋转”以产生如下输出：

column1 |      1   |   2
column2 | 'value1' |'value2'
column3 |      3   |   4

标签： sqlpostgresqlpivot

解决方案

如前所述，问题中未定义数据类型的问题。

如果您对所有结果列都可以确定类型text（每种数据类型都可以转换为text），则可以使用以下之一：

纯 SQL

WITH cte AS (
   SELECT nu.*
   FROM   tbl t
   , LATERAL (
      VALUES
        (1, t.column1::text)
      , (2, t.column2)
      , (3, t.column3::text)
      ) nu(rn, c)
   )
SELECT *
FROM   (TABLE cte OFFSET 0 LIMIT 3) c1
JOIN   (TABLE cte OFFSET 3 LIMIT 3) c2 USING (rn);

与有用的列名相同：

WITH cte AS (
   SELECT nu.*
   FROM   tbl t
   , LATERAL (
      VALUES
        ('column1', t.column1::text)
      , ('column2', t.column2)
      , ('column3', t.column3::text)
      ) nu(rn, c)
   )
SELECT * FROM (
   SELECT *
   FROM   (TABLE cte OFFSET 0 LIMIT 3) c1
   JOIN   (TABLE cte OFFSET 3 LIMIT 3) c2 USING (rn)
   ) t (key, row1, row2);

适用于任何现代版本的 Postgres。
SQL 字符串必须适应行数和列数。请参阅下面的小提琴！

使用文档类型作为垫脚石

使代码更短。
对于多行多列，SQL 解决方案的性能可能会更好地扩展，因为中间派生表更小。（线程受到限制，因为 Postgres 中的表列不能超过 ~ 1600 个。）

由于一切都转换为text反正，hstore似乎最有效。看：

SELECT key
     , arr[1] AS row1
     , arr[2] AS row2
FROM  (
   SELECT x.key, array_agg(x.value) AS arr
   FROM   tbl t, each(hstore(t)) x
   GROUP  BY 1
   ) sub
ORDER  BY 1;

从技术上讲，我们必须在 in 时强制执行正确的排序顺序array_agg()，但这应该在没有明确的情况下工作ORDER BY。绝对确定您可以添加一个：array_agg(x.value ORDER BY t.ctid) 用于ctid缺乏信息。

您可以对（Postgres 9.3+）中的 JSON 函数执行相同的操作。只需替换each(hstore(t)为json_each_text(row_to_json(t). 其余的都是一样的。

这些小提琴演示了如何扩展每个查询：

具有 2 行 3 列的原始示例：
db<>fiddle here

扩展到 3 行 4 列：
db<>fiddle here

推荐阅读