[SQL] SQL 활용

서브쿼리(Subquery)

하나의 쿼리 안에 존재하는 또 다른 쿼리

 

서브쿼리는 위치에 따라 다음과 같이 나눌 수 있다.

SELECT 절	스칼라 서브쿼리(Scalar Subquery)
FROM 절	인라인 뷰(Inline View)
WHERE 절, HAVING 절	중첩 서브쿼리(Nested Subquery)

 

스칼라 서브쿼리(Scalar Subquery)

주로 SELECT 절에 위치하지만 칼럼이 올 수 있는 대부분 위치에 사용할 수 있다. 칼럼 대신 사용되므로 반드시 하나의 값만을 반환해야 하며 그렇지 않은 경우 에러를 발생시킨다.

 

인라인 뷰(Inline View)

FROM 절 등 테이블명이 올 수 있는 위치에 사용 가능하다.

 

중첩 서브쿼리(Nested Subquery)

WHERE 절과 HAVING 절에 사용할 수 있다. 중첩 서브쿼리는 메인 쿼리와의 관계에 따라 다음과 같이 나눌 수 있다.

비연관 서브쿼리(Uncorrelated Subquery)	메인 쿼리와 관계를 맺고 있지 않음
연관 서브쿼리(Correlated Subquery)	메인 쿼리와 관계를 맺고 있음

 

중첩 서브쿼리는 반환하는 데이터 형태에 따라 다음과 같이 나눌 수 있다.

단일 행(Single Row) 서브쿼리	서브쿼리가 1건 이하의 데이터를 반환 단일 행 비교 연산자와 함께 사용 예) =, <, >, <=, >=, <>
다중 행(Multi Row) 서브쿼리	서브쿼리가 여러 건의 데이터를 반환 다중 행 비교 연산자와 함께 사용 예) IN, ALL, ANY, SOME, EXISTS
다중 컬럼(Multi Column) 서브쿼리	서브쿼리가 여러 컬럼의 데이터를 반환

 

뷰(View)

특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트이다. SQL에서 테이블처럼 사용할 수 있다. 혼돈하지 말아야 할 점은 뷰는 가상 테이블이라는 것이다. 따라서 실제 데이터를 저장하지는 않고 해당 데이터를 조회해 오는 SELECT 문만 가지고 있다.

-- VIEW 기본 형식
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

 

집합 연산자

각 쿼리의 결과 집합을 가지고 연산을 하는 명령어

UNION ALL	각 쿼리의 결과 집합의 합집합이다. 중복된 행도 그대로 출력된다.
UNION	각 쿼리의 결과 집합의 합집합이다. 중복된 행은 한 줄로 출력된다.
INTERESECT	각 쿼리의 결과 집합의 교집합이다. 중복된 행은 한 줄로 출력된다. 헤더 값은 첫 번째 쿼리를 따라간다.
MINUS/EXCEPT	앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합이다. 중복된 행은 한 줄로 출력된다.

 

SELECT * FROM A
UNION ALL
SELECT * FROM B; -- A와 B의 행 모두 출력된다. 중복값도 출력된다.

SELECT * FROM A
UNION
SELECT * FROM B; -- A와 B의 행 모두 출력된다. 중복값은 제외된다.

SELECT * FROM A
INTERSECT
SELECT * FROM B; -- A와 B의 행의 공통된 부분만 출력된다. 중복값은 제외된다.

SELECT * FROM A
MINUS
SELECT * FROM B; -- A에서 B의 결과를 제거하고 출력한다.

 

그룹 함수

데이터를 GROUP BY 하여 나타낼 수 있는 데이터를 구하는 함수이다.

집계 함수	COUNT, SUM, AVG, MAX, MIN 등
소계(총계) 함수	ROLLUP, CUBE, GROUPING SET 등

 

ROLLUP

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

ROLLUP (A)	A로 그룹핑 총합계
ROLLUP (A, B)	A, B로 그룹핑 A로 그룹핑 총합계
ROLLUP (A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A로 그룹핑 총합계

SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT
ORDER BY ORDER_DT; -- 주문 수량을 날짜별로 그룹핑하여 카운트

SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ROLLUP(ORDER_DT)
ORDER BY ORDER_DT; -- ROLLUP 함수를 적용해 그룹핑 + 카운트의 총합계 구함

 

그룹핑이란 - 아래의 사진을 참고하세요

 

CUBE

소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수. GROUP BY가 일방향으로 그룹핑하며 소계를 구했다면 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로 그룹핑 총합계

SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT
ORDER BY ORDER_DT; -- 주문 수량을 날짜별로 그룹핑하여 카운트

SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY CUBE(ORDER_DT)
ORDER BY ORDER_DT; -- CUBE 함수를 적용해 그룹핑 + 카운트의 총합계 구함, ROLLUP과 결과 같음

 

CUBE와 ROLLUP의 차이점

 

GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수이다. 인자값으로 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로 그룹핑 총합계
GROUPING SETS (A, B, ROLLUP(C))	A로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

 

GROUPING

그룹핑 함수는 ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 Row를 구분할 수 있게 해준다. 집계 결과에서 각 열이 원래 데이터의 값일 때는 0, 집계 결과 값일 때는 1이 출력된다. WHEN ~ THEN 구문을 사용하여 원하는 텍스트를 입력할 수 있다.

SELECT ORDER_DT,
GROUPING(ORDER_DT),
COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ROLLUP(ORDER_DT)
ORDER BY ORDER_DT; -- 그루핑 함수의 결과 값이 1이 되고 나머지에서는 0이 된다.

 

윈도우 함수

OVER 키워드와 함께 사용되며 역할에 따라 다음과 같이 나눌 수 있다.

순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, MAX, MIN, AVG, COUNT
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

 

순위 함수

RANK	순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다. 1, 2, 2, 4, 5, 5, 7 ...
DENSE_RANK	같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다. 1, 2, 2, 3, 4, 4, 5 ...
ROW_NUMBER	동일한 값이라도 각기 다른 순위를 부여한다. 1, 2, 3, 4, 5, 6, 7 ...

 

집계 함수

SUM	데이터의 합계를 구하는 함수
MAX	데이터의 최대값을 구하는 함수
MIN	데이터의 최소값을 구하는 함수
AVG	데이터의 평균값을 구하는 함수
COUNT	데이터의 건수를 구하는 함수

 

행 순서 함수

FIRST_VALUE	파티션 별 가장 선두에 위치한 데이터를 구하는 함수
LAST_VALUE	파티션 별 가장 끝에 위치한 데이터를 구하는 함수
LAG	파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수
LEAD	파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수

 

비율 함수

RATIO_TO_REPORT	파티션 별 합계에서 차지하는 비율을 구하는 함수
PERCENT_RANK	해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
CUME_DIST	해당 파티션에서의 누적 백분율을 구하는 함수 결과 값은 0보다 크고 1보다 작거나 같은 값을 가짐
NTILE	주어진 수만큼 행들을 N등분 한 후 현재 행에 해당하는 등급을 구하는 함수

 

Top-N 쿼리

멜론 사이트에 가면 시간대별로 멜론 TOP100이 공개된다. TOP-N도 이와 같은 맥락이라고 생각하면 된다. (N위까지 추출)

 

ROWNUM

Oracle의 ROWNUM은 슈도 컬럼(Pseudo Column)이다. Pseudo는 사전적 의미로 '가짜'라는 뜻을 가지고 있으며 컴퓨터 공학에서는 슈도 코드라는 용어로도 많이 쓰인다. ROWNUM은 실제로는 존재하지 않는 가짜 컬럼이라고 생각하면 된다. 엑셀을 작성하다 보면 순번이 필요한 상황이 종종 생긴다. 그럴 땐 보통 맨 앞에 별도의 열을 하나 만들어서 자동번호를 매기는 경우가 있는데 Oracle에서는 그런 경우 SELECT 전에 ROWNUM 컬럼을 하나 추가하면 된다. ROWNUM은 항상 < 조건이나 <= 조건으로 사용해야 한다.

 

계층 쿼리

테이블의 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있다.

 

LEVEL

현재의 DEPTH를 반환한다. 루트 노드는 1이 된다.

*루트 노드 : 계층에서 가장 위에 있는 노드. 노드는 각 요소들을 뜻함

 

SYS_CONNECT_BY_PATH (컬럼, 구분자)

루트 노드부터 현재 노드까지의 경로를 출력해주는 함수이다.

 

START WITH

경로가 시작되는 루트 노드를 생성해주는 절이다.

 

CONNECT BY

루트로부터 자식 노드를 생성해주는 절이다. 조건에 만족하는 데이터가 없을 때까지 노드를 생성한다.

 

PRIOR

바로 앞에 있는 부모 노드의 값을 반환한다.

 

CONNECT_BY_ROOT 컬럼

루트 노드의 주어진 컬럼 값을 반환한다.

 

CONNECT_BY_ISLEAF

가장 하위 노드인 경우 1을 반환하고 그 외에는 0을 반환한다.

 

셀프 조인(Self Join)

셀프 조인은 말 그대로 나 자신과의 조인이다. FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에 혼란을 막기 위해 ALIAS를 반드시 표기해주어야 한다.

SELECT * FROM CATEGORY;

SELECT
A.CATEGORY_TYPE,
A.CATEGORY_NAME,
B.CATEGORY_TYPE,
B.CATEGORY_NAME
FROM CATEGORY A, CATEGORY B
WHERE A.CATEGORY_NAME = B.PARENT_CATEGORY
AND A.CATEGORY_TYPE = '대';