用实例和组动态替代枢轴

Question

用实例和组动态替代枢轴我有张桌子看起来像这样：id    feh    bar1     10     A2     20     A3      3     B4      4     B5      5     C6      6     D7      7     D8      8     D我想让它看起来像这样bar  val1   val2   val3A     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" ) abcGROUP 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()..这里，我借助窗口函数合成了一个类别列。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交叉表查询

高级：

• 使用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_5AS '$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 3GROUP  BY row_name
LIMIT  1;

如果你想的话，这几乎是动态的个别列..数组由@Clocoldo演示或简单的文本表示形式或封装在文档类型中的结果，如json或hstore可以动态地处理任意数量的类别。

免责声明：
当用户输入转换为代码时，总是存在潜在的危险。确保这不能用于SQL注入。不要接受来自不可信用户的输入(直接)。

要求提出原始问题：

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

Answer 2

我已经在其他答案中建议了JSON方法，在9.6的方便之前。json_object_agg功能。使用以前的工具集只需要更多的工作。

引用的两个可能的缺点实际上不是。如果有必要，对随机密钥顺序进行小的修正。丢失的键(如果相关的话)需要处理几乎微不足道的代码：

select
    row_name as bar,
    json_object_agg(attrib, val order by attrib) as datafrom
    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_nameorder 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的最后一个查询使用者来说，没有缺点。唯一的原因是它不能作为表源使用。