subconsulta - MySQL ordenar por antes de agrupar por

ordenar por fecha mysql

Question

Hay muchas preguntas similares que se pueden encontrar aquí, pero no creo que ninguna responda adecuadamente a la pregunta.

Continuaré con la question actual más popular y usaré su ejemplo si eso 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 es la última publicación que se devuelve.

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 actual 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 sencilla y equivocada, 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

Mi pregunta es simple entonces: ¿hay que ordenar filas antes de agruparlas sin recurrir a una subconsulta?

Edit : Esta pregunta fue una continuación de otra pregunta y los detalles de mi situación son ligeramente diferentes. Puede (y debe) asumir que también hay un wp_posts.id que es un identificador único para esa publicación en particular.

Answer 1

** Las consultas secundarias pueden tener un impacto negativo en el rendimiento cuando se usan con grandes conjuntos de datos **

Consulta original

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;

Consulta modificada

SELECT p.post_status,
p.post_type,
Max(p.post_date),
p.post_author
FROM wp_posts P
WHERE p.post_status = "publish"
AND p.post_type = "post"
GROUP BY p.post_author
ORDER BY p.post_date;

porque estoy usando max en la select clause ==> max(p.post_date) es posible evitar las consultas de sub selección y ordenar por la columna max después del grupo.

Answer 2

No. No tiene sentido ordenar los registros antes de agrupar, ya que la agrupación mutará el conjunto de resultados. La forma de subconsulta es la forma preferida. Si esto va demasiado lento, tendría que cambiar el diseño de su tabla, por ejemplo, almacenando el id. De la última publicación para cada autor en una tabla separada, o introducir una columna booleana que indique para cada autor cuál de sus publicaciones es la última. uno.

Answer 3

Para recapitular, la solución estándar utiliza una subconsulta no correlacionada y se ve así:

SELECT x.*
FROM my_table x
JOIN (SELECT grouping_criteria,MAX(ranking_criterion) max_n FROM my_table GROUP BY grouping_criteria) y
ON y.grouping_criteria = x.grouping_criteria
AND y.max_n = x.ranking_criterion;

Si está utilizando una versión antigua de MySQL, o un conjunto de datos bastante pequeño, puede usar el siguiente método:

SELECT x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.joining_criteria = x.joining_criteria
AND y.ranking_criteria < x.ranking_criteria
WHERE y.some_non_null_column IS NULL;

Answer 4

Primero, no use * en seleccionar, afecta su rendimiento y obstaculiza el uso del grupo y el orden por. Intenta esta consulta:

SELECT wp_posts.post_author, wp_posts.post_date as pdate FROM wp_posts
WHERE wp_posts.post_status=''publish''
AND wp_posts.post_type=''post''
GROUP BY wp_posts.post_author
ORDER BY pdate DESC

Cuando no especifica la tabla en ORDER BY, solo el alias, ordenarán el resultado de la selección.

Answer 5

Prueba este. Solo obtén la lista de las últimas fechas de publicación de cada autor . Eso es

SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status=''publish''
AND wp_posts.post_type=''post'' AND wp_posts.post_date IN(SELECT MAX(wp_posts.post_date) FROM wp_posts GROUP BY wp_posts.post_author)

Answer 6

Solo usa la función max y la función group.

 select max(taskhistory.id) as id from taskhistory
group by taskhistory.taskid
order by taskhistory.datum desc

Answer 7

Su solución hace uso de una extensión a 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 se enumeran en el grupo por cláusula, o que no se utilizan en una función agregada (MIN, MAX, COUNT, etc.).

Uso correcto de la extensión a la cláusula GROUP BY

Esto es útil cuando todos los valores de las columnas no agregadas son iguales para cada fila.

Por ejemplo, suponga que tiene una mesa GardensFlowers ( name del jardín, flower que 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 jardinero, puede cambiar la condición de HAVING COUNT(DISTINCT flower)=1 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;

No hay subconsulta, ni SQL estándar, sino más simple.

Uso incorrecto de la extensión a 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 MySQL elige para esa columna?

Parece que MySql siempre elige el primer valor que encuentra.

Para asegurarse de que el primer valor que encuentra sea exactamente el valor que desea, debe aplicar un GROUP BY a una consulta ordenada, de ahí la necesidad de usar una subconsulta. No puedes hacerlo de otra manera.

Dado el supuesto de que MySql siempre elige la primera fila que encuentra, usted está clasificando correctamente las filas antes de GROUP BY. Pero, desafortunadamente, si lee la documentación detenidamente, notará que esta suposición no es cierta.

Al seleccionar columnas no agregadas que no siempre son iguales, MySql puede 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, lo uso también a veces (a mi propio riesgo). Pero como no está documentado, no puedes confiar en este comportamiento.

Este enlace (¡gracias a ypercube!) GROUP BY ha sido optimizado y muestra una situación en la que la misma consulta arroja resultados diferentes entre MySql y MariaDB, probablemente debido a un motor de optimización diferente.

Entonces, si este truco funciona, es solo una cuestión de suerte.

La respuesta aceptada en la otra pregunta me parece mal:

HAVING wp_posts.post_date = MAX(wp_posts.post_date)

wp_posts.post_date es una columna no agregada, y su valor será oficialmente indeterminado, pero probablemente será la primera post_date encontrada. Pero como el truco GROUP BY se aplica a una tabla desordenada, no está seguro de cuál es la primera fecha post_date la post_date .

Probablemente devolverá las publicaciones que son las únicas publicaciones de un solo autor, pero incluso esto no siempre es cierto.

Una posible solucion

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 de publicación máxima para cada autor. Luego estoy considerando el hecho de que el mismo autor podría tener dos publicaciones al mismo tiempo, por lo que solo obtengo la ID máxima. Y luego estoy devolviendo todas las filas que tienen esos ID máximos. Se podría hacer más rápido usando uniones en lugar de una cláusula IN.

(Si está seguro de que la ID solo aumenta, y si ID1 > ID2 también significa que post_date1 > post_date2 , la consulta podría ser mucho más sencilla, pero no estoy seguro de que este sea el caso).

Answer 8

Usar un ORDER BY una subconsulta no es la mejor solución para este problema.

La mejor solución para obtener el max(post_date) por autor es usar una subconsulta para devolver la fecha máxima y luego unirla a su tabla tanto en el post_author como en la fecha máxima.

La solución debe 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 va a devolver la fecha máxima y el autor de:

MaxPostDate | Author
2/1/2013 | Jim

Luego, ya que está uniendo eso de nuevo a la tabla, en ambos valores devolverá los detalles completos de esa publicación.

Ver SQL Fiddle con Demo .

Para ampliar mis comentarios sobre el uso de una subconsulta para obtener con precisión estos datos.

MySQL no lo obliga a GROUP BY cada columna que incluya en la lista SELECT . Como resultado, si solo GROUP BY una columna pero devuelve 10 columnas en total, no hay garantía de que los otros valores de columna que pertenecen al post_author que se devuelve. Si la columna no está en un GROUP BY MySQL, elige qué valor debe devolverse.

El uso de la subconsulta con la función agregada garantizará que el autor y la publicación correctos se devuelvan cada vez.

Como nota al margen, mientras que MySQL le permite usar un ORDER BY una subconsulta y le permite aplicar un GROUP BY no todas las columnas de la lista SELECT este comportamiento no está permitido en otras bases de datos, incluido SQL Server.

Answer 9

Lo que vas a leer es bastante raro, ¡así que no intentes esto en casa!

En SQL en general, la respuesta a su pregunta es NO , pero debido al modo relajado de 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 bajo el capó?

(post_status = ''publish'', post_type = ''post'', post_author = ''user A'', post_date = ''2012-12-01'') (post_status = ''publish'', post_type = ''post'', post_author = ''user A'', post_date = ''2012-12-31'') (post_status = ''publish'', post_type = ''post'', post_author = ''user B'', post_date = ''2012-10-01'') (post_status = ''publish'', post_type = '' post '', post_author ='' usuario B '', post_date ='' 2012-12-01 '')

Es decir, los datos están ordenados por todos esos campos en orden ascendente.

Cuando está haciendo un GROUP BY por defecto, ordena los datos por el campo de agrupación ( post_author , en nuestro caso; post_status, post_type son requeridos por la cláusula WHERE ) y si hay un índice coincidente, toma los datos de cada primer registro en forma ascendente orden. Esa es la consulta que obtendrá lo siguiente (la primera publicación para cada usuario):

(post_status = ''publish'', post_type = ''post'', post_author = ''user A'', post_date = ''2012-12-01'') (post_status = ''publish'', post_type = ''post'', post_author = ''user B'', post_date = ''2012-10-01'')

Pero GROUP BY en MySQL te permite especificar el orden explícitamente. Y cuando solicite post_user en orden descendente, recorrerá nuestro índice en el orden opuesto, aún tomando el primer registro para cada grupo que sea el último.

Es decir

...
WHERE wp_posts.post_status=''publish'' AND wp_posts.post_type=''post''
GROUP BY wp_posts.post_author DESC

nos dará

(post_status = ''publish'', post_type = ''post'', post_author = ''user B'', post_date = ''2012-12-01'') (post_status = ''publish'', post_type = ''post'', post_author = ''user A'', post_date = ''2012-12-31'')

Ahora, cuando ordena los resultados de la agrupación por post_date, obtiene los datos que desea.

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;

NB :

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 puede ser muy útil. Eche un vistazo a mi respuesta aquí: Recuperar el último registro de cada grupo

Escollos : Las desventajas del enfoque es que

• el resultado de la consulta depende del índice, que va en contra del espíritu del SQL (los índices solo deberían acelerar las consultas);
• El índice no sabe nada sobre su influencia en la consulta (usted u otra persona en el futuro puede encontrar que el índice consume demasiado recursos y puede cambiarlo de alguna manera, rompiendo los resultados de la consulta, no solo su rendimiento)
• Si no entiende cómo funciona la consulta, lo más probable es que olvide la explicación en un mes y la consulta lo confundirá a usted y a sus colegas.

La ventaja es el rendimiento en casos difíciles. En este caso, el rendimiento de la consulta debe 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 requiere la (post_status, post_type, post_author, post_date) también.

Lo que yo sugeriría :

Como dije, esas consultas hacen que MySQL pierda tiempo clasificando potencialmente enormes cantidades de datos en una tabla temporal. En caso de que necesite la paginación (es decir, que LIMIT esté involucrado), la mayoría de los datos se descartan. Lo que haría sería minimizar la cantidad de datos ordenados: eso es ordenar y limitar un mínimo de datos en la subconsulta y luego volver a unir la tabla completa.

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 .