使用CASE和GROUP BY进行数据透视的动态替代方案

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

SELECT bar, 
   MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
   MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
   MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar

#1 楼

如果尚未安装附加模块tablefunc，则对每个数据库运行一次此命令：

CREATE EXTENSION tablefunc;

问题的答案

一个非常基本的交叉表解决方案针对您的情况：

SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

这里的特殊困难是，基表中没有类别（cat）。对于基本的1参数形式，我们可以仅提供一个以虚拟值作为类别的虚拟列。仍然会忽略该值。

这是少数情况下不需要crosstab()函数的第二个参数的情况之一，因为根据该问题的定义，所有NULL值仅出现在右侧的悬空列中。并且可以通过值确定顺序。

如果我们有一个实际的类别列，其名称确定结果中值的顺序，则需要crosstab()的2参数形式。在这里，我借助窗口函数row_number()合成了一个类别列，以crosstab()为基础：

SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

其余的几乎都是常规的。在这些密切相关的答案中查找更多解释和链接。

基础知识：如果您不熟悉crosstab()函数，请先阅读本文章！


PostgreSQL Crosstab Query

高级：



使用Tablefunc在多列上枢轴
将表和更改日志合并到PostgreSQL中的视图中
>
正确的测试设置

这就是您应该提供的测试用例的开始方式：

CREATE TEMP TABLE tbl_org (id int, feh int, bar text);
INSERT INTO tbl_org (id, feh, bar) VALUES
   (1, 10, 'A')
 , (2, 20, 'A')
 , (3,  3, 'B')
 , (4,  4, 'B')
 , (5,  5, 'C')
 , (6,  6, 'D')
 , (7,  7, 'D')
 , (8,  8, 'D');

动态交叉表？

还不是很动态，正如@Clodoaldo所说。使用plpgsql很难实现动态返回类型。但是有很多方法可以解决-但有一些局限性。

为了不使其余内容变得更加复杂，我以一个更简单的测试用例进行了演示：

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

调用：

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);

返回值：

 row_name | val1 | val2 | val3
----------+------+------+------
 A        | 10   | 20   |
 B        |  3   |  4   |
 C        |  5   |      |
 D        |  6   |  7   |  8

tablefunc模块的内置功能

tablefunc模块为通用crosstab()调用提供了简单的基础结构，而没有提供列定义列表。用C编写的许多功能（通常非常快）：

crosstabN()

crosstab1()-crosstab4()是预定义的。一点要点：他们要求并返回所有text。因此，我们需要转换我们的integer值。但这简化了调用：

SELECT * FROM crosstab4('SELECT row_name, attrib, val::text  -- cast!
                         FROM tbl ORDER BY 1,2')

结果：

 row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
 A        | 10         | 20         |            |
 B        | 3          | 4          |            |
 C        | 5          |            |            |
 D        | 6          | 7          | 8          |

自定义crosstab()函数

对于更多列或其他数据类型，我们创建自己的复合类型和函数（一次）。
类型：

CREATE TYPE tablefunc_crosstab_int_5 AS (
  row_name text, val1 int, val2 int, val3 int, val4 int, val5 int);

功能：

CREATE OR REPLACE FUNCTION crosstab_int_5(text)
  RETURNS SETOF tablefunc_crosstab_int_5
AS '$libdir/tablefunc', 'crosstab' LANGUAGE c STABLE STRICT;

呼叫：

SELECT * FROM crosstab_int_5('SELECT row_name, attrib, val   -- no cast!
                              FROM tbl ORDER BY 1,2');

结果：

 row_name | val1 | val2 | val3 | val4 | val5
----------+------+------+------+------+------
 A        |   10 |   20 |      |      |
 B        |    3 |    4 |      |      |
 C        |    5 |      |      |      |
 D        |    6 |    7 |    8 |      |

所有函数都具有一个多态动态函数

这超出了tablefunc模块所涵盖的范围。
要使返回类型动态化，我使用了一种具有技术的多态类型在相关答案中进行了详细说明：


重构PL / pgSQL函数以返回各种SELECT查询的输出



1-参数形式：

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format('SELECT * FROM crosstab(%L) t(%s)'
                , _qry
                , string_agg(quote_ident(attname) || ' ' || atttypid::regtype
                           , ', ' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

2参数形式的此变型的过载：

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _cat_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format('SELECT * FROM crosstab(%L, %L) t(%s)'
                , _qry, _cat_qry
                , string_agg(quote_ident(attname) || ' ' || atttypid::regtype
                           , ', ' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

pg_typeof(_rowtype)::text::regclass：为每个用户定义的复合类型定义了一个行类型，以便属性（列）在系统目录pg_attribute中列出。获得它的捷径：将注册类型（regtype）转换为text并将此text转换为regclass。

一次创建复合类型：

您需要每次定义一次您将要使用的返回类型：

CREATE TYPE tablefunc_crosstab_int_3 AS (
    row_name text, val1 int, val2 int, val3 int);

CREATE TYPE tablefunc_crosstab_int_4 AS (
    row_name text, val1 int, val2 int, val3 int, val4 int);

...

对于临时调用，您还可以只创建具有相同（临时）效果的临时表：

CREATE TEMP TABLE temp_xtype7 AS (
    row_name text, x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int);

，或使用现有表，视图或实例化视图的类型（如果可用）。

调用

在上一行使用类型：

1参数形式（无缺失值）：

SELECT * FROM crosstab_n(
   'SELECT row_name, attrib, val FROM tbl ORDER BY 1,2'
 , NULL::tablefunc_crosstab_int_3);

2参数形式（某些值可能会丢失）：

SELECT * FROM crosstab_n(
   'SELECT row_name, attrib, val FROM tbl ORDER BY 1'
 , $$VALUES ('val1'), ('val2'), ('val3')$$
 , NULL::tablefunc_crosstab_int_3);

此函数适用于所有返回类型，而crosstabN()模块提供的tablefunc框架每个函数都需要一个单独的功能。
如果您按照上面演示的顺序命名类型，则只需替换粗体数字即可。要在基表中找到类别的最大数量：

SELECT max(count(*)) OVER () FROM tbl  -- returns 3
GROUP  BY row_name
LIMIT  1;

如果您要使用单个列，那么动态就差不多了。由@Clocoaldo演示的数组或简单的文本表示形式或包装在json或hstore这样的文档类型中的结果可以动态地用于任意多个类别。

免责声明：
它总是潜在危险当用户输入转换为代码时。确保不能将其用于SQL注入。不要直接接受不可信用户的输入。

请提出原始问题：

SELECT * FROM crosstab_n('SELECT bar, 1, feh FROM tbl_org ORDER BY 1,2'
                       , NULL::tablefunc_crosstab_int_3);

#2 楼

尽管这是一个老问题，但我想添加另一个解决方案，该解决方案是PostgreSQL最近的改进所致。该解决方案实现了从动态数据集中返回结构化结果的目标，而完全不使用交叉表功能。换句话说，这是重新审查无意和隐含的假设的一个很好的例子，这些假设使我们无法找到解决旧问题的新方法。 ;）

为了说明，您要求一种具有以下结构的数据转置方法：

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

以这种格式：

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

常规解决方案是一种创建动态交叉表查询的聪明（而且知识渊博）的方法，在Erwin Brandstetter的答案中对此进行了详细介绍。

但是，如果您的特定用例足够灵活，可以接受稍有不同的结果格式，则可以使用另一种解决方案来精美地处理动态数据透视。我从这里学到的这项技术


带有JSON和PostgreSQL的动态数据透视表

使用PostgreSQL的新jsonb_object_agg函数以表格形式动态构建数据透视表JSON对象。

我将使用Brandstetter先生的“简单测试用例”进行说明：

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

使用jsonb_object_agg函数，我们可以用这种精巧的外观创建所需的透视结果集：

SELECT
  row_name AS bar,
  json_object_agg(attrib, val) AS data
FROM tbl
GROUP BY row_name
ORDER BY row_name;

哪个输出：

 bar |                  data                  
-----+----------------------------------------
 A   | { "val1" : 10, "val2" : 20 }
 B   | { "val1" : 3, "val2" : 4 }
 C   | { "val1" : 5 }
 D   | { "val3" : 8, "val1" : 6, "val2" : 7 }

您可以看到，此功能通过在示例数据中的attrib和value列的JSON对象中创建键/值对而起作用，所有这些列均按row_name分组。

尽管此结果集看起来显然有所不同，但我认为它实际上可以满足许多（如果不是大多数）现实世界的用例，尤其是那些数据需要动态生成数据点或父应用程序使用了所得数据的情况（例如，需要重新格式化以便在http响应中传输）。

这种方法的优点：


更干净的语法。我想每个人都同意，即使是最基本的交叉表示例，该方法的语法也更加简洁明了。
完全动态。无需预先指定有关基础数据的信息。列名及其数据类型都不需要提前知道。
处理大量列。由于数据透视表保存为单个jsonb列，因此您将不会遇到PostgreSQL的列限制（我相信≤1,600列）的情况。仍然有一个限制，但是我认为它与文本字段相同：每个JSON对象创建1 GB（如果我错了，请纠正我）。有很多键/值对！
简化了数据处理。我相信在数据库中创建JSON数据将简化（并可能加快）父应用程序中的数据转换过程。 （您会注意到，我们的示例测试用例中的整数数据已正确存储在生成的JSON对象中。PostgreSQL通过根据JSON规范自动将其固有数据类型转换为JSON来进行处理。）这将有效地消除需求手动转换传递给父应用程序的数据：可以将所有数据都委派给应用程序的本机JSON解析器。

差异（以及可能的缺点）：


看起来不一样不可否认，这种方法的结果看起来有所不同。 JSON对象不如交叉表结果集那么漂亮。但是，差异纯粹是表面上的。会产生相同的信息，并且格式可能更适合父应用程序使用。
缺少键。交叉表方法中的缺失值用空值填充，而JSON对象只是缺少适用的键。您是否需要为自己决定用例，这是可以接受的折衷方案。在我看来，在PostgreSQL中解决此问题的任何尝试都会使该过程变得非常复杂，并可能涉及其他查询形式的自省。
不保留键顺序。我不知道这是否可以在PostgreSQL中解决，但是这个问题也主要是表面上的，因为任何父应用程序要么不太可能依赖键顺序，要么能够通过其他方式确定正确的键顺序。最坏的情况可能只需要对数据库进行附加查询。

结论

我很好奇听到其他人（尤其是@ErwinBrandstetter的观点）对这种方法的意见，尤其是与性能有关。当我在安德鲁·本德（Andrew Bender）的博客上发现这种方法时，就好像被脑袋撞了一样。在PostrgeSQL中采用全新方法解决难题的好方法。它完美地解决了我的用例，我相信它也同样可以为其他许多人服务。

#3 楼

这是完成@Damian的好答案。在9.6的便捷json_object_agg函数之前，我已经在其他答案中建议了JSON方法。使用以前的工具集只需要做更多的工作。

引用的两个可能的缺点实际上并不是。如有必要，可以对随机密钥顺序进行简单校正。缺少的键（如果相关）将花费几乎很少的代码来解决：

select
    row_name as bar,
    json_object_agg(attrib, val order by attrib) as data
from
    tbl
    right join
    (
        (select distinct row_name from tbl) a
        cross join
        (select distinct attrib from tbl) b
    ) c using (row_name, attrib)
group by row_name
order by row_name
;
 bar |                     data                     
-----+----------------------------------------------
 a   | { "val1" : 10, "val2" : 20, "val3" : null }
 b   | { "val1" : 3, "val2" : 4, "val3" : null }
 c   | { "val1" : 5, "val2" : null, "val3" : null }
 d   | { "val1" : 6, "val2" : 7, "val3" : 8 }

对于理解JSON的最终查询使用者而言，没有任何缺点。唯一的问题是它不能用作表源。

#4 楼

在您的情况下，我认为数组是好的。 SQL小提琴

select
    bar,
    feh || array_fill(null::int, array[c - array_length(feh, 1)]) feh
from
    (
        select bar, array_agg(feh) feh
        from foo
        group by bar
    ) s
    cross join (
        select count(*)::int c
        from foo
        group by bar
        order by c desc limit 1
    ) c(c)
;
 bar |      feh      
-----+---------------
 A   | {10,20,NULL}
 B   | {3,4,NULL}
 C   | {5,NULL,NULL}
 D   | {6,7,8}

#5 楼

很抱歉过去返回，但是解决方案“动态交叉表”返回错误的结果表。因此，valN值错误地“左对齐”，并且它们不对应于列名。当输入表的值中有“孔”时，例如“ C”具有val1和val3，但没有val2。这将产生错误：val3值将在最终表的val2列（即下一个空闲列）中变化。

CREATE TEMP TABLE tbl (row_name text, attrib text, val int); 
INSERT INTO tbl (row_name, attrib, val) VALUES ('C', 'val1', 5) ('C', 'val3', 7);

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl 
ORDER BY 1,2') AS ct (row_name text, val1 int, val2 int, val3 int);

row_name|val1|val2|val3
 C      |   5|  7 |

为了返回正确的单元格，右列中的“空洞”，交叉表查询需要在交叉表中进行第二次SELECT，例如"crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2', 'select distinct row_name from tbl order by 1')"