[mysql] 각 그룹화 된 SQL 결과 그룹에 대해 최대 값의 레코드를 가져옵니다.

Program / 글쓴이

각 그룹화 된 집합의 최대 값을 포함하는 행을 어떻게 얻습니까?

이 질문에 대해 지나치게 복잡한 변형을 보았지만 좋은 대답은 없습니다. 가장 간단한 예를 모아 보았습니다.

아래 표에 개인, 그룹 및 연령 열이있는 경우 각 그룹에서 가장 나이가 많은 사람을 어떻게 얻습니까? (그룹 내 동점은 첫 번째 알파벳 결과를 제공해야합니다)

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

원하는 결과 집합 : 

Shawn | 1     | 42
Laura | 2     | 39

답변

mysql 에서이 작업을 수행하는 매우 간단한 방법이 있습니다.

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

mysql에서는 그룹화되지 않은 열을 집계 할 수 없으므로 mysql은 첫 번째 행을 반환하기 때문에 작동 합니다. 해결책은 각 그룹에 대해 원하는 행이 먼저 오도록 데이터를 먼저 정렬 한 다음 값을 원하는 열로 그룹화하는 것입니다.

복잡한 하위 쿼리는 피하려고합니다 max()등 와 최대 값이 같은 행이 여러 개있을 때 여러 행을 반환하는 문제를 피하십시오 (다른 답변과 마찬가지로)

참고 : 이것은 mysql 전용 솔루션입니다. 내가 아는 다른 모든 데이터베이스는 “집계 열에 그룹되지 않은 열이 나열되지 않습니다”라는 메시지와 함께 SQL 구문 오류가 발생합니다. 이 솔루션을 사용하기 때문에 문서화되지 않은 동작을이 것을, 더 많은주의가 주장 테스트를 포함 할 수 있습니다 남아 의 MySQL의 미래 버전이 동작을 변경해야합니다 작업.

버전 5.7 업데이트 :

버전 5.7에서, 이후 sql-mode설정이 포함 ONLY_FULL_GROUP_BY기본적으로, 그래서이 작업을 수행해야 만들 수 없습니다 (이 설정을 제거하는 편집 서버의 옵션 파일)이 옵션을 사용할 수 있습니다.

답변

올바른 해결책은 다음과 같습니다.

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

작동 방식 :

열의 값이 같고 열의 값이 더 큰 o모든 행과 각 행을 일치 시킵니다. 열에 그룹의 최대 값이없는 행은의 행 하나 이상과 일치 합니다.bGroupAgeoAgeb

는 LEFT JOIN그것의 전체 행과 (자신의 그룹에 혼자있는 사람을 포함) 그룹에서 가장 오래된 사람과 일치한다 NULL에서의 b( ‘그룹에 더 큰 시대’).
를 사용 INNER JOIN하면 이러한 행이 일치하지 않고 무시됩니다.

이 WHERE절 NULL은에서 추출 된 필드에 s가있는 행만 유지합니다 b. 그들은 각 그룹에서 가장 오래된 사람들입니다.

추가 자료

이 솔루션과 다른 많은 것들이 책에 설명되어 있습니다. SQL Antipatterns : 데이터베이스 프로그래밍의 함정 피하기

답변

MAX(Group)및 을 가져 오는 하위 쿼리에 대해 조인 할 수 있습니다 Age. 이 방법은 대부분의 RDBMS에서 이식 가능합니다.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT `Group`, MAX(Age) AS max_age
    FROM yourTable
    GROUP BY `Group`
) t2
    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;

답변

SQLite (그리고 아마도 MySQL)에 대한 나의 간단한 해결책 :

SELECT *, MAX(age) FROM mytable GROUP BY `Group`;

그러나 PostgreSQL 및 다른 플랫폼에서는 작동하지 않습니다.

PostgreSQL에서는 DISTINCT ON 절을 사용할 수 있습니다 .

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;

답변

순위 방법을 사용합니다.

SELECT @rn :=  CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,
   @prev_grp :=groupa,
   person,age,groupa
FROM   users,(SELECT @rn := 0) r
HAVING rn=1
ORDER  BY groupa,age DESC,person

답변

MySQL에 row_number 함수가 있는지 확실하지 않습니다. 그렇다면 원하는 결과를 얻는 데 사용할 수 있습니다. SQL Server에서 다음과 유사한 작업을 수행 할 수 있습니다.

CREATE TABLE p
(
 person NVARCHAR(10),
 gp INT,
 age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
         FROM   p
        ) t
WHERE   t.row = 1;

답변

axiac의 솔루션은 결국 저에게 가장 효과적이었습니다. 그러나 두 가지 열에서 파생 된 계산 된 “최대 값”이라는 추가 복잡성이있었습니다.

같은 예를 사용하겠습니다. 각 그룹에서 가장 나이 많은 사람을 원합니다. 똑같이 나이가 많은 사람들이 있다면 가장 큰 사람을 데려가십시오.

이 동작을 얻으려면 왼쪽 조인을 두 번 수행해야했습니다.

SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height
WHERE o2.Height is NULL;

도움이 되었기를 바랍니다! 그래도 더 좋은 방법이 있어야한다고 생각합니다 …