복붙노트

[SQL] 분류 결과 각 그룹에 대해 상위 N 레코드를 가져 오기

SQL

분류 결과 각 그룹에 대해 상위 N 레코드를 가져 오기

다음은 모든 솔루션 그러나 많은 n 개의 최고 결과를 필요로 확장 할 수 있어야하지만, 가장 간단한 예입니다 :

사람, 그룹, 나이 열이 아래 같은 테이블을 감안할 때, 당신은 어떻게 각 그룹의이 오래된 사람을 얻을 것? (그룹 내의 관계는 더 많은 결과를 생성하지만, 알파벳 순서로 제 2주지한다)

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

원하는 결과 집합 :

+--------+-------+-----+
| Shawn  | 1     | 42  |
| Jill   | 1     | 34  |
| Laura  | 2     | 39  |
| Paul   | 2     | 36  |
+--------+-------+-----+

참고 :이 질문은 그룹화 SQL 결과 각 그룹에 대한 최대 값을 이전 원 - 가져 오기 기록을 기반으로 - 각 그룹에서 하나의 상위 행을 얻기를위한, 그리고 어떤이 @Bohemian에서 큰 MySQL의 특정 응답을받은 :

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

내가 표시되지 않습니다하지만,이를 구축 할 수 싶어요.

해결법

  1. ==============================

    1.여기에 UNION ALL (데모와 참조 SQL 바이올린)를 사용하여이 작업을 수행 할 수있는 한 가지 방법입니다. 이것은 당신이 두 개 이상의 그룹이있는 경우, 당신은 그룹 번호를 지정하고 각 그룹에 대해 쿼리를 추가해야합니다, 두 그룹으로 작동합니다 :

    여기에 UNION ALL (데모와 참조 SQL 바이올린)를 사용하여이 작업을 수행 할 수있는 한 가지 방법입니다. 이것은 당신이 두 개 이상의 그룹이있는 경우, 당신은 그룹 번호를 지정하고 각 그룹에 대해 쿼리를 추가해야합니다, 두 그룹으로 작동합니다 :

    (
  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;

    참조 데모

  2. ==============================

    2.다른 데이터베이스에서는 ROW_NUMBER를 사용하여이 작업을 수행 할 수 있습니다. MySQL은 ROW_NUMBER를 지원하지 않습니다하지만 당신은 그것을 모방 변수를 사용할 수 있습니다 :

    다른 데이터베이스에서는 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이 매우 비슷한 대답을 게시 것으로 나타났습니다. 그러나이 대답은 두 개의 작은 장점이 있습니다 :

    내가 경우 여기를 떠날거야 그래서 누군가를 도울 수 있습니다.

  3. ==============================

    3.이 시도:

    이 시도:

    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

    데모

  4. ==============================

    4.어떻게 자기 결합을 사용하는 방법에 대한 :

    어떻게 자기 결합을 사용하는 방법에 대한 :

    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 개의 레코드를 선택하는 빌 Karwin의 대답에 의해 영감을했다

    또한, 나는 SQLite는 사용하고 있지만, 이것은 MySQL을 작동합니다.

    또 다른 한가지는 : 위에서, 나는 편의를 위해 그룹 이름 컬럼이 그룹 컬럼을 교체했다.

    편집하다:

    다음 업이 누락 넥타이 결과에 관한 OP의 의견에, 나는 모든 관계를 보여 snuffin의 대답에 증가합니다. 마지막 사람이 관계하는 경우 다음과 같이 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
  5. ==============================

    5.이것 좀 봐:

    이것 좀 봐:

    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 바이올린 : http://sqlfiddle.com/#!2/cdbb6/15

  6. ==============================

    6.Snuffin 솔루션은 순서가 선택의 실행 후 적용함으로써, 그래서 여기에 변형 내 ENVIRONNEMENT (MySQL은 5.6)에서 작동하지 않기 때문에 당신이 행과 마크 바이어스 / 릭 제임스와 Bluefeet 솔루션을 많이 가지고 때 실행하는 것은 매우 느린 마크 바이어스의 / 릭 제임스 솔루션 (추가 선택 겹침으로)이 문제를 해결하려면 :

    Snuffin 솔루션은 순서가 선택의 실행 후 적용함으로써, 그래서 여기에 변형 내 ENVIRONNEMENT (MySQL은 5.6)에서 작동하지 않기 때문에 당신이 행과 마크 바이어스 / 릭 제임스와 Bluefeet 솔루션을 많이 가지고 때 실행하는 것은 매우 느린 마크 바이어스의 / 릭 제임스 솔루션 (추가 선택 겹침으로)이 문제를 해결하려면 :

    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 초 미만에 결과를 반환

  7. ==============================

    7.다른 답변이없는 경우 충분히 빨리이 코드를 사용 해보세요 :

    다른 답변이없는 경우 충분히 빨리이 코드를 사용 해보세요 :

    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 | ...
  8. ==============================

    8.내가 일하고 있어요 자바 프로그램에서이를 구현하는 쉬운 방법을 찾고 오랜 시간을 보냈다 때문에 나는이 공유하고 싶었다. 이것은 확실히 당신이 찾고있는 출력 만의 긴밀한를 제공하지 않습니다. MySQL의의 기능은 GROUP_CONCAT ()가 각 그룹에 반환하는 방법을 많은 결과를 지정하기위한 정말 잘 작동했다. LIMIT 또는 나를 위해 작동하지 않았다 COUNT와 함께이 일을하려고의 다른 멋진 방법 중 하나를 사용. 그래서 당신은 수정 된 출력의 훌륭한 솔루션을 받아 들일 있다면. 내가 학생 IDS, 자신의 성별, 성적과 '학생'라는 테이블이 있다고 할 수 있습니다. 나는 각 성 5 GPA들을 위로하고 싶은 말은 수 있습니다. 그리고이 같은 쿼리를 작성할 수 있습니다

    내가 일하고 있어요 자바 프로그램에서이를 구현하는 쉬운 방법을 찾고 오랜 시간을 보냈다 때문에 나는이 공유하고 싶었다. 이것은 확실히 당신이 찾고있는 출력 만의 긴밀한를 제공하지 않습니다. MySQL의의 기능은 GROUP_CONCAT ()가 각 그룹에 반환하는 방법을 많은 결과를 지정하기위한 정말 잘 작동했다. LIMIT 또는 나를 위해 작동하지 않았다 COUNT와 함께이 일을하려고의 다른 멋진 방법 중 하나를 사용. 그래서 당신은 수정 된 출력의 훌륭한 솔루션을 받아 들일 있다면. 내가 학생 IDS, 자신의 성별, 성적과 '학생'라는 테이블이 있다고 할 수 있습니다. 나는 각 성 5 GPA들을 위로하고 싶은 말은 수 있습니다. 그리고이 같은 쿼리를 작성할 수 있습니다

    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|
+--------+----------------+

    또한 변수에 의해 순서를 변경하고 그들에게 다른 방법을 주문할 수 있습니다. 나는 학생의 나이가 있다면 그래서 'GPA DESC'와 '나이 내림차순'를 대체 할 수 있으며 작동합니다! 또한 출력에 더 많은 열을 얻기 위해 문 그룹에 변수를 추가 할 수 있습니다. 이 꽤 유연하고 그냥 목록 결과를 확인하면 잘 작동 내가 찾은 단지 방법입니다 그래서.

  9. ==============================

    9.SQL 서버 ROW_NUMBER ()에서 아래와 같이 쉽게 결과를 얻을 수있는 강력한 기능입니다

    SQL 서버 ROW_NUMBER ()에서 아래와 같이 쉽게 결과를 얻을 수있는 강력한 기능입니다

    select Person,[group],age
from
(
select * ,row_number() over(partition by [group] order by age desc) rn
from mytable
) t
where rn <= 2
  10. ==============================

    10.MySQL의에서이 문제에 정말 좋은 해답이 있습니다 - 각 그룹 별 상위 N 행을 얻는 방법

    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은 n은 정상이며 your_table은 테이블의 이름입니다.

    나는 기준의 설명은 정말 분명하다 생각합니다. 빠른 참조를 위해 나는 복사하여 여기에 붙여 넣습니다 :

    11. from https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 나무에 평평한 테이블을 구문 분석하는 가장 효율적인 / 우아한 방법은 무엇입니까?  (0) 2020.03.05
[SQL] INNER가, 왼쪽, 오른쪽 조인 및 FULL은 조인의 차이점은 무엇입니까? [복제]  (0) 2020.03.05
[SQL] 명시 대 암시 SQL 조인  (0) 2020.03.05
[SQL] PostgreSQL의 크로스 탭 쿼리  (0) 2020.03.05
[SQL] INNER는 WHERE 절 대 ON 가입  (0) 2020.03.05
,

티스토리툴바