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

programing

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

itsource 2022. 9. 24. 23:33

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

그룹화된 각 세트의 최대값을 포함하는 행을 얻으려면 어떻게 해야 합니까?

나는 이 질문에 대해 지나치게 복잡한 변형을 보았지만, 좋은 답변은 하나도 없었다.가능한 한 간단한 예를 들어 보겠습니다.

아래 표에 인물, 그룹 및 연령 열이 있는 경우 각 그룹에서 가장 나이가 많은 사람을 어떻게 찾을 수 있습니까?(그룹 내 동점일 경우 첫 번째 알파벳 순으로 결과를 제시해야 함)

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

올바른 솔루션은 다음과 같습니다.

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의 이 포함되어 있습니다.b컬럼에 Group 좋습니다.Age의 행.o에 .Age는 1개의 이상의 행은 이상입니다.b.

LEFT JOIN그룹 내에서 가 많은 내에서 있는 과이 가득 찬 사람(그룹 내에서 혼자 있는 포함)을 시킵니다.NULL from 서 sb('아주머니').
「」를 사용합니다.INNER JOIN는 이러한 행을 일치하지 않게 하여 무시합니다.

WHERE은 행만 NULL에서 s.b그들은 각 그룹에서 가장 나이가 많은 사람들이다.

추가 판독치

이 솔루션과 기타 많은 솔루션에 대해서는 SQL Antiatterns라는 책에서 설명합니다. 데이터베이스 프로그래밍의 함정 회피

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

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

이는 mysql에서는 그룹별 컬럼이 아닌 컬럼을 집약할 수 없기 때문에 동작합니다.이 경우 mysql은 첫 번째 행만 반환합니다.해결책은 먼저 각 그룹에 대해 원하는 행이 먼저 되도록 데이터를 정렬한 다음 값을 원하는 열을 기준으로 그룹화하는 것입니다.

문제를 복잡한 서브쿼리는 .max() 같은 복수의 행이 경우에 되는 문제도 .etc （ 다 etc다른 ）。

주의: 이것은 mysql 전용 솔루션입니다.내가 알고 있는 다른 모든 데이터베이스는 SQL 구문 오류를 발생시키고 "집약되지 않은 열은 group by 절에 나열되지 않습니다" 또는 이와 유사한 메시지를 표시합니다.이 솔루션은 문서화되어 있지 않은 동작을 사용하기 때문에 MySQL의 향후 버전에서 이 동작이 변경되어도 동작하고 있음을 나타내는 테스트를 포함할 수 있습니다.

버전 5.7 업데이트:

버전 5.7 이후 설정에는 기본적으로 포함되어 있으므로 이 작업을 수행하려면 이 옵션을 사용할 수 없습니다(서버용 옵션파일을 편집하여 이 설정을 삭제합니다).

서브쿼리에 반대하여 가입할 수 있습니다.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`;

하지만 Postgre에서는 작동하지 않습니다.SQL 및 기타 플랫폼도 있습니다.

포스트그레SQL DISTING ON 절을 사용할 수 있습니다.

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

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;

순위 매기기.

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

이 SQL은 다음과 같이 설명할 수 있습니다.

사용자 중에서 *를 선택하고(@rn := 0 선택) 그룹별, 연령 설명, 사람별 순서
@syslog_grp는 늘입니다.
@rn : = case (@case_grp <> groupa 후 1 ELSE @rn+1 종료시)

삼연산자 입니다.
와 같이=1 != else rn+1 groupa else rn = 1 if prev_grp != groupa else rn=rn+1
rn=1이 필요한 행을 필터링하도록 합니다.

그룹당 여러 행을 선택하지 않고 인덱스를 사용할 수 있도록 axiac 솔루션 개선

SELECT o.*
FROM `Persons` o 
  LEFT JOIN `Persons` b 
      ON o.Group = b.Group AND o.Age < b.Age
  LEFT JOIN `Persons` c 
      ON o.Group = c.Group AND o.Age = c.Age and o.id < c.id
WHERE b.Age is NULL and c.id is null

Group은 예약어이기 때문에 컬럼 이름으로 사용하지 않을 것입니다.그러나 다음 SQL이 작동합니다.

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN 
(
  SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] 
  GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest

악시아크의 해법이 결국 나에게 가장 효과적이었습니다.그러나 복잡성이 하나 더 있었습니다. 두 개의 열에서 도출된 계산된 "최대값"입니다.

같은 예를 사용합니다.각 그룹에서 가장 나이가 많은 사람이었으면 좋겠어요.나이가 같은 사람이 있으면 가장 키가 큰 사람을 데려가요.

이 동작을 수행하려면 왼쪽 조인을 두 번 수행해야 합니다.

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;

이게 도움이 됐으면 좋겠네요!더 좋은 방법이 있을 것 같은데...

내 솔루션은 하나의 열만 검색할 필요가 있는 경우에만 작동하지만, 성능 면에서 가장 적합한 솔루션(단일 쿼리 사용)이었습니다.

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;

GROUP_CONCAT를 사용하여 정렬된 콩캣 목록을 만들고 첫 번째 콩캣에만 하위 문자열을 지정합니다.

CTE 사용 - 일반 테이블 표현:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

시험해 볼 수도 있습니다.

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

이것이 mysql에서 그룹당 N개의 최대 행을 가져오는 방법입니다.

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

기능:

스스로 테이블에 앉다
그룹은 에 의해 행해집니다.co.country = ci.country
그룹당 N개의 요소는) < 1따라서 3가지 요소에 대해 - ) < 3
최대값 또는 최소값을 얻는 방법은 다음과 같습니다.co.id < ci.id
- co.id < ci.id - 최대
- co.id > ci.id - min

완전한 예는 다음과 같습니다.

mysql 그룹당 최대값 n개 선택

Oracle에서는 아래 쿼리에서 원하는 결과를 얻을 수 있습니다.

SELECT group,person,Age,
  ROWNUMBER() OVER (PARTITION BY group ORDER BY age desc ,person asc) as rankForEachGroup
  FROM tablename where rankForEachGroup=1

with CTE as 
(select Person, 
[Group], Age, RN= Row_Number() 
over(partition by [Group] 
order by Age desc) 
from yourtable)`


`select Person, Age from CTE where RN = 1`

이 방법을 사용하면 다른 열을 기준으로 순위를 매길 수 있고 다른 데이터를 파기할 수 없습니다.가장 무거운 항목부터 나열하여 항목 열과 함께 주문을 나열하려는 경우에 매우 유용합니다.

출처 : http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT person, group,
    GROUP_CONCAT(
        DISTINCT age
        ORDER BY age DESC SEPARATOR ', follow up: '
    )
FROM sql_table
GROUP BY group;

테이블명을 사람으로 하다

select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and 
O.Age = 
(select max(T.age) from people T where O.grp = T.grp
  group by T.grp)
group by O.grp;

mytable의 ID(및 모든 coolmns)가 필요한 경우

SELECT
    *
FROM
    mytable
WHERE
    id NOT IN (
        SELECT
            A.id
        FROM
            mytable AS A
        JOIN mytable AS B ON A. GROUP = B. GROUP
        AND A.age < B.age
    )

SELECT o.*
FROM `Persons` o                   
  LEFT JOIN `Persons` b            
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL  
group by o.Group

언급URL : https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results

저작자표시

'programing' 카테고리의 다른 글

AngularJS : $observe 메서드와 $watch 메서드의 차이 (0)	2022.09.24
addEventListener의 useCapture 매개 변수를 이해할 수 없습니다. (0)	2022.09.24
Twig에 어레이의 특정 키가 있는지 확인합니다. (0)	2022.09.24
Galera MySQL의 추가 노드 추가 실패 (0)	2022.09.21
Tymeleaf: 조건을 사용하여 CSS 클래스를 동적으로 추가/삭제하는 방법 (0)	2022.09.21

현재글그룹화된 SQL 결과의 각 그룹에 대해 최대값을 가진 레코드를 가져옵니다.

각종 프로그래밍 정보를 다루는 블로그입니다.

jQuery, Spring3, vuejs2, Spring, Java, C, C++, c#, JavaScript, vuex,

Today :
Yesterday :

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

itsource

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

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

구조:

추가 판독치

버전 5.7 업데이트:

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

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

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

구조:

추가 판독치

버전 5.7 업데이트:

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바