PostgreSQL 9.3：动态数据透视表 [英] PostgreSQL 9.3: Dynamic pivot table

问题描述

我有一个称为矩阵的表，其中包含两列，分别为 cola 和 colb ，如所示的
下方：

I have a table called as matrix which contains two columns namely cola and colb as shown below:

表：矩阵

create table matrix
(
cola varchar(10),
colb varchar(10)
);

插入行：

insert into matrix values('A1','B1'),('A2','B2'),('A3','B3'),('A4','B4'),
             ('A5','B5'),('A6','B6'),('A7','B7'),('A8','B8'),
             ('A9','B9'),('A10','B10'),('A11','B11'),('A12','B12'),
             ('A13','B13'),('A14','B14'),('A15','B15'),('A16','B16'),
             ('A17','B17'),('A18','B18'),('A19','B19'),('A20','B20'),
             ('A21','B21'),('A22','B22'),('A23','B23'),('A24','B24'),
             ('A25','B25'),('A26','B26'),('A27','B27'),('A28','B28'),
             ('A29','B29'),('A30','B30');

注意：我想以矩阵形式显示结果，计算哪些列属于彼此，并为每个列分配矩阵中的值。我仅以
为例，添加了30条记录，但也可能有成千上万条记录。所以我需要
为此准备一个动态数据透视表。预期结果如下图所示。

Note: I want to show the result in the form of matrix and count which columns belongs to each others and assign the values in the matrix for each column. I have just added 30 records for just example but there may be thousands of records also. So I need to prepare a dynamic pivot table for that. The expected result as shown below.

预期结果：

      A1        A2      A3      A4      A5      A6 ................ A30
      ------------------------------------------------------------------
B1 |  1         0       0       0       0       0                    0  
   |    
B2 |  0         1       0       0       0       0                    0
   |
B3 |  0         0       1       0       0       0                    0 
   |
B4 |  0         0       0       1       0       0                    0
   | 
B5 |  0         0       0       0       1       0                    0 
   | 
B6 |  0         0       0       0       0       1                    0
.  |
.  |
.  |
.  |
B30|  0         0       0       0       0        0                   1 

推荐答案

您可以执行此操作与来自附加模块crosstab（） noreferrer> tablefunc ：

You can do this with crosstab() from the additional module tablefunc:

SELECT b
     , COALESCE(a1, 0) AS "A1"
     , COALESCE(a2, 0) AS "A2"
     , COALESCE(a3, 0) AS "A3"
     , ... -- all the way up to "A30"
FROM   crosstab(
         'SELECT colb, cola, 1 AS val FROM matrix
          ORDER  BY 1,2'
        , $$SELECT 'A'::text || g FROM generate_series(1,30) g$$
       ) AS t (b text
             , a1  int, a2  int, a3  int, a4  int, a5  int, a6  int
             , a7  int, a8  int, a9  int, a10 int, a11 int, a12 int
             , a13 int, a14 int, a15 int, a16 int, a17 int, a18 int
             , a19 int, a20 int, a21 int, a22 int, a23 int, a24 int
             , a25 int, a26 int, a27 int, a28 int, a29 int, a30 int);

如果 NULL 而不是 0 也可以，在外部查询中只需 SELECT * 。

详细说明：

If NULL instead of 0 works, too, it can be just SELECT * in the outer query.
Detailed explanation:

• PostgreSQL交叉表查询

• PostgreSQL Crosstab Query

此处的特殊难点是：没有实际的值。因此，在最后一列中添加 1 AS值。

The special "difficulty" here: no actual "value". So add 1 AS val as last column.

在单个查询中不可能进行完全动态的查询（结果类型未知）。您需要两个查询。首先动态地构建类似于上述的语句，然后执行它。详细信息：

A completely dynamic query (with unknown result type) is not possible in a single query. You need two queries. First build a statement like the above dynamically, then execute it. Details:

• 使用单个SQL语句选择多个max（）值

PostgreSQL将列转换为行吗？换位？

动态生成PostgreSQL中交叉表的列

使用CASE和GROUP BY进行旋转的动态替代方案

如果您超出最大列数（1600），经典的交叉表是不可能的，因为结果无法用单独的列表示。 （而且，人眼几乎无法读取具有这么多列的表）

If you exceed the maximum number of columns (1600), a classic crosstab is impossible, because the result cannot be represented with individual columns. (Also, human eyes would hardly be able to read a table with that many columns)

数组或文档类型，例如 hstore 或 jsonb 是替代方法。这是一个数组的解决方案：

Arrays or document types like hstore or jsonb are the alternative. Here is a solution with arrays:

SELECT colb, array_agg(cola) AS colas
FROM  (
   SELECT colb, right(colb, -1)::int AS sortb
        , CASE WHEN m.cola IS NULL THEN 0 ELSE 1 END AS cola
   FROM        (SELECT DISTINCT colb FROM matrix) b
   CROSS  JOIN (SELECT DISTINCT cola FROM matrix) a
   LEFT   JOIN matrix m USING (colb, cola)
   ORDER  BY sortb, right(cola, -1)::int 
   ) sub
GROUP  BY 1, sortb
ORDER  BY sortb;

• 使用以下方法构建完整的值网格：

  • Build the complete grid of values with:

                (SELECT DISTINCT colb FROM matrix) b
    CROSS  JOIN (SELECT DISTINCT cola FROM matrix) a
    

  
  

  • 左联接现有组合，按数字部分的顺序排列名称并聚合成数组。

  • LEFT JOIN existing combinations, order by the numeric part of the name and aggregate into arrays.

    
    
    • right（colb，-1）:: int 将'A3'中的前导字符修剪掉并将数字强制转换为整数，以便我们获得正确的排序顺序。
    
    
    • right(colb, -1)::int trims the leading character from 'A3' and casts the digits to integer so we get a proper sort order.

    如果您只想要一个 0 和 1 ，其中 x = y ，这可以更便宜：

    If you just want a table of 0 an 1 where x = y, this can be had cheaper:

    SELECT x, array_agg((x = y)::int) AS y_arr
    FROM   generate_series(1,10) x
         , generate_series(1,10) y
    GROUP  BY 1
    ORDER  BY 1;
    

    SQL小提琴 以您在注释中提供的小提琴为基础。

    SQL Fiddle building on the one you provided in the comments.

    请注意sqlfiddle.com当前有一个错误，该错误会杀死数组值的显示。因此，我将其投射到文本进行解决。

    Note that sqlfiddle.com currently has a bug that kills the display of array values. So I cast to text there to work around it.

    这篇关于PostgreSQL 9.3：动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！