PostgreSQL: obtenga la fila que tiene el valor máximo para una columna

Ya no tienes que indagar más por todo internet porque llegaste al lugar correcto, tenemos la respuesta que deseas sin complicarte.

Solución:

En una tabla con 158k filas pseudoaleatorias (usr_id distribuidas uniformemente entre 0 y 10k, trans_id distribuida uniformemente entre 0 y 30),

Por costo de consulta, a continuación, me refiero a la estimación de costos del optimizador basado en costos de Postgres (con el valor predeterminado de Postgres xxx_cost valores), que es una estimación de función ponderada de los recursos de E/S y CPU requeridos; puede obtener esto iniciando PgAdminIII y ejecutando “Query/Explain (F7)” en la consulta con “Query/Explain options” configurado en “Analyze”

• La consulta de Quassnoy tiene un costo estimado de 745k (!) y se completa en 1,3 segundos (dado un índice compuesto en (usr_id, trans_id, time_stamp))
• La consulta de Bill tiene un costo estimado de 93k y se completa en 2,9 segundos (dado un índice compuesto en (usr_id, trans_id))
• Consulta #1 a continuación tiene un costo estimado de 16k y se completa en 800ms (dado un índice compuesto en (usr_id, trans_id, time_stamp))
• Consulta #2 a continuación tiene un costo estimado de 14k y se completa en 800ms (dado un índice de función compuesto en (usr_id, EXTRACT(EPOCH FROM time_stamp), trans_id))
  • esto es específico de Postgres
• Consulta #3 a continuación (Postgres 8.4+) tiene un costo estimado y un tiempo de finalización comparable a (o mejor que) la consulta n.º 2 (dado un índice compuesto en (usr_id, time_stamp, trans_id)); tiene la ventaja de escanear el lives table solo una vez y, si aumenta temporalmente (si es necesario) work_mem para acomodar la ordenación en la memoria, será, con mucho, la más rápida de todas las consultas.

Todos los tiempos anteriores incluyen la recuperación del conjunto de resultados completo de 10k filas.

Su objetivo es una estimación de costo mínimo y tiempo mínimo de ejecución de consultas, con énfasis en el costo estimado. La ejecución de consultas puede depender significativamente de las condiciones de tiempo de ejecución (por ejemplo, si las filas relevantes ya están completamente almacenadas en caché en la memoria o no), mientras que la estimación del costo no lo es. Por otro lado, tenga en cuenta que la estimación de costos es exactamente eso, una estimación.

El mejor tiempo de ejecución de consultas se obtiene cuando se ejecuta en una base de datos dedicada sin carga (por ejemplo, jugando con pgAdminIII en una PC de desarrollo). El tiempo de consulta variará en producción según la carga real de la máquina/distribución de acceso a datos. Cuando una consulta aparece un poco más rápido (<20 %) que la otra pero tiene un mucho mayor costo, generalmente será más inteligente elegir el que tenga mayor tiempo de ejecución pero menor costo.

Cuando espera que no haya competencia por la memoria en su máquina de producción en el momento en que se ejecuta la consulta (por ejemplo, el caché RDBMS y el caché del sistema de archivos no se verán afectados por consultas simultáneas y/o actividad del sistema de archivos), entonces el tiempo de consulta que obtuvo en modo independiente (por ejemplo, pgAdminIII en una PC de desarrollo) será representativo. Si hay contención en el sistema de producción, el tiempo de consulta se degradará proporcionalmente a la relación de costo estimado, ya que la consulta con el costo más bajo no depende tanto de la memoria caché. mientras que la consulta con mayor costo volverá a visitar los mismos datos una y otra vez (activando E/S adicionales en ausencia de un caché estable), por ejemplo:

              cost | time (dedicated machine) |     time (under load) |
-------------------+--------------------------+-----------------------+
some query A:   5k | (all data cached)  900ms | (less i/o)     1000ms |
some query B:  50k | (all data cached)  900ms | (lots of i/o) 10000ms |

No olvides correr ANALYZE lives una vez después de crear los índices necesarios.

---

Consulta #1

-- incrementally narrow down the result set via inner joins
--  the CBO may elect to perform one full index scan combined
--  with cascading index lookups, or as hash aggregates terminated
--  by one nested index lookup into lives - on my machine
--  the latter query plan was selected given my memory settings and
--  histogram
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
    SELECT
      usr_id,
      MAX(time_stamp) AS time_stamp_max
     FROM
      lives
     GROUP BY
      usr_id
  ) AS l2
 ON
  l1.usr_id     = l2.usr_id AND
  l1.time_stamp = l2.time_stamp_max
 INNER JOIN (
    SELECT
      usr_id,
      time_stamp,
      MAX(trans_id) AS trans_max
     FROM
      lives
     GROUP BY
      usr_id, time_stamp
  ) AS l3
 ON
  l1.usr_id     = l3.usr_id AND
  l1.time_stamp = l3.time_stamp AND
  l1.trans_id   = l3.trans_max

Consulta #2

-- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass
-- this results in a single table scan and one nested index lookup into lives,
--  by far the least I/O intensive operation even in case of great scarcity
--  of memory (least reliant on cache for the best performance)
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
   SELECT
     usr_id,
     MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id])
       AS compound_time_stamp
    FROM
     lives
    GROUP BY
     usr_id
  ) AS l2
ON
  l1.usr_id = l2.usr_id AND
  EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND
  l1.trans_id = l2.compound_time_stamp[2]

2013/01/29 actualización

Finalmente, a partir de la versión 8.4, Postgres es compatible con la función de ventana, lo que significa que puede escribir algo tan simple y eficiente como:

Consulta #3

-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
  last_value(time_stamp) OVER wnd,
  last_value(lives_remaining) OVER wnd,
  usr_id,
  last_value(trans_id) OVER wnd
 FROM lives
 WINDOW wnd AS (
   PARTITION BY usr_id ORDER BY time_stamp, trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 );

Yo propondría una versión limpia basada en DISTINCT ON (ver documentos):

SELECT DISTINCT ON (usr_id)
    time_stamp,
    lives_remaining,
    usr_id,
    trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;

Aquí hay otro método, que no usa subconsultas correlacionadas o GROUP BY. No soy un experto en el ajuste del rendimiento de PostgreSQL, por lo que le sugiero que pruebe tanto esta como las soluciones proporcionadas por otras personas para ver cuál funciona mejor para usted.

SELECT l1.*
FROM lives l1 LEFT OUTER JOIN lives l2
  ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp 
   OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id)))
WHERE l2.usr_id IS NULL
ORDER BY l1.usr_id;

asumo que trans_id es único al menos sobre cualquier valor dado de time_stamp.

---

---

Utiliza Nuestro Buscador