Presto中user_data表的分组聚合与数组化查询需求

步骤1：提取每个user-target-item组合的最大score记录 #

先过滤重复的user-target-item组合，只保留score最高的那条记录。这里用窗口函数ROW_NUMBER()标记每个分组内的最高score记录：

WITH max_score_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY user, target, item ORDER BY score DESC) AS rn
    FROM user_data
)
SELECT 
    user,
    target,
    item,
    score,
    type,
    "other cols" AS other_vals
FROM max_score_records
WHERE rn = 1

步骤2：按user分组，聚合不同type的组合为数组 #

基于第一步的结果，用条件聚合和array_agg()函数，把type=T和type=F的组合分别收集到对应列中：

WITH max_score_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY user, target, item ORDER BY score DESC) AS rn
    FROM user_data
),
filtered_data AS (
    SELECT 
        user,
        target,
        item,
        score,
        type,
        "other cols" AS other_vals
    FROM max_score_records
    WHERE rn = 1
)
SELECT 
    user,
    array_agg(CASE WHEN type = 'T' THEN ARRAY[target, CAST(item AS VARCHAR), CAST(score AS VARCHAR), other_vals] END) FILTER (WHERE type = 'T') AS "type T",
    array_agg(CASE WHEN type = 'F' THEN ARRAY[target, CAST(item AS VARCHAR), CAST(score AS VARCHAR), other_vals] END) FILTER (WHERE type = 'F') AS "type F"
FROM filtered_data
GROUP BY user
ORDER BY user;

关键说明 #

• 由于item和score是数值类型，和字符串类型的target、other_vals组成数组时需要统一类型，因此用CAST()转换为VARCHAR。
• FILTER (WHERE type = 'T')用于只聚合对应type的记录，避免数组中出现NULL值。
• 如果存在多个相同user-target-item且score同为最高值的情况，ROW_NUMBER()会随机取一条；若要保留所有最高score的记录，可替换为RANK()。

简化版更优写法 #

合并CTE减少子查询层级：

SELECT 
    user,
    array_agg(CASE WHEN type = 'T' THEN ARRAY[target, CAST(item AS VARCHAR), CAST(score AS VARCHAR), "other cols"] END) FILTER (WHERE type = 'T' AND rn = 1) AS "type T",
    array_agg(CASE WHEN type = 'F' THEN ARRAY[target, CAST(item AS VARCHAR), CAST(score AS VARCHAR), "other cols"] END) FILTER (WHERE type = 'F' AND rn = 1) AS "type F"
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY user, target, item ORDER BY score DESC) AS rn
    FROM user_data
) t
WHERE rn = 1
GROUP BY user
ORDER BY user;

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