在PostgreSQL中将任意多的行转换为列

一尘不染

在PostgreSQL中将任意多的行转换为列

sql

我在Postgres中有一张表，该表旨在捕获非结构化形式的信息并重建它。从该表中导出数据时，我需要重新应用某些结构，并且正处于挣扎状态。

目前，我有一个表格的形式：

lbl |   name     |  value
----|------------|--------
1   | num        |       1
1   | colour     |   "Red"
1   | percentage |    25.0
2   | num        |       2
2   | colour     | "Green"
2   | percentage |    50.0
3   | num        |       3
3   | colour     |  "Blue"
3   | percentage |    75.0

我需要以这种形式生成一个表：

lbl | num |  colour | percentage
----|-----|---------|------------
1   | 1   | "Red"   |   25.0
2   | 2   | "Green" |   50.0
3   | 3   | "Blue"  |   75.0

我已经建立了这个查询：

SELECT lbl, 
   max(case when name = 'num' then value else '-' end) num,
   max(case when name = 'colour' then value else '-' end) colour,
   max(case when name = 'percentage' then value else '-' end) percentage
FROM example_table
GROUP BY lbl

该查询有效，但是我需要扩展它以包括任意数量的名称潜在值。我已经研究了crossfunc，但无法按预期工作。任何帮助将不胜感激。

我在这里设置了一个sqlfiddle来帮助开始工作：http
://sqlfiddle.com/#!9/8d3133/6/0

编辑：如果可以的话，我也可以使用PL / pgSQL。

阅读 298

2021-03-17

共1个答案

一尘不染

Postgres中数据透视表的主要问题是查询的结果结构（列的数量和名称）不能随所选数据而变化。一种可能的解决方案是动态创建视图，该视图由数据定义。示例函数基于表创建一个视图example_table：

create or replace function create_pivot_view()
returns void language plpgsql as $$
declare
    list text;
begin
    select string_agg(format('jdata->>%1$L "%1$s"', name), ', ')
    from (
        select distinct name
        from example_table
        ) sub
    into list;

    execute format($f$
        drop view if exists example_pivot_view;
        create view example_pivot_view as
        select lbl, %s
        from (
            select lbl, json_object_agg(name, value) jdata
            from example_table
            group by 1
            order by 1
            ) sub
        $f$, list);
end $$;

修改表后（可能在触发器中）使用该函数并查询创建的视图：

select create_pivot_view();

select *
from example_pivot_view;

 lbl | num | colour | percentage 
-----+-----+--------+------------
   1 | 1   | Red    | 25.0
   2 | 2   | Green  | 50.0
   3 | 3   | Blue   | 75.0
(3 rows)

在这里测试。

请注意，只有在将新名称添加到表中（或从中删除了一些名称）之后，才需要重新创建视图（调用函数）。如果唯一名称集不变，则可以查询视图而无需重新创建。如果对集合进行频繁修改，则创建一个临时视图将是一个更好的选择。

您可能还对JSONB字段中的扁平化聚合键/值对感兴趣？

2021-03-17