Pregunta Obtener la fila que tiene el valor Máx. Para una columna


Mesa:

UserId, Value, Date.

Quiero obtener UserId, valor para el máximo (fecha) para cada UserId. Es decir, el valor para cada ID de usuario que tiene la fecha más reciente. ¿Hay alguna manera de hacer esto simplemente en SQL? (Preferiblemente Oracle)

Actualizar: Disculpas por cualquier ambigüedad: necesito obtener TODOS los UserIds. Pero para cada ID de usuario, solo esa fila donde ese usuario tiene la fecha más reciente.


503
2017-09-23 14:34


origen


Respuestas:


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

"Funciones analíticas rock"

Editar: Con respecto al primer comentario ...

"el uso de consultas analíticas y una auto unión infringe el propósito de las consultas analíticas"

No hay auto-unión en este código. En cambio, hay un predicado sobre el resultado de la vista en línea que contiene la función analítica, una cuestión muy diferente y una práctica completamente estándar.

"La ventana predeterminada en Oracle es desde la primera fila en la partición hasta la actual"

La cláusula de ventana solo es aplicable en presencia de la cláusula order by. Con ninguna cláusula order by, ninguna cláusula windowing se aplica por defecto y ninguna se puede especificar explícitamente.

El código funciona


354
2017-09-23 20:01



Veo que mucha gente usa subconsultas o características específicas del proveedor 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: obtenga la fila de t1 donde no existe otra fila con el mismo ID de usuario y una Fecha mayor.

(Puse el identificador "Fecha" en delimitadores porque es una palabra reservada de SQL.)

En caso de que si t1."Date" = t2."Date", aparece el doblaje. Usualmente las tablas tienen auto_inc(seq) clave, p. ej. id. Para evitar doblaje se puede utilizar a continuación:

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;

Comentario de @Farhan:

Aquí hay una explicación más detallada:

Una combinación externa intenta unir t1 con t2. Por defecto, se devuelven todos los resultados de t1, y Si hay una coincidencia en t2, también se devuelve. Si no hay coincidencia en t2 para una fila determinada de t1, la consulta sigue devolviendo la fila de t1 y usa NULL como marcador de posición para todas las columnas de t2. Así es como funcionan las uniones externas en general.

El truco en esta consulta es diseñar la condición de coincidencia de la unión de manera que t2 debe coincidir con el mismo ID de usuario, y una mayor fecha. La idea es si existe una fila en t2 que tiene una fecha mayor, entonces la fila en t1 se compara con hipocresía ser la mejor fecha para ese ID de usuario. Pero si no hay coincidencia, es decir, si no existe una fila en t2 con una fecha mayor que la fila en t1, sabemos que la fila en t1 era la fila con la fecha más grande para el ID de usuario dado.

En esos casos (cuando no hay coincidencia), las columnas de t2 serán NULL, incluso las columnas especificadas en la condición de unión. Entonces es por eso que usamos WHERE t2.UserId IS NULL, porque estamos buscando los casos donde no se encontró una fila con una fecha mayor para el userid dado.


400
2017-09-23 15:18



SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

146
2017-09-23 14:39



No sé los nombres exactos de las columnas, pero sería algo como esto:

    seleccione ID de usuario, valor
      de los usuarios u1
     donde date = (seleccionar max (fecha)
                     de los usuarios u2
                    donde u1.userid = u2.userid)

45
2017-09-23 20:06



Al no estar en el trabajo, no tengo Oracle a mano, pero me parece recordar que Oracle permite que coincidan varias columnas en una cláusula IN, que al menos debería evitar las opciones que usan una subconsulta correlacionada, que rara vez es una buena idea.

Algo como esto, tal vez (no recuerdo si la lista de columnas debe estar entre paréntesis o no):

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

EDITAR: lo probé de verdad:

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
  2  where (usr, dt) in 
  3  ( select usr, max(dt) from mytable group by usr)
  4  /

U DT
- ---------
A 01-JAN-09
B 01-JAN-09

Así que funciona, aunque algunas de las cosas novedosas mencionadas en otros lugares pueden ser más eficaces.


34
2017-09-23 15:22



Sé que solicitó Oracle, pero en SQL 2005 ahora usamos esto:


-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

12
2017-10-19 16:17



¿No sería una cláusula QUALIFY la más simple y la mejor?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

Por contexto, en Teradata aquí, una prueba de tamaño decente se ejecuta en 17s con esta versión QUALIFY y en 23s con la solución 'inline view' / Aldridge # 1.


6
2017-09-23 15:47



No tengo Oracle para probarlo, pero la solución más eficiente es usar consultas analíticas. Debería verse algo como esto:

SELECT DISTINCT
    UserId
  , MaxValue
FROM (
    SELECT UserId
      , FIRST (Value) Over (
          PARTITION BY UserId
          ORDER BY Date DESC
        ) MaxValue
    FROM SomeTable
  )

Sospecho que puedes deshacerte de la consulta externa y diferenciarte en el interior, pero no estoy seguro. Mientras tanto, sé que esto funciona.

Si quieres aprender sobre consultas analíticas, te sugiero que leas http://www.orafaq.com/node/55 y http://www.akadia.com/services/ora_analytic_functions.html. Aquí está el breve resumen.

Debajo de las consultas analíticas de captación ordenar todo el conjunto de datos, luego procesarlo secuencialmente. A medida que lo procesa, crea una partición del conjunto de datos de acuerdo con ciertos criterios, y luego, para cada fila, mira una ventana (por defecto es el primer valor en la partición en la fila actual, que también es la más eficiente) y puede calcular valores usando una número de funciones analíticas (cuya lista es muy similar a las funciones agregadas).

En este caso, aquí está lo que hace la consulta interna. Todo el conjunto de datos está ordenado por UserId y luego por Fecha DESC. Luego lo procesa en una sola pasada. Para cada fila, devuelve UserId y la primera fecha que se ve para ese UserId (dado que las fechas se clasifican como DESC, esa es la fecha máxima). Esto le da su respuesta con filas duplicadas. Luego, el exterior DISTINCT aplasta los duplicados.

Este no es un ejemplo particularmente espectacular de consultas analíticas. Para una ganancia mucho mayor, considere tomar una tabla de recibos financieros y calcular para cada usuario y recibo, un total acumulado de lo que pagaron. Las consultas analíticas lo resuelven de manera eficiente. Otras soluciones son menos eficientes. Por eso son parte del estándar SQL 2003. (Desafortunadamente, Postgres aún no los tiene. Grrr ...)


6
2017-11-01 13:22



Con PostgreSQL 8.4 o posterior, puede usar esto:

select user_id, user_value_1, user_value_2
  from (select user_id, user_value_1, user_value_2, row_number()
          over (partition by user_id order by user_date desc) 
        from users) as r
  where r.row_number=1

4
2017-09-23 14:51