Postgresql:以下选择是否合理/可能？

Question

一位客户希望我创建一个数据集，如下所示。我不知道这是否可能或合乎逻辑。

我有表parent:

id       name 
-------  -------
1        parent1
2        parent2
3        parent3

和表子:

id       parent   name     age
-------  -------  -------  -------
1        1        child1   2
2        1        child2   4
3        1        child3   7
4        2        child12  5

我想进行选择查询以返回 parent1 的以下内容:

parent   child1   child2   child3
-------  -------  -------  -------
parent1  2        4        7

当然，为 parent2 返回以下内容:

parent   child12
-------  -------
parent2  5      

有人对此有解决方案或创意评论吗？

Answer 1

您可以使用 tablefunc PostgreSQL extension .

create extension tablefunc;

我将表定义为:

create table parent (id integer, name text);
create table child (id integer, parent integer, name text, age integer);

要创建数据透视表，需要指定两个查询，首先是数据查询并返回(key1，key2，value)的元组:

SELECT P.name, C.name, C.age from parent P, child C where P.id = C.parent and C.parent = 1

第二个定义列名和类型:

select C.name from child C where C.parent = 1

然后使用交叉表功能:

select * from crosstab(
    $$ SELECT P.name, C.name, C.age from parent P, child C where P.id = C.parent and C.parent = 1 $$,
    $$ select C.name from child C where C.parent = 1 $$
) AS (name text, child1 integer, child2 integer, child3 integer);
  name   | child1 | child2 | child3 
---------+--------+--------+--------
 parent1 |      2 |      4 |      7
(1 row)

注意动态生成这个有点棘手，因为您需要指定列类型(交叉表函数之后的 AS 部分)，这意味着您需要进行另一个查询以了解将有多少个不同的标签(在本例中为 child1、child2 和 child3)。

但是，虽然这个查询是可能的，但它可能不是解决您的问题的最佳案例。我在进行分析时将其用作助手，即便如此，在 Excel/LibreCalc 中导出 (key1, key2, value) 表和数据透视表通常更容易。 Crosstab 也有只有两个键的限制，其中 Excel 支持更多字段，并且可以快速轻松地拖动它们。