sql - sumar - Obtener la fila que tiene el valor máximo para una columna

sql transponer columnas a filas

Question

Mesa:

UserId, Value, Date.

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

Actualización: disculpas por cualquier ambigüedad: necesito obtener TODOS los UserIds. Pero para cada UserId, solo esa fila donde ese usuario tiene la fecha más reciente.

Answer 1

¿Creo que esto debería funcionar?

Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As ''Value''
From
Table T1
Group By
T1.UserId
Order By
T1.UserId

Answer 2

¿Una cláusula de CALIFICACIÓN no sería tan simple como mejor?

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

Para el contexto, en Teradata aquí, una prueba de tamaño decente se ejecuta en 17s con esta versión de CALIFICACIÓN y en 23s con la solución # 1 / Aldridge de ''vista en línea''.

Answer 3

(T-SQL) Primero obtenga todos los usuarios y su fecha máxima. Únase a la tabla para encontrar los valores correspondientes para los usuarios en las fechas máximas.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, ''20010101'')
insert into users values (1, 2, ''20020101'')
insert into users values (2, 1, ''20010101'')
insert into users values (2, 3, ''20030101'')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

resultados:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

Answer 4

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

Algo como esto, tal vez (no puedo recordar 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)

EDIT: acaba de intentarlo 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 nuevas y famosas que se mencionan en otros lugares pueden ser más eficaces.

Answer 5

Con PostgreSQL 8.4 o posterior, puedes 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

Answer 6

Creo que deberías hacer esta variante a la consulta anterior:

SELECT UserId, Value FROM Users U1 WHERE 
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)

Answer 7

En Oracle 12c+ , puede usar las consultas Top n junto con el rank función analítica para lograr esto de manera muy concisa sin subconsultas:

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

Lo anterior devuelve todas las filas con max my_date por usuario.

Si solo desea una fila con la fecha máxima, reemplace el rank con el número de row_number :

select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties; 

Answer 8

Esto debería ser tan simple como:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)

Answer 9

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 del rock"

Edit: Con respecto al primer comentario ...

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

No hay auto-unió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 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 orden por. Sin una cláusula orden por, no se aplica una cláusula de ventana por defecto y ninguna se puede especificar explícitamente.

El código funciona.

Answer 10

Esto también se ocupará de los duplicados (devuelva una fila para cada ID de usuario):

SELECT *
FROM (
  SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
  FROM users u
) u2
WHERE u2.rowid = u2.last_rowid

Answer 11

La respuesta aquí es solo Oracle. Aquí hay una respuesta un poco más sofisticada en todo SQL:

¿Quién tiene el mejor resultado general de tarea (suma máxima de puntos de tarea)?

SELECT FIRST, LAST, SUM(POINTS) AS TOTAL
FROM STUDENTS S, RESULTS R
WHERE S.SID = R.SID AND R.CAT = ''H''
GROUP BY S.SID, FIRST, LAST
HAVING SUM(POINTS) >= ALL (SELECT SUM (POINTS)
FROM RESULTS
WHERE CAT = ''H''
GROUP BY SID)

Y un ejemplo más difícil, que necesita una explicación, para el que no tengo tiempo de cajero automático:

Registre el libro (ISBN y título) que es más popular en 2008, es decir, que se toma prestado con mayor frecuencia en 2008.

SELECT X.ISBN, X.title, X.loans
FROM (SELECT Book.ISBN, Book.title, count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title) X
HAVING loans >= ALL (SELECT count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title);

Espero que esto ayude (a cualquiera) .. :)

Saludos, Guus

Answer 12

Llego tarde a la fiesta, pero el siguiente truco superará tanto a las subconsultas correlacionadas como a cualquier función de análisis, pero tiene una restricción: los valores deben convertirse en cadenas. Así funciona para fechas, números y otras cadenas. El código no parece bueno pero el perfil de ejecución es excelente.

select
    userid,
    to_number(substr(max(to_char(date,''yyyymmdd'') || to_char(value)), 9)) as value,
    max(date) as date
from 
    users
group by
    userid

La razón por la que este código funciona tan bien es que solo necesita escanear la tabla una vez. No requiere ningún índice y, lo que es más importante, no necesita ordenar la tabla, como hacen la mayoría de las funciones de análisis. Sin embargo, los índices ayudarán si necesita filtrar el resultado para un único ID de usuario.

Answer 13

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

select userid, value from users u1 where date = (select max(date) from users u2 where u1.userid = u2.userid)

Answer 14

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 puede deshacerse de la consulta externa y diferenciarla de la interna, pero no estoy seguro. Mientras tanto, sé que éste funciona.

Si desea obtener información sobre consultas analíticas, sugiero leer http://www.orafaq.com/node/55 y http://www.akadia.com/services/ora_analytic_functions.html . Aquí está el breve resumen.

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

En este caso aquí es 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 el Id. De usuario y la primera Fecha de visualización de ese Id. De usuario (ya que las fechas están ordenadas por DESC, esa es la fecha máxima). Esto le da su respuesta con filas duplicadas. Entonces los aplastamientos DISTINCT externos se duplican.

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. Es por eso que son parte del estándar SQL 2003. (Desafortunadamente Postgres no los tiene todavía. Grrr ...)

Answer 15

Pienso algo como esto. (Perdóneme por cualquier error de sintaxis; ¡estoy acostumbrado a usar HQL en este momento!)

EDITAR: También leer mal la pregunta! Se corrigió la consulta ...

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

Answer 16

Primero intento, malinterpreté la pregunta, siguiendo la respuesta principal, aquí hay un ejemplo completo con resultados correctos:

CREATE TABLE table_name (id int, the_value varchar(2), the_date datetime);

INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,''a'',''1/1/2000'');
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,''b'',''2/2/2002'');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,''c'',''1/1/2000'');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,''d'',''3/3/2003'');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,''e'',''3/3/2003'');

-

  select id, the_value
      from table_name u1
      where the_date = (select max(the_date)
                     from table_name u2
                     where u1.id = u2.id)

-

id          the_value
----------- ---------
2           d
2           e
1           b

(3 row(s) affected)

Answer 17

Sé que pediste 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

Answer 18

Si (UserID, Date) es único, es decir, si no aparece la fecha dos veces para el mismo usuario, entonces:

select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
                          from TheTable
                          group by UserID) UserMaxDate
     on TheTable.UserID = UserMaxDate.UserID
        TheTable.[Date] = UserMaxDate.MaxDate;

Answer 19

Si estás usando Postgres, puedes usar array_agg como

SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid

No estoy familiarizado con Oracle. Esto es lo que se me ocurrió

SELECT 
  userid,
  MAX(adate),
  SUBSTR(
    (LISTAGG(value, '','') WITHIN GROUP (ORDER BY adate DESC)),
    0,
    INSTR((LISTAGG(value, '','') WITHIN GROUP (ORDER BY adate DESC)), '','')-1
  ) as value 
FROM YOURTABLE
GROUP BY userid 

Ambas consultas devuelven los mismos resultados que la respuesta aceptada. Ver SQLFiddles:

1. Respuesta aceptada
2. Mi solución con Postgres.
3. Mi solución con Oracle

Answer 20

Solo probé esto y parece funcionar en una tabla de registro

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc

Answer 21

Solo tuve que escribir un ejemplo "en vivo" en el trabajo :)

Éste admite varios valores para UserId en la misma fecha.

Columnas: ID de usuario, valor, fecha

SELECT
   DISTINCT UserId,
   MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
   MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
   SELECT UserId, Date, SUM(Value) As Values
   FROM <<table_name>>
   GROUP BY UserId, Date
)

Puede usar FIRST_VALUE en lugar de MAX y buscarlo en el plan de explicación. No tuve tiempo de jugar con eso.

Por supuesto, si busca en tablas grandes, probablemente sea mejor si usa sugerencias COMPLETAS en su consulta.

Answer 22

Solución para MySQL que no tiene conceptos de partición KEEP, DENSE_RANK.

select userid,
       my_date,
       ...
from
(
select @sno:= case when @pid<>userid then 0
                    else @sno+1
    end as serialnumber, 
    @pid:=userid,
       my_Date,
       ...
from   users order by userid, my_date
) a
where a.serialnumber=0

Referencia: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html

Answer 23

Suponiendo que la fecha es única para un UserID dado, aquí hay algunos TSQL:

SELECT 
    UserTest.UserID, UserTest.Value
FROM UserTest
INNER JOIN
(
    SELECT UserID, MAX(Date) MaxDate
    FROM UserTest
    GROUP BY UserID
) Dates
ON UserTest.UserID = Dates.UserID
AND UserTest.Date = Dates.MaxDate 

Answer 24

Use ROW_NUMBER() para asignar una clasificación única en la Date descendente para cada UserId , luego filtre a la primera fila para cada UserId (es decir, ROW_NUMBER = 1).

SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
      FROM users) u
WHERE rn = 1;

Answer 25

Veo que muchas personas usan subconsultas o, si no, 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 simple y estándar, 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: busque la fila de t1 donde no existe ninguna otra fila con el mismo UserId y una Fecha mayor.

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

En caso de que t1."Date" = t2."Date" , aparece la duplicación. Por lo general, las tablas tienen la auto_inc(seq) , por ejemplo, id . Para evitar la duplicación 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;

Re comentario de @Farhan:

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

Una combinación externa intenta unir t1 con t2 . De forma predeterminada, 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 dada de t1 , la consulta aún retorna la fila de t1 , y usa NULL como un 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 modo que t2 deba coincidir con el mismo userid y una date mayor . La idea es que si existe una fila en t2 que tenga una date mayor, entonces la fila en t1 que se compara no puede ser la mejor date para ese userid . Pero si no hay coincidencia, es decir, si no existe una fila en t2 con una date mayor que la fila en t1 , sabemos que la fila en t1 fue la fila con la date más grande para el userid 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. Por eso utilizamos WHERE t2.UserId IS NULL , porque buscamos los casos en los que no se encontró una fila con una date mayor para el userid dado.

Answer 26

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

Answer 27

Select  
   UserID,  
   Value,  
   Date  
From  
   Table,  
   (  
      Select  
          UserID,  
          Max(Date) as MDate  
      From  
          Table  
      Group by  
          UserID  
    ) as subQuery  
Where  
   Table.UserID = subQuery.UserID and  
   Table.Date = subQuery.mDate  

Answer 28

select   UserId,max(Date) over (partition by UserId) value from users;

Answer 29

select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))

Answer 30

select userid, value, date
  from thetable t1 ,
       ( select t2.userid, max(t2.date) date2 
           from thetable t2 
          group by t2.userid ) t3
 where t3.userid t1.userid and
       t3.date2 = t1.date

En mi humilde opinión esto funciona. HTH