logo标签列表

在PostgreSQL中动态生成交叉表的列

postgresqlpivot-tableplpgsqldynamic-sqlpostgresql-9.1
26
我正在尝试在PostgreSQL中创建查询，使其自动生成列而不是硬编码。我编写了一个函数来动态生成我查询所需的列清单。我的想法是使用动态SQL将此函数的结果替换到查询中。虽然我知道如何在SQL Server中轻松完成这项工作，但我对PostgreSQL的了解有限，这使我的进展受阻。我考虑将生成动态列列表的函数的结果存储到变量中，并使用它动态构建SQL查询。如果有人能指导我做出相应操作，那将非常棒。

-- Table which has be pivoted
CREATE TABLE test_db
(
    kernel_id int,
    key int,
    value int
);

INSERT INTO test_db VALUES
(1,1,99),
(1,2,78),
(2,1,66),
(3,1,44),
(3,2,55),
(3,3,89);


-- This function dynamically returns the list of columns for crosstab
CREATE FUNCTION test() RETURNS TEXT AS '
DECLARE
    key_id int;
    text_op TEXT = '' kernel_id int, '';
BEGIN
    FOR key_id IN SELECT DISTINCT key FROM test_db ORDER BY key LOOP
    text_op := text_op || key_id || '' int , '' ;
    END LOOP;
    text_op := text_op || '' DUMMY text'';
    RETURN text_op;
END;
' LANGUAGE 'plpgsql';

-- This query works. I just need to convert the static list
-- of crosstab columns to be generated dynamically.
SELECT * FROM
crosstab
(
    'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2',
    'SELECT DISTINCT key FROM test_db ORDER BY 1'
)
AS x (kernel_id int, key1 int, key2 int, key3 int); -- How can I replace ..
-- .. this static list with a dynamically generated list of columns ?
4个回答
16

您可以使用提供的 C 函数 crosstab_hash 来完成此操作。

手册在这方面并不是很清晰。在关于带有两个参数的 crosstab() 章节的结尾处，提到了如下内容:

您可以创建预定义函数，以避免在每个查询中编写结果列名称和类型。请参见上一节中的示例。此形式的 crosstab 的底层 C 函数名为 crosstab_hash。

对于您的示例：

CREATE OR REPLACE FUNCTION f_cross_test_db(text, text)
  RETURNS TABLE (kernel_id int, key1 int, key2 int, key3 int)
  AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;

电话：

SELECT * FROM f_cross_test_db(
      'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2'
     ,'SELECT DISTINCT key FROM test_db ORDER BY 1');

请注意，您需要为每个具有不同返回类型的函数创建一个独特的函数。
相关链接：
你生成列列表的函数过于复杂，而且结果不正确（kernel_id后面缺少int），可以用这个SQL查询语句代替：

SELECT 'kernel_id int, '
       || string_agg(DISTINCT key::text, ' int, '  ORDER BY key::text)
       || ' int, DUMMY text'
FROM   test_db;

无论如何，它都不能动态使用。

感谢您的详细回复，@Erwin。我已经能够使用静态交叉表字段制作基本的交叉表。我有点惊讶的是，我们没有任何办法动态生成列的列表，从而在PostgreSQL中创建动态交叉表。您是否知道其他通过迂回方式或hacky方法在PostgreSQL中实现此功能的方法？ - invinc4u
1
@invinc4u：问题在于函数的返回类型无法动态更改。您可以动态地重新创建函数本身，然后立即调用它。但那是非常棘手的事情... - Erwin Brandstetter
1
是的，那很有道理。但我希望有一些使用动态SQL的hacky方法可以解决这个问题。在准备好动态SQL之后，我们应该只需执行查询，它就会为我们生成动态交叉表。例如，请参见：http://muhammedsalimp.wordpress.com/2010/07/16/dynamic-cross-tab-query-in-sql-server/。我已经想念我的SQL Server了:( 无论如何，非常感谢您的帮助和指导！！ - invinc4u
5
我有点困惑。这段话回答了问题吗？根据解决方案，我们仍需要打出列清单并在此处键入：RETURNS TABLE (kernel_id int, key1 int, key2 int, key3 int)。这难道不是正是 OP 想要避免的吗？ - Vikas Prasad
嗨@ErwinBrandstetter，你能帮忙看一下我的新问题吗... https://dba.stackexchange.com/questions/275575/data-mismatch-in-view2-select-from-view1-with-view1-how-to-fix - Ben
5
我知道这是一篇较旧的文章，但我也曾遇到同样的问题，并花费了一些时间解决。
我的问题简述如下：我有一个包含多个字段值的表格，希望创建一个交叉表查询，每行有40多个列标题。
我的解决方案是创建一个函数，循环遍历表格列以获取我想要在交叉表查询中用作列标题的值。
在这个函数中，我可以创建交叉表查询。在我的使用情况中，我将此交叉结果添加到单独的表格中。
例如：
CREATE OR REPLACE FUNCTION field_values_ct ()
 RETURNS VOID AS $$
DECLARE rec RECORD;
DECLARE str text;
BEGIN
str := '"Issue ID" text,';
   -- looping to get column heading string
   FOR rec IN SELECT DISTINCT field_name
        FROM issue_fields
        ORDER BY field_name
    LOOP
    str :=  str || '"' || rec.field_name || '" text' ||',';
    END LOOP;
    str:= substring(str, 0, length(str));

    EXECUTE 'CREATE EXTENSION IF NOT EXISTS tablefunc;
    DROP TABLE IF EXISTS temp_issue_fields;
    CREATE TABLE temp_issue_fields AS
    SELECT *
    FROM crosstab(''select issue_id, field_name, field_value from issue_fields order by 1'',
                 ''SELECT DISTINCT field_name FROM issue_fields ORDER BY 1'')
         AS final_result ('|| str ||')';
END;
$$ LANGUAGE plpgsql;
3

@erwin-brandstetter：如果你始终返回一个带有转换结果的JSON类型，那么函数的返回类型就不是问题。

这是我想到的函数：

CREATE OR REPLACE FUNCTION report.test(
    i_start_date TIMESTAMPTZ,
    i_end_date TIMESTAMPTZ,
    i_interval INT
    ) RETURNS TABLE (
    tab JSON
    ) AS $ab$
DECLARE
    _key_id TEXT;
    _text_op TEXT = '';
    _ret JSON;
BEGIN
    -- SELECT DISTINCT for query results
    FOR _key_id IN
    SELECT DISTINCT at_name
      FROM report.company_data_date cd 
      JOIN report.company_data_amount cda ON cd.id = cda.company_data_date_id 
      JOIN report.amount_types at ON cda.amount_type_id  = at.id 
     WHERE date_start BETWEEN i_start_date AND i_end_date
       AND interval_type_id = i_interval
    LOOP
    -- build function_call with datatype of column
        IF char_length(_text_op) > 1 THEN
            _text_op := _text_op || ', ' || _key_id || ' NUMERIC(20,2)';
        ELSE
            _text_op := _text_op || _key_id || ' NUMERIC(20,2)';
        END IF;
    END LOOP;
    -- build query with parameter filters
    RETURN QUERY
    EXECUTE '
        SELECT array_to_json(array_agg(row_to_json(t)))
          FROM (
        SELECT * FROM crosstab(''SELECT date_start, at.at_name,  cda.amount ct 
          FROM report.company_data_date cd 
          JOIN report.company_data_amount cda ON cd.id = cda.company_data_date_id 
          JOIN report.amount_types at ON cda.amount_type_id  = at.id 
         WHERE date_start between $$' || i_start_date::TEXT || '$$ AND $$' || i_end_date::TEXT || '$$ 
           AND interval_type_id = ' || i_interval::TEXT || ' ORDER BY date_start'') 
            AS ct (date_start timestamptz, ' || _text_op || ')
             ) t;';
END;
$ab$ LANGUAGE 'plpgsql';

因此，当你运行它时，你会得到JSON格式的动态结果，你不需要知道旋转了多少个值:
select * from report.test(now()- '1 week'::interval, now(), 1);
                                                                                                                     tab                                                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"date_start":"2015-07-27T08:40:01.277556-04:00","burn_rate":0.00,"monthly_revenue":5800.00,"cash_balance":0.00},{"date_start":"2015-07-27T08:50:02.458868-04:00","burn_rate":34000.00,"monthly_revenue":15800.00,"cash_balance":24000.00}]
(1 row)

编辑：如果您的数据透视表中含有混合数据类型，您可以添加逻辑来查找每个列的内容，例如以下代码：

  SELECT a.attname as column_name, format_type(a.atttypid, a.atttypmod) AS data_type 
    FROM pg_attribute a 
    JOIN pg_class b ON (a.attrelid = b.relfilenode) 
    JOIN pg_catalog.pg_namespace n ON n.oid = b.relnamespace 
   WHERE n.nspname = $$schema_name$$ AND b.relname = $$table_name$$ and a.attstattarget = -1;"
3

描述的方法 在这里 对我很有效。 不直接检索透视表，更简单的方法是让函数生成一个 SQL 查询字符串。根据需要动态执行生成的 SQL 查询字符串。

动态查询模式简要概述：

DO $$ BEGIN
DROP TABLE IF EXISTS temp_pivot;
EXEUTE(SELECT
  'CREATE TEMPORARY TABLE temp_pivot AS SELECT * FROM crosstab(
   ''$value_query'', ''$cat_query'') AS ct (rn text, "'
   || string_agg(cat_col, '" text,"') || '" text);'
   FROM $cat_query 
); 
END $$;
SELECT * FROM temp_pivot;
终于有东西可以工作了！谢谢Ben（和Erik）。你们能否更新脚本，使其立即运行生成的查询？比如获取pivotcode结果单元格的值并执行它。 - Marat
@Marat 您可以使用 CREATE TABLE AS SELECT * FROM 将输出设置为临时表。 - Ben
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接