主页 / dba / 问题 / 183703
Accepted
Randomize
Asked: 2017-08-18 00:27:18 +0800 CST

如何通过不同组统一聚合函数与 Postgres

  • 772

my_data表格是这样的（一行示例）：

 total_cost | total_exp | ind1 | ind2 | ind3 | flag
------------+-----------+------+------+------+------
 1000       | 2000      | A    | A1   | A2   | F

我有这样的功能：

CREATE OR REPLACE FUNCTION create_my_aggregation() RETURNS VOID AS $$
        DECLARE
            import_date date;
        BEGIN
            FOR import_date IN
            SELECT generate_series('2005-01-01'::date, '2015-04-01'::date, '1 month'::Interval)
            LOOP
                insert into my_timeseries

                       select
--                       ind1 as ind,
--                       ind2 as ind,
                       ind3 as ind,
                       import_date,

                       -- size

                       array_agg(total_cost) filter(where flag = 'F' and total_exp > 0) as f_cost_a,
                       array_agg(total_cost) filter(where flag = 'S' and total_exp > 50) as s_cost_a,
                       array_agg(total_cost) filter(where flag = 'D' and total_exp > 100) as d_cost_a,
                       array_agg(total_cost) filter(where flag = 'T' and total_exp > 20) as t_cost_a,

                       from my_data
                       where date_trunc('month', date_of_transfer) = date_trunc('month', import_date)
--                       group by ind1, import_date;
--                       group by ind2, import_date;
                       group by ind3, import_date;
            END LOOP;
        END
$$ LANGUAGE plpgsql;

ind1,ind2,ind3预期结果是具有重命名为ind和相对聚合的数组聚合。

该功能的问题是我必须切换（并运行 3 次）select ind1 as ind....group by ind1,import_date。

有没有办法一次性统一它？

postgresql

3 个回答

  1. Best Answer

    使用grouping sets：

    select
  coalesce(ind1, ind2, ind3) as ind,
  ...
group by grouping sets (
  (ind1, import_date),
  (ind2, import_date),
  (ind3, import_date));

    但它仅在9.5 版本中引入。

    • 1

  2. 您可以使用lateralunpivot the columns ind1，ind2并将ind3其放入单个列并将后者指定为分组标准：

    select
    v.ind,
    import_date,

    array_agg(d.total_cost) filter(where d.flag = 'F' and d.total_exp > 0) as f_cost_a,
    array_agg(d.total_cost) filter(where d.flag = 'S' and d.total_exp > 50) as s_cost_a,
    array_agg(d.total_cost) filter(where d.flag = 'D' and d.total_exp > 100) as d_cost_a,
    array_agg(d.total_cost) filter(where d.flag = 'T' and d.total_exp > 20) as t_cost_a,

from
    my_data as d,
    lateral (values (d.ind1), (d.ind2), (d.ind3)) as v (ind)
where
    date_trunc('month', d.date_of_transfer) = date_trunc('month', import_date)
group by
    v.ind, import_date;
    • 1

  3. 如何将这些列连接在一起？那是你需要的吗？

    CREATE OR REPLACE FUNCTION create_my_aggregation() RETURNS VOID AS $$
        DECLARE
            import_date date;
        BEGIN
            FOR import_date IN
            SELECT generate_series('2005-01-01'::date, '2015-04-01'::date, '1 month'::Interval)
            LOOP
                insert into my_timeseries

                       select
                       ind1||';'||ind2||';'||ind3 as ind,
                       import_date,

                       -- size

                       array_agg(total_cost) filter(where flag = 'F' and total_exp > 0) as f_cost_a,
                       array_agg(total_cost) filter(where flag = 'S' and total_exp > 50) as s_cost_a,
                       array_agg(total_cost) filter(where flag = 'D' and total_exp > 100) as d_cost_a,
                       array_agg(total_cost) filter(where flag = 'T' and total_exp > 20) as t_cost_a,

                       from my_data
                       where date_trunc('month', date_of_transfer) = date_trunc('month', import_date)
                       group by ind1||';'||ind2||';'||ind3, import_date;
            END LOOP;
        END
$$ LANGUAGE plpgsql;
    • 0

相关问题