funciones - Obtener el valor más común para cada valor de otra columna en SQL

sql server columna calculada condicional

Question

Tengo una mesa como esta:

 Column  | Type | Modifiers 
---------+------+-----------
 country | text | 
 food_id | int  | 
 eaten   | date | 

Y para cada país, quiero obtener la comida que se consume con más frecuencia. Lo mejor que puedo pensar (estoy usando postgres) es:

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;

En esa última declaración, se necesitan GROUP BY y max () para romper empates, donde dos alimentos diferentes tienen el mismo conteo.

Esto parece mucho trabajo para algo conceptualmente simple. ¿Hay una forma más directa de hacerlo?

Answer 1

Ahora es incluso más simple: PostgreSQL 9.4 introdujo la función de mode() :

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

devoluciones (como el ejemplo de user2247323):

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

Consulte la documentación aquí: https://wiki.postgresql.org/wiki/Aggregate_Mode

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

Answer 2

Aquí hay una declaración que creo que le da lo que quiere y es simple y concisa:

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

Por favor dejame saber lo que tu piensas.

Por cierto, la característica distintiva de encendido solo está disponible en Postgres.

Ejemplo, datos fuente:

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

salida:

country | food_id
US        1
GB        3

Answer 3

He aquí cómo hacerlo sin tablas temporales:

Editar: simplificado

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 4

PostgreSQL introdujo el soporte para funciones de ventana en 8.4, el año posterior a la pregunta. Vale la pena señalar que podría resolverse hoy de la siguiente manera:

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;

Lo anterior romperá lazos. Si no quiere romper lazos, podría usar DENSE_RANK () en su lugar.

Answer 5

Pruebe algo como esto

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)

Esto podría ponerse todo en una sola selección, pero no tengo tiempo para perder el tiempo ahora.

Buena suerte.

Answer 6

prueba esto:

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

Answer 7

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
    )

Bueno, escribí esto con prisa y no lo revisé realmente bien. La sub-selección puede ser bastante lenta, pero esta es la declaración de SQL más corta y simple que pude pensar. Probablemente diga más cuando estoy menos ebrio.

PD: Bueno, "foo" es el nombre de mi mesa, "comida" contiene el nombre de la comida y "país" es el nombre del país. Muestra de salida:

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

Answer 8

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

No me gusta el MAX (.) GROUP BY para romper lazos ... Tiene que haber una manera de incorporar la fecha en el JOIN de alguna manera para seleccionar arbitrariamente la más reciente ...

¡Estoy interesado en el plan de consulta para esto si lo ejecutas en tus datos en vivo!

Answer 9

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)