sqld[빈출 개념 복습05/05~05/28]

*서브쿼리

---

서브 쿼리 종류	설명
Un-Correlated(비연관) 서브 쿼리	서브 쿼리가 메인 쿼리 칼럼을 갖고 있지 않는 형태의 서브 쿼리다. 메인 쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용한다.
Correlated(연관) 서브 쿼리	서브 쿼리가 메인 쿼리 칼럼을 갖고 있는 형태의 서브 쿼리다. 일반적으로 메인 쿼리가 먼저 수행돼 읽혀진 데이터를 서브 쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용한다.

1. 단일 행 서브 쿼리

서브 커리 종류	설명
Single Row 서브 쿼리 (단일 행 서브 쿼리)	서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리를 의미한다. 단일 행 서브 쿼리는 단일 행 비교 연산자와 함께 사용된다. 단일 행 비교 연산자에는 =, <, <=, >, >=, <> 등이 있다.
Multi Row 서브 쿼리 (다중 행 서브 쿼리)	서브 쿼리의 실행 결과가 여러 건인 서브 쿼리를 의미한다. 다중 행 서브 쿼리는 다중행 비교 연산자와 함께 사용된다. 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
Multi Column 서브 쿼리 (다중 칼럼 서브 쿼리)	서브 쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브 쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

2. 다중 행 서브 쿼리

다중 행 연산자	설명
IN(서브 쿼리)	서브 쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다(Multiple OR 조건)
비교연산자 ALL(서브 쿼리)	서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. 비교 연산자로 '>'를 사용했다면 메인 쿼리는 서브 쿼리의 모든 결과 값을 만족해야 하므로, 서브 쿼리 결과의 최댓값보다 큰 모든 건이 조건을 만족한다.
비교연산자 ANY(서브 쿼리)	서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. 비교 연산자로 '>'를 사용했다면 메인 쿼리는 서브 쿼리의 값들 중 어떤 값이라도 만족하면 되므로, 서브 쿼리의 결과의 최소값보다 큰 모든 건이 조건을만족한다(SOME은 ANY와 동일함)
EXISTS(서브 쿼리)	서브 쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더이상 검색하지 않는다.

3. 다중 칼럼 서브 쿼리

다중 칼럼 서브 쿼리는 서브 쿼리의 결과로 여러 개의 칼럼이 반환돼 메인 쿼리의 조건과 동시에 비교되는 것을 의미한다. SQL Server에서는 지워되지 않는 문법이다.

소속팀별 키가 가장 작은 사람들의 정보를 출력하는 문제를 갖고 다중 칼럼 서브 쿼리를 알아보도록 한다. 소속팀별 키가 가장 작은 사람들의 정보는 GROUP BY를 이용해 찾을 수 있으므로 다음과 같이 SQL문을 작성할 수 있다.

SELECT TEAM_ID AS 팀코드, PLAYER_NAME AS 선수명, POSITION AS 포지션
     , BACK_NO AS 백넘버, HEIGHT AS 키
    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. 연관 서브 쿼리

연관 서브 쿼리(Correlated Subquery)는 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리다.

선수 자신이 속한 팀의 평균키보다 작은 선수들의 정보를 출력하는 SQL 문을 연관 서브 쿼리를 이용하여 작성해 보면 다음과 같다.

SELECT B.TEAM_NAME AS 팀명, A.PLAYER_NAME AS 선수명, A.POSITION AS 포지션
     , A.BACK_NO AS 백넘버, A.HEIGHT AS 키
    FROM PLAYER A, TEAM B
  WHERE A.HEIGHT < (SELECT AVG(X.HEIGHT)
                           FROM PLAYER
                         WHERE X.TEAM_ID = A.TEAM_ID
                      GROUP BY X.TEAM_ID)
  AND B.TEAM_ID = A.TEAM_ID
ORDER BY 선수명;

 

EXISTS 서브 쿼리는 항상 연관 서브 쿼리로 사용된다. 또한 EXISTS 서브 쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.

다음은 EXISTS 서브 쿼리를 사용해 '20220501'부터 '20220502' 사이에 경기가 있는 경기장을 조회하는 SQL 문이다.

SELECT A.STADIUM_ID AS ID, A.STADIUM_NAME AS 경기장명
    FROM STADIUM A
  WHERE EXISTS (SELECT 1 
                   FROM SCHEDULE X
                 WHERE X.STADIUM_ID = A.STADIUM_ID
                   AND X.SCHE_DATE BETWEEN '20220501' AND '20220502');

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

종류	설명
SELECT 절 - 스칼라 서브 쿼리(Scallar Subquery)	한 행, 한 칼럼만을 반환하는 서브 쿼리 ** 단일 행 서브 쿼리이기 때문에 결과가 2건 이상 반환되면 SQL 문은 오류를 반환한다.
FROM 절 - 인라인 뷰(Inline View) (중요)	인라인 뷰를 사용하면 서브 쿼리의 결과를 마치 테이블처럼 사용할 수 있다. 인라인 뷰는 쿼리 내에서 즉시 처리된다. (게시판 페이징 할때 유용하다 자세히 알아두기!)
HAVING 절	집계함수와 함께 사용될 때 그룹핑 된 결과에 대해 부가적인 조건을 주기 위해 사용한다.

 

6.집합 연산자

UNION	(합집합)여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
UNION ALL	(합집합)여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다. 일반적으로 여러 질의 결과가 상호 배타적인(Exclusive)일때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다. (결과의 정렬 순서에는 차이가 있을 수 있음)
INTERSECT	(교집합)여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
EXCEPT	(차집합)앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다. (일부 데이터베이스는 MINUS를 사용함)

*sql 문

SELECT 칼럼명1, 칼럼명2, ...
	FROM 테이블명1
[ WHERE 조건식 ]
[ GROUP BY 칼럼 or 표현식 ]
[ HAVING 그룹조건식 ]
집합연산자 -- ( UNION/UNION ALL/INTERSECT/MINUS or EXCEPT )
	SELECT 칼럼명1, 칼럼명2, ...
    	FROM 테이블명2
    [ WHERE 조건식 ]
    [ GROUP BY 칼럼 or 표현식 ]
    [ HAVING 그룹조건식 ]
    [ ORDER BY 칼럼 or 표현식 [ ASC or DESC ] ];

 

2. ROLLUP 함수(*빈출* 개념암기)

소그룹 간의 소계를 계산하는 ROLLUP 함수

-- 작성법 예제
SELECT B.DNAME,A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP B, DEPT A
  WHERE B.DEPTNO = A.DEPTNO
GROUP BY ROLLUP (B.DNAME, A.JOB);

 

3. CUBE 함수(*빈출*)

GROUP BY 항목 간 다차원적인 소계를 계산할 수 있는 CUBE 함수

CUBE는 결합 가능한 모든 값에 대해 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻는 장점이 있는 반면,

시스템에 부하를 많이 주는 단점이 있다.

-- 작성법 예제
SELECT B.DNAME,A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP B, DEPT A
  WHERE B.DEPTNO = A.DEPTNO
GROUP BY CUBE (B.DNAME, A.JOB);

 

4. GROUPING SETS 함수(*암기*)

원하는 부분의 소계만 손쉽게 추출하여 계산할 수 있는 GROUPING SETS 함수

-- 작성법 예제 Oracle

SELECT CASE GROUPING (B.DNAME) 
            WHEN 1 THEN 'All Departments' 
            ELSE B.DNAME END AS DNAME
      ,CASE GROUPING (A.JOB)
            WHEN 1 THEN 'ALL Jobs'
            ELSE A.JOB END AS JOB
      ,COUNT (*) AS EMP_CNT, SUM (A.SAL) AS SAL_SUM
    FROM EMP A, DEPT B
  WHERE B.DEPTNO = A.DEPTNO
GROUP BY GROUPING SETS (B.DNAME, A.JOB)
ORDER BY B.DNAME, A.JOB;

 

제4절 .윈도우 함수

*표 싹암기*

윈도우 함수 종류		설명
순위(RANK) 관련 함수	RANK	- 특정항목 및 파티션에 대해서 순위를 계산한다. - 동일한 순위는 동일한 값이 부여된다.
	DENSE_RANK	- 동일한 순위를 하나의 건수로 계산한다.
	ROW_NUMBER	- 동일한 순위에 대해서 고유의 순위를 부여한다.
집계(AGGREGATE) 관련 함수	SUM	- 합계를 계산한다.
	AVG	- 평균을 계산한다.
	COUNT	- 행 수를 계산한다.
	MAX, MIN	- 최대값과 최소값을 계산한다.
순서 관련 함수	FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수를 사용해서 같은 결과를 구할 수 있다.
	LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구한다.
	LAG	- 이전 행을 가지고 온다.
	LEAD	- 윈도우에서 특정 위치의 행을 가지고 온다. - 기본값은 1이다.
비율 관련 함수	CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. - 누적 분포상에 위치를 0~1사이의 값을 가진다.
	PERCENT_RANK	- 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다.
	NTILE	파티션 별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회한다.
	RATIO_TO_REPORT	파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회한다.
통계 분석 관련 함수	CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

◇ 윈도우 함수 구조

-- 윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.

SELECT WINDOW_FUNCTION (ARGUMENTS) 
  OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;

*1. 계층형 질의*

종류	설명
START WITH	계층 구조 전개의 시작 위치를 지정하는 구문, 즉 루트 데이터를 지정한다(액세스)
CONNECT BY	다음에 전개될 자식 데이터를 지정하는 구문, 자식데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다.(조인)
PRIOR	CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다.  (FK) = PRIOR (PK) 형태를 사용하면 (부모 -> 자식) 순방향 전개를 한다. (PK) = PRIOR (FK) 형태를 사용하면 (자식 -> 부모) 역방향 전개를 한다.
NOCYCLE	데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면, 이것을 가리켜 사이클(Cycle)이 발생했다고 한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. NOCYCLE을 추가하면오류를 발생시키지 않고 사이클이 발생한 이후의 데이터를 전개하지 않는다.
ORDER SIBLINGS BY	형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다(필터링)

 

제7절. PIVOT 절과 UNPIVOT 절

---

1. 개요

PIVOT은 회전시킨다는 의미를 갖고 있다. PIVOT 절은 행을 열로 회전시키고, UNPIVOT 절은 열을 행으로 회전시킨다.

 

2. PIVOT 절

PIVOT 절은 행을 열로 전환 한다. PIVOT 절의 구문은 아래와 같다.

PIVOT [XML]
        (aggregate_function (expr) [[AS] alias]
       [,aggregate_function (expr) [[AS] alias]]...
         FOR {column | (column [, column]...)}
         IN ({{{expr | (expr [, expr]...)} [[AS] alias]}...
             | subquery
             | ANY [, ANY]...
             })
         )

PIVOT 절 ( 행 -> 열 )
aggregate_function	집계할 열을 지정
FOR	PIVOT 할 열을 지정한다.
IN	PIVOT 할 열 값을 지정한다.

PIVOT 절은 집계함수와 FOR절에 지정되지 않은 열을 기준으로 집계되기 때문에 인라인 뷰를 통해 사용할 열을 지정해야 한다.

[예제 1]

-- [예제 1] PIVOT 절을 사용한 쿼리

SELECT *
    FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
  PIVOT (SUM (SAL) FOR DEPTNO IN (10, 20, 30)
ORDER BY 1;

-- [출력 화면]

JOB        10     20     30
----       ---    ---    ---
ANALYST          6000		
CLERK      1300  1900     950
MANAGER    2450  2975    2850
PRESIDENT  5000	
SALESMAN                 5600

5 행이 선택됐습니다.

다음 쿼리는 인라인 뷰에 yyyy표현식을 추가한 것이다. 행 그룹에 yyyy 표현식이 추가된 것을 확인할 수 있다.

[예제 2]

-- [예제 2]

SELECT *
   FROM (SELECT TO_CHAR (HIREDATE, 'YYYY') AS YYYY, JOB, DEPTNO, SAL FROM EMP)
  PIVOT (SUM (SAL) FOR DEPTNO IN (10, 20, 30))
ORDER BY 1, 2;

-- [출력 화면]

YYYY    JOB       10     20      30
----    ----      ---    ---    ---
1980    CLERK            800
1981    ANALYST         3000
1981    CLERK                    950
1981    MANAGER   2450  2975    2850
...

9행이 선택됐습니다.

다음 쿼리는 집계함수와 IN 절에 별칭을 지정했다. 별칭을 지정하면 결과 집합의 열 명이 변경된다.

[예제 3]

-- [예제 3]

SELECT *
   FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
  PIVOT (SUM (SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))
ORDER BY 1;

-- [출력 화면]

JOB        D10_SAL     D20_SAL     D30_SAL
----       -------     -------     -------
ANALYST                 6000		
CLERK       1300        1900           950
MANAGER     2450        2975          2850
PRESIDENT   5000	
SALESMAN                              5600

5 행이 선택됐습니다.

SELECT 절에 부여된 열 명을 지정하면 필요한 열만 조회할 수 있다.

[예제 4]

-- [예제 4]

SELECT JOB, D20_SAL
    FROM(SELECT JOB, DEPTNO, SAL FROM EMP)
   PIVOT(SUM (SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))
   WHERE D20_SAL > 2500
ORDER BY 1;

-- [출력 화면]

JOB         D20_SAL
----        ------
ANALYST      6000
MANAGER      2975

2 행이 선택됐습니다.

 

3. UNPIVOT 절

UNPIVOT 절은  PIVOT 절과 반대로 동작한다. 열이 행으로 전환된다.  UNPIVOT 절의 구문은 아래와 같다.

UNPIVOT [{INCLUDE | EXCLUDE} NULLS]
        (   {column | (column [, col]...)}
        FOR {column | (column [, col]...)}
        IN  {column | (column [, col]...)} [AS {literal | (literal [, literal]...)}]
         [, {column | (column [, col]...)} [AS {literal | (literal [, literal]...)}]]...
           )
        )
        
-- 작성법 예시 

SELECT *
    FROM ( 피벗 대상 쿼리문 )
UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )

UNPIVOT 절 ( 열 -> 행 )
UNPIVOT column	UNPIVOT된 값이 들어갈 열을 지정한다.
FOR	UNPIVOT된 값을 설명할 값이 들어갈 열을 지정한다.
IN	UNPIVOT할 열과 설명할 값의 리터럴 값을 지정한다.