在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()函数来解决两种不同食品计数相同时的竞争关系。

这看起来为了一个概念上简单的东西而付出了很多工作。有没有更直接的方法呢？

- Martin C. Martin

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

- jrouquie

Answer 2

在8.4版本中，PostgreSQL引入了对窗口函数的支持，也就是在这个问题被提出的一年后。需要注意的是，现在可能可以按照以下方式解决：

SELECT country, food_id
  FROM (SELECT country, food_id, ROW_NUMBER() OVER (PARTITION BY country ORDER BY freq DESC) AS rn
          FROM (  SELECT country, food_id, COUNT('x') AS freq
                    FROM country_foods
                GROUP BY 1, 2) food_freq) ranked_food_req
 WHERE rn = 1;

以上代码将会打破并列情况。如果您不想要打破这些情况，则可以使用DENSE_RANK()。

- pilcrow

Answer 3

SELECT DISTINCT
"F1"."food",
"F1"."country"
FROM "foo" "F1"
WHERE
"F1"."food" =
    (SELECT "food" FROM
        (
            SELECT "food", COUNT(*) AS "count"
            FROM "foo" "F2" 
            WHERE "F2"."country" = "F1"."country" 
            GROUP BY "F2"."food" 
            ORDER BY "count" DESC
        ) AS "F5"
        LIMIT 1
    )

好的，我匆忙写下了这篇文章，并没有认真检查。子查询可能会很慢，但这是我能想到的最短和最简单的SQL语句。等我清醒一些后，可能会讲得更多。

PS：哦，好吧，“foo”是我的表名，“food”包含食品名称，“country”包含国家名称。样例输出：

   food    |  country   
-----------+------------
 Bratwurst | Germany
 Fisch     | Frankreich

- jkramer

---

大多数地方需要使用单引号，我想。 - ocket8888

Answer 4

试试这个：

Select Country, Food_id
From Munch T1
Where Food_id= 
    (Select Food_id
     from Munch T2
     where T1.Country= T2.Country
     group by Food_id
     order by count(Food_id) desc
      limit 1)
group by Country, Food_id

- rubyyot

Answer 5

以下陈述简洁明了，我相信可以满足您的需求：

select distinct on (country) country, food_id
from munch
group by country, food_id
order by country, count(*) desc

请告诉我您的想法。

顺便提一下，“distinct on”功能只在Postgres中可用。

例如，源数据：

country | food_id | eaten
US        1         2017-1-1
US        1         2017-1-1
US        2         2017-1-1
US        3         2017-1-1
GB        3         2017-1-1
GB        3         2017-1-1
GB        2         2017-1-1

输出：

country | food_id
US        1
GB        3

- JCF

---

如果您打算在这么长时间之后提出一个新的答案，我建议您在一个样例表上尝试一下，并发布您得到的结果。另外，请注明您使用的数据库服务器（mysql或其他）。 - ToolmakerSteve

“distinct on”功能仅在Postgres中可用，因此我不确定您如何在其他数据库中执行类似的操作。OP正在使用Postgres，因此似乎很合适。我是根据OP建议的数据库表“munch”编写的，该表具有三个字段：country（文本）、food_id（整数）和eaten（日期）。 - JCF

Answer 6

尝试像这样做：

尝试像这样做

select country, food_id, count(*) cnt 
into #tempTbl 
from mytable 
group by country, food_id

select country, food_id
from  #tempTbl as x
where cnt = 
  (select max(cnt) 
  from mytable 
  where country=x.country 
  and food_id=x.food_id)

这些内容可以放在一个单一的选择器中，但我现在没有时间去处理它。

祝你好运。

- John MacIntyre

Answer 7

这里是无需使用任何临时表的方法： 编辑：简化

select nf.country, nf.food_id as most_frequent_food_id
from national_foods nf
group by country, food_id 
having
  (country,count(*)) in (  
                        select country, max(cnt)
                        from
                          (
                          select country, food_id, count(*) as cnt
                          from national_foods nf1
                          group by country, food_id
                          )
                        group by country
                        having country = nf.country
                        )

- kogus

---

我很想看看这个执行计划与临时表的计划有何不同——那些“having”子句是在选择检索匹配行之后进行评估的，对吗？看起来可能会有大量额外的IO。 - Ken Gentle

计划中有几个完整的表扫描。 - JosephStyons

Answer 8

SELECT country, MAX( food_id )
  FROM( SELECT m1.country, m1.food_id
          FROM munch m1
         INNER JOIN ( SELECT country
                           , food_id
                           , COUNT(*) as food_counts
                        FROM munch m2
                    GROUP BY country, food_id ) as m3
                 ON m1.country = m3.country
         GROUP BY m1.country, m1.food_id 
        HAVING COUNT(*) / COUNT(DISTINCT m3.food_id) = MAX(food_counts) ) AS max_foods
  GROUP BY country

我不喜欢使用MAX(.) GROUP BY来打破绑定...一定有一种方法可以将吃掉的日期以某种方式纳入JOIN中，以任意选择最近的一个...

如果你在实时数据上运行它，我对此查询计划很感兴趣！

- Matt

Answer 9

select country,food_id, count(*) ne  
from   food f1  
group by country,food_id    
having count(*) = (select max(count(*))  
                   from   food f2  
                   where  country = f1.country  
                   group by food_id)  

- Theo