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)