[SQLD] #2-2. SQL 활용

1. 서브 쿼리

: 하나의 쿼리 안에 존재하는 또 다른 쿼리
메인쿼리 > 서브쿼리

위치에 따라 서브쿼리는
SELECT 절 : 스칼라 서브쿼리
FROM 절 : 인라인 뷰
WHERE 절, HAVING 절 : 중첩 서브쿼리

1) 스칼라 서브쿼리

: 컬럼 대신 사용되므로 반드시 하나의 행 / 컬럼 만을 반환해야함

ex) 테이블에 존재하지 않는 PRODUCT_NAME 데이터 출력

SELECT M.PRODUCT_CODE,
	(SELECT S.PRODUCT_NAME
    	FROM PRODUCT S
     WHERE S.PRODUCT_CODE = M.PRODUCT_CODE) AS PRODUCT_NAME,
     ...

2) 인라인 뷰

FROM 절 등 테이블명이 올 수 있는 위치에 사용

SELECT 뭐1,
	뭐2,
    뭐3
FROM 테이블1,
	(SELECT 뭐4,
    	뭐5
     FROM 테이블2) S
WHERE 절

3) 중첩 서브쿼리

WHERE절 & HAVING 절에 사용

(1) 메인 쿼리와의 관계에 따라

• 비연관 서브쿼리 : 서브쿼리 내에 메인 쿼리의 컬럼이 존재 X
  - 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용

• 연관 서브쿼리 : 서브쿼리가 메인 쿼리의 칼럼을 가지고 있는 형태
  - 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용

(2) 반환하는 데이터 형태에 따라

• Single Row(단일 행) 서브쿼리 : 서브쿼리 결과가 항상 1건 이하인 서브쿼리
  단일 행 비교 연산자와 함께 사용 (등호, 부등호)

• Mulit Row(다중 행) 서브쿼리 : 실행 결과가 여러 건인 서브쿼리
  다중 행 비교 연산자와 함께 사용 (IN, ALL, ANY, SOME, EXISTS 등)
  

• Multi Column(다중 컬럼) 서브쿼리 : 실행 결과로 여러 컬럼을 반환
  메인쿼리의 조건절에 여러 칼럼을 동시에 비교 가능
  서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼

2. 뷰(View)

: 특정 SELECT 문에 이름을 붙여서 재사용 가능하도록 저장해놓은 오브젝트

• SQL에서 테이블처럼 사용 가능
  ex) 인라인 뷰를 뷰로 정의 => 인라인 뷰의 위치에 뷰 이름만 쓰면 됨
  - 가상 테이블임 -> 데이터 저장x, select문만 사용 가능

CREATE OR REPLACE VIEW DEPT_MEMBER AS
	SELECT A.DEPARTMENT_ID,
    	   A.DEPARTMENT_NAME,
           B.FIRST_NAME,
           B.LAST_NAME
    FROM DEPARTMENTS A
    LEFT OUTER JOIN EMPLOYEES B
    ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

뷰의 특징

• 보안성 : 보안이 필요한 컬럼을 제외한 별도의 뷰를 생성하여 제공 가능
• 독립성 : 테이블 구조가 변경돼도 뷰를 사용하는 응용프로그램은 변경X, 관련 뷰만 수정함
• 편리성 : 복잡한 쿼리 구문 -> 뷰명으로 단축

3. 집합 연산자

종류	특징
UNION ALL	각 쿼리의 결과 집합의 합집합, 중복된 행도 그대로 출력
UNION	각 쿼리의 결과 집합의 합집합, 중복된 행은 한 줄로 출력
INTERSECT	교집합, 중복된 행은 한 줄로 출력
MINUS / EXCEPT	앞에 있는 쿼리의 결과 - 뒤에 있는 쿼리의 결과 (차집합), 중복된 행은 한 줄로 출력

4. 그룹 함수

1) ROLLUP

• 소그룹 간의 소계 / 총계 계산
• GROUP BY ROLLUP(~)이런 식으로 작성

쿼리문	결과
ROLLUP(A)	A로 그룹핑, 총합계
ROLLUP(A,B)	A,B로 그룹핑 & A로 그룹핑 & 총합계
ROLLUP(A,B,C)	A,B,C로 그룹핑 & A,B로 그룹핑 & A로 그룹핑 & 총합계

2) CUBE

• 소그룹 간의 소계 / 총계 다차원적으로 계산 (= 모든 컬럼으로 나올 수 있는 경우의 그룹핑 한 번씩 다 함)

쿼리문	결과
CUBE(A)	A로 그룹핑, 총합계
CUBE(A,B)	A,B로 그룹핑 & A로 그룹핑 & B로 그룹핑 & 총합계
CUBE(A,B,C)	A,B,C로 그룹핑 & A,B로 그룹핑 & A,C로 그룹핑 & B,C로 그룹핑 & A로 그룹핑 & B로 그룹핑 & C로 그룹핑 & 총합계

• CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행해야 한다

3) GROUPING SETS

• 특정 항목에 대한 소계를 계산
• ROLLUP / CUBE와 달리 총합계 계산이 디폴트가 X
• 총합계 계산이 있다면 ()이나 ROLLUP(컬럼명)이 인자
• 인자값으로 ROLLUP / CUBE 사용 가능

쿼리문	결과
GROUPING SETS(A,B)	A로 그룹핑 & B로 그룹핑
GROUPING SETS(A,B,())	A로 그룹핑 & B로 그룹핑 & 총합계
GROUPING SETS(A,ROLLUP(B))	A로 그룹핑 & B로 그룹핑 & 총합계
GROUPING SETS(A,ROLLUP(B,C))	A로 그룹핑 & B,C로 그룹핑 & B로 그룹핑 & 총합계

ROLLUP 함수는 인수의 순서에 따라 결과 바뀜, CUBE & GROUPING SETS 함수는 인수의 순서에 결과 무관

4) GROUPING

• 소계를 나타내는 Row를 구분할 수 있게 함

• 앞에선 소계를 나타내는 Row에서 그룹핑 기준 컬럼 빼곤 모두 null로 표현
  but GROUPING 함수는 원하는 위치에 원하는 텍스트 출력 가능

• 소계를 나타내는 Row에선 GROUPING 함수의 결과값이 1, 나머지엔 0
  => CASE문으로 원하는 텍스트 출력 or DECODE() (오라클에선)

5. 윈도우 함수

• OVER 키워드와 함께 사용

역할	윈도우 함수
순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계	SUM, MAX, MIN, AVG,COUNT
행 순서

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

+) PARTITION BY 절 조건과 ORDER BY 절 조건이 충돌 시 ORDER BY 절로 정렬

• ARGUMENTS (인수) : 함수에 따라 0~N개의 인수
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음
• ORDER BY 절 : 어떤 항목에 대해 순위 지정할지

1) 순위 함수

• RANK 함수 : 특정 항목(컬럼) / 특정 범위(파티션) / 전체 데이터에 대한 순위를
  구하는 함수
  - 동일한 값에 대해서는 동일한 순위 (다음 순위 스킵)
• DENSE_RANK 함수 : RANK와 흡사하나, 동일한 순위를 하나의 건수로 취급 (스킵 X)
• ROW_NUMBER 함수 : 동일한 값이라도 고유한 순위를 부여

정리한다면 ROW_NUMBER는 중복을 허용하지 않고, DENSE_RANK/RANK 는 중복을 허용한다는 것이다.
DENSE_RANK/RANK의 차이점은 DENSE_RANK는 공동등수는 하나의 등수로 보고 다음 등수를 매기지만, RANK는 공동등수를 모두 포함시키고 다음 등수를 매긴다.

2) 집계 함수

RANGE UNBOUNDED PRECEDING 있으면 누적합 계산됨
SUM 하는 컬럼을 OVER 절에서 ORDER BY절에 명시하면 RANGE UNBOUNDED PRECEDING 없어도 누적합 계산됨

범위	의미
UNBOUNDED PRECEDING	위쪽 끝 행
UNBOUNDED FOLLOWING	아래쪽 끝 행
CURRENT ROW	현재 행
n PRECEDING	현재 행에서 위로 n만큼 이동
n FOLLOWING	현재 행에서 아래로 n만큼 이동

기준	의미
ROWS	행 자체가 기준이 됨
RANGE	행이 갖고 있는 데이터 값이 기준이 됨

PARTITION BY 있으면 그 소그룹 내에서 범위 조건 안에 있는 거 COUNT됨

3) 행 순서 함수 [SQL Server X]

• FIRST_VALUE : 파티션별 윈도우의 최초로 나온 값
  MIN 이용하여 같은 결과 가능 (공동 등수 인정 X)
• LAST_VALUE 함수 : 파티션별 윈도우의 가장 마지막에 나온 값
  MAX 이용하여 같은 결과 가능 (공동 등수 인정 X)
  공동 등수 의도적으로 정렬하고 싶다면 인라인 뷰, ORDER BY 조건 이용
• LAG 함수 : 파티션별 윈도우에서 이전 몇 번째 행의 값
  LAG (인수, 몇 행 앞(디폴트는 1), 없는 경우 반환)
• LEAD 함수 : 파티션별 윈도우에서 이후 몇 번째 행의 값
  LEAD (인수, 몇 행 뒤, 없을 경우 반환)

4) 비율 함수 [SQL Server X]

• RATIO_TO_REPORT 함수 : 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의
  백분율을 소수점으로 반환 [ 0 < 결과 값 <= 1 ] 개별 RATIO 합을 구하면 1
• PERCENT_RANK 함수 : 파티션별 윈도우에서 제일 먼저 나오는 것 0, 제일 늦게
  나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율 반환
  [ 0 <= 결과 값 <= 1 ]
  같은 ORDER 순위 인정 → 앞 행의 결과 값을 기준으로 삼음
• CUME_DIST 함수 : 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은
  건수에 대한 누적 백분율. [ 0 < 결과 값 <= 1 ]
  같은 ORDER 순위 인정 → 뒤 행의 결과 값을 기준으로 삼음
• NTILE 함수 : 주어진 수만큼 행을 N등분 후 등급 구하기
  - 동일한 데이터 2개 있으면 각각 1/2등급으로 할당
  - N등분 후 나머지는 맨 앞의 그룹(1등급)부터 차례로 할당
  

6. Top-N 쿼리

1) ROWNUM

: 가짜 컬럼, 랜덤으로 자동 순번 매기기
SELECT절에 ROWNUM 컬럼 추가적으로 쓰면 됨
WHERE에서 = 쓰면 안 됨
SELECT ROWNUM, 칼럼명 FROM 테이블명 WHERE ROWNUM <= N or ROWNUM < N;

ORDER BY가 WHERE절보다 나중에 수행되기 땜에
둘이 같은 단락에 쓰면 안 됨

SELECT ROWNUM,
	컬럼명,
    ...
    컬럼명
FROM (
	SELECT 컬럼명
    FROM 테이블명
    ORDER BY 컬럼명 DESC)
WHERE ROWNUM <=5

이런식으로 FROM절에 서브쿼리 사용해서 ROWNUM으로 TOP 몇 순위 나타냄

7. 셀프 조인

나 자신과의 조인
FROM 절에 같은 테이블이 2번 이상 등장
=> 구분 위해 ALIAS 반드시 사용

SELECT ALIAS명1.칼럼명1, ALIAS명2.칼럼명1, ....
FROM 테이블명 ALIAS명1, 테이블명 ALIAS명2
 WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1

8. 계층 쿼리

• LEVEL : 루트 데이터를 1로 시작하여 하위로 내려갈수록 Leaf 까지 1씩 증가

• SYS_CONNECT_BY_PATH(컬럼, 구분자) : 루트 노드 ~ 현재 노드까지의 경로 출력해주는 함수

• START WITH : 경로가 시작되는 루트 노드를 생성해주는 절

• CONNECT BY : 루트로부터 자식 노드를 생성해주는 절, 조건에 만족하는 데이터 없을 때까지 노드 생성

• PRIOR : 바로 앞에 있는 부모 노드 값 반환
  - CONNECT BY PRIOR 자식 = 부모 : [ 부모 → 자식 ] 순방향 전개
  - CONNECT BY 자식 = PRIOR 부모 : [ 자식 → 부모 ] 역방향 전개

• CONNECT_BY_ROOT 컬럼 : 루트 노드의 주어진 컬럼 값 반환

• CONNECT_BY_ISLEAF : 리프데이터이면 1, 그렇지 않으면 0

같은 레벨끼리 정렬 시엔 ORDER SIBLINGS BY 절 사용

SELECT 컬럼
FROM 테이블
WHERE 조건
START WITH 조건
CONNECT BY 조건
ORDER SIBLINGS BY 컬럼;