문과생이 공대에서 살아남기

1. WINDOW FUNTION

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

분석함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)으로도 불림

중첩해서는 사용이 불가능하나 서브쿼리에서 사용 가능

 

가. WINDOE FUNTION의 종류

1. 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER 대부분의 DBMS에서 지원
2. 집계 관련 함수 : SUM, MAX, MIN, AVG, COUNT 대부분의 DBMS에서 사용하나 SQL SERVER의 경우 OVER절 내의 ORDER BY 구문 지원하지 않음
3. 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD ORACLE에서만 지원되는 함수
4. 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT, 1,2번은 전부, 3번은 표준에는 없지만 지원, 4번은 ORACLE에서만 지원
5. 선형분석 포함하는 통계 함수

 

나. SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS)

OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] )

FROM 테이블 명;

- WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.

- ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.

- PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

- ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.

- WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.

다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.

 

1. BETWEEN 사용 타입

ROWS | RANGE BETWEEN

UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

AND

UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

 

2. BETWEEN 미사용 타입

ROWS | RANGE

UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

 

 

2. 그룹 내 순위 함수

 

가. RANK 함수

특정 항목에 대한 순위를 구하는 함수, 특정 범위내에서 순위를 구할수도 있음, 동일값에 대해서는 동일한 순서 부여

예제) RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK : 전체 중에 연봉 순위

 RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK : 그룹별로 나눈 후 연봉 순위

 

나. DENSE_RANK 함수

동일한 건수를 하나의 건수로 취급

동점이 있을 시 같은 등수 예를 들어 순위는 1, 2, 2, 3, 4, 5, 5, 6

 

다. ROW_NUMBER 함수

동일한 값이라도 고유의 순서를 부여함 예를 들어 순위는 1, 2, 3, 4, 5, 6, 7, 8 로 같은 값이여도 유니크한 순위를 정함

ORACLE 경우는 ROWID가 적은 행부터 나옴

 

3. 일반 집계 함수

 

가. SUM 함수

누적값 구할 수 있음

예제) SELECT    MGR, ENAME, SAL,

SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM

FROM EMP;

 

RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

 

나. MAX 함수

INLINE VIEW로 파티션별 최대값을 가진 행 추출 가능

예제) SELECT MGR, ENAME, SAL

FROM (SELECT MGR, ENAME, SAL,

MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL

    FROM EMP)

WHERE SAL = IV_MAX_SAL ;

각 매니져 별로 연봉이 가장 높은 직원을 추출

 

다. MIN 함수

예제) SELECT MGR, ENAME, HIREDATE, SAL,

              MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN

FROM EMP;

 

라. AVG 함수

파티션별 ROWS 윈도우를 통해 평균값 구할 수 있음

예제) SELECT MGR, ENAME, HIREDATE, SAL,

     ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG

FROM EMP;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다.

(ROWS는 현재 행의 앞뒤 건수를 말하는 것임)

 

마. COUNT 함수

예제) SELECT ENAME, SAL,

     COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT

FROM EMP;

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다.

(RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)

 

4. 그룹 내 행 순서 함수

 

가. FIRST_VALUE 함수

파티션별 윈도우에 가장 먼저 나온 값, SQL SERVER 미지원(MIN 활용 가능)

공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW나 ORDER BY 절에 칼럼 추가

예제) SELECT DEPTNO, ENAME, SAL,

     FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP

 FROM EMP;

 

나. LAST_VALUE 함수

파티션별 윈도우에 가장 나중에 나온 값, SQL SERVER 미지원(MAX 활용 가능)

공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW나 ORDER BY 절에 칼럼 추가

예제) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME)

    OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR

 FROM EMP;

 

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

 

다. LAG 함수

파티션 별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있음, SQL SERVER 미지원

NVL, ISNULL 기능과 같음

예제) SELECT ENAME, HIREDATE, SAL,

     LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL

 FROM EMP

 WHERE JOB = 'SALESMAN'

 

LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다.

 

[실행 결과] ENAME  HIREDATE   SAL   PREV_SAL

 ---------  ------------- -------- ----------

ALLEN    1981-02-20 1600              0

WARD    1981-02-22 1250              0

TURNER 1981-09-08 1500        1600

MARTIN 1981-09-28 1250        1250

 

라. LEAD 함수

파티션 별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있음, SQL SERVER 미지원

NVL, ISNULL 기능과 같음

예제) SELECT ENAME, HIREDATE,

     LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"

 FROM EMP;

 

[실행 결과] ENAME HIREDATE NEXTHIRED

----------- ------------ ---------------

ALLEN    1981-02-20 1981-02-22

WARD    1981-02-22 1981-04-02

TURNER 1981-09-08 1981-09-28

MARTIN 1981-09-28

 

5. 그룹 내 비율 함수

 

가. RATIO_TO_REPORT 함수

파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함

결과 값은 > 0 & <= 1이고, 합계는 1, SQL SERVER 미지원

예제 ) ELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R

 FROM EMP

 WHERE JOB = 'SALESMAN';

전체 합 중에 로우 별 연봉이 차지하는 비율이 나오게 됨, 개별 RATIO의 전체 합은 1

 

나. PERCENT_RANK 함수

윈도우에 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행 순서별 백분율을 구함

결과 값은 > 0 & <= 1이고,  SQL SERVER 미지원

예제) SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R

 FROM EMP;

3개의 로우가 된다면 0, 0.5, 1 순서로 되고, 5개의 로우라면 0, 0.25, 0.5, 0.75, 1로 추출됨 ORDER BY역할

 

다. CUME_DIST 함수

파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율

결과 값은 > 0 & <= 1이고,  SQL SERVER 미지원

예제) SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST

 FROM EMP;

5개의 로우가 선택되면 0.2, 0.4, 0.6, 0.8, 1의 값이 추출 ORDER BY역할

동일 순서면 뒤의 행의 함수 결과 값을 따름 예를 들면, 0.4, 0.4, 0.6, 0.8, 1

 

라. NTILE 함수

파티션별 전체 건수를 ARGUMENT 값으로 N 등분 한 결과, 조를 나누는 것과 같음

예제) SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE

 FROM EMP;

4개의 파티션으로 나뉘면, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4 등으로 로우가 추출되어서 파티션별 조를 정할 수 있음

 

1. 데이터 분석 개요

 

가. AGGREGATE FUNCTION

COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들 포함

 

나. GROUP FUNTION

레벨별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 단계를

그룹함수를 이용하면 한번에 해결 가능, CASE 함수를 이용하면 쉽게 원하는 포맷 보고서 작성 가능

• ROLLUP 함수 : 소그룹 간의 소계를 계산, 확장된 형태, 병렬로 수행가능하기에 효과적임, 계층적 분류를 포함하고 있는 데이터 집계에 적합
• CUBE 함수 : GROUP BY 항목들 간 다차원적인 소계를 계산, 모든값에 대한 다차원적인 집계 생성, 시스템 부하를 줄수 있음
• GROUPING SET 함수 : 특정 항목에 대한 소계를 계산, 원하는 부분만 추출가능
• Rollup 이나 Cube에 의한 소계가 계산된 결과에는 Grouping = 1 이 표시되고 그 외의 결과에는 Grouping = 0 으로 표시

 

다. WINDOW FUNTION

분석함수, 순위함수로 불림, 데이터웨어하우스에서 발전한 기능

 

2. ROLLUP 함수

지정된 그룹 칼럼의 리스트는 SUBTOTAL을 생성하기 위해 사용되어짐

생성되는 SUBTOTAL : 칼럼수 + 1

인수는 계층구조라 인수 순서가 바뀌면 수행결과 바뀌니 인수의 순서에도 주의해야함

STEP 1. 일반적인 GROUP BY 절 사용 + ORDER BY 절 사용

STEP 2. ROLLUP 함수 사용 + ORDER BY절 사용

→ 그룹 함수 수행시 생성되는 표준집계 + 기준칼럼에 대한 소계 + 전체의 마지막 소계

STEP 3. GROUPING 함수 사용 + CASE 함수 사용(ORACLE경우는 DECODE함수 사용)

GROUPING(EXPR) = 1, 그외의 결과는 GROUPINT(EXPR) = 0 → CASE함수로 소계에 나타내는 필드에 문자열 지정 가능 보고서 작성시 유용함

예제) SELECT    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,

CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,

COUNT(*) "Total Empl",

SUM(SAL) "Total Sal"

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB);

Oracle의 경우는 DECODE 함수를 사용해서 좀더 짧게 표현할 수 있다.

SELECT     DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,

COUNT(*) "Total Empl", SUM(SAL) "Total Sal"

FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

 

STEP 4-1. 일부 칼럼에 ROLLUP 함수 사용

예제) GROUP BY DNAME, ROLLUP(JOB) 최종 소계 결과는 나오지 않음

STEP 4-2. 칼럼을 결합하여 ROLLUP 함수 사용

예제) GROUP BY ROLLUP (DNAME, (JOB, MGR)) 하나의 집합으로 간주하여 칼럼별 집계를 구하지 않음

 

3. CUBE 함수

결합 가능한 모든 값에 대하여 다차원 집계를 생성

그룹핑 칼럼의 순서를 바꿔서 한번 더 쿼리를 추가 수행하고 양쪽 쿼리에서 총계를 모두 생성함으로 연산 대상이 많아 시스템 부하의 단점이 있음

계층구조가 아니라 평등 관계임으로 인수 순서, 정렬순서는 바뀔 수있어도 데이터 결과는 같음

STEP 1. CUBE 함수 이용

칼럼이 가질 수 있는 모든 경우의 수예 대하여 SUBTATAL이 생성됨으로 2의 컬럼수에 대한 레벨의 SUBTATAL이 발생

ROLLUP함수에 비해 업무별 집계를 표시한 추가적인 데이터가 생성

STEP 2. UNION ALL 사용

CUBE함수를 사용하면 반복 엑세스 하는 부분을 한번으로 줄일 수 있음, 수행속도 및 자원 사용율을 개선하고 가독성을 높힘

 

4. GROUPING SET 함수

다양한 소계 집합을 만들 수 있음, 여러 번 반복하지 않아도 원하는 결과 얻을 수 있음

인수별 개별 집계 가능하고 평등관계라 인수 순서 중요하지 않음

각 컬럼별의 총계들을 구함

 

STEP 1. 3개의 인수를 이용

괄호로 묶은 집합 별로 괄호 내는 계층 구조가 아닌 하나의 데이터로 간주하여 집계를 구할 수 있음

 

- Rollup(a,b,c) -

{a,b,c} 그룹의 합계

{a.b} 그룹의 합계

{a} 그룹의 합계

{} 전체 테이블에 대한 합계

 - Cube(a,b.c) -

Cube(a,b,c)

{a,b,c} 그룹의 합계 

{a,b}, {a,c}, {b,c} 그룹의 합계

{a}, {b}, {c} 그룹의 합계

{} 전체 테이블에 대한 합계

하나의 SQL문안에 포함되어 있는 또 다른 SQL문

알려지지 않은 기준을 이용해 검색하기 위해 사용

서브쿼리는 메인쿼리의 칼럼을 모두 사용 가능

메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음 (필요하다면 JOIN으로 변경하거나 스칼라 서브쿼리를 사용해야함)

서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과로 생성되어 1:M의 관계에서도 결과는 1이 됨

 

주의사항

1. 서브쿼리를 괄호로 감싸서 사용해야함
2. 단일행 또는 복수행 비교 연산자와 함께 사용가능

단일 행 비교연산자는 서브쿼리 결과가 반드시 1건 이하여야 하고

복수 행 비교연산자는 결과 건수와 상관없음

3. 서브쿼리는 ORDER BY를 사용하지 못함, 메인쿼리의 마지막 문장에 위치해야함
4. 사용 가능한 곳 - SELELC, FROM, WHERE, HAVING, ORDER BY, INSERT의 VALUES절, UADATE의 SET절

 

동작하는 방식에 따른 서브쿼리 분류

• UN-CORRELATED (비연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브쿼리

메인쿼리에 값(서브쿼리가 실행된결과)을 제공하기 위한 목적으로 사용

• CORREGATED(연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리

      메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리 조건에 맞는지 확인하고자 할 때 주로 사용

 

반환되는 데이터의 형태에 따른 서브 쿼리 분류

 

 

1. 단일 행 서브쿼리

단일 행 비교 연산자 ( =, >, >=, <, <=, <>) 와 함께 사용할 때는 서브쿼리 결과가 반드시 1건 이하여야 함

2건 이상을 반환하면 SQL문은 실행시간 오류가 발생, 컴파일할 때 알수 없는 오류

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

 FROM PLAYER

 WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER)

 ORDER BY PLAYER_NAME;

 

2. 다중 행 서브쿼리

결과가 2건 이상이면 다중 행 비교 연산자 ( IN, ALL, ANY, SOME)을 사용해야 함

다중행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용가능

 

3. 다중 칼럼 서브쿼리

여러 개의 칼럼이 반환된어 메인쿼리의 조건과 동일하게 비교되는 것을 의미, SQL SERVER에서는 지원되지 않음

예제)  SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

  FROM PLAYER

  WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)

  ORDER BY TEAM_ID, PLAYER_NAME;

 

4. 연관 서브 쿼리

서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

EXISTS 서브쿼리 : 만족하는 1건만 찾음 추가검색 진행하지 않음

예제) SELECT STADIUM_ID ID, STADIUM_NAME 경기장명

FROM STADIUM A

WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHE_DATE BETWEEN '20120501' AND '20120502');

 

5. 그 밖에 위치에서 사용 하는 서브쿼리

 

가. SELECT 절

스칼라 서브쿼리란 ? 한 행, 한 컬럼만을 반환하는 서브쿼리, 단일 행 서브쿼리 (2건이상이면 오류)

예제) SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키

 FROM PLAYER P;

 

나. FROM 절

인라인뷰(INLINE VIEW), 동적뷰 (DYNAMIC VIEW)

서브쿼리 결과가 동적으로 생성된 테이블인 것 처럼 사용 가능, 데이터 베이스에는 해당 정보가 저장되지 않음

조인방식을 사용하는 것과 같아서 인라인 뷰의 칼럼은 SQL에 자유롭게 참조 가능

ORDER BY 사용가능,

TOP-N 쿼리 : 인사인 뷰에서 먼저 정렬을 수행하고 결과 중에 일부 데이터만 추출하는 것, ORACLE은 ROWNUM으로 사용가능

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC)

WHERE ROWNUM <= 5;

• 데이터까지 전부 추출하고싶으면 RANK 함수 사용

 

다. HAVING 절

그룹핑 결과에 대한 부가적인 조건 주기 위해서

 

라. UPDATE 문의 SET 절

새로운 테이블에 이전 테이블의 값을 옮기고 싶을 때

NULL값을 반환할 경우 칼럼 결과가 NULL이 될수 있음으로 주의해야함

 

마. INSERT 문의 VALUES 절

새로운 테이블에 이전 테이블의 값, 또는 수정값을 옮길 때

 

6. 뷰(VIEW)

가상 테이블(VIRTUAL TABLE) 실제 데이터를 가지고 있지 않음, 뷰 정의(VIEW DEFINITION)을 가지고 있음

뷰 정의를 참조해서 질의를 재 작성하여 질의를 수행

이미 존재하는 뷰를 참조해서도 생성 가능

실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 가진 DBMS도 있음

 

장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 프로그램 변경 없음
2. 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작업할 수 있음, 해당 SQL을 자주 사용 할때 뷰를 이용하면 편리하게 사용
3. 보안성 : 감추고 싶은 정보를 뷰를 생성할때 제외하고 생성 가능

 

예제) CREATE VIEW V_PLAYER_TEAM

AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID;

 

제거하기 : DROP VIEW V_PLAYER_TEAM;

        DROP VIEW V_PLAYER_TEAM_FILTER;