NTILE : 입력 받은 수만큼 자동으로 등급을 나눠서 설정한다. 단, 동률에 대한 처리는 없다.
SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC)
FROM EMP;
위의 쿼리문은 NTILE을 이용 4개의 등급으로 나누도록 해주었다. 하지만 대상이 되는 DATA의 값은 17개로 4의 배수가 아니다. 이런 경우는 DATA의 값보다 작은 입력받은 수의 배수 중 가장 큰 값을 기준으로 나눈 뒤, 순차적으로 등급을 배분한다. 즉 위의 17의 경, 이보다 작은 4의 배수 중 가장 큰 16을 기준으로 처리하게 되는데 16은 4의 4배수이므로 각 등급은 4개씩 이루어진다. 그리고 1개가 남으므로 순차적으로 배분하게 되여 1등급만 5개가 되고 나머지는 각각 4개씩되는 것이다.
그렇다면 NTILE의 값이 5가 된다면 어떻게 될까.
SELECT ENAME, SAL, NTILE(5) OVER (ORDER BY SAL DESC)
FROM EMP;
이 역시 위에서 말한 방식으로 배분된다. 17보다 작은 5의 최대배수인 15를 기준으로 15/5=3이므로 3개씩의 등급을 가지게 되고 2개가 남게되므로 1등급과 2등급이 각각 4개씩이게 된다.
NTILE은 OVER에 의해 사용하게 되므로 이는 PATITION을 사용할 수 있다. 다음은 DEPTNO를 기준으로 SAL의 값에 대해 NTILE (3)하게 된 결과이다.
SELECT ENAME, DEPTNO, SAL, NTILE(3) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)
FROM EMP;
ROW_NUMBER : RECORD의 SORT된 순서에 따라 순차적으로 값을 부여한다. 이는 주로 게시판등에서 삭제된 뒤의 숫자를 부여하거나 할 때 쓰이게 된다. 예제로 간단한 게시판 형태의 테이블을 만들어보자.
CREATE TABLE TBL_GESIPAN(
GESISEQ NUMBER,
TITLE VARCHAR(200),
MEMO VARCHAR(400)
)TABLESPACE USERS;
또한, 이에 대해 순차적으로 숫자를 부여하는 SEQUNCE를 만들어 보자.
CREATE SEQUENCE SEQ_TBL_GESIPAN
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE;
이 SEQUENCE의 의미는 1부터 시작하여 1씩 증가하며, 최대값의 제한이 없고, 순환되지 않는다는 뜻으로 직관적으로 알 수 있으므로 자세한 설명은 생략하겠다.
INSERT INTO TBL_GESIPAN VALUES
(SEQ_TBL_GESIPAN.NEXTVAL, '임경민 소개', '아주 훌륭한 분 입니다.');
INSERT INTO TBL_GESIPAN VALUES
(SEQ_TBL_GESIPAN.NEXTVAL, '박사은 누구?', '멋진 남자 입니다.');
INSERT INTO TBL_GESIPAN VALUES
(SEQ_TBL_GESIPAN.NEXTVAL, '이주홍 좋은사람', '당연한 말씀!!!!');
INSERT INTO TBL_GESIPAN VALUES
(SEQ_TBL_GESIPAN.NEXTVAL, '김영훈은', '핸섬가이');
COMMIT;
SELECT * FROM TBL_GESIPAN;
만들어진 테이블에서 일정 게시번호를 지운다면 그 번호에 대한 공백이 생기게 된다.
DELETE TBL_GESIPAN
WHERE GESISEQ = 2;
SELECT * FROM TBL_GESIPAN;
이를 ROW_NUMBER를 이용하여 정렬한 값에 순차적인 숫자를 부여할 수 있다.
SELECT ROW_NUMBER() OVER (ORDER BY GESISEQ ) "계시번호", TITLE "제목", MEMO "내용" FROM TBL_GESIPAN;
즉, 외부적으로 삭제 되었더라도 정렬되어 보이도록 정리한다. 이 역시 OVER에 대한 결과이므로 PARTITION BY 구문을 사용 가능하다.
OVER : 함수(|표현식) OVER (PARITION BY COLUMN1 ORDER BY COLUMN2) 형식으로 쓰이며, COLUMN1이 같은 값을 기준으로 묶은 것을 COLUMN2을 기준으로 정렬하여 표현식을 수행한다.
EMP의 사원번호와 이름, 급여, 그리고 누적된 급여를 나타내어보자.
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여",SUM(SAL) OVER (ORDER BY EMPNO) "누적 급여"
FROM EMP;
기본적으로 그룹함수는 적용되는 DATA에 대해 다른 값을 가지는 COLUMN을 포함할 경우 처리가 불가능 하지만 OVER 구문을 사용함으로써 ORDER BY 에 의해 정렬되며, 순차적으로 SUM을 적용하여 처리하게 된다.
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여",SUM(SAL) OVER (ORDER BY EMPNO ROWS 3 PRECEDING) "누적 급여"
FROM EMP;
위의 OVER문에서는 PREPENDING을 사용함으로써 일정영역에 대한 그룹함수를 적용하도록 하였다. 자신을 포함한 앞의 값 3개를 더한 값, 즉 총 4개의 값을 더한 값을 보여준다. 이런 식으로 일부를 기준으로 적용한 DATA를 통하여 일정 시간 때에 당일의 판매량, 주요 시간 등 분석에 용의 하다.
이를 일반 쿼리로 작성한다면 상관서브를 이용 작성 가능하다.
SELECT E.EMPNO "사원번호", E.ENAME "사원명", E.SAL "급여", (
SELECT SUM(M.SAL) "누적"
FROM EMP M
WHERE E.EMPNO >= M.EMPNO
) "누적"
FROM EMP E
ORDER BY 1;
현재 쿼리의 EMPNO 보다 작은 서브쿼리의 EMPNO들의 합을 순차적으로 구함으로써 OVER와 같은 효과를 볼 수 있다.
발전소에 관하여 간단한 테이블을 만들어 보자.
CREATE TABLE TBL_BALJEON(
SIGAN DATE,
BALYANG NUMBER
)TABLESPACE USERS;
INSERT INTO TBL_BALJEON VALUES
(TO_DATE('2009-05-26 09:00', 'YYYY-MM-DD HH24:MI'),20);
INSERT INTO TBL_BALJEON VALUES
(TO_DATE ('2009-05-26 09:30', 'YYYY-MM-DD HH24:MI'),30);
INSERT INTO TBL_BALJEON VALUES
(TO_DATE ('2009-05-26 11:00', 'YYYY-MM-DD HH24:MI'),40);
INSERT INTO TBL_BALJEON VALUES
(TO_DATE ('2009-05-26 11:30', 'YYYY-MM-DD HH24:MI'),100);
INSERT INTO TBL_BALJEON VALUES
(TO_DATE ('2009-05-26 13:00', 'YYYY-MM-DD HH24:MI'),150);
INSERT INTO TBL_BALJEON VALUES
(TO_DATE ('2009-05-26 13:20', 'YYYY-MM-DD HH24:MI'),70);
INSERT INTO TBL_BALJEON VALUES
(TO_DATE ('2009-05-26 19:00', 'YYYY-MM-DD HH24:MI'),30);
COMMIT;
SELECT * FROM TBL_BALJEON
이 테이블은 시간 별 발전량을 보여준다.
이를 OVER 구문과 상관쿼리를 이용하여 누적량을 구해보자.
SELECT "시간","양",SUM("양") OVER (ORDER BY "시간") "누적"
FROM (SELECT TO_CHAR(SIGAN, 'YYYY-MM-DD HH24') "시간", SUM(BALYANG) "양"
FROM TBL_BALJEON
GROUP BY TO_CHAR(SIGAN, 'YYYY-MM-DD HH24')
ORDER BY 1) T;
WITH T AS
(SELECT TO_CHAR(SIGAN, 'YYYY-MM-DD HH24') "시간", SUM(BALYANG) "양"
FROM TBL_BALJEON
GROUP BY TO_CHAR(SIGAN, 'YYYY-MM-DD HH24')
ORDER BY 1)
SELECT "시간","양",(
SELECT SUM("양")
FROM T T2
WHERE T1."시간" >= T2."시간") "누적"
FROM T T1;
결과는 같지만, 상관쿼리는 속도의 저하가 일어난다. 왜냐하면, 각 RECORD를 가져올 때 마다 서브쿼리를 호출하여 사용하여야 하기 때문이다. 반면, OVER를 사용한 경우, GROUP BY에 따라 순차적으로 늘려감에 따른 결과를 가져오기 때문이다.
HR 계정을 이용하여 입사일이 비슷한 사람 들 중 평균 월급과 내 월급 차이를 구하여 보자. 비슷함의 기준은 해당 직원의 입사일 +/- 1년이다.
SELECT FIRST_NAME || ' '|| LAST_NAME "NAME", HIRE_DATE, SALARY,
AVG(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND
INTERVAL '1' YEAR FOLLOWING) "1년 전 후 평균 급여"
FROM EMPLOYEES;
이 쿼리는 OVER () 구문 안에 범위를 정하는 RANGE를 사용하여 (RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR FOLLOWING)로 1년 간의 INTERVAL을 범위로 하여 평균을 구하도록 하였다. 하지만 자세히 보면 년도가 같음에도 급여가 다른 경우가 생기는 데 이는 입사일의 월, 일이 다르기 때문이다.
INTERVAL : DATE 타입의 값을 연산할 때에 쓰이며, 입력 될 값에 따라 YEAR, MONTH, DAY, HOUR, MINITE, SECONDE를 명시해야 한다.
그렇다면 이전 쿼리문을 년도만을 기준으로하여 처리하여 보자
SELECT FIRST_NAME ||' '|| LAST_NAME "NAME", HIRE_DATE, SALARY,
ROUND((AVG(SALARY) OVER (ORDER BY TO_DATE(TO_CHAR(HIRE_DATE,'YYYY'),'YYYY' )
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR FOLLOWING )),1) "년도 평균"
FROM EMPLOYEES ;
위와 같이 TO_CHAR문을 이용하여 연도만을 가져온 후, 이를 이용하여 처리하면 된다.
이를 응용하여 1년 전후의 평균급여와 최고급여, 최저 급여를 구한다면 다음과 같다.
SELECT FIRST_NAME || ' '|| LAST_NAME "NAME", HIRE_DATE, SALARY,
ROUND(AVG(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND
INTERVAL '1' YEAR FOLLOWING), 1) "1년 전 후 평균 급여",
MAX(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND
INTERVAL '1' YEAR FOLLOWING) "1년 전 후 최고 급여",
MIN(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND
INTERVAL '1' YEAR FOLLOWING) "1년 전 후 최저 급여"
FROM EMPLOYEES;
이러한 응용은 다양하게 가능하다. 위의 쿼리문을 조금 수정하면 1년 전후 입사한 직원 중 가장 선임과 가장 후임의 월급을 표현할 수도 있다.
SELECT FIRST_NAME ||' '|| LAST_NAME "NAME", HIREz_DATE, SALARY, ROUND((AVG(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR FOLLOWING )),1) "년도 평균",
FIRST_VALUE(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR FOLLOWING) "최선임 급여",
LAST_VALUE(SALARY) OVER (ORDER BY HIRE_DATE
RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR FOLLOWING) "최후임 급여"
FROM EMPLOYEES ;
이런 식으로 시간을 기준으로 앞, 뒤의 값을 가져 올 수도 있지만, 그냥 현재 RECORD의 앞/뒤의 값을 가져오도록 하는 함수도 있다.
LAG : 현재 RECORD의 바로 앞의 값을 반환한다.
LEAD : 현재 RECORD의 바로 뒤의 값을 반환한다.
이전에 만든 발전소 테이블에서 시간당 발전량을 표시하면 다음과 같다. 이는 분과 초를 통합하도록 TO_CHAR를 이용하여 시간단위로 잘라낸 뒤, GROUP BY하여 처리한 값이다. 만일 분단위로 한다면 TO_CHAR의 조건에서 MI까지 처리하면 될 것이고, 10분단위 등도 역시 비슷한 방법을 이용하여 잘라낸 뒤 조건에 따라 변환하여도 될 것이다.
SELECT TO_CHAR(SIGAN, 'YYYY-MM-DD HH24') "발전 시간",
SUM(BALYANG) "발전량"
FROM TBL_BALJEON
GROUP BY TO_CHAR(SIGAN, 'YYYY-MM-DD HH24')
ORDER BY 1;
이를 기준으로 할때 시간별 발전량을 앞, 뒤의 값과 함께 본다면 DATA를 비교할 때 좀 더 손쉬울 것이다.
WITH T
AS (
SELECT TO_CHAR(SIGAN, 'YYYY-MM-DD HH24') "발전시간",
SUM(BALYANG) "발전량"
FROM TBL_BALJEON
GROUP BY TO_CHAR(SIGAN, 'YYYY-MM-DD HH24')
ORDER BY 1 )
SELECT T.발전시간,
LAG(T."발전량",1) OVER (ORDER BY 발전시간) "이전 발전량",
T.발전량 "현재 발전량",
LEAD(T.발전량,1) OVER (ORDER BY 발전시간) "이후 발전량"
FROM T;
START WITH, CONNECT BY PRIOR : TABLE의 COLUMN을 정렬함에 있어, START WITH 조건에 맞는 행부터 시작하여, CONNECT BY PRIOR 에 따른 조건에 만족하도록 연결, 반복하여 값을 가진다. 단, CONNECT BY PRIOR의 경우 방향성을 가지므로 조심해야 한다. PRIOR의 뜻이 '이전의', '우선의'의 뜻을 가지고 있으므로 이전의 값 - 맨 처음 RECORD가 START WITH 조건에 의한 RECORD이고, 그 RECORD에서 해당 COLUMN의 값 - 이 EQUL(=) 반대편의 COLUMN의 값을 비교하게 되는 것이다.
SELECT EMPNO, ENAME, MGR
FROM EMP
START WITH EMPNO = 7839
CONNECT BY PRIOR EMPNO = MGR;
위의 쿼리문의 결과를 확인하면 START WITH EMPNO = 7839 에 의 하여 KING의 RECORD가 보여지고, CONNECT BY PRIOR EMPNO = MGR 이므로 KING의 EMPNO인 7839가 다음 RECORD의 MGR와 비교되어 보여진다. 이 결과가 더 이상 없을 때 - EMPNO가 MGR와 같은 값이 없을 때 - 까지 반복 처리하게 된다. 그리고 반복값이 끝난 다면 한단계 위의 값을 가지고, 중복된 다른 값에 대하여 처리하게 된다. 이는 추후
조직체계를 만들어 보도록 하자.
위의 쿼리문에서 CONNECT BY PRIOR에 해당하는 값을 바꾸어 보았다.
SELECT EMPNO, ENAME, MGR
FROM EMP
START WITH EMPNO = 7369
CONNECT BY PRIOR MGR = EMPNO
LEVEL 과 LPAD를 이용하여 조직체계를 만들어 보자.
LEVEL : START WITH ~ CONNECT BY 문에 의해 접근하게 된 깊이를 반환한다.
SELECT LEVEL, JOB , ENAME
FROM EMP
START WITH EMPNO = 7839
CONNECT BY PRIOR EMPNO = MGR
위의 결과에서 보듯이 몇번째로 접근하는가에 따라 LEVEL이 부여가 된다. 이를 응용하여 LPAD와 함께 사용하여 다음과 같은 조직체계를 보기 쉽게 표현할 수 있다.
SELECT LPAD(' ',(LEVEL-1) * 5, ' ') || JOB || ' ' || ENAME AS "조직체계"
FROM EMP
START WITH EMPNO = 7839
CONNECT BY PRIOR EMPNO = MGR;
이는 (LEVEL -1)*5만큼의 공간을 앞에 삽입하므로, 우측으로 밀려있는 사람이 이전 행의 결과 중 왼쪽에 가까운 사람들에게 속해있음을 보여준다.
댓글 없음:
댓글 쓰기