Presto - Get Mode value by Row

Question

I would like to get the most recurring value for a row over defined columns.

For example i have this table :

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

So the output would be :

|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|

In case of a tie (for id 'd') the mode would be a default value 'unknown'.

Here is how I solve it :

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;

This is working but I would like to know if there is any better solution with Presto.

Thanks

user_2501279 · Answer

Another approach - create array column containing values in question, flatten it with unnest, group by and use histogram to determine the counts and then extract most common value:

-- sample data
WITH dataset(id, x, y, z ) AS (
    VALUES ('a', 'green', 'green', 'black'),
        ('b', 'red',   'green', 'red'),
        ('c', 'red',   'black', 'black'),
        ('d', 'red',   'green', 'black')
)

-- query
select id,
    x,
    y,
    z,
    if(cardinality(maxXYZ) = 1, maxXYZ [ 1 ], 'unknown') mode -- if multiple values have same frequency - then unknown  
from (
        select id,
            x,
            y,
            z,
            map_keys(
                map_filter(
                    mp,
                    (k, v)->v = array_max(map_values(mp))
                )
            ) AS maxXYZ
        from (
                select max(id) id, -- all values are the same so `max` or `min` should work just fine
                    max(x) x,
                    max(y) y,
                    max(z) z,
                    histogram(v) mp -- map containing counts
                from (
                        select *,
                            row_number() over() rn -- unique row id to group by later
                        from dataset
                    ) d,
                    unnest (array [ x, y, z ]) as t(v)
                group by rn
            )
    )

Output:

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

Presto - Get Mode value by Row

1 Answers