mysql — получает лучшие n записей для каждой группы сгруппированных результатов

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

Учитывая приведенную ниже таблицу с столбцами персонажа, группы и возраста, как бы вы получить 2 старейших людей в каждой группе? (Связи внутри групп не должны давать больше результатов, но дают первые 2 в алфавитном порядке)

+--------+-------+-----+
| Человек | Группа | Возраст |
+ -------- + ------- + ----- +
| Боб | 1 | 32 |
| Джилл | 1 | 34 |
| Шон | 1 | 42 |
| Джейк | 2 | 29 |
| Пол | 2 | 36 |
| Лаура | 2 | 39 |
+ -------- + ------- + ----- +

Требуемый набор результатов:

+--------+-------+-----+
| Шон | 1 | 42 |
| Джилл | 1 | 34 |
| Лаура | 2 | 39 |
| Пол | 2 | 36 |
+ -------- + ------- + ----- +

НОТА: Этот вопрос основан на предыдущем Получить записи с максимальным значением для каждой группы сгруппированных результатов SQL — для получения по одной верхней строке от каждой группы и получения отличного специфичного для MySQL ответа от @Bohemian:

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

Хотел бы быть в состоянии построить это, хотя я не понимаю, как.

122

Решение

Вот один из способов сделать это, используя UNION ALL (Увидеть SQL Fiddle с демоверсией). Это работает с двумя группами, если у вас более двух групп, вам нужно будет указать group нумерация и добавление запросов для каждого group:

(
select *
from mytable
where `group` = 1
order by age desc
LIMIT 2
)
UNION ALL
(
select *
from mytable
where `group` = 2
order by age desc
LIMIT 2
)

Есть множество способов сделать это, см. Эту статью, чтобы определить лучший маршрут для вашей ситуации:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Редактировать:

Это также может работать для вас, он генерирует номер строки для каждой записи. Используя пример из ссылки выше, это вернет только те записи с номером строки, меньшим или равным 2:

select person, `group`, age
from
(
select person, `group`, age,
(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number
from test t
CROSS JOIN (select @num:=0, @group:=null) c
order by `Group`, Age desc, person
) as x
where x.row_number <= 2;

Увидеть демонстрация

81

Другие решения

В других базах данных вы можете сделать это, используя ROW_NUMBER, MySQL не поддерживает ROW_NUMBER но вы можете использовать переменные для эмуляции:

SELECT
person,
groupname,
age
FROM
(
SELECT
person,
groupname,
age,
@rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
@prev := groupname
FROM mytable
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

Посмотрите, как работает онлайн: sqlfiddle


редактировать Я только что заметил, что bluefeet опубликовал очень похожий ответ: +1 к нему. Однако этот ответ имеет два небольших преимущества:

  1. Это один запрос. Переменные инициализируются внутри инструкции SELECT.
  2. Он обрабатывает связи, как описано в вопросе (в алфавитном порядке по имени).

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

52

Попробуй это:

SELECT a.person, a.group, a.age FROM person AS a WHERE
(SELECT COUNT(*) FROM person AS b
WHERE b.group = a.group AND b.age >= a.age) <= 2
ORDER BY a.group ASC, a.age DESC

DEMO

34

Как насчет использования самосоединения:

CREATE TABLE mytable (person, groupname, age);
INSERT INTO mytable VALUES('Bob',1,32);
INSERT INTO mytable VALUES('Jill',1,34);
INSERT INTO mytable VALUES('Shawn',1,42);
INSERT INTO mytable VALUES('Jake',2,29);
INSERT INTO mytable VALUES('Paul',2,36);
INSERT INTO mytable VALUES('Laura',2,39);

SELECT a.* FROM mytable AS a
LEFT JOIN mytable AS a2
ON a.groupname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER BY a.groupname, a.age DESC;

дает мне:

a.person    a.groupname  a.age
----------  -----------  ----------
Shawn       1            42
Jill        1            34
Laura       2            39
Paul        2            36

Я был сильно вдохновлен ответом Билла Карвина на Выберите 10 лучших записей для каждой категории

Кроме того, я использую SQLite, но это должно работать на MySQL.

Другое дело: в приведенном выше я заменил group колонка с groupname колонка для удобства.

редактировать:

В продолжение комментария ОП относительно недостающих результатов связей я увеличил ответ Снаффина, чтобы показать все связи. Это означает, что если последние являются связями, может быть возвращено более 2 строк, как показано ниже:

.headers on
.mode column

CREATE TABLE foo (person, groupname, age);
INSERT INTO foo VALUES('Paul',2,36);
INSERT INTO foo VALUES('Laura',2,39);
INSERT INTO foo VALUES('Joe',2,36);
INSERT INTO foo VALUES('Bob',1,32);
INSERT INTO foo VALUES('Jill',1,34);
INSERT INTO foo VALUES('Shawn',1,42);
INSERT INTO foo VALUES('Jake',2,29);
INSERT INTO foo VALUES('James',2,15);
INSERT INTO foo VALUES('Fred',1,12);
INSERT INTO foo VALUES('Chuck',3,112);


SELECT a.person, a.groupname, a.age
FROM foo AS a
WHERE a.age >= (SELECT MIN(b.age)
FROM foo AS b
WHERE (SELECT COUNT(*)
FROM foo AS c
WHERE c.groupname = b.groupname AND c.age >= b.age) <= 2
GROUP BY b.groupname)
ORDER BY a.groupname ASC, a.age DESC;

дает мне:

person      groupname   age
----------  ----------  ----------
Shawn       1           42
Jill        1           34
Laura       2           39
Paul        2           36
Joe         2           36
Chuck       3           112
30

Проверь это:

SELECT
p.Person,
p.`Group`,
p.Age
FROM
people p
INNER JOIN
(
SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`
UNION
SELECT MAX(p3.Age) AS Age, p3.`Group` FROM people p3 INNER JOIN (SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`) p4 ON p3.Age < p4.Age AND p3.`Group` = p4.`Group` GROUP BY `Group`
) p2 ON p.Age = p2.Age AND p.`Group` = p2.`Group`
ORDER BY
`Group`,
Age DESC,
Person;

SQL Fiddle: http://sqlfiddle.com/#!2/cdbb6/15

10

Решение Snuffin кажется довольно медленным для выполнения, когда у вас есть много строк, а решения Mark Byers / Rick James и Bluefeet не работают в моей среде (MySQL 5.6), потому что order by применяется после выполнения select, поэтому здесь есть вариант Решений Марка Байерса / Рика Джеймса для решения этой проблемы (с дополнительным замкнутым выбором):

select person, groupname, age
from
(
select person, groupname, age,
(@rn:=if(@prev = groupname, @rn +1, 1)) as rownumb,
@prev:= groupname
from
(
select person, groupname, age
from persons
order by groupname ,  age desc, person
)   as sortedlist
JOIN (select @prev:=NULL, @rn :=0) as vars
) as groupedlist
where rownumb<=2
order by groupname ,  age desc, person;

Я пробовал аналогичный запрос для таблицы с 5 миллионами строк, и он возвращает результат менее чем за 3 секунды

6

Если другие ответы не достаточно быстры, дайте этот код попытка:

SELECT
province, n, city, population
FROM
( SELECT  @prev := '', @n := 0 ) init
JOIN
( SELECT  @n := if(province != @prev, 1, @n + 1) AS n,
@prev := province,
province, city, population
FROM  Canada
ORDER BY
province   ASC,
population DESC
) x
WHERE  n <= 3
ORDER BY  province, n;

Выход:

+---------------------------+------+------------------+------------+
| province                  | n    | city             | population |
+---------------------------+------+------------------+------------+
| Alberta                   |    1 | Calgary          |     968475 |
| Alberta                   |    2 | Edmonton         |     822319 |
| Alberta                   |    3 | Red Deer         |      73595 |
| British Columbia          |    1 | Vancouver        |    1837970 |
| British Columbia          |    2 | Victoria         |     289625 |
| British Columbia          |    3 | Abbotsford       |     151685 |
| Manitoba                  |    1 | ...
5

Я хотел поделиться этим, потому что я потратил много времени на поиск простого способа реализовать это в Java-программе, над которой я работаю. Это не совсем дает результат, который вы ищете, но его закрытие. Функция в MySQL называется GROUP_CONCAT() работал очень хорошо для определения, сколько результатов нужно вернуть в каждой группе. С помощью LIMIT или любой другой причудливый способ попытаться сделать это с COUNT не работает для меня Так что, если вы готовы принять измененный вывод, это отличное решение. Допустим, у меня есть таблица «ученик» с идентификаторами учеников, их полом и gpa. Допустим, я хочу увеличить 5 gpas для каждого пола. Тогда я могу написать запрос так

SELECT sex, SUBSTRING_INDEX(GROUP_CONCAT(cast(gpa AS char ) ORDER BY gpa desc), ',',5)
AS subcategories FROM student GROUP BY sex;

Обратите внимание, что параметр «5» сообщает, сколько записей нужно объединить в каждой строке.

И вывод будет выглядеть примерно так

+--------+----------------+
| Male   | 4,4,4,4,3.9    |
| Female | 4,4,3.9,3.9,3.8|
+--------+----------------+

Вы также можете изменить ORDER BY переменная и заказать их по-другому. Поэтому, если бы у меня был возраст студента, я мог бы заменить «gpa desc» на «age desc», и это сработает! Вы также можете добавить переменные в оператор group by, чтобы получить больше столбцов в выводе. Так что я обнаружил, что это довольно гибкий способ, который хорошо работает, если вы согласны с простым перечислением результатов.

2
По вопросам рекламы [email protected]