Uzyskaj najwyższe N rekordów dla każdej grupy wyników zgrupowanych
Poniżej przedstawiamy najprostszy możliwy przykład, choć każde rozwiązanie powinno być w stanie skalować do dowolnej liczby n najlepszych wyników:
Biorąc pod uwagę tabelę jak ta poniżej, z kolumnami osoby, grupy i wieku, jak można uzyskać 2 najstarsze osoby w każdej grupie? (powiązania w grupach nie powinny dawać więcej wyników, ale podawać pierwsze 2 w porządku alfabetycznym)
+--------+-------+-----+ | Person | Group | Age | +--------+-------+-----+ | Bob | 1 | 32 | | Jill | 1 | 34 | | Shawn | 1 | 42 | | Jake | 2 | 29 | | Paul | 2 | 36 | | Laura | 2 | 39 | +--------+-------+-----+
Pożądany zestaw wyników:
+--------+-------+-----+ | Shawn | 1 | 42 | | Jill | 1 | 34 | | Laura | 2 | 39 | | Paul | 2 | 36 | +--------+-------+-----+
Uwaga: to pytanie opiera się na poprzednim one - Get records with max value for each group of grouped SQL results - for getting a single top row from each group, and which received a great MySQL-specific answer from @ Bohemian:
select *
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`
Chciałbym móc to zbudować, choć Nie wiem jak. 10 answers
Tutaj jest jeden sposób, aby to zrobić, używając UNION ALL
(Zobacz SQL Fiddle z Demo ). Działa to z dwiema grupami, jeśli masz więcej niż dwie grupy, musisz podać numer group
i dodać zapytania dla każdej 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
)
Istnieje wiele sposobów, aby to zrobić, zobacz ten artykuł, aby określić najlepszą drogę dla twojej sytuacji:
Http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
Edit:
To może również działać dla Ciebie, generuje numer wiersza dla każdego rekordu. Używając przykładu z linku powyżej zwróci to tylko te rekordy z liczbą wierszy mniejszą lub równą 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;
Zobacz Demo
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-27 14:44:31
W innych bazach danych można to zrobić za pomocą ROW_NUMBER
. MySQL nie obsługuje ROW_NUMBER
, ale możesz użyć zmiennych, aby go emulować:
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
Zobacz to działa online: sqlfiddle
Edit właśnie zauważyłem, że bluefeet napisał do niego bardzo podobną odpowiedź: +1. Jednak ta odpowiedź ma dwie małe zalety:
- jest to pojedyncze zapytanie. Zmienne są inicjowane wewnątrz instrukcji SELECT.
- obsługuje krawaty opisane w pytaniu (kolejność alfabetyczna według nazwy).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-24 23:02:12
Spróbuj tego:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-24 17:54:37
Jak o użyciu samo-łączenia:
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;
Daje mi:
a.person a.groupname a.age
---------- ----------- ----------
Shawn 1 42
Jill 1 34
Laura 2 39
Paul 2 36
Zainspirowała mnie odpowiedź Billa Karwina, aby wybrać top 10 płyt dla każdej kategorii
Również używam SQLite, ale to powinno działać na MySQL.
Inna sprawa: w powyższym przykładzie dla wygody zamieniłem kolumnę group
na kolumnę groupname
.
Edit :
W związku z komentarzem OP dotyczącym brakujących wyników remisu, dodałem tabakierkę Odpowiedz, aby pokazać wszystkie krawaty. Oznacza to, że jeśli ostatnie są wiązaniami, można zwrócić więcej niż 2 wiersze, jak pokazano poniżej:
.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;
Daje mi:
person groupname age
---------- ---------- ----------
Shawn 1 42
Jill 1 34
Laura 2 39
Paul 2 36
Joe 2 36
Chuck 3 112
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:03:05
Zobacz to:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-24 17:48:35
Rozwiązanie Snuffin wydaje się dość powolne w wykonaniu, gdy masz dużo wierszy i rozwiązania Mark Byers / Rick James i Bluefeet nie działają na moim środowisku (MySQL 5.6), ponieważ order by jest stosowane po wykonaniu select, więc oto wariant rozwiązań Marc Byers/Rick James, aby rozwiązać ten problem (z dodatkowym imbricated 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;
Wypróbowałem podobne zapytanie na tabeli zawierającej 5 milionów wierszy i zwraca wynik w czasie krótszym niż 3 sekundy
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-12 20:10:53
Jeśli Pozostałe odpowiedzi nie są wystarczająco szybkie daj ten kod spróbuj:
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;
Wyjście:
+---------------------------+------+------------------+------------+
| 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 | ...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-08 03:20:34
Chciałem się tym podzielić, ponieważ długo szukałem łatwego sposobu na zaimplementowanie tego w programie java, nad którym pracuję. To nie daje wyjścia, którego szukasz, ale jest blisko. Funkcja w mysql o nazwie GROUP_CONCAT()
działała naprawdę dobrze do określenia, ile wyników ma być zwróconych w każdej grupie. Używanie LIMIT
lub innych wymyślnych sposobów na zrobienie tego z COUNT
nie zadziałało na mnie. Więc jeśli chcesz zaakceptować zmodyfikowane wyjście, jest to świetne rozwiązanie. Powiedzmy, że mam tabela o nazwie "student" z identyfikatorami uczniów, ich płcią i średnią ocen. Powiedzmy, że chcę top 5 GPA dla każdej płci. Następnie mogę napisać zapytanie w ten sposób
SELECT sex, SUBSTRING_INDEX(GROUP_CONCAT(cast(gpa AS char ) ORDER BY gpa desc), ',',5)
AS subcategories FROM student GROUP BY sex;
Zauważ, że parametr "5" mówi, ile wpisów należy połączyć w każdy wiersz
I wyjście wyglądałoby jak
+--------+----------------+
| Male | 4,4,4,4,3.9 |
| Female | 4,4,3.9,3.9,3.8|
+--------+----------------+
Można również zmienić zmienną ORDER BY
I zamówić je w inny sposób. Więc gdybym miał wiek ucznia, mógłbym zastąpić 'gpa desc' na 'age desc' i będzie działać! Możesz Dodaj również zmienne do instrukcji group by, aby uzyskać więcej kolumn w wyjściu. Tak więc jest to po prostu sposób, który znalazłem, że jest dość elastyczny i działa dobrze, jeśli nie masz nic przeciwko tylko wynikom aukcji.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-10 03:50:32
W Sql Server row_numer()
jest potężną funkcją, która może łatwo uzyskać wynik jak poniżej
select Person,[group],age
from
(
select * ,row_number() over(partition by [group] order by age desc) rn
from mytable
) t
where rn <= 2
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-05 14:28:39
Jest naprawdę fajna odpowiedź na ten problem w MySQL-jak uzyskać górne n wierszy dla każdej grupy
W oparciu o rozwiązanie w odnośnym linku, Twoje zapytanie będzie wyglądać następująco:
SELECT Person, Group, Age
FROM
(SELECT Person, Group, Age,
@group_rank := IF(@group = Group, @group_rank + 1, 1) AS group_rank,
@current_group := Group
FROM `your_table`
ORDER BY Group, Age DESC
) ranked
WHERE group_rank <= `n`
ORDER BY Group, Age DESC;
Gdzie n
to top n
i your_table
to nazwa Twojej tabeli.
Myślę, że wyjaśnienie w odnośniku jest naprawdę jasne. Dla szybkiego odniesienia skopiuję i wkleję go tutaj:
Obecnie MySQL nie obsługuje funkcji ROW_NUMBER (), która może przypisać Sekwencja numer w grupie, ale jako obejście możemy użyć MySQL zmienne sesji.
Te zmienne nie wymagają deklaracji i mogą być użyte w zapytaniu do wykonywania obliczeń i przechowywania wyników pośrednich.
@ current_country: = country ten kod jest wykonywany dla każdego wiersza i przechowuje wartość kolumny country do zmiennej @ current_country.
@ country_rank: = IF(@current_country = country, @country_rank + 1, 1) W tym kodzie, jeśli @current_country jest tym samym zwiększamy rangę, w przeciwnym razie Ustaw na 1. Dla pierwszego wiersza @current_country jest NULL, więc ranga jest również ustawiona na 1.
Aby uzyskać poprawny ranking, musimy uporządkować według kraju, populacji DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-28 09:04:56