그룹화된 결과의 각 그룹에 대해 상위 n개의 레코드를 가져옵니다.
다음은 가장 간단한 예입니다.다만, 어떠한 솔루션이라도 n개의 톱 결과를 필요로 하는 만큼 확장할 수 있어야 합니다.
아래 표에 인물, 그룹 및 연령 열이 있는 경우 각 그룹에서 최고령자 2명을 어떻게 얻을 수 있습니까? (그룹 내 타이는 더 많은 결과를 산출하지 말고 알파벳 순으로 첫 번째 2명을 제시합니다.)
+--------+-------+-----+| 인물 | 그룹 | 연령 |+--------+-------+-----+| 밥 | 1 | 32 || 질 | 1 | 34 || Shawn | 1 | 42 || 제이크 | 2 | 29 || Paul | 2 | 36 || Laura | 2 | 39 |+--------+-------+-----+
원하는 결과 세트:
+--------+-------+-----+| Shawn | 1 | 42 || 질 | 1 | 34 || Laura | 2 | 39 || Paul | 2 | 36 |+--------+-------+-----+
메모: 이 질문은 이전 SQL 결과의 각 그룹에 대해 최대값을 가진 레코드를 가져옵니다.각 그룹에서 1개의 맨 위 행을 가져오고 @Bohemian에서 MySQL 고유의 훌륭한 답변을 받았습니다.
select *
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`
어떻게 된 건지 모르겠지만 이걸 쌓을 수 있었으면 좋겠어요.
하는 중 가 있어요.UNION ALL
('SQL Fidle with Demo' 참조).이것은 2개의 그룹에서 동작합니다.여러 개의 그룹이 있는 경우는,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;
데모 참조
에서는 이 을 사용할 수 .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
편집 나는 방금 파란발이 그에게 +1이라는 매우 유사한 답변을 게시한 것을 알아챘다.그러나 이 답변에는 두 가지 작은 장점이 있습니다.
- 이것은 단일 쿼리입니다.변수는 SELECT 문 내에서 초기화됩니다.
- 질문의 설명에 따라 넥타이를 처리합니다(이름별 영숫자 순서).
그래서 누군가에게 도움이 될 수 있을지 모르니까 여기 두고 갈게요.
이것을 시험해 보세요.
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
셀프 조인을 사용하는 것은 어떨까요?
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개의 레코드를 선정하는 것에 대한 Bill Karwin의 답변에 강한 영감을 받았습니다.
또한 SQLite를 사용하고 있지만 MySQL에서 작동해야 합니다.
하나에서는 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★.group
with with with with with with with withgroupname
참の참
편집:
결선 결과에 대한 OP의 코멘트에 이어서 스너핀의 답변을 늘려 모든 동점자를 표시했습니다.즉, 마지막 행이 동점일 경우 다음과 같이 3개 이상의 행을 반환할 수 있습니다.
.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
snaffin 솔루션은 행이 많고 Mark Byers/Rick James 및 Bluefeet 솔루션이 내 환경(MySQL 5.6)에서 작동하지 않는 경우 실행 속도가 매우 느립니다.선택 항목을 실행한 후 주문 기준이 적용되므로 이 문제를 해결하기 위한 Marc Byers/Rick James 솔루션의 변종이 여기에 추가 선택 항목이 있습니다.
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;
500만 행이 있는 테이블에서 유사한 쿼리를 시도했는데 3초 이내에 결과가 반환됩니다.
다른 답변이 충분히 빠르지 않은 경우 이 코드를 사용해 보십시오.
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 | ...
이것 좀 봐.
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 Fidle: http://sqlfiddle.com/ #!2/cdbb 6/15
Server의 SQL Server »row_numer()
는 다음과 같이 수 입니다.
select Person,[group],age
from
(
select * ,row_number() over(partition by [group] order by age desc) rn
from mytable
) t
where rn <= 2
작업 중인 Java 프로그램에서 쉽게 구현할 수 있는 방법을 찾느라 오랜 시간을 소비했기 때문에 공유하고자 합니다.이것은 당신이 원하는 출력을 제공하지 않지만 거의 비슷합니다.에서 mysql이라고 하는 .GROUP_CONCAT()
각 그룹에서 반환할 결과 수를 지정하는 데 매우 효과적이었습니다.「」를 사용합니다.LIMIT
다른 합니다.COUNT
해결책입니다.따라서 수정된 출력을 받아들일 의향이 있다면 훌륭한 솔루션입니다.학생 ID, 성별, 학점이 기재된 '학생'이라는 표가 있다고 칩시다.각 성별에 대해 상위 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'를 '나이 desc'로 대체할 수 있습니다. 그러면 효과가 있을 것입니다!또한 문별로 그룹에 변수를 추가하여 출력에 더 많은 열을 가져올 수도 있습니다.이 방법은 매우 유연하고 결과만 나열해도 문제없을 때 사용할 수 있다는 것을 알게 되었습니다.
MySQL - 각 그룹별 상위 N개의 행을 얻는 방법에는 이 문제에 대한 매우 좋은 답이 있습니다.
참조된 링크의 솔루션에 따라 쿼리는 다음과 같습니다.
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;
어디에n
는top n
그리고.your_table
테이블 이름입니다.
참고문헌에 있는 설명은 매우 명확하다고 생각합니다.빠른 참조를 위해 여기에 복사하여 붙여넣습니다.
현재 MySQL은 그룹 내에서 시퀀스 번호를 할당할 수 있는 ROW_NUMBER() 함수를 지원하지 않지만 회피책으로 MySQL 세션 변수를 사용할 수 있습니다.
이러한 변수는 선언이 필요하지 않으며 쿼리에서 계산을 수행하고 중간 결과를 저장하는 데 사용할 수 있습니다.
@current_country := country 이 코드는 각 행에 대해 실행되며 country column 값을 @current_country 변수에 저장합니다.
@country_rank : = IF(@current_country = country, @country_rank + 1, 1) 이 코드에서는 @current_country가 같을 경우 랭크 값을 1로 설정합니다.첫 번째 행 @current_country는 NULL이므로 랭크도 1로 설정됩니다.
올바른 순위를 매기려면 국가별 주문, 모집단 설명이 필요합니다.
WITH cte_window AS (
SELECT movie_name,director_id,release_date,
ROW_NUMBER() OVER( PARTITION BY director_id ORDER BY release_date DESC) r
FROM movies
)
SELECT * FROM cte_window WHERE r <= <n>;
위의 쿼리는 각 감독에 대해 최신 n편의 영화를 반환합니다.
SELECT
p1.Person,
p1.`GROUP`,
p1.Age
FROM
person AS p1
WHERE
(
SELECT
COUNT( DISTINCT ( p2.age ) )
FROM
person AS p2
WHERE
p2.`GROUP` = p1.`GROUP`
AND p2.Age >= p1.Age
) < 2
ORDER BY
p1.`GROUP` ASC,
p1.age DESC
언급URL : https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results
'programing' 카테고리의 다른 글
디버깅 로그 인쇄 방법 (0) | 2022.10.30 |
---|---|
여러 Python 버전과 PIP를 다루고 있습니까? (0) | 2022.10.30 |
위의 디렉토리에 있는 Python 클래스를 Import하려면 어떻게 해야 합니까? (0) | 2022.10.30 |
파이프에서 'subprocess' 명령어를 사용하는 방법 (0) | 2022.10.30 |
JavaScript 문자열 암호화 및 암호 해독 (0) | 2022.10.30 |