[PostgreSQL] ROW_NUMBER() OVER PARTITION BY를 이용한 다중컬럼 그룹핑 조회

[PostgreSQL] ROW_NUMBER() OVER PARTITION BY를 이용한 다중 칼럼 그룹핑 조회

 

쿼리문은 알면 알수록 오묘한 것.

다양한 형태로 내가 원하는대로 데이터를 뽑기 위해서는 마찬가지로 다양한 스킬들을 알고 있어야 한다.

특히 이 row_number() 함수를 이용한 방법은 두고두고 도움이 된다! 

개발자는 쿼리를 짤 때 퍼포먼스를 고려한, 성능이 저하되지 않도록 옵티마이징 한다. 혹은 데이터를 분산배치, 마트 테이블을 구성하기도 한다.

마케터는 데이터를 뽑아내기만 하면 된다. 그래서 이런 쿼리를 공부하는게 개발자뿐 아니라 sql을 사용할 마케터에게도 도움이 될 수 있다.

일단 샘플테이블을 만들어 데이터를 넣어준다.

 

/* 테이블 생성 */
create table sample(
	id varchar not null,
	name varchar not null,
	age int not null,
	university varchar
)

/* 샘플데이터 삽입 */
insert into sample values 
('1000','밍구1','20','서울대'),
('2000','밍구2','25','고려대'),
('1000','밍구3','25','연세대'),
('3000','밍구4','28','서울대'),
('2000','밍구5','27','연세대'),
('2000','밍구6','30','중앙대'),
('5000','밍구7','30','고려대'),
('4000','밍구8','30','서울대')
;

 

 

자 여기서 id가 중복되는 경우도 있고 age나 university 칼럼이 중복되는 경우도 있다.

내가 중복되지 않은 특정컬럼을 뽑아내고자 한다면

동일한 컬럼을 묶어내는 group by와 중복을 제거해서 unique 한 값만을 나타내는 select절의 distinct 조건을 사용하는 방법이 있을 것이다.

distinct 와 group by의 사용

그러나 이 방법엔 치명적인 단점이 있는데, 내가 중복 제거한 칼럼 이외의 데이터는 볼 수가 없다는 것이다.

group by 절을 아래와 같이 사용한다면 SQL Error 가 발생한다.

 

이럴 때 사용하는 방법이 바로 ROW_NUMBER() 함수이다.

이 함수는 내가 보고 싶은 순서대로 정렬을 하고 ROW에 대해 순서를 매기는 기능을 한다.

select *, row_number() over (order by age) from sample;

age를 내림차순 기준으로 row number를 매겼다.

그럼 단순히 결과를 칼럼 순으로 정렬하는 order by 와 무슨 차이가 있느냐 할 텐데

여기에 PARTITION BY 절을 추가하면 된다. 

select *, row_number() over (PARTITION by university order by age desc) from sample;

동일한 university 컬럼 , age의 내림차순 기준으로 row number를 매겼다.

 

결론적으로 내가 여기서 row_number 칼럼이 1인 녀석들만 가지고 오면 같은 university의 1명만 가져올 수 있다.

select *
from (
	select *, row_number() over (PARTITION by university order by age desc) as rnum from sample
	) t
where rnum = 1;

 

<참고>

https://www.postgresqltutorial.com/postgresql-row_number/