[빅쿼리 - SQL]OVER 절과 ORDER BY로 순서 정의하기(row_number,rank,dense_rank, lag,lead 함수 사용)

샘플 데이터

↓↓↓

결과값

---

샘플데이터를 아래 결과값처럼 나타내기 위해선 어떤 함수를 어떻게 사용해야 할까?

결과값을 자세히 보면 크게 4 부분으로 나눌 수 있다.

결과 구분

이번 글에서는 위 4부분을 구현하는 방법을 알아보겠다.

 

1. 기존 테이블의 열들(설명x)

2. 순위를 메기는 열들

• row_number 열 : 점수 순위로 유일한 순위를 붙임
  • ROW_NUMBER() OVER(ORDER BY score DESC)
• rank 열 : 같은 순위가 있을 때 같은 순위 다음 순위를 건너뛰고 순위를 붙임
  • RANK() OVER(ORDER BY score DESC)
• dense_rank열 : 같은 순위를 허용해서 순위를 붙임 
  • dense_rank() OVER(ORDER BY score DESC)

 

row_number, rank, dense_rank의 기능은 한눈에 알겠는데, OVER(ORDER BY score DESC)는 무엇인지 헷갈릴 것이다.

over의 역할은 이전 글 2022.04.29 - [SQL] - 빅쿼리 - OVER 절과 PARTITION BY

빅쿼리 - OVER 절과 PARTITION BY

에서 다루었으니 이전 글에서 참고하기로 하고, over 안의 order by는 테이블 내부의 순서를 다루는 쿼리이다.

'OVER(ORDER BY score DESC')를 통해 테이블 내부의 상품을 스코어가 높은 순서로 정렬할 수 있다.

내부  상황

row_number, rank, dense_rank 함수는 이 내부 순서에 각자의 방식으로 순서를 메기는 것이다.

ROW_NUMBER,RANK,DENSE_RANK

3. 현재 행 보다 앞에 있는 행의 값 추출하기

• LAG(열 이름, N(번째 앞 값))OVER()

LAG 함수란 현재 행보다 앞을 행의 값을 추출하는 함수이다.

이 역시 OVER가 필요하다. 내부의 순서 정렬이 우선 이루어져야 사용할 수 있다.

내부 상황을 화살표로 나타내면 이해하기 쉽다.

내부 상황

4. 현재 행 보다 뒤에 있는 행의 값 추출하기

• LEAD(열 이름, N(번째 앞 값))OVER()

LEAD 함수란 현재 행보다 뒤의 행의 값을 추출하는 함수이다. LAG 함수의 반대라고 이해하면 쉽다.

 

 

최종 코드 및 결과