361 votos

Capturar la fila que tiene el valor máximo de una columna

Tabla: Id De Usuario, El Valor, La Fecha.

Quiero conseguir el id de usuario, el Valor para el max(Fecha) para cada id de usuario. Es decir, el Valor para cada id de usuario que tenga la fecha más reciente. Hay una manera para hacer esto, simplemente en SQL? (Preferiblemente Oracle)

Actualización: pedimos Disculpas por cualquier ambigüedad: necesito conseguir TODAS las identificaciones del usuario. Pero para cada id de usuario, solo que la fila donde el usuario tiene la última fecha.

313voto

Bill Karwin Puntos 204877

Veo a muchas personas el uso de subconsultas o más características específicas del proveedor para ello, pero a menudo me hacen este tipo de consulta sin subconsultas de la siguiente manera. Utiliza papel, estándar SQL 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: capturar la fila de la t1 donde ninguna otra fila con el mismo id de usuario y una mayor Fecha.

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

En caso de que si t1."Fecha" = t2."La fecha", duplicando aparece. Generalmente las tablas ha auto_inc(seq), por ejemplo, Id. Para evitar la duplicación puede ser utilizado de la siguiente manera:

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:

He aquí una explicación más detallada:

Una combinación externa de los intentos para unirse t1 con la t2. De forma predeterminada, todos los resultados de la t1 se devuelven, y si hay un partido en t2, también es devuelto. Si no hay ninguna coincidencia en t2 para una fila determinada de la t1, a continuación, la consulta devolverá la fila de la t1, y utiliza NULL como un marcador de posición para todos los de la t2 columnas. Eso es sólo cómo combinaciones externas trabajo en general.

El truco en esta consulta es el diseño de la combinación de la coincidencia de condición tal 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 mayor fecha, luego la fila en la t1 es comparado contra puede no ser la mejor fecha para que userid. Pero si no hay ninguna coincidencia, es decir. si no hay ninguna fila existe en la t2 con una mayor fecha de la fila en t1, sabemos que la fila en la t1 fue de la fila con el mayor fecha para el id de usuario.

En esos casos (cuando no hay ninguna coincidencia), las columnas de t2, será NULA, incluso las columnas especificadas en la condición de combinación. Es por eso que uso WHERE t2.UserId IS NULL, debido a que estamos buscando para los casos en los que no hay ninguna fila se encontró con un mayor fecha para el id de usuario.

245voto

David Aldridge Puntos 27624

Esto va a recuperar todas las filas para que el my_date valor de la columna es igual al valor máximo de my_date para que userid. Esto puede recuperar varias filas para el id donde la fecha máxima es 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

"La analítica de funciones de rock"

Edit: Con respecto al primer comentario ...

"el uso de la analítica de las consultas y de la auto-join derrota el propósito de la analítica de consultas"

No hay auto-unirse en este código. Por el contrario, existe un predicado que se colocan en el resultado de la vista en línea que contiene la analítica de la función -- un asunto muy diferente, y totalmente práctica estándar.

"El defecto de la ventana en Oracle desde la primera fila en la partición a la actual"

Las ventanas de la cláusula sólo es aplicable en la presencia de la cláusula order by. Sin cláusula order by, sin ventanas cláusula se aplica por defecto y ninguno de ellos puede ser especificado de forma explícita.

El código funciona.

116voto

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

38voto

Steve K Puntos 10475

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

 seleccione id de usuario, el valor
 de los usuarios u1
 date = (select max(fecha)
 de los usuarios de u2
 donde u1.userid = u2.userid)

28voto

Mike Woodhouse Puntos 27748

No está en el trabajo, no tengo Oracle a mano, pero me parece recordar que Oracle permite que varias columnas de a coincide en una cláusula, que al menos debe evitar las opciones que utilizar una subconsulta correlacionada, que rara vez es una buena idea.

Algo como esto, tal vez (no recuerdo si la lista de columnas debe ser parenthesised o no):

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

EDIT: Sólo lo intentó real:

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í funciona, aunque algunos de los nuevos-fangly cosas mencionadas en otros lugares puede ser más competitivos.

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X