Получить наиболее распространенное значение для каждого значения другого столбца в 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() необходимы для разрыва связей, где две разные продукты имеют одинаковый счет.

Это похоже на большую работу над чем-то концептуально простым. Есть ли более прямой способ сделать это?

Ответ 1

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() вместо этого.

Ответ 2

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   
-----------+------------
 Bratwurst | Germany
 Fisch     | Frankreich

Ответ 3

попробуйте следующее:

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

Ответ 4

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

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

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
                        )

Ответ 5

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

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

Ответ 6

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)

Ответ 7

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

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)

Это может быть все в одном, но у меня нет времени гадоваться с ним прямо сейчас.

Удачи.

Ответ 8

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

Ответ 9

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

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

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

BTW, отличительная функция доступна только в 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