restricciones - PostgreSQL-busca la fila que tiene el valor Máx. Para una columna

restriccion check sql server

Question

Estoy tratando con una tabla Postgres (llamada "lives") que contiene registros con columnas para time_stamp, usr_id, transaction_id y lives_remaining. Necesito una consulta que me proporcione el total de vidas restante más reciente para cada usr_id

1. Hay múltiples usuarios (diferentes usr_id''s)
   
2. time_stamp no es un identificador único: a veces los eventos del usuario (uno por fila en la tabla) ocurrirán con el mismo time_stamp.
   
3. trans_id es único solo para intervalos de tiempo muy pequeños: con el tiempo se repite
   
4. remaining_lives (para un usuario dado) puede aumentar y disminuir con el tiempo

ejemplo:

time_stamp|lives_remaining|usr_id|trans_id ----------------------------------------- 07:00 | 1 | 1 | 1 09:00 | 4 | 2 | 2 10:00 | 2 | 3 | 3 10:00 | 1 | 2 | 4 11:00 | 4 | 1 | 5 11:00 | 3 | 1 | 6 13:00 | 3 | 3 | 1

Como necesitaré acceder a otras columnas de la fila con los datos más recientes para cada usr_id dado, necesito una consulta que arroje un resultado como este:

time_stamp|lives_remaining|usr_id|trans_id ----------------------------------------- 11:00 | 3 | 1 | 6 10:00 | 1 | 2 | 4 13:00 | 3 | 3 | 1

Como se mencionó, cada usr_id puede ganar o perder vidas, y a veces estos eventos con marcas de tiempo ocurren tan juntos que tienen la misma marca de tiempo. Por lo tanto, esta consulta no funcionará:

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
      (SELECT usr_id, max(time_stamp) AS max_timestamp 
       FROM lives GROUP BY usr_id ORDER BY usr_id) a 
JOIN lives b ON a.max_timestamp = b.time_stamp

En cambio, necesito usar time_stamp (first) y trans_id (second) para identificar la fila correcta. También necesito pasar esa información de la subconsulta a la consulta principal que proporcionará los datos para las otras columnas de las filas apropiadas. Esta es la consulta pirateada que he puesto a trabajar:

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
      (SELECT usr_id, max(time_stamp || ''*'' || trans_id) 
       AS max_timestamp_transid
       FROM lives GROUP BY usr_id ORDER BY usr_id) a 
JOIN lives b ON a.max_timestamp_transid = b.time_stamp || ''*'' || b.trans_id 
ORDER BY b.usr_id

De acuerdo, entonces esto funciona, pero no me gusta. Requiere una consulta dentro de una consulta, una unión automática, y me parece que podría ser mucho más simple al tomar la fila que MAX encontró con la marca de tiempo y trans_id más grandes. La tabla "vidas" tiene decenas de millones de filas para analizar, por lo que me gustaría que esta consulta sea lo más rápida y eficiente posible. Soy nuevo en RDBM y Postgres en particular, así que sé que necesito hacer un uso efectivo de los índices adecuados. Estoy un poco perdido sobre cómo optimizar.

Encontré una discusión similar here . ¿Puedo realizar algún tipo de Postgres equivalente a una función analítica de Oracle?

Cualquier consejo sobre el acceso a la información de la columna relacionada utilizada por una función agregada (como MAX), crear índices y crear mejores consultas sería muy apreciado.

PD Puedes usar lo siguiente para crear mi caso de ejemplo:

create TABLE lives (time_stamp timestamp, lives_remaining integer, 
                    usr_id integer, trans_id integer);
insert into lives values (''2000-01-01 07:00'', 1, 1, 1);
insert into lives values (''2000-01-01 09:00'', 4, 2, 2);
insert into lives values (''2000-01-01 10:00'', 2, 3, 3);
insert into lives values (''2000-01-01 10:00'', 1, 2, 4);
insert into lives values (''2000-01-01 11:00'', 4, 1, 5);
insert into lives values (''2000-01-01 11:00'', 3, 1, 6);
insert into lives values (''2000-01-01 13:00'', 3, 3, 1);

Answer 1

Actaully hay una solución hacky para este problema. Digamos que desea seleccionar el árbol más grande de cada bosque en una región.

SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1]
FROM tree JOIN forest ON (tree.forest = forest.id)
GROUP BY forest.id

Cuando agrupe árboles por bosques, habrá una lista de árboles sin clasificar y deberá encontrar la más grande. Lo primero que debe hacer es ordenar las filas por sus tamaños y seleccionar la primera de su lista. Puede parecer ineficiente, pero si tiene millones de filas, será bastante más rápido que las soluciones que incluyen las condiciones de JOIN y WHERE .

Por cierto, tenga en cuenta que ORDER_BY para array_agg se introduce en Postgresql 9.0

Answer 2

Aquí hay otro método, que no usa subconsultas correlacionadas ni GROUP BY. No soy experto en la optimización del rendimiento de PostgreSQL, por lo que te sugiero que pruebes esto y las soluciones que ofrecen otras personas para ver cuál funciona mejor para ti.

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;

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

Answer 3

Creo que aquí tienes un problema importante: no hay un "contador" que aumente de forma monótona para garantizar que una fila determinada haya sucedido más adelante en el tiempo que otra. Toma este ejemplo:

timestamp   lives_remaining   user_id   trans_id
10:00       4                 3         5
10:00       5                 3         6
10:00       3                 3         1
10:00       2                 3         2

No puede determinar a partir de estos datos cuál es la entrada más reciente. ¿Es el segundo o el último? No existe una función sort o max () que pueda aplicar a cualquiera de estos datos para darle la respuesta correcta.

Aumentar la resolución de la marca de tiempo sería de gran ayuda. Como el motor de la base de datos serializa las solicitudes, con una resolución suficiente puede garantizar que no habrá dos marcas de tiempo iguales.

Alternativamente, use un trans_id que no se volcará durante mucho, mucho tiempo. Tener un trans_id que se transfiere significa que no se puede decir (para la misma marca de tiempo) si trans_id 6 es más reciente que trans_id 1 a menos que se haga una matemática complicada.

Answer 4

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

Por costo de consulta, a continuación, me refiero al cálculo del costo del optimizador basado en costos de Postgres (con los valores xxx_cost predeterminados de xxx_cost ), que es una estimación de la función ponderada de los recursos necesarios de E / S y CPU; puede obtener esto activando PgAdminIII y ejecutando "Query / Explain (F7)" en la consulta con "Opciones de consulta / explicación" en "Analizar"

• La consulta de Quassnoy tiene un costo estimado de 745k (!) Y 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 completa en 2.9 segundos (dado un índice compuesto en ( usr_id , trans_id ))
• La consulta n. ° 1 a continuación tiene un costo estimado de 16k y completa en 800ms (dado un índice compuesto en ( usr_id , trans_id , time_stamp ))
• La consulta n. ° 2 a continuación tiene un costo estimado de 14k y completa en 800ms (dado un índice de función compuesta en ( usr_id , EXTRACT(EPOCH FROM time_stamp) , trans_id ))
  • esto es específico de Postgres
• La consulta n. ° 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 la tabla de lives solo una vez y, si aumenta temporalmente (si es necesario) work_mem para acomodar el género 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 un tiempo mínimo de ejecución de consulta, 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 la memoria caché o no), mientras que la estimación del costo no lo es. Por otro lado, tenga en cuenta que el cálculo del costo 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, jugar con pgAdminIII en una PC de desarrollo). El tiempo de consulta variará en la producción según la carga de la máquina real / acceso a datos. Cuando una consulta aparece un poco más rápido (<20%) que la otra pero tiene un costo mucho más alto, generalmente será más inteligente elegir la que tenga un tiempo de ejecución más alto pero un costo menor.

Cuando espere que no exista competencia por la memoria en su máquina de producción en el momento en que se ejecuta la consulta (p. Ej., El caché RDBMS y el caché del sistema de archivos no se verán afectados por consultas concurrentes y / o actividad del sistema de archivos), 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 estimada, ya que la consulta con menor costo no depende tanto de la caché, mientras que la consulta con mayor costo revisará los mismos datos una y otra vez (activación E / S adicional 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 se olvide de ejecutar ANALYZE lives una vez después de crear los índices necesarios.

Consulta n. ° 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 n. ° 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 admite Window Function, lo que significa que puede escribir algo tan simple y eficiente como:

Consulta n. ° 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
 );

Answer 5

Hay una nueva opción en Postgressql 9.5 llamada DISTINCT ON

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

Elimina las filas duplicadas y deja solo la primera fila como definió mi cláusula ORDER BY.

ver la documentation oficial

Answer 6

Me gusta el estilo de la respuesta de Mike Woodhouse en la otra página que mencionaste. Es especialmente conciso cuando lo que se maximiza es solo una columna, en cuyo caso la subconsulta solo puede usar MAX(some_col) y GROUP BY las otras columnas, pero en su caso tiene una cantidad de 2 partes para maximizar, usted aún puede hacerlo usando ORDER BY más LIMIT 1 lugar (como lo hizo Quassnoi):

SELECT * 
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
    SELECT usr_id, time_stamp, trans_id
    FROM lives sq
    WHERE sq.usr_id = outer.usr_id
    ORDER BY trans_id, time_stamp
    LIMIT 1
)

Me parece útil usar la sintaxis row-constructor WHERE (a, b, c) IN (subquery) porque reduce la cantidad de verbosidad necesaria.

Answer 7

Propondría una versión limpia basada en DISTINCT ON (ver docs ):

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;

Answer 8

SELECT  l.*
FROM    (
        SELECT DISTINCT usr_id
        FROM   lives
        ) lo, lives l
WHERE   l.ctid = (
        SELECT ctid
        FROM   lives li
        WHERE  li.usr_id = lo.usr_id
        ORDER BY
          time_stamp DESC, trans_id DESC
        LIMIT 1
        )

Crear un índice en (usr_id, time_stamp, trans_id) mejorará en gran medida esta consulta.

Siempre debes tener siempre algún tipo de PRIMARY KEY en tus tablas.