SQL выбирает только строки с максимальным значением в столбце
У меня есть эта таблица для документов (упрощенная версия здесь):
+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
+------+-------+--------------------------------------+
Как выбрать одну строку для каждого идентификатора и только наибольшее число оборотов?
С данными выше, результат должен содержать две строки: [1, 3, ...] а также [2, 1, ..], Я использую MySQL.
В настоящее время я использую чеки в while цикл для обнаружения и перезаписи старых оборотов из набора результатов. Но является ли это единственным методом достижения результата? Разве нет решения SQL?
Обновить
Как показывают ответы, существует SQL-решение, а здесь демонстрация sqlfiddle.
Обновление 2
Я заметил, после добавления вышеупомянутого sqlfiddle, скорость, с которой вопрос поднимается, превысила скорость ответов. Это не было намерением! Скрипка основана на ответах, особенно принятых ответах.
29 ответов
На первый взгляд...
Все, что вам нужно, это GROUP BY оговорка с MAX агрегатная функция:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
Это никогда не было так просто, правда?
Я просто заметил, что вам нужно content колонна также.
Это очень распространенный вопрос в SQL: найти все данные для строки с некоторым максимальным значением в столбце для некоторого идентификатора группы. Я много это слышал за свою карьеру. На самом деле, это был один из вопросов, на которые я ответил на техническом собеседовании.
На самом деле, это настолько распространено, что сообщество Stackru создало один тег для решения таких вопросов: наибольший-на-группу.
По сути, у вас есть два подхода к решению этой проблемы:
Присоединение с простым group-identifier, max-value-in-group Суб-запрос
При таком подходе вы сначала находите group-identifier, max-value-in-group (уже решено выше) в подзапросе. Затем вы присоединяете свою таблицу к подзапросу с равенством на обоих group-identifier а также max-value-in-group:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
Оставлено соединение с самим собой, настройка условий соединения и фильтров
При таком подходе вы оставляете за столом присоединение к себе. Равенство, конечно, идет в group-identifier, Затем 2 умных хода:
- Второе условие соединения имеет значение левой стороны меньше правого значения
- Когда вы выполните шаг 1, строки, которые на самом деле имеют максимальное значение, будут иметь
NULLв правой части (этоLEFT JOIN, Помните?). Затем мы фильтруем объединенный результат, показывая только те строки, где правая сторонаNULL,
Итак, вы в конечном итоге:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
Заключение
Оба подхода дают одинаковый результат.
Если у вас есть две строки с max-value-in-group за group-identifierобе строки будут в результате в обоих подходах.
Оба подхода совместимы с SQL ANSI, поэтому будут работать с вашей любимой СУБД, независимо от ее "аромата".
Оба подхода также влияют на производительность, однако ваш пробег может отличаться (СУБД, структура БД, индексы и т. Д.). Поэтому, когда вы выбираете один подход, а другой - эталонный. И убедитесь, что вы выбрали тот, который имеет наибольшее значение для вас.
Я предпочитаю использовать как можно меньше кода...
Вы можете сделать это с помощью INпопробуй это:
SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
FROM t1
GROUP BY id
)
На мой взгляд, это менее сложно... легче читать и поддерживать.
Я поражен, что ни один ответ не предложил решение для оконной функции SQL:
SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable) a
WHERE a.rank = 1
Добавленные в стандарт SQL ANSI/ISO Standard SQL:2003 и более поздние, дополненные стандартом ANSI / ISO SQL:2008, оконные (или оконные) функции теперь доступны для всех основных поставщиков. Есть еще несколько типов функций ранга, доступных для решения проблемы связи: RANK, DENSE_RANK, PERSENT_RANK,
Еще одним решением является использование коррелированного подзапроса:
select yt.id, yt.rev, yt.contents
from YourTable yt
where rev =
(select max(rev) from YourTable st where yt.id=st.id)
Наличие индекса (id,rev) делает подзапрос почти простым поиском...
Ниже приводятся сравнения с решениями в ответе @AdrianCarneiro (подзапрос, левое соединение), основанный на измерениях MySQL с таблицей InnoDB ~1 миллион записей, размер группы: 1-3.
В то время как для полных сканирований таблицы подзапрос / оставленное соединение / коррелированные моменты времени относятся друг к другу как 6/8/9, когда дело доходит до прямого поиска или пакета (id in (1,2,3)), подзапрос намного медленнее остальных (из-за повторного выполнения подзапроса). Однако я не мог различить левое соединение и коррелированные решения по скорости.
И последнее замечание: поскольку leftjoin создает n*(n+1)/2 объединений в группах, его производительность может сильно зависеть от размера групп...
Я не могу ручаться за производительность, но вот трюк, вдохновленный ограничениями Microsoft Excel. У этого есть несколько хороших особенностей
ХОРОШАЯ ВЕЩЬ
- Это должно вызвать возврат только одной "максимальной записи", даже если есть связь (иногда полезно)
- Не требует объединения
ПОДХОД
Это немного уродливо и требует, чтобы вы знали кое-что о диапазоне допустимых значений столбца rev. Предположим, что мы знаем, что столбец rev - это число от 0,00 до 999, включая десятичные дроби, но справа от десятичной точки всегда будет только две цифры (например, 34.17 будет допустимым значением).
Суть в том, что вы создаете один синтетический столбец путем объединения строк / упаковки основного поля сравнения вместе с данными, которые вы хотите. Таким образом, вы можете заставить агрегатную функцию SQL MAX() возвращать все данные (потому что они были упакованы в один столбец). Затем вы должны распаковать данные.
Вот как это выглядит на примере выше, написанном на SQL
SELECT id,
CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev
FROM (SELECT id,
CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
FROM yourtable
)
GROUP BY id
Упаковка начинается с того, что значение столбца rev будет равным известной длине символа независимо от значения rev, например,
- 3.2 становится 1003.201
- 57 становится 1057,001
- 923,88 становится 1923,881
Если вы все сделаете правильно, сравнение строк двух чисел должно привести к тому же "макс", что и сравнение чисел двух чисел, и его легко преобразовать обратно в исходное число с помощью функции подстроки (которая доступна в той или иной форме в значительной степени везде).
Я думаю, что это самое простое решение:
SELECT *
FROM
(SELECT *
FROM Employee
ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
- SELECT *: вернуть все поля.
- ОТ сотрудника: Таблица искалась.
- (ВЫБРАТЬ *...) подзапрос: Вернуть всех людей, отсортированных по зарплате.
- GROUP BY employeeub.Salary:: принудительно возвращает отсортированный сверху ряд зарплат каждого сотрудника в качестве возвращаемого результата.
Если вам нужен только один ряд, это еще проще:
SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1
Я также думаю, что проще всего разобраться, понять и изменить для других целей:
- ЗАКАЗАТЬ Employee.Salary DESC: Упорядочить результаты по зарплате, сначала с самой высокой зарплатой.
- ПРЕДЕЛ 1: вернуть только один результат.
Понимание этого подхода позволяет решить любую из этих схожих проблем: получить сотрудника с наименьшей зарплатой (смените DESC на ASC), получить работающего в десятке лучших (сменить LIMIT 1 на LIMIT 10), отсортировать с помощью другого поля (сменить ORDER BY Employee.Salary to ORDER BY Employee.Commission) и т. Д.
Что-то вроде этого?
SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
SELECT id, max(rev) as maxrev FROM yourtable
WHERE yourtable
GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
Другой способ выполнить работу - использовать аналитическую функцию MAX() в предложении OVER PARTITION.
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev
Другое решение ПРОШЛОГО РАЗДЕЛА, уже задокументированное в этом посте,
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable
) t
WHERE t.rank = 1
Эти 2 SELECT хорошо работают на Oracle 10g.
Мне нравится использовать NOT EXISTрешение для этой проблемы:
SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
SELECT * FROM Employee, в котором Employee.Salary (выбрать максимальную (зарплату) из группы Employee по Employe_id) ORDER BY Employee.Salary
Третье решение, которое я почти никогда не упоминал, касается MySQL и выглядит так:
SELECT id, MAX(rev) AS rev
, 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id
Да, это выглядит ужасно (преобразование в строку, обратно и т. Д.), Но по моему опыту это обычно быстрее, чем другие решения. Может быть, это только для моих случаев использования, но я использовал его в таблицах с миллионами записей и многими уникальными идентификаторами. Возможно, это связано с тем, что MySQL плохо умеет оптимизировать другие решения (по крайней мере, за 5,0 дней, когда я придумал это решение).
Одна важная вещь заключается в том, что GROUP_CONCAT имеет максимальную длину для строки, которую он может создать. Вы, вероятно, хотите поднять этот предел, установив group_concat_max_len переменная. И имейте в виду, что это будет предел для масштабирования, если у вас есть большое количество строк.
В любом случае, вышеприведенное не работает напрямую, если ваше поле контента уже текстовое. В этом случае вы, вероятно, захотите использовать другой разделитель, например, \0. Вы также столкнетесь с group_concat_max_len ограничить быстрее.
Я думаю, ты этого хочешь?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle: проверьте здесь
НЕ mySQL, но для других людей, которые находят этот вопрос и используют SQL, существует другой способ решения проблемы " наибольшее число групп". Cross Apply в MS SQL
WITH DocIds AS (SELECT DISTINCT id FROM docs)
SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
SELECT Top 1 * FROM docs d
WHERE d.id = d1.id
ORDER BY rev DESC
) d2
Вот пример в SqlFiddle
Так как это самый популярный вопрос в отношении этой проблемы, я еще раз отвечу на этот вопрос здесь:
Похоже, есть более простой способ сделать это (но только в MySQL):
select *
from (select * from mytable order by id, rev desc ) x
group by id
Пожалуйста, отметьте ответ пользователя Bohemian в этом вопросе за краткий и элегантный ответ на эту проблему.
РЕДАКТИРОВАТЬ: хотя это решение работает для многих людей, оно может быть нестабильным в долгосрочной перспективе, поскольку MySQL не гарантирует, что оператор GROUP BY будет возвращать значимые значения для столбцов, не входящих в список GROUP BY. Так что используйте это решение на свой страх и риск
Я бы использовал это:
select t.*
from test as t
join
(select max(rev) as rev
from test
group by id) as o
on o.rev = t.rev
Подзапрос SELECT, возможно, не слишком эффективен, но в предложении JOIN кажется пригодным для использования. Я не эксперт в оптимизации запросов, но я пробовал в MySQL, PostgreSQL, FireBird, и это работает очень хорошо.
Вы можете использовать эту схему в нескольких соединениях и с предложением WHERE. Это мой рабочий пример (решение идентично вашей проблеме с таблицей "фирма"):
select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
from firmy
group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'
Он задается для таблиц, имеющих десятки и десятки записей, и это займет менее 0,01 секунды на действительно не слишком сильной машине.
Я бы не использовал предложение IN (как упомянуто где-то выше). IN предоставляется для использования с короткими списками констант, а не как фильтр запросов, построенный на подзапросе. Это связано с тем, что подзапрос в IN выполняется для каждой отсканированной записи, что может сделать запрос очень долгим.
Как насчет этого:
select all_fields.*
from (select id, MAX(rev) from yourtable group by id) as max_recs
left outer join yourtable as all_fields
on max_recs.id = all_fields.id
Если в операторе select много полей и вы хотите получить последнее значение для всех этих полей через оптимизированный код:
select * from
(select * from table_name
order by id,rev desc) temp
group by id
Вот еще одно решение для извлечения записей только с полем, которое имеет максимальное значение для этого поля. Это работает для SQL400 - платформы, на которой я работаю. В этом примере записи с максимальным значением в поле FIELD5 будут получены с помощью следующего оператора SQL.
SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
FROM MYFILE A
WHERE RRN(A) IN
(SELECT RRN(B)
FROM MYFILE B
WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
ORDER BY B.FIELD5 DESC
FETCH FIRST ROW ONLY)
Мне нравится делать это, ранжируя записи по некоторым столбцам. В этом случае ранг rev значения сгруппированы по id, Те, у кого выше rev будет иметь более низкий рейтинг. Так высоко rev будет иметь рейтинг 1.
select id, rev, content
from
(select
@rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
id, rev, content,
@prevValue := id
from
(select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
(select @rowNum := 1 from DUAL) X,
(select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;
Не уверен, что введение переменных замедляет процесс. Но по крайней мере я не запрашиваю YOURTABLE дважды.
Это решение делает только один выбор из YourTable, поэтому это быстрее. Это работает только для MySQL и SQLite(для SQLite удалить DESC) в соответствии с тестом на sqlfiddle.com. Может быть, он может быть настроен для работы на других языках, с которыми я не знаком.
SELECT *
FROM ( SELECT *
FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
UNION
SELECT 2, 1, 'content2'
UNION
SELECT 1, 2, 'content3'
UNION
SELECT 1, 3, 'content4'
) as YourTable
ORDER BY id, rev DESC
) as YourTable
GROUP BY id
Вот хороший способ сделать это
Используйте следующий код:
with temp as (
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)
Отсортировал поле rev в обратном порядке, а затем сгруппировал по id, который дал первую строку каждой группировки, которая является самой высокой с числом оборотов.
SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;
Протестировано в http://sqlfiddle.com/ со следующими данными
CREATE TABLE table1
(`id` int, `rev` int, `content` varchar(11));
INSERT INTO table1
(`id`, `rev`, `content`)
VALUES
(1, 1, 'One-One'),
(1, 2, 'One-Two'),
(2, 1, 'Two-One'),
(2, 2, 'Two-Two'),
(3, 2, 'Three-Two'),
(3, 1, 'Three-One'),
(3, 3, 'Three-Three')
;
Это дало следующий результат в MySql 5.5 и 5.6
id rev content
1 2 One-Two
2 2 Two-Two
3 3 Three-Two
Вот еще одно решение, надеюсь, это поможет кому-то
Select a.id , a.rev, a.content from Table1 a
inner join
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
Ни один из этих ответов не сработал для меня.
Это то, что сработало для меня.
with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
объяснение
Это не чистый SQL. Это будет использовать SQLAlchemy ORM.
Я пришел сюда в поисках помощи по SQLAlchemy, поэтому я продублирую ответ Адриана Карнейро с версией python/SQLAlchemy, в частности, с внешней частью соединения.
Этот запрос отвечает на вопрос:
"Можете ли вы вернуть мне записи в этой группе записей (на основе одного и того же идентификатора), которые имеют наибольший номер версии".
Это позволяет мне дублировать запись, обновлять ее, увеличивать ее номер версии и получать копию старой версии таким образом, чтобы я мог показывать изменения во времени.
Код
MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
MyTable,
MyTableAlias,
onclause=and_(
MyTable.id == MyTableAlias.id,
MyTable.version_int < MyTableAlias.version_int
),
isouter=True
)
).filter(
MyTableAlias.id == None,
).all()
Протестировано на базе данных PostgreSQL.
Вы можете сделать выбор без объединения, когда вы объединяете rev а также id в один maxRevId значение для MAX() а затем разделить его обратно на исходные значения:
SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
FROM YourTable
GROUP BY id) x;
Это особенно быстро, когда есть сложное соединение вместо одной таблицы. При традиционных подходах комплексное соединение будет выполнено дважды.
Приведенная выше комбинация проста с битовыми функциями, когда rev а также id являются INT UNSIGNED (32 бита) и комбинированное значение соответствует BIGINT UNSIGNED (64 бит). Когда id & rev больше 32-битных значений или состоят из нескольких столбцов, необходимо объединить значение, например, в двоичное значение с подходящим заполнением для MAX(),
Если кто-то ищет Linq Verson, мне кажется, это работает:
public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
.Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );
return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}
Я использовал ниже, чтобы решить проблему самостоятельно. Сначала я создал временную таблицу и вставил значение максимального числа оборотов для каждого уникального идентификатора.
CREATE TABLE #temp1
(
id varchar(20)
, rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as a
GROUP BY a.id
ORDER BY a.id
Затем я соединил эти максимальные значения (#temp1) со всеми возможными комбинациями id/content. Делая это, я естественным образом отфильтровываю не максимальные комбинации id/content, и у меня остаются только максимальные значения оборотов для каждой.
SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id
Это работает для меня в sqlite3:
SELECT *, MAX(rev) FROM t1 GROUP BY id
С * вы получаете повторяющийся столбец rev, но это не большая проблема.