我有这样一张桌子:
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()来打破关系,其中两种不同的食物具有相同的数量.
对于概念上简单的事情来说,这似乎是很多工作.有没有更直接的方式来做到这一点?
在问到这个问题的那一年后,PostgreSQL 在8.4中引入了对窗口函数的支持.值得注意的是,今天可能会解决如下问题:
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().
它现在更简单: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
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
试试这个:
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