SQL 활용

728x90

서브쿼리(SubQuery)

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

SELECT 절 스칼라 서브쿼리(Scalar Subquery)

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

스칼라 서브쿼리(Scalar Subquery)

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

select m.product_code
			(select s.product_name
					from product s
					where s.product_code = m.product_code) as product_name) as product_name
from product_review m

인라인 뷰(Inline View)

FROM 절 등 테이블명이 올 수 있는 곳에 사용된다.

select m.product_code,
			s.product_name
from product_review m,
		( select product_code,
							product_name,
							price
			from product ) s

중첩 서브쿼리(Nested Subquery)

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

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

연관 서브쿼리(Correlated Subquery)

메인쿼리와 관계를 맺고 있음

비연관 서브쿼리

서브쿼리 내에 메인쿼리의 컬럼이 존재하지 않는다.

select name, job, birthday, agency_code
from entertainer
where agency_code = (select agency_code
											from agency
											where agency_name = 'EDAM엔터테이먼트');

연관 서브쿼리

서브쿼리 내에 메인쿼리의 컬럼이 존재한다.

select order_no,
				drink_code,
				order_cnt,
from cafe_order a
where order_cnt = ( select max(order_cnt)
										from cafe_order b
										where b.drink_code = a.drink_code);

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

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

뷰(View)

특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트이다. SQL에서 테이블처럼 사용할 수 있으며 앞서 배운 인라인 뷰를 뷰로 정의한다고 가정해보면 쿼리 작성시 인라인 뷰가 들어가는 위치에 뷰 이름만 기술하게 될 것이다. 혼돈하지 말아야 할 점은 뷰는 가상의 테이블이다. 따라서 실제 데이터를 저장하지는 않고 해당 데이터를 조회해오는 SELECT 문만 가지고 있다.

CTEs(Common Table Expressions) vs. View

가장 큰 차이점은 CTEs는 임시로 만든 테이블이고, View는 데이터베이스에 영구적으로 존재하며 필요할 때마다 참조 할 수 있는 객체입니다.

CTEs는 보통 계층적 또는 재귀적 쿼리를 작성할때 유용합니다.
View는 사용 권한을 제어하거나, 테이블 간의 조인을 캡슐화 하는데 자주 사용됩니다.

집합 연산자

집합 연산자는 각 쿼리의 집합을 가지고 연산하는 명령어이다.

UNION ALL 각 쿼리의 결과 집합의 합집합이다. 중복된 행도 그대로 출력된다.

UNION	각 쿼리의 결과 집합의 합집합이다. 중복된 행은 한줄로 출력된다.
INTERSECT	각 쿼리의 결과 집합의 교집합이다. 중복된 행은 한줄로 출력된다.
MINUS/EXCEPT	앞에 있는 쿼리의 결과 집합에서 뒤에있는 쿼리의 결과 집합을 뺀 차집합니다. 중복된 행은 한줄로 출력된다.

그룹 합수

데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수이다. 역할에 따라 구분해보면 집계 함수와 소계(총계) 함수로 나눌 수 있다.

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

ROLLUP

소그룹 간의 소계 및 총계를 계산하는 함수이다. Group By와 다른점은 좀더 디테일하게 계층적으로 그룹핑을 할 수 있다.

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

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로 그룹핑 … A 그룹핑, B그룹핑, C그룹핑, 총 합계

GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수이다. 인자값으로 ROOLUP이나 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

GROUPING 함수는 ROLLUP CUBE, GROUPING SET 등과 함께 쓰이며 소계를 나타내는 Row를 구분할 수 있게 해준다. 앞서 보여준 예제에서는 소계를 나타내는 Row에서 그룹핑의 기준이 되는 컬럼을 제외하고는 모두 NULL 값으로 표현되었지만 GROUPING 함수를 이용하면 원하는 위치에 텍스트를 출력 할 수 있다.

윈도우함수

윈도우 함수(Window Function)

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

순위 함수 RANK, DENSE_RANK, ROW_NUMBER

집계 함수	SUM, MAX, 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

RANK

순위를 매기면서 같은 순위가 존재하는 수만큼 다음 순위를 건너뛴다.

DENSE_RANK

순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다. DENSE가 사전적 의미로 ‘밀접한’이라는 뜻을 가지고 있으므로 ‘순위가 밀집되어 있다’ 라고 기억하면 된다.

ROW_NUMBER

순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.

윈도우함수 연습

본인보다 점수가 높거나 같은 건수를 카운티 하는 쿼리

select student_name,
				subject,
				score,
				count(*) over(partition by subject
											order by score desc
											range unbounded preceding) as higher_count
from sqld;

과목별로 본인 점수와 5점 이하로 차이가 나거나 점수가 같은 건수를 카운팅 하는 쿼리

select student_name,
				subject,
				score,
				count(*) over(partition by subject
											order by score desc
											range between 5 preceding and 5 following) as similar_count
from sqld;

행 순서 함수

FIRST_VALUE

파티션별 가장 선두에 위치한 데이터를 구하는 함수이다. SQL Server(MS SQL) 에서는 지원하지 않는다.

LAST_VALUE

파티션별 가장 끝에 위치한 데이터를 구하는 함수이다. SQL Server(MS SQL) 에서는 지원하지 않는다.

LAG

파티션별로 특정 수만큼 앞선 데이터를 구하는 함수이다. SQL Server(MS SQL) 에서는 지원하지 않는다.

두번째 인자값을 생략하면 default 는 1이다.

시험 성적 데이터에 LAG 함수를 적용해본 결과

select student_name,
				subject,
				score,
				lag(score, 3) over(order by score) as lag
	from sqld;

LEAD

파티션별 특정 수 만큼 뒤에 있는 데이터를 구하는 함수이다. SQL Server(MS SQL) 에서는 지원하지 않는다.

두번째 인자값을 생략하면 default 는 1이다.

시험 성적 데이터에 LEAD 함수를 적용해본 결과이다.

select student_name,
				subject,
				score,
				lead(score, 3) over(order by score) as lead
from sqld;

비율 함수

RATIO_TO_REPORT

파티션별 합계에서 차지하는 비율을 구하는 함수이다. SQL Server(MS SQL) 에서는 지원하지 않는다.

시험 성적 데이터에 RATIO_TO_REPORT 함수를 적용한 결과이다.

select student_name,
				subject,
				score,
				sum(score) over() as sum,
				score/sum(score) over() as "score/sum", -- ratio_to_report 와 값이 같다.
				ratio_to_report(score) over() as ratio_to_report
from sqld;

PERCENT_RANK

해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수이다.

시험 성적 데이터에 PERCENT_RANK 함수를 적용해본 결과이다.

select student_name,
				subject,
				rank() over(order by score) as rank,
				count(*) over() as count,
				(rank() over(order by score)-1)/(count(*)over() -1) as "(RANK-1)/(COUNT-1)",
				percent_rank() over(order by score) as percent_rank -- 위의 컬럼과 같다.
from sqld;

CUME_DIST

해당 파티션에서의 누적 백분율을 구하는 함수이다. 결과값은 0보다 크고 1보다 작거나 같은 값을 가진다. SQL Server(MS SQL) 에서는 지원하지 않는다.

시험 성적 데이터에 CUME_DIST 함수를 적용해본 결과이다.

select student_name,
				subject,
				score,
				count(*) over(order by score) as count,
				count(*) over() as total_count,
				count(*) over(order by score)/count(*) over() as "count/total_count",
				cume_dist() over(order by score) as cume_dist -- 위의 컬럼과 같다.
from sqld;

NTILE

주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수이다.

시험 성적 데이터에 NTILE 함수를 적용해본 결과이다.

select student_name,
				subject,
				score,
				ntile(1) over(order by score desc) as ntile1,
				ntile(3) over(order by score desc) as ntile3,
				ntile(5) over(order by score desc) as ntile5,

Top-N 쿼리

멜론사이트에 가면 시간대별로 멜론 TOP100이 공개된다. 멜론TOP100은 모두가 알다시피 멜론에서 랭킹 100위까지 차지한 음원을 의미하는데 Top-N 쿼리도 이와 같은 맥락이라고 생각하면 된다. 이런 Top-N 데이터를 추출하기 위한 몇가지 방식들이 있는데 구체적으로 어떤 방식들이 있는지 함께 살펴보도록 하자.

ROWNUM

Oracle의 ROWNUM은 슈도 컬럼이다. Psudo는 사전적 의미로 ‘가짜’라는 뜻을 가지고 있으며 컴퓨터 공학에서 슈도 코드라는 용어로 많이 쓰인다. 그래서 ROWNUM이 ‘수도 컬럼이라는 무슨뜻이야?’ 라고 묻는다면 ROWNUM은 실제로 존재하지 않는 가짜 컬럼이라고 할 수 있다. 엑셀을 작성하다보면 순번이 필요한 상황이 종종 생긴다. 그럴 땐 보통 맨 앞에 별도의 열을 하나 만들어서 엑셀 자동번호를 매기는 경우가 있는데 Oracle에서는 그런 경우 SELECT 절에 ROWNUM 커럼을 하나 추가하면 된다.

ROWNUM은 항상 < 조건이나 ≤ 조건으로 사용해야한다.

SELECT 절에서는 논리적으로 ORDER BY 절이 WHERE 절보다 나중에 수행되기 때문에 위 쿼리는 데이터를 랜덤으로 5개 뽑아낸 뒤 그걸 가지고서 순위를 매기는 격이 된다.

select rownum,
				이름,
				국어,
				영어,
				수학
from exam_score
where rownum <= 5
order by 국어 desc, 영어 desc, 수학 desc

윈도우 함수의 순위 함수

앞 절에 나왔던 윈도우 함수의 순위 함수를 이용하여 Top-N 쿼리를 작성 할 수 있다. 다음 쿼리는 ROW_NUMBER 함수를 사용하여 Top-N 쿼리를 작성한 것이다.

select * from (
	select row_number() over(order by 국어 desc, 영어 desc, 수학 desc) as rnum,
					국어,
					영어,
					수학
	from exam_score)
where RNUM <= 5;

셀프 조인(Self Join)

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

select a.employee_id as manager_id,
			a.name as manager_name,
			b.employee_id,
			b.name
from employees a, employees b
where a.employee_id = b.manager_id
order by manager_id;

계층 쿼리

테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있다. 그럼 앞서 셀프 조인으로 작성했던 쿼리를 계층 쿼리로 변환해보자

select level,
				sys_connect_by_path('[' || category_type||']' category_name, '-') as path
from categoty
start with parent_categoty is null
connect by prior categoty_name = parent_category;

위 쿼리를 보면 생소한 구문이 다소 보일 것이다.

LEVEL
- 현재의 DEPTH를 반환한다. 루트노드는 1이 된다.
SYS_CONNECT_BY_PATH(컬럼, 구분자)
- 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수이다.
START_WITH
- 경로가 시작되는 루트노드를 생성해주는 절이다.
CONNECT_BY
- 루트노드로부터 자식노드를 생성해주는 절이다. 조건에 만족하는 데이터가 없을때까지 노드를 생성한다.
PRIOR
- 바로 앞에 있는 부모 노드의 값을 반환한다.

그 박에 계층 쿼리에서 사용할 수 있는 항목을 보면 다음과 같다.

CONNECT_BY_ROOT 컬럼
- 루트 노드의 주어진 컬럼을 반환한다.
CONNECT_BY_ISLEAF
- 가장 하위 노드인 경우 1을 반환하고 그 외에는 0을 반환한다.

위의 쿼리는 순방향(루트 → 리프)로 내려가는 구조이다. 그렇다면 역방향으로 전개인 리프 → 루트로 올라가게 하려면 어떻게 해야할까?

select level,
		category_type as type,
		categoty_name as name,
		parent_category as parent,
		sys_connect_by_path('[' || category_type||']' category_name, '-') as path
from category
start with category_type = '소'
connect by category_name = prior parent_category

계층형 구조에서 ORDER BY절을 사용하면 계층과 상관 없이 전체 정렬이 되기때문에 계층 구조를 유지 할 수 없다. ORDER SIBLINGS BY 절을 사용하면 같은 레벨들끼리 정렬을 수행하기 때문에 계층 구조를 유지 할 수 있다.

계층형 구조에서 ORDER BY를 사용하는 방법은 아래의 쿼리에서 알 수 있다.

select level,
		category_type as type,
		categoty_name as name,
		parent_category as parent,
		sys_connect_by_path('[' || category_type||']' category_name, '-') as path
from category
start with category_type = '소'
connect by category_name = prior parent_category
order by siblings by name;

728x90

저작자표시

'CS > 데이터베이스' 카테고리의 다른 글

SQL 기본 및 활용 (0)	2024.04.14
데이터 모델과 SQL (0)	2024.04.14
데이터 모델의 이해 (1)	2024.04.14
윈도우 함수 (Window Function) (0)	2024.04.11
Redis란? (0)	2023.08.18

BigKwangs 기술 블로그

SQL 활용

서브쿼리(SubQuery)

뷰(View)

CTEs(Common Table Expressions) vs. View

집합 연산자

그룹 합수

윈도우함수

윈도우함수 연습

행 순서 함수

비율 함수

Top-N 쿼리

셀프 조인(Self Join)

계층 쿼리

'CS > 데이터베이스' 카테고리의 다른 글

티스토리툴바

SQL 활용

서브쿼리(SubQuery)

뷰(View)

CTEs(Common Table Expressions) vs. View

집합 연산자

그룹 합수

윈도우함수

윈도우함수 연습

행 순서 함수

비율 함수

Top-N 쿼리

셀프 조인(Self Join)

계층 쿼리

'CS > 데이터베이스' 카테고리의 다른 글

'CS/데이터베이스' Related Articles

티스토리툴바