mysql - valor - sql promedio varios campos

Obtenga registros con el valor máximo para cada grupo de resultados de SQL agrupados

Question

¿Cómo se obtienen las filas que contienen el valor máximo para cada conjunto agrupado?

He visto algunas variaciones demasiado complicadas en esta pregunta, y ninguna con una buena respuesta. Intenté armar el ejemplo más simple posible:

Dada una tabla como la siguiente, con columnas de persona, grupo y edad, ¿cómo conseguirías a la persona más vieja en cada grupo? (Un empate dentro de un grupo debe dar el primer resultado alfabético)

Person | Group | Age
---
Bob | 1 | 32
Jill | 1 | 34
Shawn| 1 | 42
Jake | 2 | 29
Paul | 2 | 36
Laura| 2 | 39

Conjunto de resultados deseado:

Shawn | 1 | 42
Laura | 2 | 39

Answer 1

Así es como obtengo las N filas máximas por grupo en mysql

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE co.country = ci.country AND co.id < ci.id
) < 1
;

cómo funciona:

• únete a la mesa
• los grupos son hechos por co.country = ci.country
• N elementos por grupo están controlados por ) < 1 por lo que para 3 elementos -) <3
• obtener máximo o mínimo depende de: co.id < ci.id
  • co.id <ci.id - max
  • co.id> ci.id - min

Ejemplo completo aquí:

mysql selecciona n valores máximos por grupo

Answer 2

Este método tiene el beneficio de permitirte clasificar por una columna diferente y no descartar los otros datos. Es bastante útil en una situación en la que está tratando de hacer una lista de pedidos con una columna para artículos, enumerando primero los más pesados.

Fuente: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT person, group,
GROUP_CONCAT(
DISTINCT age
ORDER BY age DESC SEPARATOR '', follow up: ''
)
FROM sql_table
GROUP BY group;

Answer 3

Hay una manera súper simple de hacer esto en mysql:

select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

Esto funciona porque en mysql no puedes agregar columnas que no sean grupales, en cuyo caso mysql solo devuelve la primera fila. La solución es ordenar primero los datos de modo que para cada grupo la fila que desea sea la primera, luego agrupe por las columnas para las que desea el valor.

Evita las subconsultas complicadas que intentan encontrar max() , etc. y también los problemas de devolver varias filas cuando hay más de una con el mismo valor máximo (como harían las otras respuestas)

Nota: Esta es una solución de solo mysql . Todas las otras bases de datos que conozco generarán un error de sintaxis SQL con el mensaje "las columnas no agregadas no se enumeran en el grupo por cláusula" o similar. Debido a que esta solución utiliza un comportamiento no documentado , es posible que los más cautelosos quieran incluir una prueba para afirmar que sigue funcionando en caso de que una versión futura de MySQL modifique este comportamiento.

Actualización de la versión 5.7:

Desde la versión 5.7, la configuración del sql-mode incluye ONLY_FULL_GROUP_BY de forma predeterminada, por lo que para que esto funcione no debe tener esta opción (edite el archivo de opciones para que el servidor elimine esta configuración).

Answer 4

La solución correcta es:

SELECT o.*
FROM `Persons` o # ''o'' from ''oldest person in group''
LEFT JOIN `Persons` b # ''b'' from ''bigger age''
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL # bigger age not found

Cómo funciona:

Coincide con cada fila de o con todas las filas de b tienen el mismo valor en la columna Group y un valor más grande en la columna Age . Cualquier fila de o no tenga el valor máximo de su grupo en la columna Age coincidirá con una o más filas de b .

La LEFT JOIN hace que coincida con la persona más anciana del grupo (incluidas las personas que están solas en su grupo) con una fila llena de NULL s de b ("sin mayor edad en el grupo").
El uso de INNER JOIN hace que estas filas no coincidan y se ignoran.

La cláusula WHERE mantiene solo las filas que tienen NULL en los campos extraídos de b . Ellos son las personas más viejas de cada grupo.

Lecturas adicionales

Esta solución y muchas otras se explican en el libro SQL Antipatterns: Cómo evitar las trampas de la programación de bases de datos

Answer 5

La solución de axiac es lo que mejor funcionó para mí al final. Sin embargo, tenía una complejidad adicional: un "valor máximo" calculado, derivado de dos columnas.

Usemos el mismo ejemplo: me gustaría la persona más vieja en cada grupo. Si hay personas que son igualmente viejas, toma la persona más alta.

Tuve que realizar la combinación izquierda dos veces para obtener este comportamiento:

SELECT o1.* WHERE
(SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL) o1
LEFT JOIN
(SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height
WHERE o2.Height is NULL;

¡Espero que esto ayude! Supongo que debería haber una mejor manera de hacer esto ...

Answer 6

Mi solución simple para SQLite (y probablemente MySQL):

SELECT *, MAX(age) FROM mytable GROUP BY `Group`;

Sin embargo, no funciona en PostgreSQL y tal vez en otras plataformas.

En PostgreSQL puede usar la cláusula DISTINCT ON :

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;

Answer 7

Mi solución solo funciona si necesita recuperar solo una columna, sin embargo, para mis necesidades era la mejor solución encontrada en términos de rendimiento (¡solo usa una sola consulta!):

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),'','',1) AS xyz,
column_z
FROM table_name
GROUP BY column_z;

Utiliza GROUP_CONCAT para crear una lista ordenada de concat y luego subcadenas solo al primero.

Answer 8

No estoy seguro de si MySQL tiene la función row_number. Si es así, puede usarlo para obtener el resultado deseado. En SQL Server puede hacer algo similar a:

CREATE TABLE p
(
person NVARCHAR(10),
gp INT,
age INT
);
GO
INSERT INTO p
VALUES (''Bob'', 1, 32);
INSERT INTO p
VALUES (''Jill'', 1, 34);
INSERT INTO p
VALUES (''Shawn'', 1, 42);
INSERT INTO p
VALUES (''Jake'', 2, 29);
INSERT INTO p
VALUES (''Paul'', 2, 36);
INSERT INTO p
VALUES (''Laura'', 2, 39);
GO
SELECT t.person, t.gp, t.age
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
FROM p
) t
WHERE t.row = 1;

Answer 9

No usaría Group como nombre de columna ya que es una palabra reservada. Sin embargo, el siguiente SQL funcionaría.

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN
(
SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME]
GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest

Answer 10

Puede unirse contra una subconsulta que extrae el MAX(Group) y la Age . Este método es portátil en la mayoría de los RDBMS.

SELECT
yourtable.*
FROM
yourtable
JOIN (
SELECT `Group`, MAX(Age) AS age
FROM yourtable
GROUP BY `Group`
) maxage
/* join subquery against both Group and Age values */
ON yourtable.`Group` = maxage.`Group`
AND yourtable.Age = maxage.age

Answer 11

Si ID (y todos los coulmns) se necesita de mytable

SELECT
*
FROM
mytable
WHERE
id NOT IN (
SELECT
A.id
FROM
mytable AS A
JOIN mytable AS B ON A. GROUP = B. GROUP
AND A.age < B.age
)

Answer 12

También puedes probar

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

Answer 13

Tengo una solución simple al usar WHERE IN

SELECT a.* FROM `mytable` AS a
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )
ORDER BY a.group ASC, a.person ASC

Answer 14

Usando el método de clasificación.

SELECT @rn := CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,
@prev_grp :=groupa,
person,age,groupa
FROM users,(SELECT @rn := 0) r
HAVING rn=1
ORDER BY groupa,age DESC,person

Answer 15

Uso de CTE - Expresiones de tabla comunes:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
)
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2
--Note: MyTablePKID is the PrimaryKey of MyTable

Answer 16

deja que el nombre de la tabla sea personas

select O.* -- > O for oldest table
from people O , people T
where O.grp = T.grp and
O.Age =
(select max(T.age) from people T where O.grp = T.grp
group by T.grp)
group by O.grp;

Answer 17

with CTE as
(select Person,
[Group], Age, RN= Row_Number()
over(partition by [Group]
order by Age desc)
from yourtable)`
`select Person, Age from CTE where RN = 1`