sql - Mode-within-group equivalent in Presto

Question

In Postgres, the following query outputs the most-frequently-purchased cheese for each customer:

SELECT
    customer,
    MODE() WITHIN GROUP (ORDER BY "subcategory") AS "fav_cheese"
FROM dft
WHERE category = 'CHEESE'
GROUP BY
    customer

This returns:

customer   fav_cheese
       1      cheddar    # customer1's most-frequently-purchased cheese is cheddar
       2         blue    # customer2's most-frequently-purchased cheese is blue
       3     shredded    # customer3's most-frequently-purchased cheese is shredded

How to achieve the same output in Presto?

I've tried different methods with no success so far.

标签： sqlaggregate-functionspresto

Answer 1

作为一种解决方法，您可以使用直方图方法：

SELECT customer, 
MAP_KEYS(hist)[
    ARRAY_POSITION(MAP_VALUES(hist), ARRAY_MAX(MAP_VALUES(hist)))
] as fav_cheese 
FROM (
   SELECT customer, histogram(subcategory) as hist
   FROM dft
   WHERE category = 'CHEESE'
   GROUP BY customer
) as f