정미나, "유선배 SQL 개발자(SQLD) 과외노트", 시대고시기획
해당 책을 바탕으로 작성하였습니다.
그냥 개인적으로 다시 보고 공부하려고 만들었습니다.😀
서브쿼리
하나의 쿼리 안에 존재하는 또 다른 쿼리.
스칼라 서브쿼리
주로 select 절에 위치. 컬럼이 올 수 있는 대부분의 위치에 사용 가능.
컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야 함.
인라인 뷰
from 절 등 테이블명이 올 수 있는 위치에 사용 가능.
중첩 서브쿼리
where, having 절에 사용 가능.
비연관 서브쿼리
메인 쿼리와 관계를 맺고 있지 않음.
서브쿼리 내에 메인 쿼리의 컬럼이 존재 X.
연관 서브쿼리
메인쿼리와 관계를 맺고 있음.
서브쿼리 내에 메인 쿼리의 컬럼이 존재.
단일 행 서브쿼리
서브쿼리가 1건 이하의 데이터를 반환
단일 행 비교 연산자와 함께 사용(=, <, >, <>, <=, >=)
다중 행 서브쿼리
서브쿼리가 여러 건의 데이터를 반환.
다중 행 비교 연산자와 함께 사용(IN, ALL, ANY, SOME, EXISTS)
다중 컬럼 서브쿼리
서브쿼리가 여러 컬럼의 데이터를 반환.
뷰
특정 select 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트.
테이블처럼 사용할 수 있음.
뷰는 가상 테이블임. 따라서 실제 데이터를 저장하지는 않고 해당 데이터를 조회해오는 select 문만 가지고 있음.
뷰의 특징
- 보안성 : 보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안을 유지할 수 있다.
- 독립성 : 테이블 스키마가 변경되었을 경우 어플리케이션은 변경하지 않고 관련 뷰만 수정.
- 편리성 : 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성, 편리성을 높임.
집합 연산자
UNION ALL / UNION
UNION ALL
쿼리 1의 결과와 쿼리 2의 결과의 합집합. 중복 행 출력.
- 각 컬럼의 데이터 타입이 다르거나 컬럼의 수가 다르면 에러.
- 각각의 쿼리에 order by 불가.
합쳐진 최종 쿼리 마지막에만 가능.
UNION
쿼리 1의 결과와 쿼리 2의 결과의 합집합. 중복 행 출력 X.
두 개의 SQL을 UNION으로 연결할 경우 헤드의 명칭은 첫 번째 SQL의 컬럼명 혹은 ALIAS를 따름.
INTERSECT
쿼리 1의 결과와 쿼리 2의 결과의 교집합. 중복 행 출력 X.
MINUS / EXCEPT
쿼리 1의 결과에서 쿼리 2의 결과를 제거한 차집합.
여러 행이 겹치면 해당 행 모두 제거.
그룹 함수
데이터를 group by 하여 나타낼 수 있는 데이터를 구하는 함수.
ROLLUP
소그룹 간의 소계 및 총계를 계산하는 함수.
ROLLUP(A)
- A로 그룹핑
- 총합계
ROLLUP(A, B)
- A, B로 그룹핑
- A로 그룹핑
- 총합계
ROLLUP(A, B, C)
- A, B, C로 그룹핑
- A, B로 그룹핑
- A로 그룹핑
- 총합계
ROLLUP((A, B), C)
- A, B, C로 그룹핑
- A, B로 그룹핑
- 총합계
ROLLUP (A, (B, C))
- A, B, C로 그룹핑
- A로 그룹핑
- 총합계
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((A, B), C)
- A, B, C로 그룹핑
- A, B로 그룹핑
- C로 그룹핑
- 총합계
CUBE(A, (B, C))
- A, B, C로 그룹핑
- A로 그룹핑
- B, C로 그룹핑
- 총합계
GROUPING SETS
특정 항목에 대한 소계를 계산하는 함수. 인자값으로 ROLLUP, CUBE 사용 가능.
GROUPING SETS(A, B)
- A로 그룹핑
- B로 그룹핑
GROUPING SETS(A, B, ())
GROUPING SETS(A, ROLLUP(B))
GROUPING SETS(ROLLUP(A), B)
- A로 그룹핑
- B로 그룹핑
- 총합계
GROUPING SETS(A, ROLLUP(B, C))
- A로 그룹핑
- B, C로 그룹핑
- B로 그룹핑
- 총합계
GROUPING SETS(A, B, ROLLUP(c))
- A로 그룹핑
- B로 그룹핑
- C로 그룹핑
- 총합계
GROUPING SETS(A, B, C)
- A로 그룹핑
- B로 그룹핑
- C로 그룹핑
ROLLUP은 순서 중요. CUBE, GROUPING SETS는 순서 중요 X.
GROUPING
ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 ROW를 구분할 수 있게 해줌. 기존 예제에서는 소계를 나타내는 ROW는 그룹핑의 기준이 되는 컬럼을 제외하고 NULL로 출력됐지만 GROUPING을 사용하여 원하는 위치에 원하는 텍스트 사용 가능.
GROUPING(COL1) 시 소계가 계산된 ROW는 1이 되고 나머지는 0이 됨.
- select case grouping(COL1) when 1 then 'total' else COL1
end as COL1,
count(*)
from smp
group by rollup(COL1)
order by COL1;
이렇게 사용하면 소계에 'total'이 출력.
오라클의 경우 DECODE로도 사용 가능
- select decode(grouping(COL1), 1, 'total', COL1) as COL1,
count(*)
from smp
group by rollup(COL1)
order by COL1;
여러 개도 가능.
- select COL1, grouping(COL1),
COL2, grouping(COL2),
count(*)
from smp
group by rollup(COL1, COL2)
order by COL1;
윈도우 함수
OVER 키워드와 함께 사용됨.
순위 함수
RANK() OVER()
순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뜀.
- select COL1, count(*),
rank() over(order by count(*) desc) as rank
from smp
group by COL1;
=> 1, 2, 3, 4, 5, 6, 7, 7, 7, 10
DENSE_RANK() OVER()
순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매김. '순위 밀집'
- select COL1, count(*),
dense_rank() over(order by count(*) desc) as dense_rank
from smp
group by COL1;
=> 1, 2, 3, 4, 5, 6, 7, 7, 7, 8
ROW_NUMBER() OVER()
순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여.
- select COL1, count(*),
row_number() over(order by count(*) desc) as row_number
from smp
group by COL1;
=> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
partition by 문을 사용하면 원하는 그룹 별로 따로 순위를 매길 수 있다.
- select COL1, COL2, COL3,
row_number() over(partition by COL2 order by COL3 desc) as row_number
from smp;
집계 함수
SUM
MIN
MAX
AVG
COUNT
윈도우 함수 사용 옵션
WINDOWING 절을 이용하여 집계하려는 데이터의 범위 지정 가능.
- ROWS 사용 시 행 자체가 기준
- RANGE 사용 시 행이 가지고 있는 데이터 기준
ex)
- range netween unbounded preceding and current row
처음부터 현재 행까지 - range netween 10 preceding and current row
현재 행이 가지고 있는 값보다 10만큼 적은 행부터 현재 행까지 - rows between current row and unbounded following
현재 행부터 끝까지 - rows between current row and 5 follwoing
현재 행부터 아래로 5만큼 이동한 행까지
아무것도 작성하지 않았을 시 range unbounde preceding이 default
행 순서 함수
FIRST_VALUE (MSSQL 지원 X)
파티션 별 가장 선두에 위치한 데이터를 구함.
LAST_VALUE (MSSQL 지원 X)
파티션 별 가장 끝에 위치한 데이터를 구함.
파티션을 따로 명시하지 않은 경우 default가 range unbounde preceding이기 때문에 맨 끝부터 현재행까지임. 따라서 WINDOWING 절을 명시해야 원하는 결과 출력 가능.
LAG(COL [, 수, 기본값]) OVER() (MSSQL 지원 X)
파티션 별 특정 수만큼 앞선 데이터를 구함. 없으면 NULL 혹은 기본값.
수를 명시하지 않으면 기본값 1임.
LEAD(COL [, 수, 기본값]) OVER() (MSSQL 지원 X)
파티션 별 특정 수만큼 뒤진 데이터를 구함. 없으면 NULL 혹은 기본값.
수를 명시하지 않으면 기본값 1임.
비율 함수
RATIO_TO_REPORT (MSSQL 지원 X)
파티션 별 합계에서 차지하는 비율을 구하는 함수.
ration_to_report(score) over(partition by subject)는
score/sum(score) over(partition by subject)와 같다.
즉 본인 score 값 / score 총 합
0~1의 값을 가짐
PERCENT_RANK (MSSQL 지원 X)
해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구함.
percent_rank() over(order by score)은
(rank() over(order by score) - 1) / (count(*) over() -1)과 같다.
즉 랭크-1 / 전체 카운트 -1
0~1의 값을 가짐
CUME_DIST (MSSQL 지원 X)
해당 파티션에서의 누적 백분율을 구함.
cume_dist() over(order by score)은
count(*) over(order by score) / count(*) over()과 같다.
즉 누적 건수 / 전체 건수
0~1의 값을 가짐
NTILE(수)
주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구함.
할당할 행이 남았을 경우 맨 앞의 그룹부터 하나씩 채워짐.
인자값에 주어진 수대로 등급이 할당.
TOP-N 쿼리
N위까지 순위를 추출.
ROWNUM
이건 그냥 랜덤으로 순위를 매기는 것.
'=' 연산자 사용 불가. 사용 시 빈 데이터 나옴.
항상 '<' 또는 '<=' 사용.
order by 절보다 where rownum 절이 먼저 나오면 안 됨.
왜? order by 절이 where 절보다 나중에 수행 즉 데이터를 랜덤으로 뽑아내고 그걸 가지고 순위를 매기는 것이 됨.
주의
ROW_NUMBER와 헷갈릴 수 있는데 ROW_NUMBER은 over 키워드와 함께 사용.
ROWNUM은 over 사용 불가.
윈도우 함수의 순위 함수
앞에 나왔던 윈도우 함수의 순위 함수를 이용하여 TOP_N 쿼리를 작성할 수 있음.
셀프 조인
나 자신과의 조인. from 절에 같은 테이블이 두 번 이상 등장하기 때문에 혼란을 막기 위해 ALIAS 반드시 표기.
계층 쿼리
테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력 가능.
-
LEVEL
현재의 DEPTH를 반환. 루트 노드는 1. -
SYS_CONNECT_BY_PATH(컬럼, 구분자)
루트 노드부터 현재 노드까지의 경로를 출력. -
START WITH
경로가 시작되는 루트 노드를 생성. -
CONNECT BY
루트로부터 자식 노드를 생성해주는 절. 조건에 만족하는 데이터가 없을 때까지 노드를 생성. -
CONNECT_BY_ROOT
루트 노드의 주어진 컬럼 값을 반환. -
CONNECT_BY_ISLEAF
가장 하위 노드인 경우 1, 그 외에 0을 반환. -
PRIOR
바로 앞에 있는 부모 노드의 값을 반환. -
ORDER SIBLINGS BY
같은 레벨들끼리 정렬을 수행.
