Presto如何按行获取指定列的众数？求更优实现方案

我需要获取数据表每行指定列中出现次数最多的值（众数），如果多个值出现次数相同（平局），则众数设为默认值unknown。

示例数据表 #

|id |x    |y    |z    |
|---|-----|-----|-----|
|a  |green|green|black|
|b  |red  |green|red  |
|c  |red  |black|black|
|d  |red  |green|black|

预期输出 #

|id |x    |y    |z    |mode   |
|---|-----|-----|-----|-------|
|a  |green|green|black|green  |
|b  |red  |green|red  |red    |
|c  |red  |black|black|black  |
|d  |red  |green|black|unknown|

我当前的Presto实现方案如下：

with dt (id, x, y, z) as (
  values
    ('a', 'green', 'green', 'black'),
    ('b', 'red', 'green', 'red'),
    ('c', 'red', 'black', 'black'), 
    ('d', 'red', 'green', 'black')
), dt_map as (
    select 
        *,
        transform_values(
            multimap_from_entries(
                transform(array[x, y, z], x -> row(x, 1))
            ),
            (k, v) -> reduce(v, 0, (s, x) -> s + x, s -> s)
        ) as m
    from dt
), dt_map_filter as (
    select 
        id, 
        x, 
        y, 
        z,
        map_keys(
            map_filter(
                m,
                (k, v) -> v = array_max(map_values(m)) 
            )
        ) as m
    from dt_map
)
select 
    id, 
    x, 
    y, 
    z,
    if(cardinality(m) > 1, 'unknown', element_at(m, 1)) as mode
from dt_map_filter;

这个方案能正常运行，但想知道有没有更优的Presto解决方案。

可以利用Presto内置的histogram函数直接统计数组元素的出现频次，简化代码逻辑并提升效率：

简化版（可读性优先） #

with dt (id, x, y, z) as (
  values
    ('a', 'green', 'green', 'black'),
    ('b', 'red', 'green', 'red'),
    ('c', 'red', 'black', 'black'), 
    ('d', 'red', 'green', 'black')
)
select
    id,
    x,
    y,
    z,
    case
        when cardinality(top_candidates) > 1 then 'unknown'
        else element_at(top_candidates, 1)
    end as mode
from (
    select
        *,
        map_keys(
            map_filter(
                histogram(array[x, y, z]),
                (k, v) -> v = array_max(map_values(histogram(array[x, y, z])))
            )
        ) as top_candidates
    from dt
);

性能优化版（避免重复计算） #

如果数据量较大，建议把频次统计和最大频次计算抽出来，避免重复调用histogram：

with dt (id, x, y, z) as (
  values
    ('a', 'green', 'green', 'black'),
    ('b', 'red', 'green', 'red'),
    ('c', 'red', 'black', 'black'), 
    ('d', 'red', 'green', 'black')
), freq_stats as (
    select
        *,
        histogram(array[x, y, z]) as freq_map,
        array_max(map_values(histogram(array[x, y, z]))) as max_freq
    from dt
)
select
    id,
    x,
    y,
    z,
    case
        when cardinality(map_keys(map_filter(freq_map, (k, v) -> v = max_freq))) > 1 then 'unknown'
        else element_at(map_keys(map_filter(freq_map, (k, v) -> v = max_freq)), 1)
    end as mode
from freq_stats;

优化说明 #

• 代码简洁性：用histogram(array[x, y, z])直接生成元素-频次映射，替代原方案中multimap_from_entries+transform+reduce的复杂组合，逻辑更直观。
• 性能提升：减少了中间CTE的层数，性能优化版还避免了重复计算频次统计，在大数据量场景下优势更明显。
• 可读性：变量命名更清晰（如freq_map、top_candidates），代码维护成本更低。

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