■예 4_3_7 || NTILE 함수를 이용한 그룹 지정 ______SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE, NTILE(6) OVER( PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC) AS NTILE FROM INSA WHERE JOIN_GBN_CODE = 'FRE';
■예 4_3_8 || NTILE 함수의 나머지 값 처리 SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE, NTILE(7) OVER( PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC) AS NTILE FROM INSA WHERE JOIN_GBN_CODE = 'FRE';
■예 4_3_9 || NTITLE 분석 함수 사용 예제_2 SELECT * FROM ( SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE, NTILE(7) OVER( PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC ) AS SAL_G FROM INSA WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013' ) WHERE JOIN_GBN_CODE = 'RGL' AND SAL_G = '1';
■예 4_3_10 || SALARY의 데이터 값으로 누적 분포를 추출 SELECT * FROM (SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE, ROW_NUMBER() OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC) AS ROW_NUMBER, ROUND(CUME_DIST() OVER (PARTITION BY JOIN_GBN_CODE ORDER BY SALARY),3) AS CUME_DIST FROM INSA ORDER BY JOIN_GBN_CODE DESC ) WHERE ROW_NUMBER <= 3;
■예 4_3_11 || 윈도우 분석 함수 적용 예 SELECT SABUN, ENG_NAME, SALARY, JOIN_DAY, JOIN_GBN_CODE, SUM(SALARY) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN ROWS 1 PRECEDING) AS TOTAL_SAL FROM INSA WHERE JOIN_DAY < TO_CHAR(ADD_MONTHS(SYSDATE,-36),'YYYYMMDD');
■예 4_3_12 || FIRST_VALUE SELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, SALARY, FIRST_VALUE(ENG_NAME) OVER(PARTITION BY SUBSTR(JOIN_DAY,1,6) ORDER BY SABUN) AS START_DAY FROM INSA WHERE JOIN_GBN_CODE='RGL';
■예 4_3_13 || LAST_VALUE SELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, SALARY, LAST_VALUE(ENG_NAME) OVER(PARTITION BY SUBSTR(JOIN_DAY,1,6) ORDER BY SABUN) AS FINAL_DAY FROM INSA WHERE JOIN_GBN_CODE='RGL';
■예 4_3_14 || FIRST_VALUE & LAST_VALUE SELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, SALARY, FIRST_VALUE(ENG_NAME) OVER(PARTITION BY SUBSTR(JOIN_DAY,1,6) ORDER BY SABUN) AS START_DAY, LAST_VALUE(ENG_NAME) OVER(PARTITION BY SUBSTR(JOIN_DAY,1,6) ORDER BY SABUN ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FINAL_DAY FROM INSA WHERE JOIN_GBN_CODE='RGL';
■예 4_3_15_1 || LEAD & LAG 분석 함수 사용 SQL1 SELECT SABUN, ENG_NAME, JOIN_DAY, JOIN_GBN_CODE, LEAD(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS NEXT_MEM, LAG(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS PREV_MEM FROM INSA WHERE JOIN_GBN_CODE='RGL';
■예 4_3_15_2 || LEAD & LAG 분석 함수 사용 SQL2 SELECT SABUN, ENG_NAME, JOIN_DAY, JOIN_GBN_CODE, LEAD(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS NEXT_MEM, LAG(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS PREV_MEM FROM INSA WHERE SABUN = :SABUN;
■예 4_3_16 || LEAD & LAG 분석 함수 사용 SQL3 SELECT * FROM ( SELECT SABUN, ENG_NAME, JOIN_DAY, JOIN_GBN_CODE, LEAD(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS NEXT_MEM, LAG(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS PREV_MEM FROM______ INSA ) WHERE SABUN = :SABUN;
INFO
실전 DB모델링과 SQL과정
