아래에 테이블 SERVICE_CHECK_LIST는 매일 담당자가 체크를 해야 할 대상 서버를 유지한다.
TABLE service_check_list
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL VARCHAR2(3)
SEQ NOT NULL NUMBER
SERVER_NAME NOT NULL VARCHAR2(20)
CHECKED_AT DATE
CHECK_DATE NOT NULL DATE
DISCONTINUED NOT NULL VARCHAR2(1)
매일 아침 해당 테이블에서 DISCONTINUED가 1이 아닌 행들 중에서 어제 생성되었던 행들을 복제해서 오늘 날짜로 밀어 넣으려고 한다.
EMPID SEQ SERVER_NAME CHECKED_AT CHECK_DATE DISCONTINUED
----- ---------- -------------------- ---------- ---------- ------------
JJM 1 MICRO1 18/04/10 0
JJM 0 MICRO2 18/04/10 0
SAM 1 BIG1 18/04/10 0
SAM 0 BIG2 18/04/10 1
EMPID, SEQ가 키 이므로 매번 실행시 마다 SEQ의 최대값을 구해서 새로 넣어주어야 한다. 이 때 어려움은 JJM의 경우에는 MAX값만을 구해서 +1을 해준 값을 넣어줄 수 없다는 것이다. 루프를 쓰지 않고 SQL로 해결하자.
group by 를 써서 insert into 하려고 하면 다음처럼 된다.
SELECT EMPID, SERVER_NAME, MAX(SEQ)+1, SYSDATE, 0
FROM SERVICE_CHECK_LIST
WHERE DISCONTINUED <> 1
GROUP BY EMPID, SERVER_NAME
EMPID SERVER_NAME MAX(SEQ)+1 SYSDATE 0
----- -------------------- ---------- -------- ----------
JJM MICRO1 2 18/04/10 0
JJM MICRO2 1 18/04/10 0
SAM BIG1 2 18/04/10 0
원하는 결과가 아니다. JJM, 1 이 unique key 제약을 해친다. 이 문제를 group by를 통해서 해결하려고 하면 문제가 계속 꼬인다. empid, server_name등이 모두 group by에 나타나야 하기 때문이다.
이 테이블에서 SEQ에 대한 기준은 EMPID뿐인데, 이렇게 하려면 EMPID에 대한 ROW_NUMBER를 구하는 서브쿼리를 얻은 후 다시 대상 행들을 선택해서 ROW_NUMBER를 더하고 이걸... 이런식으로 쿼리를 복잡하게 만들지 말자.
이 테이블에서 SEQ에 대한 기준은 EMPID뿐인데, 이렇게 하려면 EMPID에 대한 ROW_NUMBER를 구하는 서브쿼리를 얻은 후 다시 대상 행들을 선택해서 ROW_NUMBER를 더하고 이걸... 이런식으로 쿼리를 복잡하게 만들지 말자.
Analytic function 을 사용하자. 다음 쿼리는 위 문제를 해결한다. 이 문장으로 INSERT INTO하면 문제는 해결된다.
SELECT EMPID,
SERVER_NAME,
MAX (SEQ) OVER (PARTITION BY EMPID ORDER BY EMPID)
+ ROW_NUMBER () OVER (PARTITION BY EMPID ORDER BY EMPID)
SEQ,
SYSDATE,
0
FROM SERVICE_CHECK_LIST
WHERE DISCONTINUED <> 1;
EMPID SERVER_NAME SEQ SYSDATE 0
----- -------------------- ---------- -------- ----------
JJM MICRO2 2 18/04/10 0
JJM MICRO1 3 18/04/10 0
SAM BIG1 2 18/04/10 0
3 rows selected.