Two-Rabbits

윈도함수

윈도함수는 데이터베이스를 온라인에서 분석 처리용으로 사용하기 위해 만들어진 함수입니다. 다차원에서 데이터 분석을 할수 있도록 도와준다하여 OLAP(OnLine Analytical Processing)함수라고도 불립니다.

SELECT 컬럼, 함수명(파라미터) OVER ([PARTITION BY 컬럼])

[ORDER BY 컬럼] 

FROM 테이블명

윈도 함수는 SELECT 뒤에 사용되며 위에서 나온 PARTITION BY는 범위를 지정하는 쿼리로 선택항목입니다.

윈도함수는 순위함수, 행순서 함수, 그룹내 비율함수가 있습니다. 

순위함수

순위함수는 레코드 값을 계산하는 함수입니다.

- RANK : 특정 컬럼에 대한 순위를 구하는 함수로 동일 레코드가 존재하면 동일 순위를 준 뒤 다음값이 후순위로 넘어갑니다.

만약 4명 중 2등이 2명이면 1등 , 2등, 2등, 4등 이렇게 순위가 매겨집니다.

- DENSE_RANK : 특정 컬럼에 대한 순위를 구하는 함수로 동일 레코드가 존재하면 동일 순위를 준 뒤 RANK와는 달리 다음 값이 후순위로 넘어가지 않습니다.

만약 4명 중 2등이 2명이면 1등 , 2등, 2등, 3등 이렇게 순위가 매겨집니다.

- ROW_NUMBER : 특정 컬럼에 대한 순위를 구하는 함수로 동일 레코드가 존재해도 연속값을 부여합니다.

만약 4명 중 2등이 2명이면 1등 , 2등, 3등, 4등 이렇게 순위가 매겨집니다.

예제로 사용할 SW연봉 테이블은 다음과 같습니다.

SELECT 이름, 연봉,

          RANK( ) OVER (ORDER BY 연봉 DESC) RANK,

          DENSE_RANK( ) OVER (ORDER BY 연봉 DESC) DENSE_RANK,

          ROW_NUMBER( ) OVER (ORDER BY 연봉 DESC) ROW_NUMBER, 

FROM SW연봉

ORDER BY 연봉 DESC;

행순서 함수

행순서 함수는 레코드 내에서 가장 먼저나오거나 가장 뒤에 나오는 값 또는 이후에 나올 값과 이전에 나온 값 등을 계산하는 함수입니다.

- FIRST_VALUE : 윈도에서 가장 먼저 나오는 값을 찾습니다.

- LAST_VALUE : 윈도에서 가장 나중에 나오는 값을 찾습니다.

- LAG : 윈도에서 이전의 값을 반환합니다.

- LEAD : 윈도에서 이후의 값을 반환합니다.

예제로 사용할 SW연봉 테이블은 아까와 같습니다.

SELECT 이름, 연봉,

          FIRST_VALUE(이름) OVER (ORDER BY 연봉 DESC) FIRST_VALUE,

          LAST_VALUE(이름) OVER (ORDER BY 연봉 DESC) LAST_VALUE,

          LAG(이름) OVER (ORDER BY 연봉 DESC) LAG,

          LEAD(이름) OVER (ORDER BY 연봉 DESC) LEAD, 

FROM SW연봉

ORDER BY 연봉 DESC;

그룹 내 비율 함수

그룹 내 비율 함수는 백분율을 보여주는 등 통계를 보여주는 함수입니다.

- RATIO_TO_REPORT : 주어진 그룹에 대한 합을 기준으로 하여 행 값의 상대적인 비율을 반환하는 함수이며 총합은 1이 됩니다. 각 행의 결과 값은 0에서 1사이의 값이 됩니다.

- PERCENT_RANK : 주어진 결과 값에서 가장 먼저 나오는 값을 0, 가장 늦게 나오는 값을 1로하여 행의 순서를 구하는 함수입니다. 이 함수도 결과 값이 0에서 1사이가 나옵니다.

SW연봉 테이블을 가지고 그룹내 비율 함수를 알아봅시다.

SELECT 이름, 연봉,

          RATIO_TO_REPORT( ) OVER (ORDER BY 연봉 DESC) RATIO_TO_REPORT,

          PERCENT_RANK( ) OVER (ORDER BY 연봉 DESC) PERCENT_RANK,

FROM SW연봉

ORDER BY 연봉 DESC;

→ 박비의 RATIO_TO_REPORT는 박비의 연봉인 6000을 총 연봉인 20000으로 나눈 값(6000/20000=0.3)이 됩니다. 그리고 PERCENT_RANK의 경우 후순위로 넘어가는 RANK를 기준으로 순서별 백분위를 구하게 됩니다.

그룹함수

그룹함수는 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라서 그룹화 후 결과를 출력하는 함수입니다.

그룹함수는 ROLLUP, CUBE, GROUPING SETS총 세가지 방식으로 나눠집니다.

ROLLUP함수

ROLLUP함수는 중간 집계 값을 산출하기 위해서 사용되는 그룹함수입니다.

최종 결과 값으로는 지정했던 컬럼의 수보다 하나 큰 레벨의 중간 집계가 나오게 됩니다.

ROLLUP은 지정된 컬럼에 대해서 계층별로 구성되기 때문에 순서에 영향을 받습니다. 즉, 순서가 변경되면 ROLLUP의 결과도 달라지게 됩니다. 

SELECT 컬럼, 그룹함수

FROM 테이블명

[WHERE 조건]

GROUP BY 컬럼 ROLLUP 컬럼

[HAVING 조건]

[ORDER BY 컬럼];

ROLLUP은 다음과 같은 형식으로 사용하면 됩니다.

예제를 통해 ROLLUP을 알아봅시다.

다음은 회사 테이블로 부서별로 연봉이 나와있습니다.

이 테이블을 ROLLUP을 이용해 확인해봅시다.

SELECT 부서, 직위, SUM(연봉)

FROM 회사

GROUP BY ROLLUP(부서, 직위);

다음 쿼리를 실행하게 되면 부서와 직위가 같은 사람의 연봉의 합에 대한 집계와 부서에 대한 연봉합, 전체 연봉에 대한 합이 나오게 됩니다.

→ 다음 쿼리를 실행하게 되면 부서와 직위가 같은 사람의 연봉의 합에 대한 집계와 부서에 대한 연봉합, 전체 연봉에 대한 합이 나오게 됩니다.

IT부서의 부장 직위를 가진 사람이 2명인데 둘다 각각 연봉이 4000이므로 두번째 행의 연봉의 합이 8000이 됩니다. 부서별 직위에 대한 집계가 끝나면 부서의 모든 연봉의 합을 구하고, 그마저도 끝나면 총합을 구합니다.

CUBE함수

CUBE함수는 결합이 가능한 모든 값에 대한 다차원 집계를 생성하는 그룹함수입니다.

모든 결과를 보는데에는 용이하지만 연산이 많아 시스템에 부담을 줍니다.  

SELECT 컬럼, 그룹함수

FROM 테이블명

[WHERE 조건]

GROUP BY 컬럼 CUBE 컬럼

[HAVING 조건]

[ORDER BY 컬럼];

예제를 통해 CUBE함수에 대해서 알아봅시다.

아까 이용했던 회사 테이블로 CUBE연산을 진행해 봅시다.

SELECT 부서, 직위, SUM(연봉)

FROM 회사

GROUP BY CUBE(부서, 직위);

→ 다음 쿼리를 실행하게 되면 CUBE의 컬럼인 부서와 직위로 나눌 수 있는 모든 경우의 수에 대해서 연봉의 합 결과를 얻을수 있습니다.

GROUPING SETS함수

GROUPING SETS함수는 집계 대상에 대해서만 결과를 얻을 수 있는 그룹함수입니다.

순서가 결과에 영향을 주는 ROLLUP과 CUBE와는 달리 순서가 상관없습니다.  

SELECT 컬럼, 그룹함수

FROM 테이블명

[WHERE 조건]

GROUP BY 컬럼 GROUPING SETS 컬럼

[HAVING 조건]

[ORDER BY 컬럼];

 회사 테이블로 GROUPING SETS연산을 진행해 봅시다.

SELECT 부서, 직위, SUM(연봉)

FROM 회사

GROUP BY GROUPING SETS(부서, 직위)

→ 다음 쿼리의 실행 결과로 각 부서별 연봉의 합과 직위별 연봉의 합을 결과로 얻을수 있습니다.

집계함수

집계함수는 여러 행또는 전체 행으로 부터 하나의 결과 값을 반환하는 함수입니다.

집계함수는 SELECT문 또는 HAVING에서 사용 됩니다.

SELECT문은 최종 결과로 나올 컬럼값을 나열하거나 집계함수를 사용하고, HAVING문은 GROUP BY로 그룹화 할 대상의 컬럼을 준 뒤 그룹화된 후의 조건을 지정할 수 있다는 특징을 가지고 있습니다. 

HAVING문과 WHERE문의 쓰임새는 비슷하지만 HAVING문은 그룹화 된 결과에 대한 조건을 지정하며 집계함수를 사용할 수 있습니다.

SELECT 컬럼, 집계함수

FROM 테이블명

[WHERE 조건]

GROUP BY 컬럼

[HAVING 조건, 집계함수]

사용할 수 있는 집계함수의 종류와 사용 방법은 다음과 같다.

집계함수에서 예제로 사용할 학생 테이블은 다음과 같다.

- COUNT : 여러 행이 있는 줄 수

SELECT COUNT(*)

FROM 학생;

→ 학생테이블에 있는 모든 학생의 숫자를 구하는 SQL쿼리문이다.

SELECT COUNT(이름)

FROM 학생

WHERE 국어 >= 90;

→ 학생테이블에 있는 국어 점수가 90점 이상인 학생의 숫자를 구하는 쿼리문이다.

- SUM : 여러 행이 있는 선택된 컬럼 간의 합계

SELECT SUM(국어), SUM(영어)

FROM 학생;

→ 학생테이블의 국어 점수를 모두 더한 값과 영어 점수를 모두 더한값을 각각 구한다.

- AVG : 여러 행이 있는 선택된 컬럼 간의 평균

SELECT AVG(국어), AVG(영어)

FROM 학생;

→ 학생테이블의 국어 점수의 평균과 영어 점수의 평균을 각각 구한다.

- MAX : 여러 행이 있는 선택된 컬럼 중 최대값 

SELECT MAX(국어), MAX(영어)

FROM 학생;

→ 학생테이블의 국어 점수와 영어 점수의 최대값을 각각 알려준다.

- MIN : 여러 행이 있는 선택된 컬럼 중 최솟값

SELECT MIN(국어), MIN(영어)

FROM 학생;

→ 학생테이블의 국어 점수와 영어 점수의 최솟값을 각각 알려준다.

- STDDEV : 여러 행의 선택된 컬럼 간의 표준편차

- VARIAN : 여러 행의 선택된 컬럼 간의 분산

표준편차와 분산의 경우는 자주 쓰이지 않으므로 생략합니다. 사용방법은 다른 집계함수들과 같습니다.

트리거

트리거는 지정된 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트가 발생하면 자동으로 실행되는 프로그램입니다.

트리거는 언제 실행되는지에 따라 두가지로 분류 됩니다.

데이터 변화가 생길때마다 실행되는 행 트리거가 있고, 한번 만 실행이되는 문장 트리거로 나눠져 있습니다. 

또한 다른 절차형 SQL인 프로시저나 사용자 정의함수와는 다르게 외부 변수가 사용되지 않는다는 특징을 가지고 있습니다.

이제 트리거의 구조에 대해서도 알아봅시다.

트리거의 선언부 (DECLARE)에서는 트리거의 이름과 변수, 데이터 타입 등을 정의합니다.

CREATE TRIGGER PUT_EMPLOYEE_HIST

CREATE TRIGGER을 이용해 PUT_EMPLOYEE_HIST라는 트리거를 생성하였습니다. 

다음은 트리거에서만 나타나는 특징인 이벤트부 (EVENT)입니다.

이벤트부에서는 트리거가 전(BEFORE)에 실행되는지 끝난 후(AFTER)에 실행되는지를 정하는 타이밍과 INSERT, UPDATE, DELETE 중 어떤 SQL 이벤트가 일어날지를 명시하게 됩니다. 

AFTER UPDATE OR DELETE
ON EMPLOYEE
FOR EACH ROW

다음 코드는 EMPLOYEE의 수정 및 삭제가 발생할 경우에 PUT_EMPLOYEE_HIST 트리거가 실행되도록 이벤트를 지정하였습니다. 

다음으로는 시작부(BEGIN)와 종료부(END)입니다. 

다른 절차형 SQL과 마찬가지로 시작부와 종료부는 다수의 실행을 제어하는 기본 단위입니다. 반드시 시작부가 나오면 종료부로 마무리 지어야합니다.

BEGIN은 선언부의 뒤에 오며 선언부를 제외한 프로시저의 모든 구조들은 BEGIN과 END사이에 정의되어야합니다.  

제어부 (CONTROL)는  조건문인 IF문과 CASE문을 사용해 문장을 처리합니다.

트리거의 SQL에서는 SELECT, INSERT, DELETE, UPDATE와 같은 DML이 주로 사용되며 가끔 DDL도 사용됩니다.

다음은 제어부와 SQL을 같이 사용한 형태입니다. 

IF UPDATING
 THEN
    INSERT INTO EMPLOYEE_HIST(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_STATUS)
    VALUES ( :OLD. EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, "부서이동");

ELSIF DELETING
  THEN
    INSERT INTO EMPLOYEE_HIST(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_DEPT)
    VALUES ( :OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, "퇴사");
END IF;

다음 코드는 IF문을 이용하여 UPDATE 될 경우와 DELETE될 경우 SQL 이벤트를 분리했습니다.

UPDATE는 EMPLOYEE에서 갱신 전 EMPLOYEE_ID와 EMPLOYEE_NAME을 그대로 쓰고 EMPLOYEE_STATUS를 부서이동으로 EMPLOYEE_HIST에 삽입하도록 합니다.

DELETE는 EMPLOYEE에서 값을 삭제 전에 EMPLOYEE_ID와 EMPLOYEE_NAME를 그대로 넣고 EMPLOYEE_DEPT에 퇴사를 로 EMPLOYEE_HIST를 삽입하는 동작을 하게 됩니다.

예외부(EXCEPTION)는 BEGIN~END문 사이의 SQL문의 실행에 예외 발생시 처리 방법을 정의하는 처리부분입니다.

예외부는 반드시 사용해야할 부분이 아니므로 생략해도 상관없습니다.

트리거는 이벤트 발생시 자동으로 호출되므로 별 다른 호출 방법이 존재하지 않습니다.