programing

그룹화된 결과의 각 그룹에 대해 상위 n개의 레코드를 가져옵니다.

kingscode 2022. 10. 30. 20:30
반응형

그룹화된 결과의 각 그룹에 대해 상위 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이라는 매우 유사한 답변을 게시한 것을 알아챘다.그러나 이 답변에는 두 가지 작은 장점이 있습니다.

  1. 이것은 단일 쿼리입니다.변수는 SELECT 문 내에서 초기화됩니다.
  2. 질문의 설명에 따라 넥타이를 처리합니다(이름별 영숫자 순서).

그래서 누군가에게 도움이 될 수 있을지 모르니까 여기 두고 갈게요.

이것을 시험해 보세요.

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

'programing'의 다른글

  • 현재글그룹화된 결과의 각 그룹에 대해 상위 n개의 레코드를 가져옵니다.

관련글

댓글

티스토리툴바