WINDOW FUNCTION

create table emp01(
empid varchar2(20),
deptid varchar2(20),
sal number
);

--001부터 시작하는 값 Sequence 생성 
CREATE SEQUENCE empid_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 1000  -- 시퀀스가 생성할 수 있는 최대값을 설정할 수 있다.
NOCYCLE        -- 시퀀스가 최대값에 도달했을 때 재사용하지 않도록 설정한다.
CACHE 10;       -- 시퀀스 값을 캐시하여 성능을 향상시킬 수 있다.


--trigger 생성
--insert시 자동으로 empid 값을 설정 

create or replace trigger trig_empid
before insert on emp01

for each row
begin
    :new.empid := LPAD(empid_seq.NEXTVAL,3,'0');
end;
/

INSERT INTO emp01 (deptid, sal) VALUES ('D01', 2500);
INSERT INTO emp01 (deptid, sal) VALUES ('D01', 3000);
INSERT INTO emp01 (deptid, sal) VALUES ('D02', 4500);
INSERT INTO emp01 (deptid, sal) VALUES ('D02', 3000);
INSERT INTO emp01 (deptid, sal) VALUES ('D02', 2500);
INSERT INTO emp01 (deptid, sal) VALUES ('D03', 4500);
INSERT INTO emp01 (deptid, sal) VALUES ('D03', 3000);


SELECT * FROM EMP01;

 

 

○ ROW_NUMBER()

SELECT EMPID,DEPTID,SAL,
ROW_NUMBER() OVER(PARTITION BY DEPTID ORDER BY SAL DESC) FROM EMP01;

 

 

EMP01 테이블에서 각 부서(DEPTID)별로 급여(SAL)를 내림차순으로 정렬하여 순위를 매기는 작업을 수행한다.
 
ORDER BY 절에는 SAL DESC가 지정되어 있어서 각 부서 내에서 급여를 내림차순으로 정렬한다 

ROW_NUMBER() 함수는 각 행에 순차적인 숫자(순위)를 부여하여 PARTITION BY 절로 지정된 각 그룹(부서) 내에서 ORDER BY 절로 정렬된 순서에 따라 숫자가 부여된다.

 

 

 

 

 

 

○ SUM() OVER()

 

SELECT EMPID,DEPTID,SAL,
SUM(SAL) OVER(PARTITION BY DEPTID ORDER BY EMPID) C2 FROM EMP01;

 

 

 EMP01 테이블에서 각 부서(DEPTID)별로 급여(SAL)의 누적 합을 계산하는 작업을 수행한다.

여기서 각 행은 PARTITION BY 절에 지정된 DEPTID에 따라 그룹화된다.

 

ORDER BY EMPID는 EMPID를 기준으로 정렬된 순서에 따라 누적 합을 계산한다. 

SUM(SAL) OVER(PARTITION BY DEPTID ORDER BY EMPID)은 각 행에서 해당 부서(DEPTID)의 이전 행까지의 SAL의 합계를 나타낸다.

 

 

 

 

 

 

 

○ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

 

SELECT 
    EMPID,
    DEPTID,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTID ORDER BY EMPID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS C2
FROM 
    EMP01;

 

 
     - SUM(SAL): SAL 컬럼의 누적 합계를 계산한다.
     - OVER (PARTITION BY DEPTID ORDER BY EMPID): DEPTID로 파티션을 나누고, EMPID 순서로 정렬하여 각 파티션 내에서 순서를 지정한다.
     - ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 현재 행까지의 범위 내에서 합계를 계산한다. 즉, 각 행에 대해 그 행을 포함하여 이전 모든 행의 SAL 합계를 구한다.

 

 

 

 

 

 

○ MAX(SAL) OVER(ORDER BY SAL DESC ROWS CURRENT ROW) 

SELECT EMPID,DEPTID,SAL,
MAX(SAL) OVER(ORDER BY SAL DESC ROWS CURRENT ROW) AS C3 
FROM EMP01;

 

 

 

각 행의 급여(SAL)를 기준으로 정렬하고, 그 행에서 최대 급여를 계산하는 작업을 수행한다. 

 

ROWS CURRENT ROW
 현재 행만을 포함하는 윈도우 프레임을 지정하여 윈도우 프레임이 현재 행으로만 제한된다.

 

각 행에서 자신이 포함된 윈도우 프레임 내에서 최대 SAL 값을 계산하지만, 현재 윈도우 프레임이 CURRENT ROW로만 제한되었기 때문에 현재 행의 SAL 값을 그대로 반환한다.