如何使用SQL查询计算数组类型列的总和、平均值、最大值与最小值

数组列逐行计算聚合值实现方案 #

不同数据库的数组处理内置函数存在差异，主流数据库的实现写法如下： #

PostgreSQL #

• 兼容所有版本写法：通过unnest将数组拆分为行后聚合

SELECT 
    col1,
    col2,
    col3,
    sum(elem) AS sum_col3,
    avg(elem)::INT AS avg_col3, -- 如需和示例一致返回整数可强转类型
    max(elem) AS max_col3,
    min(elem) AS min_col3
FROM Table_ab,
     unnest(col3) AS elem
GROUP BY col1, col2, col3;

• PostgreSQL 14及以上版本可直接使用内置数组聚合函数，写法更简洁：

SELECT
    col1,
    col2,
    col3,
    array_sum(col3) AS sum_col3,
    array_avg(col3)::INT AS avg_col3,
    array_max(col3) AS max_col3,
    array_min(col3) AS min_col3
FROM Table_ab;

ClickHouse #

SELECT
    col1,
    col2,
    col3,
    arraySum(col3) AS sum_col3,
    CAST(arrayAvg(col3) AS INT) AS avg_col3,
    arrayMax(col3) AS max_col3,
    arrayMin(col3) AS min_col3
FROM Table_ab;

Spark SQL #

SELECT
    col1,
    col2,
    col3,
    aggregate(col3, 0, (acc, x) -> acc + x) AS sum_col3,
    CAST(aggregate(col3, 0, (acc, x) -> acc + x) / size(col3) AS INT) AS avg_col3,
    array_max(col3) AS max_col3,
    array_min(col3) AS min_col3
FROM Table_ab;

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