[SQL] Oracle 결재 신청된 결재자 리스트와 최종 결재자 조회(MAX 분석 함수 KEEP 활용)

결재와 결재자(승인자) 정보가 담겨져 있는 데이터 이며, 최종 결재자(승인자)를 뽑으려고 하는 규칙은 NULL 값이 아니고, APV_APPNUM 숫자가 높은 데이터를 뽑으면 됩니다.

즉, 최종 결재자는 결재 승인에 대한 코멘트를 반드시 남긴다는 규칙을 만들고, 조회 쿼리를 작성해야 합니다.

그리고 최종 결과에 APV_APPROVER 컬럼의 결재자 리스트는 결재 별로 APV_APPNUM 컬럼을 기준으로 오름차순으로 표현 합니다. (예 : estenpark, evanlee, kidonghong) 

1. 테이블 생성

CREATE TABLE EST_APV
(
  APV_ID        VARCHAR2(60 BYTE),
  APV_CNT       NUMBER(2),
  APV_APPNUM    NUMBER(2),
  APV_APPROVER  VARCHAR2(60 BYTE),
  APV_DATE      DATE,
  APV_COMMENTS  VARCHAR2(4000 BYTE)
)
;

2. 데이터 생성

Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10000', 3, 1, 'estenpark', TO_DATE('05/18/2016 16:48:55', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10000', 3, 2, 'evanlee', TO_DATE('05/18/2016 16:48:56', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE)
 Values
   ('APV10000', 3, 3, 'kidonghong', TO_DATE('05/18/2016 17:23:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10001', 3, 1, 'estenpark', TO_DATE('05/19/2016 10:44:39', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10001', 3, 2, 'evanlee', TO_DATE('05/19/2016 10:44:41', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE)
 Values
   ('APV10001', 3, 3, 'seocha', TO_DATE('05/19/2016 10:44:42', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10002', 3, 1, 'seocha', TO_DATE('05/19/2016 10:50:54', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10002', 3, 2, 'estenpark', TO_DATE('05/19/2016 10:50:56', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE)
 Values
   ('APV10002', 3, 3, 'evanlee', TO_DATE('05/19/2016 10:50:57', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10003', 4, 1, 'seocha', TO_DATE('05/19/2016 10:52:29', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE, 
    APV_COMMENTS)
 Values
   ('APV10003', 4, 2, 'soul1004', TO_DATE('05/19/2016 10:52:31', 'MM/DD/YYYY HH24:MI:SS'), 
    'TEST');
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE)
 Values
   ('APV10003', 4, 3, 'estenpark', TO_DATE('05/19/2016 10:52:32', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EST_APV
   (APV_ID, APV_CNT, APV_APPNUM, APV_APPROVER, APV_DATE)
 Values
   ('APV10003', 4, 4, 'evenlee', TO_DATE('05/19/2016 10:52:33', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

3. MAX 분석 함수와 KEEP를 활용해서 쿼리 작성

SELECT    A.APV_ID
         ,LISTAGG(A.APV_APPROVER, ', ') WITHIN GROUP (ORDER BY A.APV_APPNUM) AS APV_USER_ID
         ,MAX(A.APV_APPROVER) KEEP (DENSE_RANK FIRST ORDER BY
                                      CASE
                                        WHEN A.APV_COMMENTS IS NULL THEN 0
                                        ELSE 1
                                      END DESC
                                      ,APV_APPNUM DESC)
            AS LAST_APPROVER
FROM      EST_APV A
GROUP BY  A.APV_ID
;

4. 최종 결과