主页 / dba / 问题 / 182185
Accepted
Randomize
Asked: 2017-07-30 12:51:18 +0800 CST

使用 Postgres 将值累加到数组中

  • 772

目前我有这个查询：

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;

所以如果my_table有值：

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

查询将返回：

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

如何替换sum以将值累积到数组中以获取？

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

2 个回答

  1. Best Answer

    如果您的 PostggreSQL 版本是 9.4 或更高版本，请使用FILTER子句：

    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. 如果您想拥有数组，请使用array_agg, 而不是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 | 半独立的_current_asking_price
:------------------------ | :--------------------------------
{5000,NULL,6000} | {NULL,9000,NULL}

    如果您想摆脱空值，请改用一个小技巧（array_to_string 摆脱它们）：

    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 | 半独立的_current_asking_price
:------------------------ | :--------------------------------
{5000,6000} | {9000}

    dbfiddle在这里

    附录

    根据 Abelisto 的评论，您还可以使用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在这里

    • 1

相关问题