Получить наиболее распространенное значение для каждого значения другого столбца в SQL

У меня есть такая таблица:

 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 sql greatest-n-per-group
person Martin C. Martin    schedule 05.12.2008    source источник

Ответы (8)


arrow_upward
20
arrow_downward

Теперь это стало еще проще: в 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

person jrouquie    schedule 25.04.2017

arrow_upward
19
arrow_downward

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().

person pilcrow    schedule 16.09.2012

arrow_upward
8
arrow_downward 

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
person jkramer    schedule 05.12.2008
comment
Я думаю, что в большинстве мест они должны быть в одинарных кавычках. - person ocket8888; 06.04.2017

arrow_upward
6
arrow_downward

попробуй это:

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
person Jamal Hansen    schedule 06.12.2008

arrow_upward
3
arrow_downward

Попробуйте что-нибудь вроде этого

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)

Все это можно было бы поместить в один выбор, но у меня сейчас нет времени возиться с этим.

Удачи.

person John MacIntyre    schedule 05.12.2008

arrow_upward
3
arrow_downward

Вот как это сделать без каких-либо временных таблиц:

Изменить: упрощено

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
                        )
person JosephStyons    schedule 05.12.2008
comment
Мне было бы интересно увидеть план для этого выполнения по сравнению с временной таблицей - те, у которых есть предложения, оцениваются после того, как select извлекает совпадающие строки, верно? Похоже, что может быть куча дополнительных IO. - person Ken Gentle; 05.12.2008
comment
В плане есть пара полных сканов таблиц, да. - person JosephStyons; 05.12.2008

arrow_upward
3
arrow_downward 

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, чтобы произвольно выбрать самую последнюю...

Меня интересует план запроса для этой штуки, если вы запустите его на своих живых данных!

person Matt Rogish    schedule 05.12.2008

arrow_upward
3
arrow_downward

Вот утверждение, которое, как мне кажется, дает вам то, что вы хотите, оно простое и лаконичное:

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

Пожалуйста, дайте мне знать, что вы думаете.

Кстати, функция различить доступна только в 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
person JCF    schedule 13.04.2016
comment
Если вы собираетесь предложить новый ответ после всего этого времени, я рекомендую попробовать его на образце таблицы и опубликовать полученные результаты. Кроме того, укажите, какой сервер базы данных вы используете (mysql или что-то еще). - person ToolmakerSteve; 12.01.2017
comment
Функция distinct on доступна только в Postgres, поэтому я не уверен, как бы вы сделали что-то подобное в другой базе данных. OP использует Postgres, поэтому это кажется подходящим. Я написал это, используя таблицу базы данных, предложенную оператором, под названием munch, которая имеет три поля: страна (текст), food_id (int) и съедено (дата). - person JCF; 13.01.2017