Presto SQL如何从数组列中选取关联排名最高的元素

你可以直接将生成的映射Map结合Presto的内置数组函数实现需求，优先推荐使用自定义排序的方案，语法简洁性能更优：

WITH country_rank_map AS (
    -- 生成国家编号到排名的映射Map
    SELECT map(ARRAY_AGG(number), ARRAY_AGG(rank)) AS rank_map
    FROM table2
)
SELECT
    t1.countries,
    -- 对国家数组按对应排名升序排序，取下标为1的第一个元素即为结果
    array_sort(t1.countries, (a, b) -> rank_map[a] - rank_map[b])[1] AS answer
FROM table1 t1
-- 映射Map全局唯一，直接交叉关联即可
CROSS JOIN country_rank_map m

方案说明 #

• array_sort第二个参数传入自定义比较函数：两个国家编号a、b，取对应排名做差，排名越小的元素排序后位置越靠前
• Presto数组下标从1开始，排序后第一个元素就是排名最高的国家编号

如果偏好更直观的展开再聚合的写法，也可以使用如下方案：

WITH country_rank_map AS (
    SELECT map(ARRAY_AGG(number), ARRAY_AGG(rank)) AS rank_map
    FROM table2
),
-- 将每行数组的国家编号展开为单独行
country_exploded AS (
    SELECT
        t1.countries,
        country_val,
        rank_map[country_val] AS country_rank
    FROM table1 t1
    CROSS JOIN country_rank_map m
    CROSS JOIN UNNEST(t1.countries) AS t(country_val)
)
-- 按原数组分组，取排名最小的对应国家编号
SELECT
    countries,
    ARBITRARY(country_val) FILTER (WHERE country_rank = MIN(country_rank)) AS answer
FROM country_exploded
GROUP BY countries

两种方案输出结果都和你给出的预期完全一致。

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