[mssql] ROW_NUMBER() OVER () , PARTITION BY

ROW_NUMBER 함수란?

 - 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서를 기준으로 고유한 값을 반환하는 함수입니다.

 - 윈도우 함수(Window Funtion)로 그룹 내 순위 함수입니다.

 

문법

ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])

- PARTITION BY는 선택, ORDER BY는 필수

---

공부하게 된 계기 

구조

이번 프로젝트에서 point 라는 도메인에 유저들이 포인트가 지급되면 하나씩 쌓이는 구조이며,
totalPoint에 최근에 적립된 totalPoint에서 더해져 DB에 적재가 되는 구조입니다.

@Getter
@Setter
@Entity
public class Point extends DateTime {

   /**
    * 사용자계정
    */
   @NotNull
   @ManyToOne(fetch = FetchType.LAZY, optional = false)
   private User user;

   /**
    * 적립금
    */
   @NotNull
   private Integer point;

   /**
    * 총잔액 적립금
    */
   @NotNull
   private Integer totalPoint;
}

 

DB

위에서 말 했듯이 user_id 가 1 인 회원이 500 포인트가 적립되어 totalPoint에 500포인트 쌓였고 다음에 200포인트가 지급되어서 700포인트가 되었다. 

user_id가 2인 회원도 마찬가지 입니다.

id	point	total_point	user_id
1	500	500	1
2	200	700	1
3	350	350	2
4	350	700	2

 

 

문제점

한명의 회원의 최근의 totalPoint를 조회하는 것을 어렵지 않습니다. 예를 들면 아래와 같은 쿼리가 나올 수 있겠네요

SELECT top(1) *
  FROM POINT P
WHERE p.id = :userId
ORDER BY P.id DESC

 

하지만 여러 명의 회원들을 현재 가지고 있는 포인트를 조회하는 것이 매우 어려워 결국은 팀장님께 자문을 구할 수 밖에 없었습니다. ㅠㅠ 팀장님 덕분에 row_nuber over 와 partition By 를 알게되었고 공부하여 적용 시켰습니다.

SELECT
    *
FROM
    (    SELECT
             ROW_NUMBER() OVER (PARTITION BY P.USER_ID ORDER BY P.ID DESC) AS ROW_NUM            ,
             P.USER_ID AS USER_ID           ,
             P.TOTAL_POINT
         FROM
             POINT P
    ) B
WHERE
        B.ROW_NUM = 1;

PARTITION BY 로 user_id 기준으로 나눴고 id 내림차순으로 정렬하여 ROW_NUM이 매겨집니다. 아래와 같이요

DB  데이터 적재 상황

id	point	total_point	user_id
1	500	500	1
2	200	700	1
3	350	350	2
4	350	700	2

 

outSql 제외하고 inSQL조회로 인해 조회된 데이터

SELECT
     ROW_NUMBER() OVER (PARTITION BY P.USER_ID ORDER BY P.ID DESC) AS ROW_NUM            ,
     P.USER_ID AS USER_ID           ,
     P.TOTAL_POINT
 FROM
     POINT P

ROW_NUM	id	USER_ID	TOTAL_POINT
1	2	1	700
2	1	1	500
1	4	2	350
2	3	2	350

 

전체쿼리조회

SELECT
    *
FROM
    (    SELECT
             ROW_NUMBER() OVER (PARTITION BY P.USER_ID ORDER BY P.ID DESC) AS ROW_NUM            ,
             P.USER_ID AS USER_ID           ,
             P.TOTAL_POINT
         FROM
             POINT P
     ) B
WHERE
        B.ROW_NUM = 1;

outSQL 의 where 에 row_num 이 1 인 것들만 조회 될 것이니 아래와 같이 정리됩니다.

ROW_NUM	id	USER_ID	TOTAL_POINT
1	2	1	700
1	4	2	350

 

이제는 회원 1, 2 에 대한 totalPoint를 알게 되었습니다.