mysql - строку - sql максимальное значение в группе




SQL выбирает только строки с максимальным значением в столбце (20)

На первый взгляд...

Все, что вам нужно, это предложение GROUP BY с функцией агрегации MAX :

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Это никогда не бывает так просто, не так ли?

Я просто заметил, что вам нужна колонка content .

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

На самом деле настолько распространено, что сообщество StackOverflow создало единый тег, чтобы справиться с такими вопросами: greatest-n-per-group .

В принципе, у вас есть два подхода к решению этой проблемы:

Объединение с простым group-identifier, max-value-in-group суб-запрос 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 . Затем два умных движения:

  1. Второе условие соединения имеет значение левой стороны меньше правого значения
  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, поэтому они будут работать с вашей любимой РСУБД, независимо от ее «вкуса».

Оба подхода также совместимы с производительностью, однако ваш пробег может варьироваться (РСУБД, структура БД, индексы и т. Д.). Поэтому, когда вы выбираете один подход по сравнению с другим, ориентир . И убедитесь, что вы выбрали тот, который имеет для вас большой смысл.

У меня есть эта таблица для документов (упрощенная версия здесь):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Как выбрать одну строку на один идентификатор и только наибольший оборот?
С приведенными выше данными результат должен содержать две строки: [1, 3, ...] и [2, 1, ..] . Я использую MySQL .

В настоящее время я использую проверки в цикле while для обнаружения и перезаписи старых revs из набора результатов. Но является ли это единственным методом достижения результата? Разве нет решения SQL ?

Обновить
Как показывают ответы, есть SQL-решение, а здесь демо-версия sqlfiddle .

Обновление 2
Я заметил, что после добавления вышеуказанного sqlfiddle скорость, с которой встал вопрос, превзошла скорость ответов. Это не было намерением! Эта скрипка основана на ответах, особенно на принятом ответе.


SELECT * FROM Employee, где Employee.Salary in (выберите max (зарплата) из группы Employee Employee_id) ORDER BY Employee.Salary


Вот хороший способ сделать это

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

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 в одно значение 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) );
}

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

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

Как насчет этого:

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

Мне нравится делать это, оценивая записи по некоторым столбцам. В этом случае значения ранга, сгруппированные по 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 .


Ни один из этих ответов не сработал для меня.

Это то, что сработало для меня.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Поскольку это самый популярный вопрос в отношении этой проблемы, я снова отправлю еще один ответ на этот вопрос:

Похоже, что есть более простой способ сделать это (но только в MySQL ):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Пожалуйста, примите к сведению ответ пользователя Bohemian в этом вопросе за предоставление такого краткого и элегантного ответа на эту проблему.

EDIT: хотя это решение работает для многих людей, оно может быть нестабильным в долгосрочной перспективе, поскольку MySQL не гарантирует, что оператор GROUP BY вернет значимые значения для столбцов, не входящих в список GROUP BY. Поэтому используйте это решение на свой страх и риск


Третье решение, о котором я почти никогда не упоминал, имеет специфику 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 лимитом group_concat_max_len .


Что-то вроде этого?

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)

Это решение делает только один выбор из 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

Я бы использовал это:

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. Это мой рабочий пример (решение идентично вашей проблеме с таблицей «firmy»):

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 выполняется для каждой отсканированной записи, которая может сделать запрос очень длительным.


Я использовал ниже, чтобы решить проблему самостоятельно. Сначала я создал временную таблицу и вставил максимальное значение rev на уникальный идентификатор.

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, и оставляю их только с максимальными значениями rev.

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

Я не могу ручаться за производительность, но вот трюк, вдохновленный ограничениями 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

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


Я предпочитаю использовать как можно меньше кода ...

Вы можете сделать это, используя IN :

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

на мой взгляд, он менее сложный ... легче читать и поддерживать.


вот еще одно решение, надеюсь, что это поможет кому-то

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

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);




greatest-n-per-group