[MSSQL] ROW_NUMBER()을 이용한 그룹내 최상위 데이터 출력

 

상품 구매 후 영수증 신청 테이블 예시

idx    product          status     reg_date        price      receipt_type    issue_date
1      스위트홈           발행대기      2021-05-05      29000      세금계산서
2      종이의 집          발행대기      2021-05-06      28000      현금영수증
3      인간수업           발행대기      2021-05-08      32000      세금계산서
4      킹덤              발행완료      2021-05-09      28000      현금영수증         2021-05-13
5      보건교사 안은영      발행완료      2021-05-09      31500      현금영수증         2021-05-13
6      좋아하면 울리는      발행완료      2021-05-10      27000      세금계산서         2021-05-17
7      아이 엠 낫 오케이    발행대기      2021-05-14      32000      세금계산서
8      안나라수마나라       발행완료      2021-05-20      34000      세금계산서         2021-05-23
9      지금 우리 학교는     발행완료      2021-05-20      27800      현금영수증         2021-05-23
10     너의 모든 것        발행대기      2021-05-22      28000      세금계산서
11     나 홀로 그대        발행완료      2021-05-25      31000      현금영수증         2021-05-28

기존에는 영수증 신청을 취소 했을 때 영수증 신청 테이블에서

기존 데이터를 삭제하고 새 행을 추가하는 로직으로 구현되어 있었다.

(3번인 '인간수업'의 세금영수증을 취소하고 현금영수증을 신청했다.)

idx    product          status     reg_date        price      receipt_type    issue_date
1      스위트홈           발행대기      2021-05-05      29000      세금계산서
2      종이의 집          발행대기      2021-05-06      28000      현금영수증
4      킹덤              발행완료      2021-05-09      28000      현금영수증         2021-05-13
5      보건교사 안은영      발행완료      2021-05-09      31500      현금영수증         2021-05-13
6      좋아하면 울리는      발행완료      2021-05-10      27000      세금계산서         2021-05-17
7      아이 엠 낫 오케이    발행대기      2021-05-14      32000      세금계산서
8      안나라수마나라       발행완료      2021-05-20      34000      세금계산서         2021-05-23
9      지금 우리 학교는     발행완료      2021-05-20      27800      현금영수증         2021-05-23
10     너의 모든 것        발행대기      2021-05-22      28000      세금계산서
11     나 홀로 그대        발행완료      2021-05-25      31000      현금영수증         2021-05-28
12     인간수업           발행대기      2021-05-28      32000      현금영수증

 

 

 

취소된 데이터도 관리할 수 있었으면 좋겠다는 요구사항에 따라

취소된 날짜를 입력받는 필드를 추가했다.

idx    product    status    reg_date    price    receipt_type    issue_date    cancel_date   

그리고 요구사항 대로 취소 상태를 추가하고 취소된 날짜를 입력했다.

idx    product          status     reg_date        price      receipt_type    issue_date    cancel_date
1      스위트홈           발행대기      2021-05-05      29000      세금계산서
2      종이의 집          발행대기      2021-05-06      28000      현금영수증
3      인간수업           취소         2021-05-08      32000      세금계산서                       2021-05-28
4      킹덤              발행완료      2021-05-09      28000      현금영수증         2021-05-13
5      보건교사 안은영      발행완료      2021-05-09      31500      현금영수증         2021-05-13
6      좋아하면 울리는      발행완료      2021-05-10      27000      세금계산서         2021-05-17
7      아이 엠 낫 오케이    발행대기      2021-05-14      32000      세금계산서
8      안나라수마나라       발행완료      2021-05-20      34000      세금계산서         2021-05-23
9      지금 우리 학교는     발행완료      2021-05-20      27800      현금영수증         2021-05-23
10     너의 모든 것        발행대기      2021-05-22      28000      세금계산서
11     나 홀로 그대        발행완료      2021-05-25      31000      현금영수증         2021-05-28
12     인간수업           발행대기      2021-05-28      32000      현금영수증

 

 

 

여기서 문제는 사용자에게는 영수증 발행취소 후에 

1. 재신청 안함 - 취소된 데이터 출력
2. 재신청 했을 때 - 취소된 데이터가 아닌 가장 최근에 신청한 정보만 출력

하려고 한다. 결국 최근 정보만을 출력하고자 한다는 것!

 

 

 

처음 리스트를 출력하는 sql query는 아래와 같다.

SELECT *
FROM RECEIPT_TABLE
ORDER BY reg_date DESC

여기서 특정 조건에 따라 그룹 짓고 최상위 데이터만을 출력하기 위해서는

ROW_NUMBER() OVER() 함수를 이용하면 된다.

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY product ORDER BY reg_date DESC) AS RANK_NO
    FROM RECEIPT_TABLE
    ORDER BY reg_date DESC
) t
WHERE rank_no = 1
ORDER BY reg_date DESC

Inner Query 안에 예약어에 대해 부가설명

• PARTITION BY : 그룹 짓고자 하는 필드명을 지정
• ORDER BY : 순위를 매길 필드명을 지정

 

 

 

해당 쿼리의 결과는 아래와 같다.

idx    product          status     reg_date        price      receipt_type    issue_date    cancel_date
1      스위트홈           발행대기      2021-05-05      29000      세금계산서
2      종이의 집          발행대기      2021-05-06      28000      현금영수증
4      킹덤              발행완료      2021-05-09      28000      현금영수증         2021-05-13
5      보건교사 안은영      발행완료      2021-05-09      31500      현금영수증         2021-05-13
6      좋아하면 울리는      발행완료      2021-05-10      27000      세금계산서         2021-05-17
7      아이 엠 낫 오케이    발행대기      2021-05-14      32000      세금계산서
8      안나라수마나라       발행완료      2021-05-20      34000      세금계산서         2021-05-23
9      지금 우리 학교는     발행완료      2021-05-20      27800      현금영수증         2021-05-23
10     너의 모든 것        발행대기      2021-05-22      28000      세금계산서
11     나 홀로 그대        발행완료      2021-05-25      31000      현금영수증         2021-05-28
12     인간수업           발행대기      2021-05-28      32000      현금영수증

(참고 : https://spaghetti-code.tistory.com/25)