Obtenga las filas que tienen el valor máximo para una columna para cada valor distinto de otra columna
Mesa:
UserId, Value, Date.
Quiero obtener el ID de usuario, el valor para el máximo (fecha) para cada ID de usuario. Es decir, el Valor de cada UserId que tiene la última fecha.
¿Cómo hago esto en SQL? (Preferiblemente Oráculo).
Necesito obtener TODOS los ID de usuario. Pero para cada ID de usuario, solo esa fila donde ese usuario tiene la fecha más reciente.
Veo que muchas personas usan subconsultas o funciones de ventana para hacer esto, pero a menudo hago este tipo de consulta sin subconsultas de la siguiente manera. Utiliza SQL estándar y simple, por lo que debería funcionar en cualquier marca de RDBMS.
SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;
En otras palabras: recuperar la fila desde t1donde no existe otra fila con la misma UserIdFecha y una mayor.
(Puse el identificador "Fecha" en los delimitadores porque es una palabra reservada de SQL).
En caso de que t1."Date" = t2."Date"aparezca la duplicación. Por lo general, las tablas tienen auto_inc(seq)clave, por ejemplo id. Para evitar la duplicación se puede utilizar lo siguiente:
SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date")
OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;
Re comentario de @Farhan:
Aquí hay una explicación más detallada:
Una combinación externa intenta unirse t1con t2. t1De forma predeterminada, se devuelven todos los resultados de y, si hay una coincidencia en t2, también se devuelve. Si no hay ninguna coincidencia t2para una fila determinada de t1, la consulta aún devuelve la fila de t1y la utiliza NULLcomo marcador de posición para todas las t2columnas de . Así es como funcionan las uniones externas en general.
El truco en esta consulta consiste en diseñar la condición coincidente de la unión de modo que t2deba coincidir con la misma userid y una mayor date . La idea es que si existe una fila t2que tiene un valor mayor date, entonces la fila en la t1que se compara no puede ser la mayor datepara eso userid. Pero si no hay coincidencia (es decir, si no existe ninguna fila t2con un valor mayor dateque la fila de t1), sabemos que la fila de t1era la fila con el mayor datepara lo dado userid.
En esos casos (cuando no hay coincidencia), las columnas de t2serán NULL, incluso las columnas especificadas en la condición de unión. Por eso usamos WHERE t2.UserId IS NULL, porque estamos buscando los casos en los que no se encontró ninguna fila con un valor mayor datepara lo dado userid.
Esto recuperará todas las filas para las cuales el valor de la columna my_date es igual al valor máximo de my_date para ese ID de usuario. Esto puede recuperar varias filas para el ID de usuario donde la fecha máxima está en varias filas.
select userid,
my_date,
...
from
(
select userid,
my_date,
...
max(my_date) over (partition by userid) max_my_date
from users
)
where my_date = max_my_date
"Las funciones analíticas son geniales"
Editar: Con respecto al primer comentario...
"El uso de consultas analíticas y una autounión anula el propósito de las consultas analíticas"
No hay autounión en este código. En cambio, hay un predicado colocado sobre el resultado de la vista en línea que contiene la función analítica: un asunto muy diferente y una práctica completamente estándar.
"La ventana predeterminada en Oracle va desde la primera fila de la partición hasta la actual"
La cláusula de ventana sólo es aplicable en presencia de la cláusula orden por. Sin cláusula de orden por, no se aplica ninguna cláusula de ventana de forma predeterminada y no se puede especificar ninguna explícitamente.
El código funciona.
SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
FROM table
GROUP BY userid
