Получение записей с максимальным значением для каждой группы сгруппированных результатов SQL


Как вы получаете строки, содержащие максимальное значение для каждого сгруппированного набора?

Я видел некоторые чрезмерно сложные вариации на этот вопрос, и ни один с хорошим ответом. Я попытался собрать самый простой пример:

учитывая таблицу, подобную приведенной ниже, с столбцами person, group и age, как бы вы получили самого старого человека в каждой группе? (Галстук в группе должен дать первый алфавитный результат)

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39  

желаемому результату набор:

Shawn | 1     | 42    
Laura | 2     | 39  
17 162

17 ответов:

есть супер-простой способ сделать это в mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

это работает, потому что в mysql вам разрешено не aggregate non-group-BY columns, в этом случае mysql просто возвращает первый строки. Решение состоит в том, чтобы сначала упорядочить данные так, чтобы для каждой группы сначала была выбрана строка, а затем сгруппировать по столбцам, для которых требуется значение.

вы избегаете сложных подзапросов, которые пытаются найти max() etc, а также проблемы возврат нескольких строк, когда есть более одного с тем же максимальным значением (как и другие ответы)

Примечание: это только mysql решение. Все другие базы данных, которые я знаю, вызовут синтаксическую ошибку SQL с сообщением "неагрегированные столбцы не перечислены в предложении group by" или аналогично. Потому что это решение использует недокументированные поведение, более осторожный может захотеть включить тест, чтобы утверждать, что он остается работа должна будущая версия MySQL изменить это поведение.

обновление версии 5.7:

начиная с версии 5.7, в sql-mode установка включает в себя ONLY_FULL_GROUP_BY по умолчанию, поэтому, чтобы сделать эту работу, вы должны не есть эта опция (отредактируйте файл опций для сервера, чтобы удалить эту настройку).

правильное решение:

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

как работает:

он соответствует каждой строке из o все строки b имея то же значение в столбце Group и большее значение в столбце Age. Любая строка из o не имея максимального значения своей группы в столбце Age будет соответствовать одной или более строк из b.

The LEFT JOIN делает его соответствовать самый старый человек в группе (в том числе лиц, которые одиноки в своей группе) с рядом, полным NULLС b ("нет самого большого возраста в группе").
используя INNER JOIN делает эти строки не совпадают, и они игнорируются.

The WHERE предложение сохраняет только строки, имеющие NULL s в полях, извлеченных из b. Это самые старые люди из каждой группы.

дополнительная литература

это решение и многие другие описаны в книге антипаттерны SQL: избегая ловушек Программирование Баз Данных

мое простое решение для SQLite (и, вероятно, MySQL):

SELECT *, MAX(age) FROM mytable GROUP BY `Group`;

однако он не работает в PostgreSQL и, возможно, на некоторых других платформах.

в PostgreSQL вы можете использовать DISTINCT ON статья:

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;

вы можете присоединиться к подзапросу, который тянет MAX(Group) и Age. Этот метод переносим через большинство СУБД.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT `Group`, MAX(Age) AS max_age
    FROM yourTable
    GROUP BY `Group`
) t2
    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;

используя метод ранжирования.

SELECT @rn :=  CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,  
   @prev_grp :=groupa,
   person,age,groupa  
FROM   users,(SELECT @rn := 0) r        
HAVING rn=1
ORDER  BY groupa,age DESC,person

решение axiac-это то, что лучше всего сработало для меня в конце концов. Однако у меня была дополнительная сложность: вычисленное "максимальное значение", полученное из двух столбцов.

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

Я должен был выполнить левое соединение два раза чтобы получить такое поведение:

SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height 
WHERE o2.Height is NULL;

надеюсь, что это помогает! Я думаю, что должен быть лучший способ сделать это хотя...

С Использованием Обобщенных Табличных Выражений - Общие Табличные Выражения:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

Не уверен, что MySQL имеет функцию row_number. Если это так, вы можете использовать его, чтобы получить желаемый результат. На SQL Server вы можете сделать что-то подобное:

CREATE TABLE p
(
 person NVARCHAR(10),
 gp INT,
 age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
         FROM   p
        ) t
WHERE   t.row = 1;

мое решение работает только в том случае, если вам нужно получить только один столбец, однако для моих нужд было найдено лучшее решение с точки зрения производительности (он использует только один запрос!):

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;

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

вы также можете попробовать

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

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

Источник: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT person, group,
    GROUP_CONCAT(
        DISTINCT age
        ORDER BY age DESC SEPARATOR ', follow up: '
    )
FROM sql_table
GROUP BY group;

пусть имя таблицы будет people

select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and 
O.Age = 
(select max(T.age) from people T where O.grp = T.grp
  group by T.grp)
group by O.grp; 

Если ID (и все coulmns) требуется от mytable

SELECT
    *
FROM
    mytable
WHERE
    id NOT IN (
        SELECT
            A.id
        FROM
            mytable AS A
        JOIN mytable AS B ON A. GROUP = B. GROUP
        AND A.age < B.age
    )

вот как я получаю N max строк на группу в mysql

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

как работает:

  • self join to the table
  • группы осуществляется co.country = ci.country
  • N элементы каждой группы находятся под контролем ) < 1 Так для 3 элементов )
  • чтобы получить Макс или мин зависит от: co.id < ci.id
    • co.id
    • co.id > ci.id -мин

полный пример здесь:

mysql выбирает N максимальных значений на группу

у меня есть простое решение с помощью WHERE IN

SELECT a.* FROM `mytable` AS a    
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )    
ORDER BY a.group ASC, a.person ASC
with CTE as 
(select Person, 
[Group], Age, RN= Row_Number() 
over(partition by [Group] 
order by Age desc) 
from yourtable)`


`select Person, Age from CTE where RN = 1`

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

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN 
(
  SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] 
  GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest