PostgreSQL 动态列名和多个输入列的交叉表查询

Question

PostgreSQL 动态列名和多个输入列的交叉表查询

postgresqlcrosstabentity-attribute-value

4

问题

我有一个 PostgreSQL 9.6 数据库，其中有一张按照 EAV 模型设计的表，包含不同类型的值。以下是一个示例摘录：

 name |arrivalTime | boolValue | intValue | floatValue | stringValue
------+------------+-----------+----------+------------+------------
 a1   |  10:00:00  |   true    |          |            |
 c3   |  10:00:00  |           |   12     |            |
 d4   |  10:00:00  |           |          |            | hello
 e5   |  15:00:00  |           |          |    45.67   |
 c3   |  15:00:00  |           |   45     |            |
 b2   |  20:00:00  |           |          |    4.567   |
 a1   |  20:00:00  |   false   |          |            |
 d4   |  22:00:00  |           |          |            |  bye
 b2   |  22:00:00  |           |          |    12.34   |

数据库中的空单元格表示null值。

现在我想要一个透视表，其中新列是arrivalTime和name的内容。对于上面的示例，它应该如下所示:

arrivalTime |  a1   |  b2   |  c3   |  d4   |  e5
------------+-------+-------+-------+-------+-------
  10:00:00  | true  |       |  12   | hello |
  15:00:00  |       |       |  45   |       | 45.67
  20:00:00  | false | 4.567 |       |       |
  22:00:00  |       | 12.34 |       |  bye  |

作为检索此结果的查询输入，我会得到一个与name匹配的模式以及指定arrivalTime范围的开始和结束时间。

原始表格的属性：

- 名称列中的条目是不稳定的，即新名称经常出现并且旧名称消失。 - 每个name和arrivalTime组合在一个值列中有一个条目，并且是唯一的。 - 每个name和arrivalTime组合在一个值列中有且仅有一个条目。

想法：

我已经考虑了一些方案：

我想应该使用函数
由于列是动态的，需要两个查询，如在PostgreSQL中动态生成列或执行动态交叉表查询所述。
使用函数生成第一个查询可能是一个好主意。

示例表格

这里是创建示例表格的SQL代码：

CREATE TABLE IF NOT EXISTS playTable (
  name TEXT NOT NULL,
  arrivalTime TIME NOT NULL,
  floatValue REAL NULL,
  intValue INT NULL,
  boolValue BOOLEAN NULL,
  stringValue TEXT NULL,
  PRIMARY KEY (name, arrivalTime),
  CONSTRAINT single_value CHECK(
    (boolValue IS NOT NULL)::INT + 
    (intValue IS NOT NULL)::INT + 
    (floatValue IS NOT NULL)::INT +
    (stringValue IS NOT NULL)::INT = 1
  )
);

并插入值：

INSERT INTO playTable ( name, arrivalTime, boolValue ) VALUES ( 'a1', '10:00:00', true );
INSERT INTO playTable ( name, arrivalTime, intValue ) VALUES ( 'c3', '10:00:00', 12 );
INSERT INTO playTable ( name, arrivalTime, stringValue ) VALUES ( 'd4', '10:00:00', 'hello' );
INSERT INTO playTable ( name, arrivalTime, floatValue ) VALUES ( 'e5', '15:00:00', 45.67 );
INSERT INTO playTable ( name, arrivalTime, intValue ) VALUES ( 'c3', '15:00:00', 45 );
INSERT INTO playTable ( name, arrivalTime, floatValue ) VALUES ( 'b2', '20:00:00', 4.567 );
INSERT INTO playTable ( name, arrivalTime, boolValue ) VALUES ( 'a1', '20:00:00', false );
INSERT INTO playTable ( name, arrivalTime, stringValue ) VALUES ( 'd4', '22:00:00', 'bye' );
INSERT INTO playTable ( name, arrivalTime, floatValue ) VALUES ( 'b2', '22:00:00', 12.34 );

数据透视表，非动态

klin提供了解决方案的起点：

SELECT *
FROM crosstab(
    $ct$
        SELECT 
            arrivalTime, name, concat(boolValue, intValue, floatValue, stringValue)
        FROM playTable
        ORDER BY 1, 2
    $ct$,
    $ct$
        SELECT DISTINCT name 
        FROM playTable
        ORDER BY 1
    $ct$) 
AS ct("arrivalTime" time, "a1" BOOLEAN, "b2" REAL, "c3" INT, "d4" TEXT, "e5" REAL);

这个解决方案缺少动态性。输入是name的LIKE模式和arrivalTime的范围（即最小值和最大值）。这使得as ct(...)的参数是动态的。

- user711270

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- klin · Accepted Answer

使用 coalesce() 函数来处理最后四列。您需要将这些列强制转换为 text 类型:

select *
from crosstab(
    $ct$
        select 
            arrivaltime, name, 
            coalesce(boolvalue::text, intvalue::text, floatvalue::text, stringvalue)
        from my_table
        order by 1, 2
    $ct$,
    $ct$
        select distinct name 
        from my_table
        order by 1
    $ct$) 
as ct("arrivalTime" time, "a1" text, "b2" text, "c3" text, "d4" text, "e5" text);

 arrivalTime |  a1   |  b2   | c3 |  d4   |  e5   
-------------+-------+-------+----+-------+-------
 10:00:00    | true  |       | 12 | hello | 
 15:00:00    |       |       | 45 |       | 45.67
 20:00:00    | false | 4.567 |    |       | 
 22:00:00    |       | 12.34 |    | bye   | 
(4 rows)

因为示例数据的格式，我使用了arrivalTime time，请将其更改为timestamp。