Orden de MySQL por antes de agrupar por
Hay muchas preguntas similares aquí, pero no creo que ninguna responda la pregunta adecuadamente.
Continuaré con la pregunta más popular actual y usaré su ejemplo si está bien.
La tarea en este caso es obtener la última publicación de cada autor en la base de datos.
La consulta de ejemplo produce resultados inutilizables ya que no siempre se devuelve la última publicación.
SELECT wp_posts.* FROM wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
ORDER BY wp_posts.post_date DESC
La respuesta actualmente aceptada es
SELECT
wp_posts.*
FROM wp_posts
WHERE
wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
HAVING wp_posts.post_date = MAX(wp_posts.post_date) <- ONLY THE LAST POST FOR EACH AUTHOR
ORDER BY wp_posts.post_date DESC
Desafortunadamente, esta respuesta es simple y llanamente incorrecta y en muchos casos produce resultados menos estables que la consulta original.
Mi mejor solución es utilizar una subconsulta del formulario
SELECT wp_posts.* FROM
(
SELECT *
FROM wp_posts
ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
Entonces mi pregunta es simple: ¿Existe alguna forma de ordenar las filas antes de agruparlas sin recurrir a una subconsulta?
Editar : esta pregunta fue una continuación de otra pregunta y los detalles de mi situación son ligeramente diferentes. Puedes (y debes) asumir que también hay un wp_posts.id que es un identificador único para esa publicación en particular.
Usar un ORDER BYen una subconsulta no es la mejor solución a este problema.
La mejor solución para obtener el max(post_date)autor es utilizar una subconsulta para devolver la fecha máxima y luego unirla a su tabla tanto en la fecha máxima post_authorcomo en la fecha máxima.
La solución debería ser:
SELECT p1.*
FROM wp_posts p1
INNER JOIN
(
SELECT max(post_date) MaxPostDate, post_author
FROM wp_posts
WHERE post_status='publish'
AND post_type='post'
GROUP BY post_author
) p2
ON p1.post_author = p2.post_author
AND p1.post_date = p2.MaxPostDate
WHERE p1.post_status='publish'
AND p1.post_type='post'
order by p1.post_date desc
Si tiene los siguientes datos de muestra:
CREATE TABLE wp_posts
(`id` int, `title` varchar(6), `post_date` datetime, `post_author` varchar(3))
;
INSERT INTO wp_posts
(`id`, `title`, `post_date`, `post_author`)
VALUES
(1, 'Title1', '2013-01-01 00:00:00', 'Jim'),
(2, 'Title2', '2013-02-01 00:00:00', 'Jim')
;
La subconsulta devolverá la fecha máxima y el autor de:
MaxPostDate | Author
2/1/2013 | Jim
Luego, dado que se unirá nuevamente a la tabla, en ambos valores devolverá los detalles completos de esa publicación.
Consulte SQL Fiddle con demostración .
Para ampliar mis comentarios sobre el uso de una subconsulta para devolver estos datos con precisión.
MySQL no te obliga a acceder a GROUP BYtodas las columnas que incluyes en la SELECTlista. Como resultado, si solo devuelve GROUP BYuna columna pero devuelve 10 columnas en total, no hay garantía de que se devuelvan los valores de las otras columnas que pertenecen a la post_authorque se devuelve. Si la columna no está en GROUP BYMySQL, elige qué valor se debe devolver.
El uso de la subconsulta con la función agregada garantizará que se devuelva el autor y la publicación correctos en todo momento.
Como nota al margen, si bien MySQL le permite usar un ORDER BYen una subconsulta y le permite aplicar un GROUP BYa no a todas las columnas de la SELECTlista, este comportamiento no está permitido en otras bases de datos, incluido SQL Server.
Su solución utiliza una extensión de la cláusula GROUP BY que permite agrupar por algunos campos (en este caso, solo post_author):
GROUP BY wp_posts.post_author
y seleccione columnas no agregadas:
SELECT wp_posts.*
que no figuran en la cláusula group by, o que no se utilizan en una función agregada (MIN, MAX, COUNT, etc.).
Uso correcto de la extensión de la cláusula GROUP BY
Esto resulta útil cuando todos los valores de las columnas no agregadas son iguales para cada fila.
Por ejemplo, supongamos que tienes una mesa GardensFlowers( namedel jardín, flowerque crece en el jardín):
INSERT INTO GardensFlowers VALUES
('Central Park', 'Magnolia'),
('Hyde Park', 'Tulip'),
('Gardens By The Bay', 'Peony'),
('Gardens By The Bay', 'Cherry Blossom');
y quieres extraer todas las flores que crecen en un jardín, donde crecen múltiples flores. Luego tienes que usar una subconsulta, por ejemplo podrías usar esto:
SELECT GardensFlowers.*
FROM GardensFlowers
WHERE name IN (SELECT name
FROM GardensFlowers
GROUP BY name
HAVING COUNT(DISTINCT flower)>1);
Si necesita extraer todas las flores que son las únicas flores en el jardín, puede simplemente cambiar la condición HAVING a HAVING COUNT(DISTINCT flower)=1, pero MySql también le permite usar esto:
SELECT GardensFlowers.*
FROM GardensFlowers
GROUP BY name
HAVING COUNT(DISTINCT flower)=1;
sin subconsulta, no SQL estándar, pero es más simple.
Uso incorrecto de la extensión de la cláusula GROUP BY
Pero, ¿qué sucede si SELECCIONA columnas no agregadas que no son iguales para cada fila? ¿Cuál es el valor que elige MySql para esa columna?
Parece que MySql siempre elige el PRIMER valor que encuentra.
Para asegurarse de que el primer valor que encuentre sea exactamente el valor que desea, debe aplicar a GROUP BYuna consulta ordenada, de ahí la necesidad de utilizar una subconsulta. No puedes hacerlo de otra manera.
Dada la suposición de que MySql siempre elige la primera fila que encuentra, está ordenando correctamente las filas antes de GROUP BY. Pero desafortunadamente, si lees la documentación detenidamente, notarás que esta suposición no es cierta.
Al seleccionar columnas no agregadas que no siempre son iguales, MySql es libre de elegir cualquier valor, por lo que el valor resultante que realmente muestra es indeterminado .
Veo que este truco para obtener el primer valor de una columna no agregada se usa mucho, y generalmente/casi siempre funciona, yo también lo uso a veces (bajo mi propia responsabilidad). Pero como no está documentado, no puedes confiar en este comportamiento.
Este enlace (¡gracias ypercube!) El truco GROUP BY ha sido optimizado muestra una situación en la que la misma consulta devuelve resultados diferentes entre MySql y MariaDB, probablemente debido a un motor de optimización diferente.
Entonces, si este truco funciona, es sólo cuestión de suerte.
La respuesta aceptada a la otra pregunta me parece incorrecta:
HAVING wp_posts.post_date = MAX(wp_posts.post_date)
wp_posts.post_datees una columna no agregada y su valor será oficialmente indeterminado, pero probablemente será la primera post_dateque se encuentre. Pero dado que el truco GROUP BY se aplica a una tabla desordenada, no está seguro de cuál es la primera que post_datese encuentra.
Probablemente devolverá publicaciones que sean las únicas publicaciones de un solo autor, pero incluso esto no siempre es seguro.
Una posible solución
Creo que esta podría ser una posible solución:
SELECT wp_posts.*
FROM wp_posts
WHERE id IN (
SELECT max(id)
FROM wp_posts
WHERE (post_author, post_date) = (
SELECT post_author, max(post_date)
FROM wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY post_author
) AND wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY post_author
)
En la consulta interna, devuelvo la fecha máxima de publicación para cada autor. Luego estoy tomando en consideración el hecho de que, en teoría, el mismo autor podría tener dos publicaciones al mismo tiempo, por lo que solo obtengo la identificación máxima. Y luego devuelvo todas las filas que tienen esos ID máximos. Podría hacerse más rápido usando uniones en lugar de la cláusula IN.
(Si está seguro de que eso IDsolo está aumentando, y si ID1 > ID2también significa eso post_date1 > post_date2, entonces la consulta podría hacerse mucho más simple, pero no estoy seguro de si este es el caso).
Lo que vas a leer es bastante complicado, ¡así que no intentes esto en casa!
En SQL en general, la respuesta a su pregunta es NO , pero debido al modo relajado del GROUP BY(mencionado por @bluefeet ), la respuesta es SÍ en MySQL.
Supongamos que tiene un índice BTREE en (post_status, post_type, post_author, post_date). ¿Cómo se ve el índice debajo del capó?
(post_status='publicar', post_type='publicar', post_author='usuario A', post_date='2012-12-01') (post_status='publicar', post_type='publicar', post_author='usuario A', post_date='2012-12-31') (post_status='publicar', post_type='publicar', post_author='usuario B', post_date='2012-10-01') (post_status='publicar', post_type=' publicación', post_author='usuario B', post_date='2012-12-01')
Es decir, los datos se ordenan por todos esos campos en orden ascendente.
Cuando realiza una operación GROUP BYde forma predeterminada, ordena los datos por campo de agrupación ( post_author, en nuestro caso; post_status, post_type son requeridos por la WHEREcláusula) y si hay un índice coincidente, toma datos para cada primer registro en orden ascendente. Esa es la consulta que obtendrá lo siguiente (la primera publicación de cada usuario):
(post_status='publicar', post_type='publicar', post_author='usuario A', post_date='2012-12-01') (post_status='publicar', post_type='publicar', post_author='usuario B', post_date='2012-10-01')
Pero GROUP BYen MySQL te permite especificar el orden explícitamente. Y cuando lo solicite post_useren orden descendente, recorrerá nuestro índice en el orden opuesto, tomando aún el primer registro de cada grupo que en realidad sea el último.
Eso es
...
WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author DESC
nos dará
(post_status='publicar', post_type='publicar', post_author='usuario B', post_date='2012-12-01') (post_status='publicar', post_type='publicar', post_author='usuario A', post_date='2012-12-31')
Ahora, cuando ordenas los resultados de la agrupación por post_date, obtienes los datos que deseabas.
SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author DESC
ORDER BY wp_posts.post_date DESC;
Nota :
Esto no es lo que recomendaría para esta consulta en particular. En este caso, usaría una versión ligeramente modificada de lo que sugiere @bluefeet . Pero esta técnica podría resultar muy útil. Eche un vistazo a mi respuesta aquí: Recuperar el último registro de cada grupo
Errores : La desventaja del enfoque es que
- el resultado de la consulta depende del índice, lo que va en contra del espíritu de SQL (los índices sólo deberían acelerar las consultas);
- El índice no sabe nada sobre su influencia en la consulta (usted u otra persona en el futuro podría encontrar que el índice consume demasiados recursos y cambiarlo de alguna manera, alterando los resultados de la consulta, no solo su rendimiento).
- Si no comprende cómo funciona la consulta, lo más probable es que olvide la explicación en un mes y la consulta los confundirá a usted y a sus colegas.
La ventaja es el rendimiento en casos difíciles. En este caso, el rendimiento de la consulta debería ser el mismo que en la consulta de @bluefeet, debido a la cantidad de datos involucrados en la clasificación (todos los datos se cargan en una tabla temporal y luego se ordenan; por cierto, su consulta (post_status, post_type, post_author, post_date)también requiere el índice) .
Lo que sugeriría :
Como dije, esas consultas hacen que MySQL pierda tiempo clasificando cantidades potencialmente enormes de datos en una tabla temporal. En caso de que necesite paginación (es decir, se trata de LIMIT), la mayoría de los datos incluso se descartan. Lo que haría es minimizar la cantidad de datos ordenados: es decir, ordenar y limitar un mínimo de datos en la subconsulta y luego volver a unirlos a toda la tabla.
SELECT *
FROM wp_posts
INNER JOIN
(
SELECT max(post_date) post_date, post_author
FROM wp_posts
WHERE post_status='publish' AND post_type='post'
GROUP BY post_author
ORDER BY post_date DESC
-- LIMIT GOES HERE
) p2 USING (post_author, post_date)
WHERE post_status='publish' AND post_type='post';
La misma consulta utilizando el enfoque descrito anteriormente:
SELECT *
FROM (
SELECT post_id
FROM wp_posts
WHERE post_status='publish' AND post_type='post'
GROUP BY post_author DESC
ORDER BY post_date DESC
-- LIMIT GOES HERE
) as ids
JOIN wp_posts USING (post_id);
Todas esas consultas con sus planes de ejecución en SQLFiddle .
