Den häufigsten Wert für jeden Wert einer anderen Spalte in SQL erhalten

Question

Ich habe eine Tabelle wie diese:

 Spalte  | Typ  | Modifikatoren 
---------+------+-----------
 country | text | 
 food_id | int  | 
 eaten   | datum | 

Und für jedes Land möchte ich das Essen bekommen, das am häufigsten gegessen wird. Das Beste, was mir einfällt (ich benutze postgres), ist:

CREATE TEMP TABELLE zählt ALS 
   SELECT country, food_id, count(*) as count FROM munch GRUPPIEREN NACH country, food_id;

CREATE TEMP TABELLE max_counts ALS 
   SELECT country, max(count) as max_count FROM counts GRUPPIEREN NACH country;

SELECT country, max(food_id) FROM counts 
   WO (country, count) IN (SELECT * FROM max_counts) GRUPPIEREN NACH country;

In dieser letzten Anweisung sind die GRUPPIEREN NACH und max() erforderlich, um Unentschieden zu brechen, wo zwei verschiedene Lebensmittel die gleiche Anzahl haben.

Dies scheint viel Arbeit für etwas konzeptionell Einfaches zu sein. Gibt es einen einfacheren Weg, um dies zu tun?

Answer 1

So geht das ohne temporäre Tabellen:

Bearbeitet: vereinfacht

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
                        )

Answer 2

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

Ich mag es nicht, dass MAX(.) GROUP BY Unentschieden bricht... Es muss einen Weg geben, das Datum der Mahlzeit in das JOIN aufzunehmen, um auf irgendeine Weise die neueste auszuwählen...

Ich bin interessiert am Abfrageplan für dieses Ding, wenn Sie es auf Ihren Live-Daten ausführen!

Answer 3

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)

Answer 4

Hier ist eine Aussage, von der ich glaube, dass sie das ist, was Sie wollen, und die einfach und prägnant ist:

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

Bitte lassen Sie mich wissen, was Sie denken.

Übrigens, das distinct on Feature ist nur in Postgres verfügbar.

Beispiel, Ausgangsdaten:

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

Ergebnis:

country | food_id
US        1
GB        3