-- 분석함수
-- 집계 함수는 행 그룹으로 값을 집계하고 // 행 그룹별로 단일 행을 반환 하기 때문에 데이터 집합이 변경됨
-- 분석 함수는 파티션과 윈도우로 값을 집계한다. // 데이터 집합을 변경하지 않고 값을 집계하므로, 원본값과 집계 값을 함께 분석할수 있다.
SELECT A.EMPNO, A.SAL, B.SAL AS C1, A.SAL / B.SAL AS C2
FROM
EMP A
, (SELECT DEPTNO, SUM(SAL) AS SAL FROM EMP GROUP BY DEPTNO ) B
WHERE
A.DEPTNO = 10
AND B.DEPTNO = A.DEPTNO
ORDER BY
1;
-- 분석 함수를 사용하면 EMP 테이블을 1번만 조회하면 된다.
-- SUM(), RATIO_TO_REPORT() 함수이다.
-- RATIO_TO_REPORT() -> 전체 합계 대비 비율 또는 백분율 구하는 함수
SELECT
EMPNO
, SAL
, SUM(SAL) OVER() AS C1
, RATIO_TO_REPORT(SAL) OVER() AS C2
FROM
EMP
WHERE
DEPTNO = 10
ORDER BY
1;
-- 분석 함수는 OVER 키워드를 사용한다.
-- 집계 함수에 OVER 키워드를 기술하면 분석함수로 동작한다.
-- OVER 키워드에 [QUERY PARTITION 절] [ORDER BY 절 [ WINDOWING 절 ] ] 로 구성된다.
-- QUERY PARTITION 절은 GROUP BY 와 유사하게 동작한다. 파티션은 행 그룹과 유사하다.
-- 분석을 위한 정적 그룹으로 생각 할 수 있다. qUERY PARTITION절을 생략하면 전체 행이 하나의 파티션으로 동작한다.
SELECT
EMPNO
, JOB
, SAL
, SUM(SAL) OVER (PARTITION BY JOB) AS C1
, SUM(SAL) OVER() AS C2
FROM
EMP
WHERE
DEPTNO = 30
ORDER BY
2,1;
-- ORDER BY 절로 파티션내 정렬 순서를 지정할 수 있다, C1열은 SAL의 오름차순 정렬에 대한 누적 합계 값을 반환한다.
SELECT
EMPNO
, SAL
, SUM(SAL) OVER (ORDER BY SAL, EMPNO) AS C1
FROM
EMP
WHERE
DEPTNO = 30
ORDER BY
2,1;
drop table t1 purge;
create table t1(c1 number, c2 number);
insert into t1 values(1,1);
insert into t1 values(2,1);
insert into t1 values(3,2);
insert into t1 values(4,3);
insert into t1 values(5,3);
insert into t1 values(6,4);
insert into t1 values(7,5);
insert into t1 values(8,5);
insert into t1 values(9,6);
commit;
select * from t1;
--- ORDER BY 절 ROW 기준 ( ORDER BY 의 기준이 되는 C2가 동일 값일경우 다르게 처리된다. 즉 물리적 행으로 처리를 하기 때문에)
-- -- R01 에서 C1이 5일때 행의 절대적인 위치로 2개 앞 ~ 맨앞까지의 갯수이므로 3을 반환하게 된다.
select
c1
, c2
, count(*) over ( order by c2 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING) AS R01
, count(*) over ( order by c2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS R02
, count(*) over ( order by c2 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) AS R03
, count(*) over ( order by c2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS R04
, count(*) over ( order by c2 ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS R05
, count(*) over ( order by c2 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS R06
, count(*) over ( order by c2 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS R07
, count(*) over ( order by c2 ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) AS R08
, count(*) over ( order by c2 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS R09
, count(*) over ( order by c2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS R10
, count(*) over ( order by c2 ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS R11
, count(*) over ( order by c2 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS R12
, count(*) over ( order by c2 ROWS UNBOUNDED PRECEDING) AS R13
, count(*) over ( order by c2 ROWS 2 PRECEDING) AS R14
, count(*) over ( order by c2 ROWS CURRENT ROW ) AS R15
FROM
T1
ORDER BY 1;
--- ORDER BY 절 RANGE 기준( ORDER BY 의 기준이 되는 C2가 동일 값일 경우 묶음으로 처리가 되어 같은 값을 반환하게 된다.)
-- R01 에서 C1이 4일때 C2가 3인데 2 PRECEDING 이므로 3-2 을 하게되면 = 1 인데 C2가 1인경우는 2개이므로 2를 반환하게 된다.
-- RANGE 기준에서 오름차순 기준 PRECEDING (-) , FOLLOWING (+) , 내림차순의 경우는 반대라고 생각하면된다.
select
c1
, c2
, count(*) over ( order by c2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING) AS R01
, count(*) over ( order by c2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS R02
, count(*) over ( order by c2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) AS R03
, count(*) over ( order by c2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS R04
, count(*) over ( order by c2 DESC RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING) AS R05
, count(*) over ( order by c2 DESC RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS R06
, count(*) over ( order by c2 DESC RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS R07
, count(*) over ( order by c2 DESC RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) AS R08
, count(*) over ( order by c2 DESC RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS R09
, count(*) over ( order by c2 DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS R10
, count(*) over ( order by c2 DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS R11
, count(*) over ( order by c2 DESC RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS R12
, count(*) over ( order by c2 DESC RANGE UNBOUNDED PRECEDING) AS R13
, count(*) over ( order by c2 DESC RANGE 2 PRECEDING) AS R14
, count(*) over ( order by c2 DESC RANGE CURRENT ROW ) AS R15
FROM
T1
ORDER BY
1 DESC;
SELECT
EMPNO
, ENAME
, SAL
, SUM(SAL) OVER (ORDER BY SAL ROWS UNBOUNDED PRECEDING) AS C1
, SUM(SAL) OVER (ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS C2 -- 1250 이 2명인 사람의 SAL을 한번에 더해서 나타내줌
, SUM(SAL) OVER (ORDER BY SAL, EMPNO ROWS UNBOUNDED PRECEDING) AS C3 -- 정렬기준이 고유하다(중복되는 정렬 값이 없다) 따라서 ROWS/RANGE와 동일한 결과가 나옴
, SUM(SAL) OVER (ORDER BY SAL, EMPNO RANGE UNBOUNDED PRECEDING) AS C4 -- 정렬기준이 고유하다(중복되는 정렬 값이 없다) 따라서 ROWS/RANGE와 동일한 결과가 나옴
FROM EMP
WHERE DEPTNO = 30
ORDER BY 3,1;
-- C1 = 정적그룹인 JOB 내에서 동적 그룹인 윈도우로 값이 집계된다.
SELECT
JOB
, HIREDATE
, SAL
, SUM(SAL) OVER( PARTITION BY JOB ORDER BY HIREDATE) AS C1
FROM
EMP
WHERE DEPTNO = 30
ORDER BY 1,2;
-- KEEP 키워드
-- KEEP 키워드를 사용하면 OVER절에 QUERY PARTITION 을 넣을수 있다.
-- JOB 파티션 별 SAL가 최소인 행을 대상으로 COMM의 최고값을 갖는다.
SELECT
ENAME
, JOB
, SAL
, COMM
, MAX(COMM) KEEP(DENSE_RANK FIRST ORDER BY SAL) OVER(PARTITION BY JOB) AS C1
FROM
EMP
WHERE DEPTNO = 30
ORDER BY 2,3,4;
--주의사항
-- RANGE 방식에 VALUE_EXPR 를 지정할시 ORDER BY 절에 숫자 값이나 날짜 값을 사용해야 한다.
-- SELECT SUM(SAL) OVER( ORDER BY JOB RANGE 1 PRECEDING) AS C1 --> ORDER BY 기준이 DATE나 NUMBER가 되어야 한다. ORA-00902
-- 또한 정렬 표현식을 1개만 사용할 수 있다.
-- SELECT SUM(SAL) OVER (ORDER BY SAL, COMM RANGE 1 PRECEDING) AS C1 --> ORDER BY 기준이 1개가 되어야 한다. ORA-30486
-- 분석함수는 SELECT 절과 ORDER BY 절에 사용할 수 있다.
-- 오라클의 날짜 DATE 컬럼에서 년도를 더하고 뺄때 ADD_MONTH() 내장 함수를 사용하기도 한다.
-- 하지만 INTERVAL 을 이용하는게 직관적이고 쉽게 컨트롤 할 수 있다.
-- SYSDATE + ( INTERVAL '1' DAY[YEAR,MONTH,DAY,HOUR 등...]
-- SYSDATE + ( INTERVAL '03:30' HOUR TO MINUTE/ MINUTE TO SECOND / 1-1 YEAR TO MONTH 등으로 사용된다.
-- 시간은 : 으로 구분 연도와 개월의 경우 - 으로 구분한다.
select
HIREDATE
, SAL
, SUM(SAL) OVER( ORDER BY HIREDATE RANGE 90 PRECEDING) AS C1
, SUM(SAL) OVER ( ORDER BY HIREDATE RANGE INTERVAL '3' MONTH PRECEDING) AS C2
FROM EMP
WHERE DEPTNO = 30
ORDER BY 1;
-- FROM 절의 인라인뷰를 사용하면 WHERE절에서 분석함수의 결과값을 사용할 수 있다.
SELECT
DEPTNO
,ENAME
,SAL
,C1
FROM
(SELECT A.*, SUM(A.SAL) OVER (PARTITION BY A.DEPTNO) AS C1 FROM EMP A)
WHERE
C1 >= 10000
ORDER BY 1,2;
-- C2 ,C3열에서 사용한 분석 함수는 그룹핑이 완료된 후 수행된다.
SELECT
DEPTNO
, SUM(SAL) AS C1
, SUM(SUM(SAL)) OVER() AS C2
, COUNT(*) OVER() AS C3
FROM EMP
GROUP BY DEPTNO
ORDER BY 1;
--아래와 같이 인라인 뷰를 통해 집계 쿼리와 분석함수를 분리하는 편이 가독성측면에서 좋다
SELECT DEPTNO, C1, SUM(C1) OVER() AS C2, COUNT(*) OVER() AS C3
FROM (SELECT DEPTNO, SUM(SAL) AS C1 FROM EMP GROUP BY DEPTNO)
ORDER BY 1;
-- DETPNO별 SAL의 합계 값을 집계한다.
SELECT DISTINCT
DEPTNO
, SUM(SAL) OVER(PARTITION BY DEPTNO) AS C1
FROM EMP;
-- 행 그룹으로 집계할 경우에는 분석함수가 아닌 GROUP BY 절을 사용해야 한다.
SELECT
DEPTNO
, SUM(SAL)
FROM
EMP
GROUP BY
DEPTNO;
-- 분석함수의 대부분은 집계 함수에 over 키워드를 기술하는 방식으로 사용한다.
-- 분석함수는 집곟삼수와 유사하게 구분할 수 있다.
-- 기본함수 = count, min, max, sum, avg
-- 통계함수 = STDDEV, VARIANCE
-- 순위함수 = RANK, DENSE_RANK, ROW_NUMBER, NTILE, CUME_DIST, PERCENT_RANL, RATIO_TO_REPORT
-- 분포함수 = PERCENTILE_CONT, PERCENTILE_DISC, MEDIAN
-- 순차함수 = FIRST_VALUE, LAST_VALUE, NTH_VALUE
-- 기타함수 = LAG, LEAD, LISTAGG
-- COUNT함수 : 행의 갯수나 EXPR의 갯수를 반환한다.
SELECT JOB, COUNT(*) OVER (PARTITION BY JOB) AS C1
FROM EMP
WHERE DEPTNO = 30
ORDER BY 1;
-- MIN 함수 : EXPR의 최저 값을 반환한다.
SELECT
EMPNO
,SAL
,COMM
,MIN(COMM) OVER ( ORDER BY SAL, EMPNO ROWS UNBOUNDED PRECEDING) AS C1
FROM EMP
WHERE DEPTNO = 30
ORDER BY 2,1;
--MAX 함수 : EXPR의 최고값을 반환한다
SELECT
EMPNO
, SAL
, COMM
, MAX(COMM) OVER (ORDER BY SAL) AS C1
FROM
EMP
WHERE DEPTNO = 30
ORDER BY 2,1;
--AVG 함수 : EXPR 의 평균 값을 반환한다.
-- ROWS 기준
SELECT
EMPNO
, ENAME
, SAL
, AVG(SAL) OVER (ORDER BY SAL, EMPNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS C1
, COUNT(*) OVER ( ORDER BY SAL, EMPNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS C2
FROM
EMP
WHERE DEPTNO = 30
ORDER BY 3,1;
-- RANGE 기준
SELECT
EMPNO
, ENAME
, SAL
, AVG(SAL) OVER (ORDER BY SAL RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING) AS C1
, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING) AS C2
FROM EMP
WHERE DEPTNO = 30
ORDER BY 3,1;
DROP TABLE T1 PURGE;
CREATE TABLE T1 AS
SELECT DT, ROWNUM * 100 AS VL
FROM (SELECT DATE '2050-01-01' + ROWNUM-1 AS DT FROM XMLTABLE('1 to 31'))
WHERE TO_CHAR(DT,'D') NOT IN ('1','7');
select * from t1;
SELECT
DT,
VL
, AVG(VL) OVER (ORDER BY DT ROWS 9 PRECEDING) AS C1
, AVG(VL) OVER (ORDER BY DT RANGE 9 PRECEDING) AS C2
FROM T1
ORDER BY 1;
-- STDDEV함수 EXPR의 표준 편차를 반환한다
SELECT
JOB
, SAL
,STDDEV(SAL) OVER (PARTITION BY JOB) AS C1
FROM EMP
WHERE DEPTNO = 30
ORDER BY 2,1;
-- VARIANCE 함수 : EXPR의 분산을 반환한다.
SELECT
JOB
, SAL
, VARIANCE(SAL) OVER (PARTITION BY JOB) AS C1
FROM EMP
WHERE DEPTNO = 30
ORDER BY 2,1;
-- 순위 함수
-- 순위 함수는 정렬 조건에 따른 순위를 반환한다. 순위 집계 함수는 가상의 행을 생성하지만 순위 분석 함수는 실제의 행으로 값을 계산한다.
--RANK : EPXR 이 동일하면 동순위를 부여하고 , 다음 순위는 동순위의 개수만큼 건너 뛴다.
SELECT
EMPNO
,ENAME
,SAL
,RANK() OVER (ORDER BY SAL) AS C1
FROM EMP
WHERE DEPTNO = 30
ORDER BY 3,1;
--DENSE_RANK 동순위가 존재하더라도 다음 순위를 이어서 부여한다
SELECT
EMPNO
,ENAME
, SAL
, DENSE_RANK() OVER (ORDER BY SAL) AS C1
FROM EMP
WHERE DEPTNO = 30
ORDER BY 3,1;
--ROW_NUMBER함수
SELECT
EMPNO
, ENAME
, SAL
, ROW_NUMBER() OVER (ORDER BY SAL) AS C1
, ROW_NUMBER() OVER (ORDER BY SAL, EMPNO) AS C2
FROM EMP
WHERE DEPTNO = 30
ORDER BY 3,1;
--부서별로 가장 최근에 입사한 사원을 반환한다
SELECT * FROM (SELECT A.*, ROW_NUMBER() OVER( PARTITION BY DEPTNO ORDER BY HIREDATE DESC, EMPNO DESC) AS RN FROM EMP A) WHERE RN=1;
SELECT * FROM (SELECT A.*, ROW_NUMBER() OVER( PARTITION BY DEPTNO ORDER BY HIREDATE DESC, EMPNO DESC) AS RN FROM EMP A) WHERE RN = 1;
SELECT
EMPNO
, ENAME
, SAL
, ROW_NUMBER() OVER (ORDER BY SAL) AS C1
, RANK() OVER(ORDER BY SAL) AS C2
, DENSE_RANKS() OVER (ORDER BY SAL) AS C3
FROM EMP
WHERE DEPTNO = 30
ORDER BY 2,1;
-- NTILE 함수 : EXPR 의 갯수만큼 버킷을 생성한 후 행에 해당하는 버킷 번호를 할당한다 (100 -> (3)) 0-34 35-68 69-100 이렇게 나뉘고 해당 범위에 맞는 버킷 번호를 부여
SELECT C1, COUNT(*) AS C2, SUM(SAL) AS C3 FROM ( SELECT SAL, NTILE(4) OVER (ORDER BY SAL) AS C1 FROM EMP WHERE DEPTNO = 30 ) GROUP BY C1 ORDER BY C1;
