Início / dba / Perguntas / 183703
Accepted
Randomize
Asked: 2017-08-18 00:27:18 +0800 CST

Como unificar a função de agregação com o Postgres por grupo diferente por

  • 772

my_datatable é assim (exemplo de uma linha):

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

E eu tenho uma função assim:

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;

O resultado esperado é uma agregação de matriz com as agregações ind1,ind2,ind3renomeadas inde relativas.

O problema com essas funções é que eu tenho que mudar (e executar 3 vezes) select ind1 as ind....group by ind1,import_date.

Existe uma maneira de unificá-lo de uma só vez?

postgresql

3 respostas

  1. Best Answer

    Usar grouping sets:

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

    Mas foi introduzido apenas na versão 9.5 .

    • 1

  2. Você pode usar lateralpara desarticular as colunas ind1e em uma única coluna e especificar a última como critério de agrupamento 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. Que tal juntar essas colunas? É aquilo que você precisa?

    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

relate perguntas