[MSSQL] ROW_NUMBER에 대한 고찰

소개

6개월차 신입 데이터 분석가입니다. SQL에 대해 조금 더 깊게 이해하고자 공부를 시작합니다. 

 

www.yes24.com/Product/Goods/64391533

불친절한 SQL 프로그래밍

본 시리즈는 기본적으로 해당 도서를 참조하여 작성되었습니다.

아래와 관련된 키워드로 검색해보시면 환경세팅 관련 많은 도움을 받으실 수 있습니다.
- oracle sql developer scott hr
- oracle sql developer 설치
11g 설치는 https://data-make.tistory.com/2 해당 링크가 설명이 잘되어 있습니다.

환경은 Oracle SQL Developer에서 제공해주는 hr과 scott DB를 사용합니다. 해당 DB에서 기본적으로 제공해주는 테이블이 있고 제가 직접 테이블을 업로드하거나 생성할 수도 있습니다. 

 

gibles-deepmind.tistory.com/5

 

SQL 관련 포스팅 링크를 모아둔 아카이브 페이지가 있습니다. 

본 시리즈의 기본적인 문법은 Oracle SQL입니다. 현업에서 몇개월 간 종사해본 입장에서 DB별 SQL 문법 차이는 그렇게 크지 않습니다. 문법보다는 원리에 집중하시면 좋을 것 같습니다. 

글의 목표

1. 번호라는 단어에 대해서 생각해봅니다 
2. ROW_NUMBER 함수의 구조에 대해서 생각해봅니다.
3. ROW_NUMBER가 활용 될 수 있는 실제 사례에 대해 생각해봅니다.

---

1. 왜 번호를 부여할까?

그대의 이름도 성도
난 필요없소
하지만 정말 나
원하는 게 하나 있소
네 전화번호
- 지누션 전화번호 

번호?라고 하니 갑자기 옛날 노래가 생각나서 들고 왔다.(???)

https://gibles-deepmind.tistory.com/6?category=891999

[Oracle SQL] 직접 만들어보며 배우는 SQL 기본키(Primary Key) VS 외래키(Foreign Key)

이전 글에서 "기본키"와 "외래키"에 대해서 다루었었다. 뜻은 다르지만, 키를 만드는 목적을 "식별"이라고 했었는데 번호도 마찬가이지다. 

 

저~ 멀리 까마득한 학창시절을 생각해보자. 우리는 초등학교, 중학교, 고등학교, 대학교를 거쳐오며 각 단계마다 "번호"를 부여받는다. 초등학교때는 15번, 중학교때는 20번, 대학교때는 20120XXXX이런 식으로 말이다.

 

학번의 의미는 대학생이 되면서 더 크게 와닿는데 내 학번을 입력하고 그 학번으로 성적을 확인할 수 있기 때문이다. 어쨋든, 번호를 부여하는 행위에는 "식별"을 하겠다는 의도가 담겨있다. 

http://www.footballist.co.kr/news/articleView.html?idxno=127461

손흥민, 한국인 최초 빅 리그 통산 100골 대기록 - 풋볼리스트(FOOTBALLIST)

손흥민이 지금까지 기록했던 골들에도 각자의 번호가 부여되어 있다. 그리고, 그 번호를 통해 각 골을 식별하고 통산 100골 이라는 의미를 부여한다.

 

ROW_NUMBER 함수는 번호를 부여하여 특정 대상의 행위를 식별한다.

---

2. ROW_NUMBER 함수의 구조

SELECT ....
	 , ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) AS ...
FROM TABLE

ROW_NUMBER 함수는 위와 같은 구조로 사용한다. 한번, 코드를 뜯어보자.

1. ROW_NUMBER()
-> 큰 의미는 없다. 형식적인 부분이다. 
2. ROW_NUMBER() OVER(PARTITION BY .......)
-> 굉장히 중요한 부분이다. 잠깐, 아래에서 상세히 다루고 넘어가자.

구글: 파티션 검색

흔히, 파티션이라고 하면 위 이미지와 같이 공간과 공간을 나누는 도구를 말한다. ROW_NUMBER에서 파티션도 이와 비슷한 의미를 가지는데 예시로 다음의 테이블을 생각해보자.

유저 번호	구매 품목	구매 금액	구매 일시
1	컴퓨터	1,300,000	2025-11-02 11:00:12
1	컴퓨터	1,500,000	2021-12-05 15:00:12
1	마우스	10,000	2019-03-02 17:50:12
2	노트북	1,000,000	2030-01-09 12:00:12
2	노트북	1,100,000	2028-11-02 18:00:12
2	키보드	20,000	2029-11-02 20:00:12

ROW_NUMBER() OVER(PARTITION BY 유저 번호)

근데, 여기서 잠깐 유저 번호에 따라 분할을 하기는 하는데 뭐를 기준으로 번호를 부여하지?? 그때 등장하는 것이 바로!!

ROW_NUMBER() OVER(PARTITION BY 유저 번호 ORDER BY ... )

ORDER BY 절이다. 즉, ORDER BY 다음에 나오는 컬럼을 기준으로 유저를 분할하라는 의미가 된다. 이러한 원칙에 따라 문법을 완성시키면

ROW_NUMBER() OVER(PARTITION BY 유저 번호 ORDER BY 구매 일시) AS ROW_NUMS

유저 번호	구매 품목	구매 금액	구매 일시	ROW_NUMS
1	컴퓨터	1,300,000	2025-11-02 11:00:12	3
1	컴퓨터	1,500,000	2021-12-05 15:00:12	2
1	마우스	10,000	2019-03-02 17:50:12	1
2	노트북	1,000,000	2030-01-09 12:00:12	3
2	노트북	1,100,000	2028-11-02 18:00:12	1
2	키보드	20,000	2029-11-02 20:00:12	2

위와 같이 유저를 분할하여 구매 일시에 따라 번호를 부여할 수 있다!

3. ROW_NUMBER의 실제 활용 의의

ROW_NUMBER 함수는 실무에서 정말 유용하다. 예를 들어, 해당 테이블에서 고객의 최초 구매일을 구한다고 생각해보자. 만약에 ROW_NUMS 행이 없다면? 앞길이 깜깜할 것이다. 하지만, ROW_NUMS 행이 있다면?

 

SELECT *
FROM (
  SELECT *
       , ROW_NUMBER() OVER(PARTITION BY 유저 번호 ORDER BY 구매 일시) AS ROW_NUMS
  FROM TABLE
  ) A
 WHERE ROW_NUMS = 1

위와 같이 서브쿼리를 이용해 ROW_NUMS = 1 이라는 간단한 조건을 통해 고객의 최초 구매일을 구할 수 있다. 

 

그렇다면, 반대로 고객의 마지막 구매일을 구한다면?

SELECT *
FROM (
  SELECT *
       , ROW_NUMBER() OVER(PARTITION BY 유저 번호 ORDER BY 구매 일시 DESC) AS ROW_NUMS
  FROM TABLE
  ) A
 WHERE ROW_NUMS = 1

구매 일시에 DESC만 추가해주면 된다! 즉, 값이 내림차순으로 정렬되기 때문에 가장 마지막에 구매한 일시에 1번이 부여된다.