在Presto/Hive中如何基于列值聚合列并生成排序分组数组？

我需要创建一个数据集，基于offensive列的值聚合id列，生成两个按score排序的id数组，并将这两个数组添加到每条数据中。

示例输入数据：

id | score | id2 | offensive
---------+-------+-----+-----------
 a1 | 1.2 | 1 | false
 a2 | 36.0 | 1 | true
 a3 | 1.2 | 1 | true
 a4 | 36.0 | 1 | false

期望输出结果（按offensive分组，数组按score降序排序）：

id |id2 | clean_group | offensive_group
---------+-----+-------------+-----------------
 a1 | 1 | [a2, a3] | [a4, a1]
 a2 | 1 | [a2, a3] | [a4, a1]
 a3 | 1 | [a2, a3] | [a4, a1]
 a4 | 1 | [a2, a3] | [a4, a1]

已知id唯一、所有数据的id2值相同，我尝试过Presto的array_agg和Hive的collect_set函数但遇到困难，想知道能否在聚合函数中传入条件，比如array_agg(id where offensive=true)。

当然可以实现！针对你提到的Presto和Hive两种引擎，我分别给出适配的解决方案：

1. Presto 实现 #

Presto的array_agg原生支持FILTER子句来实现条件聚合，同时结合窗口函数可以让每条数据都带上全局的聚合结果：

SELECT
    id,
    id2,
    -- 生成offensive=true的id数组，按score降序排序
    array_agg(id) FILTER (WHERE offensive = true) 
        OVER (PARTITION BY id2 ORDER BY score DESC 
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS clean_group,
    -- 生成offensive=false的id数组，按score降序排序
    array_agg(id) FILTER (WHERE offensive = false) 
        OVER (PARTITION BY id2 ORDER BY score DESC 
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS offensive_group
FROM your_table;

关键说明： #

• OVER (PARTITION BY id2)：确保同一个id2分组下的所有数据共享这两个数组；
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：强制窗口包含分区内的所有行，避免默认窗口范围导致结果不全；
• FILTER (WHERE ...)：实现你需要的条件聚合逻辑；
• ORDER BY score DESC：直接在聚合时指定数组的排序规则。

2. Hive 实现 #

Hive的实现分两种情况，取决于你的Hive版本：

情况一：Hive 2.3+（支持FILTER子句） #

写法和Presto非常接近，用collect_list（因为id唯一，无需去重，替代collect_set）结合窗口函数：

SELECT
    id,
    id2,
    collect_list(id) FILTER (WHERE offensive = true)
        OVER (PARTITION BY id2 ORDER BY score DESC 
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS clean_group,
    collect_list(id) FILTER (WHERE offensive = false)
        OVER (PARTITION BY id2 ORDER BY score DESC 
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS offensive_group
FROM your_table;

情况二：Hive 2.3以下（不支持FILTER） #

可以通过CASE WHEN过滤无效值，结合sort_array和struct实现按score排序：

WITH aggregated_data AS (
    SELECT
        id2,
        -- 将score和id组成struct，排序后提取id数组
        sort_array(collect_list(CASE WHEN offensive = true THEN struct(score, id) END), false) AS clean_struct,
        sort_array(collect_list(CASE WHEN offensive = false THEN struct(score, id) END), false) AS offensive_struct
    FROM your_table
    GROUP BY id2
)
SELECT
    t.id,
    t.id2,
    -- 从排序后的struct数组中提取id字段
    transform(clean_struct, x -> x.id) AS clean_group,
    transform(offensive_struct, x -> x.id) AS offensive_group
FROM your_table t
JOIN aggregated_data a ON t.id2 = a.id2;

关键说明： #

• struct(score, id)：将排序字段和目标字段绑定，确保排序后id的顺序正确；
• sort_array(..., false)：第二个参数false表示按score降序排序（默认是升序）；
• transform：从排序后的struct数组中提取id，生成最终的目标数组。

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