在PostgreSQL中将任意多的行转换为列 [英] Turning arbitrarily many rows into columns in PostgreSQL

问题描述

我在Postgres中有一个表，该表旨在捕获非结构化形式的信息并重建它。从该表中导出数据时，我需要重新应用某些结构，并且很费劲。

I have a table in Postgres that was designed to capture information in unstructured form and rebuild it. I need to re-apply some structure when exporting data from that table and am struggling.

当前，我有一个表格，其形式为：

Currently, I have a table of the form:

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

我需要生成以下形式的表：

And I need to generate a table in this form:

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，但无法按预期工作。任何帮助将不胜感激。

The query works but I need to expand it to include an arbitrary number of potential values for name. I have investigated crossfunc but was unable to get it to work as I intended. Any help would be greatly appreciated.

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

I've set up an sqlfiddle here to help kick things off: http://sqlfiddle.com/#!9/8d3133/6/0

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

edit: I can use PL/pgSQL also if that makes it possible.

推荐答案

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

The main problem with pivot tables in Postgres is that the result structure (number and names of columns) of a query cannot vary depending on the selected data. One of the possible solutions is to dynamically create a view, which structure is defined by the data. The example function creates a view based on the table 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 $$;

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

Use the function after the table is modified (maybe in a trigger) and query the created view:

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)

在此处进行测试。

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

Note, that it's necessary to recreate a view (call the function) only after new name is added to the table (or some name is removed from it). If the set of distinct names doesn't change you can query the view without recreating it. If the set is modified frequently creating a temporary view would be a better option.

您可能还对从JSONB字段中平整的聚合键/值对？

这篇关于在PostgreSQL中将任意多的行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！