Извлечь строку, которая имеет значение Max для столбца

469

Таблица:

UserId, Value, Date.

Я хочу получить UserId, Value для max (Date) для каждого UserId. То есть значение для каждого пользователя, имеющего последнюю дату. Есть ли способ сделать это просто в SQL? (Желательно Oracle)

Обновление: Извинения за любую двусмысленность: мне нужно получить ВСЕ пользовательские идентификаторы. Но для каждого UserId используется только эта строка, в которой этот пользователь имеет самую последнюю дату.

Показать ещё 5 комментариев
Теги:
greatest-n-per-group

33 ответа

313
Лучший ответ

Это приведет к извлечению всех строк, для которых значение столбца my_date равно максимальному значению my_date для этого идентификатора пользователя. Это может извлечь несколько строк для идентификатора пользователя, где максимальная дата указана в нескольких строках.

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

"Аналитические функции рок"

Изменить: Что касается первого комментария...

", используя аналитические запросы и самосоединение, поражает цель аналитических запросов

В этом коде нет самостоятельного объединения. Вместо этого существует предикат, помещенный на результат встроенного представления, который содержит аналитическую функцию - совсем другое дело и полностью стандартную практику.

"Окно по умолчанию в Oracle относится к первой строке раздела к текущему"

Предложение windowing применимо только при наличии предложения order by. Без предложения order by по умолчанию не применяется предложение windowing, и никто не может быть явно указан.

Код работает.

  • 0
    "Я был укушен окном по умолчанию в присутствии ORDER BY ..." Я тоже :)
  • 0
    Работает отлично! Особенно, когда нужно пробежать 100 миллионов строк! Благодарю. :)
Показать ещё 9 комментариев
381

Я вижу, что многие люди используют подзапросы или другие специфические для вендора функции, но я часто делаю такой запрос без подзапросов следующим образом. Он использует простой стандартный SQL, поэтому он должен работать в любом бренде СУБД.

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;

Другими словами: выберите строку из t1, где не существует другой строки с тем же UserId и большей датой.

(я помещаю идентификатор "Дата" в разделители, потому что это зарезервированное слово SQL.)

В случае, если t1."Date" = t2."Date", появляется удвоение. Обычно таблицы имеют auto_inc(seq) ключ, например. id. Во избежание удвоения можно использовать следующее:

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 comment от @Farhan:

Вот более подробное объяснение:

Внешнее соединение пытается соединить t1 с t2. По умолчанию возвращаются все результаты t1, и если есть совпадение в t2, оно также возвращается. Если в t2 для данной строки t1 нет совпадения, запрос по-прежнему возвращает строку t1 и использует NULL в качестве заполнителя для всех столбцов t2. Это то, как работают внешние соединения.

Трюк в этом запросе заключается в разработке условия согласования соединения, так что t2 должен соответствовать одному и тому же идентификатору пользователя и более высокой дате. Идея заключается в том, что строка существует в t2 с более высокой датой, тогда строка в t1, которую она сравнивает, не может быть наибольшей датой для этого пользователя. Но если совпадения нет - т.е. Если в t2 не существует строки с большей датой, чем строка в t1 - мы знаем, что строка в t1 была строкой с наибольшей датой для данного идентификатора пользователя.

В тех случаях (когда нет совпадения) столбцы t2 будут NULL - даже столбцы, указанные в условии соединения. Поэтому мы используем WHERE t2.UserId IS NULL, потому что мы ищем случаи, когда ни одна строка не была найдена с более высокой датой для данного пользователя.

  • 15
    Как разработчик, который не очень часто мыслит на SQL, я нахожу это очень умным!
  • 6
    Ух ты, Билл. Это самое креативное решение этой проблемы, которое я видел. Это довольно производительно на моем довольно большом наборе данных. Это наверняка превосходит многие другие решения, которые я видел, или мои собственные попытки решить эту проблему.
Показать ещё 32 комментария
142
SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid
  • 12
    Самый эффективный запрос во всей этой цепочке. Должен быть поддержан гораздо больше, чем только я.
  • 2
    В моих тестах с использованием таблицы с большим количеством строк это решение занимало примерно вдвое больше времени, чем в принятом ответе.
Показать ещё 4 комментария
46

Я не знаю ваших точных имен столбцов, но это было бы примерно так:

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.userid)
  • 3
    Наверное, не очень эффективно, Стив.
  • 6
    Вы, вероятно, недооцениваете оптимизатор запросов Oracle.
Показать ещё 11 комментариев
33

Не работая, у меня нет Oracle, но я, похоже, помню, что Oracle позволяет сопоставлять несколько столбцов в предложении IN, что должно, по крайней мере, избегать опций, которые используют коррелированный подзапрос, который редко хорошая идея.

Что-то вроде этого, возможно (не помню, должен ли список столбцов быть заключен в скобки или нет):

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

EDIT: просто попробовал это для реального:

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

Таким образом, это работает, хотя некоторые из новых причудливых вещей, упомянутых в других местах, могут быть более эффективными.

  • 4
    Это хорошо работает и на PostgreSQL. И мне нравится его простота и универсальность - подзапрос говорит: «Вот мои критерии», внешний запрос - «И вот детали, которые я хочу увидеть». +1.
  • 0
    Brilliant! В моих очень ограниченных тестах производительность была в порядке.
11

Я знаю, что вы попросили Oracle, но в SQL 2005 мы теперь используем это:


-- 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
  • 1
    Ах, хорошо, это интересная идея. Создайте CTE и Order по RowNumber ...
6

Разве предложение QUALIFY не было бы простым и лучшим?

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

В контексте, на Teradata, здесь приличный размер теста выполняется в 17 с этой версией QUALIFY, а в 23-х с "встроенным представлением" /решением Aldridge №1.

  • 1
    Это лучший ответ на мой взгляд. Однако будьте осторожны с функцией rank() в ситуациях, когда есть связи. Вы можете получить более одного rank=1 . Лучше использовать row_number() если вы действительно хотите вернуть только одну запись.
  • 1
    Также имейте в QUALIFY предложение QUALIFY относится только к Teradata. В Oracle (по крайней мере) вы должны вкладывать свой запрос и фильтровать его, используя предложение WHERE в операторе select обтекания (что, я думаю, может повлиять на производительность).
6

У меня нет Oracle для тестирования, но наиболее эффективным решением является использование аналитических запросов. Он должен выглядеть примерно так:

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

Я подозреваю, что вы можете избавиться от внешнего запроса и наделить его внутренним, но я не уверен. Тем временем я знаю, что это работает.

Если вы хотите узнать об аналитических запросах, я бы предложил прочитать http://www.orafaq.com/node/55 и http://www.akadia.com/services/ora_analytic_functions.html. Вот краткое резюме.

Под капотом аналитические запросы сортируют весь набор данных, затем обрабатывают его последовательно. По мере его обработки вы разбиваете набор данных в соответствии с определенными критериями, а затем для каждой строки просматривается какое-то окно (по умолчанию используется первое значение раздела в текущей строке - это значение по умолчанию также является наиболее эффективным) и может вычислять значения с помощью количество аналитических функций (список которых очень похож на совокупные функции).

В этом случае вот что делает внутренний запрос. Весь набор данных сортируется по UserId, а затем DES DES. Затем он обрабатывает его за один проход. Для каждой строки вы возвращаете UserId и первую Date, указанную для этого UserId (поскольку даты сортируются DESC, это максимальная дата). Это дает вам ответ с повторяющимися строками. Затем внешний DISTINCT выдает дубликаты.

Это не особенно впечатляющий пример аналитических запросов. Для гораздо большей победы подумайте о принятии таблицы финансовых поступлений и расчета для каждого пользователя и квитанции, общая сумма того, что они заплатили. Аналитические запросы эффективно решаются. Другие решения менее эффективны. Именно поэтому они являются частью стандарта SQL 2003. (К сожалению, у Postgres их еще нет. Grrr...)

  • 0
    Вам также необходимо вернуть значение даты, чтобы полностью ответить на вопрос. Если это означает еще одно предложение first_value, то я бы предположил, что решение является более сложным, чем должно быть, и аналитический метод, основанный на max (date), выглядит лучше.
  • 0
    В постановке вопроса ничего не говорится о возврате даты. Вы можете сделать это либо добавив еще один FIRST (Date), либо просто запросив Date и изменив внешний запрос на GROUP BY. Я бы использовал первое и ожидал, что оптимизатор рассчитает оба за один проход.
Показать ещё 3 комментария
4

С PostgreSQL 9 вы можете использовать это:

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
3
select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))
3

Просто нужно было написать "живой" пример на работе:)

Этот параметр поддерживает несколько значений для UserId в дате той же.

Колонки: UserId, Value, Date

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
)

Вы можете использовать FIRST_VALUE вместо MAX и искать его в плане объяснения. У меня не было времени, чтобы поиграть с ним.

Конечно, при поиске по огромным таблицам, вероятно, лучше, если вы будете использовать ПОЛНЫЕ подсказки в своем запросе.

3
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  
2

В Oracle 12c+ вы можете использовать Top n запросов вместе с аналитической функцией rank для достижения этого очень сжато без подзапросов:

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

Приведенное выше возвращает все строки с max my_date для каждого пользователя.

Если вам нужна только одна строка с максимальной датой, замените rank на 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; 
2

Я опаздываю на вечеринку, но следующий хак будет превосходить как коррелированные подзапросы, так и любую функцию аналитики, но имеет одно ограничение: значения должны преобразовываться в строки. Таким образом, он работает для дат, чисел и других строк. Код выглядит не очень хорошо, но профиль выполнения отлично.

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

Причина, по которой этот код работает настолько хорошо, заключается в том, что ему нужно только один раз сканировать таблицу. Он не требует каких-либо индексов, и, самое главное, ему не нужно сортировать таблицу, что делает большинство функций аналитики. Индексы помогут, хотя если вам нужно отфильтровать результат для одного идентификатора пользователя.

  • 0
    Это хороший план выполнения по сравнению с большинством, но применение всех этих трюков к нескольким полям будет утомительным и может сработать против него. Но очень интересно - спасибо. см. sqlfiddle.com/#!4/2749b5/23
  • 0
    Вы правы, это может стать утомительным, поэтому это следует делать только тогда, когда этого требует производительность запроса. Такое часто случается со сценариями ETL.
Показать ещё 1 комментарий
2

Ответ здесь - только Oracle. Вот немного более сложный ответ во всех SQL:

У кого лучший результат домашней работы (максимальная сумма домашних заданий)?

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)

И более сложный пример, который нуждается в некотором объяснении, для которого у меня нет времени atm:

Дайте книгу (ISBN и название), которая наиболее популярна в 2008 году, то есть, которая чаще всего заимствована в 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);

Надеюсь, что это поможет (кому угодно)..:)

С уважением, Гус

  • 0
    Принятый ответ - не «только Oracle» - это стандартный SQL (поддерживается многими СУБД)
2

Я думаю, что-то вроде этого. (Простите меня за любые синтаксические ошибки, я использую HQL на этом этапе!)

РЕДАКТИРОВАТЬ: Также неправильно прочитал вопрос! Исправлен запрос...

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)
  • 0
    Не соответствует условию "для каждого идентификатора пользователя"
  • 0
    Где это потерпит неудачу? Для каждого UserID в Users будет гарантировано, что будет возвращена хотя бы одна строка, содержащая этот UserID. Или я где-то пропускаю особый случай?
2

i вещь, которую вы shuold внесите этот вариант в предыдущий запрос:

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

Предполагаемая дата уникальна для данного UserID, здесь некоторые 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 
2

(T-SQL) Сначала получите всех пользователей и их maxdate. Присоединитесь к таблице, чтобы найти соответствующие значения для пользователей в maxdates.

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

результаты:

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

проверьте эту ссылку, если ваши вопросы похожи на эту страницу, тогда я предлагаю вам следующий запрос, который даст решение для этой ссылки

select distinct sno,item_name,max(start_date) over(partition by sno),max(end_date) over(partition by sno),max(creation_date) over(partition by sno), max(last_modified_date) over(partition by sno) from uniq_select_records order by sno,item_name asc;

дадут точные результаты, связанные с этой ссылкой

1

Если вы используете Postgres, вы можете использовать array_agg как

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

Я не знаком с Oracle. Это то, что я придумал

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 

Оба запроса возвращают те же результаты, что и принятый ответ. См. SQLFiddles:

1

Просто протестировал это и, похоже, работает с таблицей протоколирования

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

Это также позаботится о дубликатах (верните одну строку для каждого user_id):

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
1

Это должно быть просто:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)
  • 2
    неверно, это не делает то, что нужно OP.
1

Я думаю, что это должно работать?

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
1
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

ИМХО это работает. HTH

1

Сначала попробуйте, я неправильно прочитал вопрос, следуя главному ответу, вот полный пример с правильными результатами:

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)
0

Используйте ROW_NUMBER(), чтобы присвоить уникальный рейтинг по убыванию Date для каждого UserId, затем отфильтровать в первую строку для каждый UserId (т.е. 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;
0

Используйте код:

select T.UserId,T.dt from (select UserId,max(dt) 
over (partition by UserId) as dt from t_users)T where T.dt=dt;

Это приведет к получению результатов, независимо от повторяющихся значений для UserId. Если ваш UserId уникален, он становится более простым:

select UserId,max(dt) from t_users group by UserId;
0

Решение для MySQL, у которого нет понятий раздела 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

Ссылка: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html

  • 0
    Это не работает " на других БД ". Это работает только на MySQL и, возможно, на SQL Server, потому что он имеет похожую концепцию переменных. Это определенно не будет работать на Oracle, Postgres, DB2, Derby, H2, HSQLDB, Vertica, Greenplum. Кроме того, принятым ответом является стандартный ANSI SQL (который, как известно, только MySQL не поддерживает)
  • 0
    лошадь, я думаю, ты прав. У меня нет знаний о других БД или ANSI. Мое решение способно решить проблему в MySQL, которая не имеет надлежащей поддержки ANSI SQL для ее стандартного решения.
0
select   UserId,max(Date) over (partition by UserId) value from users;
  • 2
    Это вернет все строки, а не только одну строку на пользователя.
0

Если (UserID, Date) уникально, то есть ни одна дата не появляется дважды для одного и того же пользователя, а затем:

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;
  • 0
    Я считаю, что вам нужно присоединиться и по UserID
  • 0
    Ты прав. Исправлена.
-1
SELECT a.* 
FROM user a INNER JOIN (SELECT userid,Max(date) AS date12 FROM user1 GROUP BY userid) b  
ON a.date=b.date12 AND a.userid=b.userid ORDER BY a.userid;

Ещё вопросы

Сообщество Overcoder
Наверх
Меню