오라클 rownum만들기. rownum 구현 (rownum 못쓸때 직접 만드는법)

저번시간에

partition by를 구현한거와 비슷하게

 

이번엔 rownum을 직접 구현하고 싶을경우이다.

각각의 인라인뷰에서 rownum을 구하거나, 

mysql 처럼 @rownum = rownum +1 처럼 rownum을 변수처럼 사용하고싶은데 못할경우 활용됨

 

저번에 사용한 테스트데이터를 공유하겠습니다.

저는 주로 집에서 웹 sql 테스트 사이트를 이용하면서 공부해요

https://sqltest.net/

SQL Test

 

테스트 데이터는

CREATE TABLE sql_test_a 
( 
    TDATE  VARCHAR2(8 ), 
    IDX1 NUMBER(16 ), 
    IDX2  NUMBER(16 )
); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 5, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 7, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 8, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 10, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 15, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 16, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 17, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 18, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 19, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 24, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 27, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 31, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 33, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 35, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 38, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 41, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 42, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 47, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 50, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 53, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 55, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 56, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 58, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 59, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 61, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 62, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 64, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 66, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 68, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 500001, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250203', 900001, 1); 

INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 1, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 2, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 3, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 4, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 5, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 6, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 7, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 8, 1); 
INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 9, 1); 

 

라고 합시다

우리는 각각 20250203 과 202050204 일의 데이터를 하나의 쿼리에서 rownum을 만들어볼겁니다

 

 

기준 A 테이블을 잡고 B를 매핑하면서 B의 IDX1보다 작은갯수를 구해주면 IDX값과 일치하기에 ROWNUM 이 됩니다

결과

 

 

근데 여기서. 만약 IDX1의 값이 중복된 IDX2가 있다면 ROWNUM이 맞지않게됩니다.

테스트 데이터에

INSERT INTO sql_test_a (TDATE, IDX1, IDX2) VALUES ('20250204', 5, 2); 

를 추가하죠. 그럴경우 20250204 결과가

 

이렇게 맞지 않게됩니다.

 

이럴 경우는 어떻게 해야할까요? 

이럴경우 쿼리 조건에 로직이 들어가야합니다.

 

 

 

설명을 하자면.

A의 IDX1과 B의 IDX1이 같을때

(문제가 됐던 데이터 5,2를 기준으로 잡고 설명하자면)

5에서 

5,2 가 아닐때.

즉 5,1 이면 기존 처럼 계산하고.

5,2 이면 5,2 이전의 ROW (5,1) 까지 카운트 한다는겁니다. 

 

여기에 붙인 OR 절은 

IDX1과 해당하지 않는 행을 모조리 더하여 누적시키는 위한용 입니다.

 

 

 

결과는

 

 저희가 원하는 순서입니다.