Postgres 动态列标题(来自另一个表) [英] Postgres dynamic column headers (from another table)

问题描述

举个简单的例子:

表A:

1. 鸡蛋
2. 面包
3. 奶酪

B 桌(吃的时候):

1. 鸡蛋 |日期
2. 面包|日期
3. 鸡蛋 |日期
4. 奶酪 |日期
5. 面包|日期

对于统计目的，我需要按日期对每种食物类型进行统计，如下所示:

For statistics purpouses, i need to have statistics per date per food type in a look like this:

表格统计:

             egg   |   bread   |   cheese

date1         2          1            0    

date2         6          4            2

date3         2          0            0

我需要报表中的列标题是动态的(如果添加了新的，它应该会自动出现).

I need the column headers to be dynamic in the report (if new ones are added, it should automatically appear).

知道如何在 postgres 中制作这个吗?

Any idea how to make this in postgres?

谢谢.

推荐答案

基于答案 Postgres 动态列标题(来自另一个表)(Eric Vallabh Minikel 的作品)我改进了该功能，使其更加灵活方便.我认为它也可能对其他人有用，特别是因为它只依赖于 pg/plsql 并且不需要像 erics 工作的其他派生(即 plpython)那样安装扩展.使用 9.3.5 进行测试，但也应该至少可以运行到 9.2.

based on answer Postgres dynamic column headers (from another table) (the work of Eric Vallabh Minikel) i improved the function to be more flexible and convenient. I think it might be useful for others too, especially as it only relies on pg/plsql and does not need installation of extentions as other derivations of erics work (i.e. plpython) do. Testet with 9.3.5 but should also work at least down to 9.2.

改进:

• 处理包含空格的旋转列名
• 处理多行标题列
• 处理枢轴单元和非聚合枢轴单元中的聚合函数(最后一个参数可能是sum(cellval)"以及cellval"，以防底层表/视图已经进行聚合)
• 自动检测数据透视单元格的数据类型(不再需要将其传递给函数)

用途:

SELECT get_crosstab_statement('table_to_pivot', ARRAY['rowname' [, <other_row_header_columns_as_well>], 'colname', 'max(cellval)');

代码:

CREATE OR REPLACE FUNCTION get_crosstab_statement(tablename character varying, row_header_columns character varying[], pivot_headers_column character varying, pivot_values character varying)
  RETURNS character varying AS
$BODY$
--returns the sql statement to use for pivoting the table
--based on: http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
--based on: https://stackoverflow.com/questions/4104508/postgres-dynamic-column-headers-from-another-table
--based on: http://www.postgresonline.com/journal/categories/24-tablefunc

DECLARE
    arrayname CONSTANT character varying := 'r';

    row_headers_simple character varying;
    row_headers_quoted character varying;
    row_headers_castdown character varying;
    row_headers_castup character varying;
    row_header_count smallint;
    row_header record;

    pivot_values_columnname character varying;
    pivot_values_datatype character varying;
    pivot_headers_definition character varying;
    pivot_headers_simple character varying;

    sql_row_headers character varying;
    sql_pivot_headers character varying;
    sql_crosstab_result character varying;

BEGIN
    -- 1. create row header definitions
    row_headers_simple   :=         array_to_string(row_header_columns, ', ');
    row_headers_quoted   := '''' || array_to_string(row_header_columns, ''', ''') || '''';
    row_headers_castdown :=         array_to_string(row_header_columns, '::text, ') || '::text';

    row_header_count     := 0;
    sql_row_headers      := 'SELECT column_name, data_type
                            FROM information_schema.columns
                            WHERE table_name = ''' || tablename || ''' AND column_name IN (' || row_headers_quoted || ')';
    FOR row_header IN EXECUTE sql_row_headers LOOP
        row_header_count := row_header_count + 1;
        row_headers_castup := COALESCE(row_headers_castup || ', ', '') || arrayname || '[' || row_header_count || ']::' || row_header.data_type || ' AS ' || row_header.column_name;
    END LOOP;

    -- 2. retrieve basic column name in case an aggregate function is used
    SELECT coalesce(substring(pivot_values FROM '.*\((.*)\)'), pivot_values)
    INTO pivot_values_columnname;

    -- 3. retrieve pivot values datatype
    SELECT data_type
    FROM information_schema.columns
    WHERE table_name = tablename AND column_name = pivot_values_columnname
    INTO pivot_values_datatype;

    -- 4. retrieve list of pivot column names.
    sql_pivot_headers := 'SELECT string_agg(DISTINCT quote_ident(' || pivot_headers_column || '), '', '' ORDER BY quote_ident(' || pivot_headers_column || ')) as names, string_agg(DISTINCT quote_ident(' || pivot_headers_column || ') || '' ' || pivot_values_datatype || ''', '', '' ORDER BY quote_ident(' || pivot_headers_column || ') || '' ' || pivot_values_datatype || ''') as definitions FROM ' || tablename || ';';
    EXECUTE sql_pivot_headers INTO pivot_headers_simple, pivot_headers_definition;

    -- 5. set up the crosstab query
    sql_crosstab_result := 'SELECT  ' || replace (row_headers_castup || ', ' || pivot_headers_simple, ', ', ',
        ') || '
FROM    crosstab (
        ''SELECT ARRAY[' || row_headers_castdown || '] AS ' || arrayname || ', ' || pivot_headers_column || ', ' || pivot_values || '
        FROM ' || tablename || '
        GROUP BY ' || row_headers_simple || ', ' || pivot_headers_column || (CASE pivot_values_columnname=pivot_values WHEN true THEN ', ' || pivot_values ELSE '' END) || '
        ORDER BY ' || row_headers_simple || '''
    ,
        ''SELECT DISTINCT ' || pivot_headers_column || '
        FROM ' || tablename || '
        ORDER BY ' || pivot_headers_column || '''
    ) AS newtable (
        ' || arrayname || ' varchar[]' || ',
        ' || replace(pivot_headers_definition, ', ', ',
        ') || '
    );';

    RETURN sql_crosstab_result;
 END

 $BODY$
  LANGUAGE plpgsql STABLE
  COST 100;

这篇关于Postgres 动态列标题(来自另一个表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！