sql - 从表中选择一组动态列并获取每个列的总和

是否可以在 Postgres 中执行以下操作:

SELECT column_name FROM information_schema WHERE table_name = 'somereport' AND data_type = 'integer';

SELECT SUM(coulmn_name[0]),SUM(coulmn_name[1]) ,SUM(coulmn_name[3]) FROM somereport;

换句话说，我需要根据某些条件从表中选择一组列，然后对表中的每一列求和。

我知道我可以在循环中执行此操作，因此我可以独立计算每一列，但显然这需要对从信息架构查询返回的每一列进行查询。例如:

FOR r IN select column_name from information_schema where report_view_name = 'somereport' and data_type = 'integer';
LOOP
    SELECT SUM(r.column_name) FROM somereport;
END

最佳答案

此查询创建您所追求的完整 DML 语句:

WITH x AS (
   SELECT 'public'::text     AS _schema  -- provide schema name ..
         ,'somereport'::text AS _tbl     -- .. and table name once
   )
SELECT 'SELECT ' || string_agg('sum(' || quote_ident(column_name)
                 || ') AS sum_' || quote_ident(column_name), ', ')
       || E'\nFROM   ' || quote_ident(x._schema) || '.' || quote_ident(x._tbl)
FROM   x, information_schema.columns
WHERE  table_schema = _schema
AND    table_name = _tbl
AND    data_type = 'integer'
GROUP  BY x._schema, x._tbl;

您可以单独执行它或将此查询包装在 plpgsql 函数中并使用 EXECUTE 自动运行查询:

全自动化

使用 PostgreSQL 9.1.4 测试

CREATE OR REPLACE FUNCTION f_get_sums(_schema text, _tbl text)
  RETURNS TABLE(names text[], sums bigint[]) AS
$BODY$
BEGIN

RETURN QUERY EXECUTE (
    SELECT 'SELECT ''{'
           || string_agg(quote_ident(c.column_name), ', ' ORDER BY c.column_name)
           || '}''::text[],
           ARRAY['
           || string_agg('sum(' || quote_ident(c.column_name) || ')'
                                                   , ', ' ORDER BY c.column_name)
           || ']
    FROM   '
           || quote_ident(_schema) || '.' || quote_ident(_tbl)
    FROM   information_schema.columns c
    WHERE  table_schema = _schema
    AND    table_name = _tbl
    AND    data_type = 'integer'
    );

END;
$BODY$
  LANGUAGE plpgsql;

称呼:

SELECT unnest(names) AS name, unnest (sums) AS col_sum
FROM   f_get_sums('public', 'somereport');

返回:

   name        | col_sum
---------------+---------
 int_col1      |    6614
 other_int_col |    8364
 third_int_col | 2720642

解释

难点在于为函数定义 RETURN 类型，而返回的列的数量和名称会有所不同。一个有帮助的细节:您只需要 integer 列。

我通过形成一个 bigint 数组解决了这个问题( sum(int_col) 返回 bigint )。此外，我返回一个列名数组。两者都按列名称的字母顺序排序。

在函数调用中，我将这些数组与 unnest() 拆分为显示的漂亮格式。

动态创建和执行的查询是高级的东西。不要被多层引号所迷惑。基本上你有 EXECUTE ，它接受一个包含要执行的 SQL 查询的文本参数。反过来，此文本由构建主查询的查询字符串的辅助 SQL 查询提供。

如果这一次太多了或者 plpgsql 对你来说相当新，从 this related answer 开始，我解释了处理更简单功能的基础知识，并提供了主要功能手册的链接。

如果性能至关重要，请直接查询 Postgres 目录 (pg_catalog.pg_attributes)，而不是使用标准化(但速度较慢)的 information_schema.columns。这是一个 simple example with pg_attributes 。

关于sql - 从表中选择一组动态列并获取每个列的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12028041/