postgres row_number 함수에 대해 알아봅시다.

postgres row_number 함수는 결과에 행 번호를 붙여주는 함수입니다. 행 번호는 중복되는 것이 없습니다. 따라서, rank 함수와 다르게 중복된 결과가 나오지 않습니다. 이 글에서는, 간단하게 사용법을 알아보고 실전 문제를 하나 풀어보도록 합니다.

postgres row_number 함수 사용하기

이 함수 또한, rank, dense_rank 함수와 마찬가지로, over 절이 뒤에 붙습니다.

이 쿼리는 score 테이블의 모든 내용을 뽑습니다. 그리고, row_number()도 같이 뽑습니다. over 절에 아무것도 붙지 않았기 때문에, 결과가 뽑히는 순서대로 1, 2, 3, 4, 5번이 붙여지게 됩니다.

실행 결과는 위와 같습니다. 뒤에 r을 봅시다. 정렬 기준이 정해지지 않았기 때문에, 그냥 아무렇게나 번호가 붙여졌음을 볼 수 있어요. 이 함수는 그냥, 행 번호를 붙이는 함수이기 때문에, 이 형태로 쓰는 경우도 적지 않습니다.

over 절 뒤에는 partition by랑, order by도 붙을 수 있습니다. 이 둘의 역할은 rank 함수와 동일합니다.

이 쿼리를 보겠습니다. over 절 뒤에 뭐가 붙어있나요? order by가 붙어 있어요. name을 기준으로 오름차순 정렬을 한 결과에, row_number를 붙이겠다는 의미입니다. 이것도 결과를 볼까요?

실행 결과를 봅시다. 그러면, cho, cho2, cho3, ga, hui 순으로 결과가 나옴을 볼 수 있습니다. 그리고, 이 순서대로 행 번호가 붙여졌음을 알 수 있습니다. 이제, partition by를 앞에 붙여보겠습니다.

over 뒤에 무엇이 붙었나요?

partition by depart
order by name

이렇게 2개가 붙었어요. depart, 부서를 기준으로 나누고, 이 나눈 그룹들에 대해서, name 오름차순으로 행 번호를 매기라는 의미에요.

결과는 위와 같습니다. 보면, depart a로 묶인 그룹 ga, hui에 대해서 행 번호가 1, 2가 매겨졌음을 알 수 있습니다. 그리고, depart b로 묶인 그룹 cho, cho2, cho3에 대해, 행 번호가 1, 2, 3이 매겨졌습니다. 사용 방법은 대강 아셨으리라 생각합니다.

예제 문제 하나 뽀개기

그러면, sql 코딩테스트에 나올 만한 어려운 문제를 보도록 하겠습니다.

score 테이블에 있는 모든 사람들이 획득한 점수를 뽑아주세요.
- 30점 이상인 응시자들에 대해, score 내림차순으로 행 번호를 매겨주세요.
- 30점 미만인 사람에 대해서, 행 번호는 ‘-‘으로 매겨주세요.

이런. 쉽지 않아 보입니다. 문제 상황을 보면, 모든 사람에 대해 결과를 뽑는데, 의미 있는 결과는 따로 있는 셈이지요. 집계를 하는데, condition을 만족하는 것은 랭크를 매기고, 그렇지 않으면 빈 결과로 두라는 문제는 의외로 자주 보이는 문제입니다. 이런 문제. 어떻게 풀어야 할까요? left outer join 냄새가 납니다. 왜?

왼쪽 테이블은 score 테이블에 있는 유저 전체의 데이터입니다. 오른쪽은 이 중, condition 조건으로, 30점 이상인 사람만 가져온 것입니다. 유저 전체의 데이터를 가져오고 싶어요. inner join을 하면 군청색 부분이 보존이 안 됩니다. outer join을 하면 보존이 되겠네요?

어떻게 보존이 될까요? 5와 3은 left outer join을 하면, 우측 테이블에 없었기 때문에, 오른쪽 table에 나오는 내용들은 null로 되게 됩니다. 그러면 여기까지 코드를 작성해 볼게요. 우측 테이블은 어떻게 쿼리를 가져오면 될까요?

그냥 where절 하나 추가해 주면 됩니다. 좌측 테이블은 score에서 전체를 가져오는 것이고요. 이제 이 둘을 left outer join으로 합쳐 줍시다. 그리고 우측에 나타난 null 값은 coalesce 함수로 돌려주면 됩니다. 당연하게도, 이 함수는 아래 글에 있으니, 간단하게 보고 오셔도 좋겠습니다.