Consulta PostgreSQL con fecha máxima y mínima más identificación asociada por fila

Por fin después de tanto trabajar ya encontramos la respuesta de esta preocupación que muchos lectores de este sitio han tenido. Si quieres aportar algo no dejes de aportar tu conocimiento.

Solución:

Tú pueden calcule esto en un solo paso con funciones de ventana:

CREATE OR REPLACE VIEW daily_trans AS
SELECT DISTINCT
       trans_date
     , first_value(trans_time) OVER w AS first_time
     , first_value(id)         OVER w AS first_id
     , last_value(trans_time)  OVER w AS last_time
     , last_value(id)          OVER w AS last_id
     , calculate_status(min(trans_time) OVER w
                      , max(trans_time) OVER w) AS status 
FROM   trans
WINDOW w AS (PARTITION BY trans_date ORDER BY trans_time, id
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY 1;

yo añadí id como secundaria ORDER columna en la cláusula de la ventana, para hacer que el orden de clasificación sea estable en caso de horas idénticas por día.

Si no está familiarizado con las funciones de las ventanas, asegúrese de leer este capítulo del manual.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING es una cláusula poco utilizada para ajustar el marco de la ventana, porque queremos last_value() para devolver siempre el último valor de toda la partición (la fecha), no el último valor hasta el actual (incluidos los pares) según el orden de clasificación.

Como combinar DISTINCT con funciones de ventana:

• PostgreSQL: cuenta corriente de filas para una consulta ‘por minuto’

Aproximadamente, quieres algo como

select min_trans_time, min_trans.id as min_trans_time_id,
       max_trans_time, max_trans.id as max_trans_time_id
from (
    select   trans_date,
             max(trans_time) as max_trans_time, 
             min(trans_time) as min_trans_time,
    from     trans        
    group by trans_date) min_max_trans,
       trans as min_trans,
       trans as max_trans
where min_trans.trans_time = min_max_trans.min_trans_time
and   max_trans.trans_time = min_max_trans.max_trans_time

Que podría no ser estable si varios transes compartir lo mismo trans_time (es decir, ejecutar la consulta en el mismo conjunto de datos puede arrojar resultados diferentes. Una manera fácil de resolver esto, si es una preocupación, es elegir la identificación máxima o mínima, por ejemplo. Por supuesto, esto podría sesgar los resultados :).

También puede usar funciones de ventana si está usando PostgreSQL 8.4 o posterior; proporcionarán una consulta más clara (clara si está familiarizado con las funciones de ventana :), y le permitirán hacer cosas que son bastante difíciles de hacer con la estrategia anterior (por ejemplo, obtener el segundo valor más alto en lugar del máximo). Sin embargo, en mi corta experiencia, funcionaron peor que el enfoque de autounión que propongo aquí. La respuesta de Erwin contiene una versión de la consulta que usa funciones de ventana. Sería interesante observar qué consulta se ejecuta mejor y/o se puede optimizar mejor (agregando índices, etc.).

Reseñas y calificaciones

Nos encantaría que puedieras dar difusión a este artículo si te fue de ayuda.

---

---

Utiliza Nuestro Buscador