PostgreSQL在同一unest上有许多标量函数

Question

我有这个查询：

select distinct * ,  
(select count(A) filter(where A < 360) from unnest(cust_journey_time_series) as A) as count_journey_time_series  
, (select avg(A) filter(where A < 360) from unnest(cust_journey_time_series) as A) as avg_journey_time_series  
, (select sum(A)  filter(where A < 360) from unnest(cust_journey_time_series) as A) as order_journey_time
, (select sum(A)  .. 
, (select count(distinct A) ..  
from (
select a,b,c, max(s) s, max(ev) ev, max(ord) ord,
array_agg(cust_journey_time_seconds order by ev asc) as cust_journey_time_series, min(mi) mi,
array_agg(col order by ev asc)  as cus,
min(ra) ra, max(de) de, max(to) to,
max(orde) orde, max(cam) cam, 
max(pag) pag,
count(collec) FILTER (WHERE collec <> las )  AS  pag_count,

max (fi)  as ad,
array_agg(fi)  as dd2c,
array_agg(ut)  as ut_places,
array_agg(ct)  as ct_places,
array_agg(ut)  as utaces

from temp_j
group by a,b,c

order by ev
)aaa 

我的直觉代码是select distinct * , (select count(A), sum(A),avg(A) filter(where A < 360) from unnest(cust_journey_time_series) as A) as count_journey_time_series, sum_journey_time_series , avg_journey_time_series

但是我收到这个错误

子查询必须只返回一列

有没有优化查询的方法，或者PostgreSQL在后台进行优化？

Answer 1

我将尝试将您提供的片段翻译成更好的片段：

select distinct tab.*,  
count(cjts.A) filter (where cjts.A < 360) as count_journey_time_series,
avg(cjts.A) filter (where cjts.A < 360) as avg_journey_time_series,
sum(cjts.A) filter (where cjts.A < 360) as order_journey_time,
...
from (select ...) as tab
cross join lateral unnest(tab.cust_journey_time_series) as cjts
group by tab.pkey;

这样，您只需要对函数求值一次。

修改后的查询的错误消息来自：

SELECT
(SELECT count(A), sum(A), avg(A) FROM ...)
...

这是不允许的。在SELECT列表中的子查询中只能使用单个结果列。以我的方式编写查询可以避免这个问题。