sql - 获取 SQL 中另一列的每个值的最常见值

Question

我有一个这样的表:

 Column  | Type | Modifiers 
---------+------+-----------
 country | text | 
 food_id | int  | 
 eaten   | date | 

对于每个国家，我想得到最常吃的食物。我能想到的最好的(我正在使用 postgres)是:

CREATE TEMP TABLE counts AS 
   SELECT country, food_id, count(*) as count FROM munch GROUP BY country, food_id;

CREATE TEMP TABLE max_counts AS 
   SELECT country, max(count) as max_count FROM counts GROUP BY country;

SELECT country, max(food_id) FROM counts 
   WHERE (country, count) IN (SELECT * from max_counts) GROUP BY country;

在最后一个语句中，需要 GROUP BY 和 max() 来打破平局，其中两种不同的食物具有相同的计数。

对于概念上简单的事情，这似乎需要做很多工作。有更直接的方法吗？

Answer 1

现在更简单了:PostgreSQL 9.4 引入了 mode() 函数:

select mode() within group (order by food_id)
from munch
group by country

返回(如 user2247323 的示例):

country | mode
--------------
GB      | 3
US      | 1

请参阅此处的文档: https://wiki.postgresql.org/wiki/Aggregate_Mode

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE