使用 Postgres 將值累加到數組中

目前我有這個查詢：

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}

如果您的 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;

如果您想擁有數組，請使用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，空，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在這裡

引用自：https://dba.stackexchange.com/questions/182185