▷AGRUPAR POR una columna, mientras ordena por otra en PostgreSQL ✔️ Foro Ayuda 【 2024 】

Puede que se de el caso de que halles algún error en tu código o trabajo, recuerda probar siempre en un entorno de testing antes aplicar el código al proyecto final.

Solución:

Filas consistentes

La pregunta importante que no parece estar todavía en tu radar:
De cada conjunto de filas para el mismo seriesName, quieres las columnas de una fila, o simplemente ningún valores de varias filas (que pueden ir juntas o no)?

Tu respuesta hace lo último, combinas el máximo dbid con el maximo retreivaltime, que puede provenir de una fila diferente.

Llegar consistente filas, uso DISTINCT ON y envuélvalo en una subconsulta para ordenar el resultado de manera diferente:

SELECT * FROM (
   SELECT DISTINCT ON (seriesName)
          dbid, seriesName, retreivaltime
   FROM   FileItems
   WHERE  sourceSite = 'mk' 
   ORDER  BY seriesName, retreivaltime DESC NULLS LAST  -- latest retreivaltime
   ) sub
ORDER BY retreivaltime DESC NULLS LAST
LIMIT  100;

Detalles para DISTINCT ON:

¿Seleccionar la primera fila de cada grupo GROUP BY?

Aparte: probablemente debería ser retrievalTime, o mejor aún: retrieval_time. No cotizado mixed Los identificadores de casos son una fuente común de confusión en Postgres.

Mejor rendimiento con rCTE

Dado que estamos tratando con una tabla grande aquí, necesitaríamos una consulta que pueda usar un índice, lo cual no es el caso de la consulta anterior (excepto para WHERE sourceSite = 'mk')

En una inspección más cercana, su problema parece ser un caso especial de escaneo de índice suelto. Postgres no admite escaneos de índices sueltos de forma nativa, pero se puede emular con un CTE recursivo. Hay un ejemplo de código para el caso simple en Postgres Wiki.

Respuesta relacionada sobre SO con soluciones más avanzadas, explicación, violín:

Optimice la consulta GROUP BY para recuperar el último registro por usuario

Sin embargo, su caso es más complejo. Pero creo que encontré una variante para que te funcione. Sobre la base de este índice (sin WHERE sourceSite = 'mk')

CREATE INDEX mi_special_full_idx ON MangaItems
(retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST, dbid)

O con WHERE sourceSite = 'mk')

CREATE INDEX mi_special_granulated_idx ON MangaItems
(sourceSite, retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST, dbid)

El primer índice se puede utilizar para ambas consultas, pero no es completamente eficaz con la condición adicional WHERE. El segundo índice tiene un uso muy limitado para la primera consulta. Dado que tiene ambas variantes de la consulta, considere la posibilidad de crear ambos índices.

yo añadí dbid al final para permitir Solo índice exploraciones.

Esta consulta con un CTE recursivo hace uso del índice. Probé con Postgres 9.3 y funciona para mí: sin escaneo secuencial, todos solo índice exploraciones:

WITH RECURSIVE cte AS (
   (
   SELECT dbid, seriesName, retreivaltime, 1 AS rn, ARRAY[seriesName] AS arr
   FROM   MangaItems
   WHERE  sourceSite = 'mk'
   ORDER  BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT i.dbid, i.seriesName, i.retreivaltime, c.rn + 1, c.arr || i.seriesName
   FROM   cte c
   ,      LATERAL (
      SELECT dbid, seriesName, retreivaltime
      FROM   MangaItems
      WHERE (retreivaltime, seriesName) < (c.retreivaltime, c.seriesName)
      AND    sourceSite = 'mk'  -- repeat condition!
      AND    seriesName <> ALL(c.arr)
      ORDER  BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST
      LIMIT  1
      ) i
   WHERE  c.rn < 101
   )
SELECT dbid
FROM   cte
ORDER  BY rn;

Ustedes necesitar para incluir seriesName en ORDER BY, ya que retreivaltime no es único. "Casi" único sigue siendo no único.

Explique

La consulta no recursiva comienza con la última fila.
La consulta recursiva agrega la última fila siguiente con un seriesName eso no está en la lista, todavía etc., hasta que tengamos 100 filas.
Las partes esenciales son las JOIN condición (b.retreivaltime, b.seriesName) < (c.retreivaltime, c.seriesName) y el ORDER BY cláusula ORDER BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST. Ambos coinciden con el orden de clasificación del índice, lo que permite que suceda la magia.
Coleccionando seriesName en un array para descartar duplicados. El costo de b.seriesName <> ALL(c.foo_arr) crece progresivamente con el número de filas, pero para solo 100 filas sigue siendo barato.
Solo regresando dbid como se aclara en los comentarios.

Alternativa con índices parciales:

Hemos estado lidiando con problemas similares antes. Aquí hay una solución completa altamente optimizada basada en índices parciales y una función de bucle:

¿Puede el índice espacial ayudar a una consulta de "rango - ordenar por - límite"?

Probablemente la forma más rápida (a excepción de una vista materializada) si se hace correctamente. Pero más complejo.

Vista materializada

Dado que no tiene muchas operaciones de escritura y no son críticas para el rendimiento como se indica en los comentarios (debería estar en la pregunta), ahorrar las primeras n filas precalculadas en una vista materializada y actualícela después de los cambios relevantes en la tabla subyacente. En su lugar, base sus consultas críticas para el rendimiento en la vista materializada.

Podría ser un mv "delgado" de los últimos 1000 dbid más o menos. En la consulta, únase a la tabla original. Por ejemplo, si el contenido se actualiza a veces, pero las n filas superiores pueden permanecer sin cambios.
O un MV "gordo" con filas enteras para regresar. Más rápido, todavía. Necesita ser actualizado con más frecuencia, obviamente.

Detalles en el manual aquí y aquí.

Ok, he leído más los documentos y ahora entiendo el problema al menos un poco mejor.

Básicamente, lo que sucede es que hay varios valores posibles para dbid como resultado de la GROUP BY seriesName agregación. Con SQLite y MySQL, aparentemente el motor de base de datos solo elige uno al azar (lo cual está absolutamente bien en mi aplicación).

Sin embargo, PostgreSQL es mucho más conservador, por lo que en lugar de elegir un valor aleatorio, arroja un error.

Una forma sencilla de hacer que esta consulta funcione es aplicar un función de agregación al valor relevante:

SELECT MAX(dbid) AS mdbid, seriesName, MAX(retreivaltime) AS mrt
    FROM MangaItems 
    WHERE sourceSite='mk' 
    GROUP BY seriesName
    ORDER BY mrt DESC 
    LIMIT 100 
    OFFSET 0;

Esto hace que la salida de la consulta esté completamente calificada y la consulta ahora funciona.

Si conservas alguna perplejidad o forma de modernizar nuestro tutorial eres capaz de realizar una anotación y con placer lo leeremos.

¡Haz clic para puntuar esta entrada!

(Votos: 0 Promedio: 0)

AGRUPAR POR una columna, mientras ordena por otra en PostgreSQL