precios - sql database for beginners

Pregunta de mejor rendimiento para "seleccionar max en grupo"?

Question

Tengo una tabla simple de comentarios (id INT, revision INT, comment VARCHAR(140)) con un contenido como este:

1|1|hallo1|
1|2|hallo2|
1|3|hallo3|
2|1|hallo1|
2|2|hallo2|

Estoy buscando una declaración SQL que devolverá cada comentario con la revisión más alta:

1|3|hallo3|
2|2|hallo2|

He encontrado esta solución:

select id, revision, comment 
  from comments 
  where revision = (
      select max(revision) 
        from comments as f 
        where f.id = comments.id
  );

pero es muy lento en grandes conjuntos de datos. ¿Hay mejores consultas para lograr esto?

Answer 1

Esta es una forma de que con la indexación adecuada no sea tremendamente lenta y no use una subselección:

SELECT comments.ID, comments.revision, comments.comment FROM comments 
LEFT OUTER JOIN comments AS maxcomments 
ON maxcomments.ID= comments.ID
AND maxcomments.revision > comments.revision
WHERE maxcomments.revision IS NULL

Adaptado de las consultas aquí: http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-in-sql-without-subqueries/

(De la búsqueda de google: grupo máximo por sql)

Answer 2

Idea del campo izquierdo, pero ¿qué tal agregar un campo adicional a la tabla?

CurrentRevision bit not null

Luego, cuando realice un cambio, configure el indicador en la nueva revisión y elimínelo en todos los anteriores.

Su consulta simplemente se convertiría en:

select  Id,
        Comment
from    Comments
where   CurrentRevision = 1

Esto sería mucho más fácil en la base de datos y, por lo tanto, mucho más rápido.

Answer 3

Una manera bastante clara de hacer las consultas de tipo "latest x by id" es esta. También debería ser bastante fácil de indexar correctamente.

SELECT id, revision, comment 
FROM comments
WHERE (id, revision) IN (
  SELECT id, MAX(revision)
  FROM comments
  -- WHERE clause comes here if needed
  GROUP BY id
)

Answer 4

Para las grandes tablas, creo que esta solución puede tener un mejor rendimiento:

    SELECT c1.id, 
           c1.revision, 
           c1.comment 
      FROM comments c1 
INNER JOIN ( SELECT id, 
                max(revision) AS max_revision
               FROM comments 
           GROUP BY id ) c2
        ON c1.id = c2.id
       AND c1.revision = c2.max_revision

Answer 5

Analytics sería mi recomendación.

select id, max_revision, comment
from (select c.id, c.comment, c.revision, max(c.revision)over(partition by c.id) as max_revision
      from comments c)
where revision = max_revision;

Answer 6

1. Asegúrese de tener sus índices configurados apropiadamente. Indización en id, la revisión sería buena.

2. Aquí hay una versión diferente de su consulta. No ha revisado su plan de ejecución, pero si configura bien el índice, debería ayudar:

   SELECT c.* 
     FROM comments c
     INNER JOIN (
           SELECT id,max(revision) AS maxrev 
             FROM comments 
             GROUP BY id
     ) b
       ON c.id=b.id AND c.revision=b.maxrev

Editado para agregar:

3. Si está en SQL Server, también debería verificar las vistas indizadas:
   http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Editado de nuevo para agregar información:

Subquery:
25157 records
2 seconds
Execution plan includes an Index Seek (82%) base and a Segment (17%)

Left Outer Join:
25160 records
3 seconds
Execution plan includes two Index Scans @ 22% each with a Right Outer Merge at 45% and a Filter at 11%

Todavía iría con la sub consulta.

Answer 7

Probado con una de nuestras tablas que tiene casi 1 millón de filas en total. Los índices existen en ambos campos FIELD2 Y FIELD3. Query devolvió 83953 filas en menos de 3 segundos en nuestro cuadro dev.

select
FIELD1, FIELD2, FIELD3
from
OURTABLE (nolock) T1
WHERE FIELD3 = 
(
SELECT MAX(FIELD3) FROM 
OURTABLE T2 (nolock)
WHERE T1.FIELD2=T2.FIELD2
)
ORDER BY FIELD2 DESC

Answer 8

Sin subselecciones (o tablas temporales):

SELECT c1.ID, c1.revision, c1.comment 
FROM comments AS c1
LEFT JOIN comments AS c2 
    ON c1.ID = c2.ID
    AND c1.revision < c2.revision
WHERE c2.revision IS NULL