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

Question

我有一个看起来像这样的表：

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

这是一种非常灵活的方法，如果要创建很多新的列，则会变得笨拙。我想知道是否CASE可以使语句更好，以使此查询更具动态性？另外，我很乐意看到其他实现此目的的方法。

Answer 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值只出现在这一问题的界定，晃来晃去的列到右边。并且顺序可以由 值 确定。

如果我们有一个实际的 类别 列，其名称确定结果中值的顺序，则需要使用 2参数形式
的crosstab()。在这里，我借助window函数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?

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

正确的测试设置

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

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（通常非常快）：

[**crosstab _N_ ()**](http://www.postgresql.org/docs/current/interactive/tablefunc.html#AEN144256)

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

这个 函数 适用于所有返回类型，而模块提供的框架需要为每种返回函数使用单独的函数。
如果像上面演示的那样按顺序命名了类型，则只需替换粗体数字即可。要在基表中找到最大类别数：crosstab _N_ ()``tablefunc

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

如果您需要单独的列，那么它的动态性就差不多。@Clocoaldo演示)数组，简单的文本表示形式或包装在文档类型中的结果（例如json或）hstore可以动态地用于任何数量的类别。

尽管这是一个老问题，但我想添加另一个解决方案，该解决方案是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对象的形式动态构造数据透视表。

我将使用布兰德斯特先生的“简单测试用例”进行说明：

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中的难题的绝妙方法。它完美地解决了我的用例，我相信它也将同样为许多其他用户服务。

免责声明：将
用户输入转换为代码时，始终有潜在的危险。确保不能将其用于SQL注入。不接受（直接）不受信任用户的输入。

要求原始问题：

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