Obtener el último conjunto distinto de los registros

Tengo una tabla de base de datos que contiene las siguientes columnas:

id   code   value   datetime   timestamp

En esta tabla con el sólo valores únicos residen en id, es decir, la clave principal.

Quiero recuperar el último conjunto distinto de registros en esta tabla se basa en el valor de datetime. Por ejemplo, vamos a decir a continuación es de mi mesa

id   code   value   datetime               timestamp
1    1023   23.56   2011-04-05 14:54:52    1234223421
2    1024   23.56   2011-04-05 14:55:52    1234223423
3    1025   23.56   2011-04-05 14:56:52    1234223424
4    1023   23.56   2011-04-05 14:57:52    1234223425
5    1025   23.56   2011-04-05 14:58:52    1234223426
6    1025   23.56   2011-04-05 14:59:52    1234223427
7    1024   23.56   2011-04-05 15:00:12    1234223428
8    1026   23.56   2011-04-05 15:01:14    1234223429
9    1025   23.56   2011-04-05 15:02:22    1234223430

Quiero recuperar los registros con Id 4, 7, 8, y 9 es decir, el último conjunto de registros con distintos códigos (basado en el valor de datetime). Lo que he resaltado es simplemente un ejemplo de lo que estoy tratando de lograr, ya que esta tabla se va a contener millones de registros, y varios cientos de los valores de código.

¿Qué instrucción SQL se puede utilizar para lograr esto? Me parece que no puede hacerlo con una única instrucción SQL. Mi base de datos MySQL 5.

5 Kommentare

  1. 72

    Esto debería funcionar para usted.

     SELECT * 
     FROM [tableName] 
     WHERE id IN (SELECT MAX(id) FROM [tableName] GROUP BY code)

    Si id es AUTO_INCREMENT, no hay necesidad de preocuparse acerca de la datetime que es mucho más caro para calcular, como el más reciente datetime también tendrá el id más alto.

    Actualización: Desde un punto de vista del rendimiento, asegúrese de que el id y code columnas están indexados al tratar con un gran número de registros. Si id es la clave principal, esto es, construido en el, pero usted puede necesitar agregar un índice no agrupado cubriendo code y id.

    • +1 para evitar datetime pruebas si autoincrement… me tomé la libertad de volver a formatear la respuesta.
    • Funciona como un encanto! Muchas gracias por esta.
    • Muy Bonito… salvo mi tiempo.
    • no trabajo a un gran número de filas.
    • Tuve que implementar esto en Elocuente ORM… trabajó primer intento, gracias
  2. 9

    Intente esto:

    SELECT * 
      FROM <YOUR_TABLE>
     WHERE (code, datetime, timestamp) IN
     (
       SELECT code, MAX(datetime), MAX(timestamp)
         FROM <YOUR_TABLE>
        GROUP BY code
     )
    • Ya que la tabla tiene una clave principal, usted no tiene que hacer un complicado cláusula where. ver mi respuesta.
    • De acuerdo, pero que requiere una asunción de la identificación de ser de incremento automático.
    • no, en absoluto, smdrager respuesta, la subconsulta en la mina vuelve el buen identificador de usar debido a la having cláusula
  3. 3

    Es y viejo post, pero las pruebas @smdrager respuesta con tablas de gran tamaño era muy lento. Mi solución a este fue el uso de «inner join» en lugar de «¿en dónde».

    SELECT * 
     FROM [tableName] as t1
     INNER JOIN (SELECT MAX(id) as id FROM [tableName] GROUP BY code) as t2
     ON t1.id = t2.id

    Esto funcionó muy rápido.

    • Gracias. Voy a darle una oportunidad al siguiente que tengo que usar esto.
  4. 1

    Voy a intentar algo como esto :

    select * from table
    where id in (
        select id
        from table
        group by code
        having datetime = max(datetime)
    )

    (descargo de responsabilidad: esto no es probado)

    Si la fila con la mayor datetime también tienen la más grande de identificación, la solución propuesta por smdrager es más rápido.

    • gracias a ello, trabajó para mí ++
  5. 0

    Parece como si todas las respuestas sugieren hacer GROUP BY code en toda la tabla. Cuando es lógicamente correcto, en realidad esta consulta va a ir a través de todo(!) tabla (uso EXPLAIN para asegurarse). En mi caso, tengo menos de 500k de filas en la tabla y la ejecución de ...GROUP BY codetoma de 0.3 segundos, que no es en absoluto aceptable.

    Sin embargo puedo usar el conocimiento de mis datos aquí (se lee «mostrar los últimos comentarios de posts»):

    • Necesito para seleccionar solo top-20 los registros de
    • Cantidad de registros con el mismo código a través de la última X registros es relativamente pequeño
    • Cantidad Total de registros >> cantidad de code‘s >> la cantidad de la «parte superior» de los registros que desea obtener

    Por experimentar con los números me enteré de que siempre se puede encontrar a más de 20 diferentes code si puedo elegir sólo los últimos 50 registros. Y en este caso siguiente consulta (teniendo en cuenta @smdrager comentario sobre la alta probabilidad de utilizar id en lugar de datetime)

    SELECT id, code
    FROM tablename
    ORDER BY id DESC 
    LIMIT 50

    Seleccionando sólo los últimos 50 entradas es super rápido, debido a que no tiene la necesidad de revisar toda la tabla. Y el resto es para seleccionar el top-20 con distintas code de los 50 entradas.

    Obviamente, las consultas en el set de 50 (100, 500) elementos son significativamente más rápido que en la tabla completa con cientos de miles de entradas.

    Raw SQL «Postproceso»

    SELECT MAX(id) as id, code FROM 
        (SELECT id, code
         FROM tablename
         ORDER BY id DESC 
         LIMIT 50) AS nested 
    GROUP BY code
    ORDER BY id DESC 
    LIMIT 20

    Esto le dará la lista de id‘s realmente rápido y si desea realizar otras Combinaciones, poner esta consulta como una consulta anidada y realizar todas las combinaciones en él.

    Servidor-lado «Postproceso»

    Y después de que usted necesita para procesar los datos en el lenguaje de programación para incluir al conjunto final sólo los registros con distintos code.

    Algún tipo de Python pseudocódigo:

    records = select_simple_top_records(50)
    added_codes = []
    top_records = []
    for record in records:
        # If record for this code was already found before
        # Note: this is not optimal, better to use structure allowing O(1) search and insert
        if record['code'] in added_codes:
            continue
        # Save record
        top_records.append(record)
        added_codes.append(record['code'])
        # If we found all top-20 required, finish
        if len(top_records) >= 20:
            break

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea