PostgreSQL如何在同一查询中统计数组列唯一值数量及其他聚合？

问题描述 #

我有一张表t1，包含grouper、value和arr_col列——其中arr_col是text类型数组。需要在同一查询中，按grouper分组统计所有数组中的去重值数量，同时计算value列的平均值。

尝试了以下语句，但因unnest是集合类函数，执行失败：

select grouper, avg(value), count(distinct(unnest(arr_col)))
  from t1
 group by grouper

自己想到了CTE替代方案，但实际代码复杂度高（涉及任意数量分组、多个聚合操作，且基于SQLAlchemy ORM），不想大幅重构查询，我的方案示例如下（语法可能存在瑕疵，但能体现核心思路）：

with (select distinct grouper, unnest(arr_col) unnested_col
        from t1) as unnested_distinct_rows,
     (select grouper, avg(value) avg_value
        from t1
       group by grouper) as avg_agg,
     (select grouper, count(*) distinct_arr_col_values
        from unnested_distinct_rows
        group by grouper) as distinct_agg
select avg_agg.grouper, avg_agg.avg_value, distinct_agg.distinct_arr_col_values
  from avg_agg
  join distinct_agg on avg_agg.grouper = distinct_agg.grouper

优化解决方案 #

1. 紧凑关联子查询写法 #

无需拆分多个CTE，将数组去重计数逻辑放到子查询中，与平均值聚合结果关联，结构更简洁：

select 
    t.grouper,
    avg(t.value) as avg_value,
    agg.distinct_arr_col_values
from t1 t
left join (
    select grouper, count(distinct unnest(arr_col)) as distinct_arr_col_values
    from t1
    group by grouper
) agg on t.grouper = agg.grouper
group by t.grouper, agg.distinct_arr_col_values

2. PostgreSQL专属嵌套聚合写法 #

如果使用PostgreSQL，可以直接用嵌套聚合函数实现，无需拆分查询，改动量最小：

select 
    grouper,
    avg(value) as avg_value,
    count(distinct elem) as distinct_arr_col_values
from t1, unnest(arr_col) as elem
group by grouper

说明：该写法会先将每行的数组展开为多行，再按grouper分组统计。虽然value会因数组展开被重复计算，但平均值的计算逻辑（总和/行数）不受影响，最终结果正确。

3. SQLAlchemy适配写法 #

针对ORM场景，可通过子查询结合SQLAlchemy的func构建逻辑，避免大幅重构原有查询：

from sqlalchemy import func, select

# 构建数组去重计数的子查询
distinct_subq = (
    select(
        t1.c.grouper,
        func.count(func.distinct(func.unnest(t1.c.arr_col))).label("distinct_arr_col_values")
    )
    .group_by(t1.c.grouper)
    .subquery()
)

# 主查询：关联子查询并计算平均值
main_query = (
    select(
        t1.c.grouper,
        func.avg(t1.c.value).label("avg_value"),
        distinct_subq.c.distinct_arr_col_values
    )
    .outerjoin(distinct_subq, t1.c.grouper == distinct_subq.c.grouper)
    .group_by(t1.c.grouper, distinct_subq.c.distinct_arr_col_values)
)

内容的提问来源于stack exchange，提问作者Panda