Início / dba / Perguntas / 182185
Accepted
Randomize
Asked: 2017-07-30 12:51:18 +0800 CST

Acumule valores em um array com Postgres

  • 772

Atualmente tenho esta consulta:

select 
    sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
    sum(case when my_table.property_type_mapped = 'SEMIDETACHED' then my_table.price else 0 end) as Semidetached_Current_Asking_Price
from 
    my_table;

Então se my_tabletem os valores:

property_type | price
--------------+-------
FLAT          | 5000
SEMIDETACHED  | 9000
FLAT          | 6000

a consulta retornará:

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000                     | 9000

Como posso substituir o sumpara acumular os valores em arrays para obter?

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000}              | {9000}
postgresql aggregate

2 respostas

  1. Best Answer

    Se sua versão do PostggreSQL for 9.4 ou posterior, use a FILTERcláusula:

    select
    array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price,
    array_agg(my_table.price) filter(where my_table.property_type = 'SEMIDETACHED') as Semidetached_Current_Asking_Price
from 
    my_table;
    • 8

  2. Se você quiser ter os arrays, use array_agg, em vez de SUM:

    SELECT
    array_agg(case when my_table.property_type = 'FLAT'         then my_table.price end) as Flat_Current_Asking_Price,
    array_agg(case when my_table.property_type = 'SEMIDETACHED' then my_table.price end) as Semidetached_Current_Asking_Price
FROM 
    my_table;

    flat_current_asking_price | semidetached_current_asking_price
:------------------------ | :--------------------------------
{5000,NULO,6000} | {NULO,9000,NULO}

    Se você quiser se livrar dos nulos, use um truque pequeno (array_to_string se livra deles):

    SELECT
    string_to_array(array_to_string(array_agg(case when my_table.property_type = 'FLAT'         then my_table.price end), '|'), '|') as Flat_Current_Asking_Price,
    string_to_array(array_to_string(array_agg(case when my_table.property_type = 'SEMIDETACHED' then my_table.price end), '|'), '|') as Semidetached_Current_Asking_Price
FROM 
    my_table;

    flat_current_asking_price | semidetached_current_asking_price
:------------------------ | :--------------------------------
{5000,6000} | {9000}

    dbfiddle aqui

    termo aditivo

    Por comentário de Abelisto, você também pode usar array_remove:

    SELECT
    array_remove(array_agg(case when my_table.property_type = 'FLAT'         then my_table.price end), NULL) as Flat_Current_Asking_Price,
    array_remove(array_agg(case when my_table.property_type = 'SEMIDETACHED' then my_table.price end), NULL) as Semidetached_Current_Asking_Price
FROM 
    my_table;

    dbfiddle aqui

    • 1

relate perguntas