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

Es ist jetzt noch einfacher: PostgreSQL 9.4 hat die mode() Funktion eingeführt:

select mode() within group (order by food_id)
from munch
group by country

gibt zurück (wie im Beispiel von user2247323):

country | mode
--------------
GB      | 3
US      | 1

Siehe Dokumentation hier: https://wiki.postgresql.org/wiki/Aggregate_Mode

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE

Answer 2

PostgreSQL hat in Version 8.4 die Unterstützung für Window-Funktionen eingeführt, was ein Jahr nach dem Zeitpunkt dieser Frage war. Es sei darauf hingewiesen, dass es heute möglicherweise wie folgt gelöst werden könnte:

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;

Das oben Genannte wird bei Gleichstand aufgelöst. Wenn Sie kein Auflösen von Gleichständen wünschen, könnten Sie stattdessen DENSE_RANK() verwenden.

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
    )

Nun ja, ich habe das hier schnell geschrieben und nicht wirklich gut überprüft. Die Unterabfrage könnte ziemlich langsam sein, aber dies ist die kürzeste und einfachste SQL-Anweisung, die mir eingefallen ist. Ich werde wahrscheinlich mehr erzählen, wenn ich weniger betrunken bin.

PS: Nun ja, "foo" ist der Name meiner Tabelle, "food" enthält den Namen des Essens und "country" den Namen des Landes. Beispielausgabe:

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

Answer 4

Versuche dies:

Wähle Land, Food_id
Von Munch T1
Wo Food_id= 
    (Wähle Food_id
     von Munch T2
     wo T1.Land= T2.Land
     gruppiert nach Food_id
     sortiert nach count(Food_id) desc
     limit 1)
gruppiert nach Land, Food_id

Answer 5

Probieren Sie etwas Ähnliches wie dieses aus:

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)

Dies könnte alles in ein einziges SELECT gesteckt werden, aber ich habe gerade keine Zeit, mich damit herumzuschlagen.

Viel Glück.