[오답노트] 2-2. SQL 활용

🎈계층형 질의의 구조
select 칼럼명, (level), (connect_by_isleaf)
from 테이블명
where 조건
start with 시작조건 -> 예) start with mgr is null / start with mrg = 'D' / start with mrg in(111,222)*시작노드 2개
connect by prior 방향 
- (순방향) prior 자식 = 부모 -> connect by prior empno = mgr (자식이 먼저 나온 부모다) 
- (역방향) prior 부모 = 자식 -> connect by prior mgr = empno
(순방향) leve l: 시작노드root =1, 자식노드 2,3,4...
(순방향) connect_by_isleaf : 해당노드의 후속 노드가 있으면 1, 아니면 0으로 표현
(순방향) connect_by_root : 시작노트 표시
(순방향) sys_connect_by_path(~~) : 시작노드부터 현재까지 경로 표시

(역방향) leve l: 시작노드root=1, 부모노드 2,3,4...
(역방향) connect_by_isleaf : 해당노드의 후속 노드가 없으면 1 (즉, root면 1, 아니면 죄다 0)

order siblings by : 형제노드(동일 level)사이에서 정렬을 수행

🎈서브쿼리
서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다
서브쿼리에서는 order by절을 사용하지 못한다
메인쿼리 레벨이 1이고, 서브쿼리가 M이면 항상 메인쿼리 레벨1로 결과집합이 생성된다



🎈다중행 서브쿼리
서브쿼리의 결과건수가 2건 이상일 가능성이 있을 때
1. IN(서브쿼리) : 임의의 결과 중 하나만 만족해도 참
2. 비교연산자 ALL(서브쿼리) : 결과의 모든 값을 만족해야 하는 조건
3. 비교연산자 any/some(서브쿼리) : 결과의 어느 하나의 값이라도 만족하면 되는 조건
4. exist (서브쿼리) : 조건을 만족하는 값이 존재하는지 여부를 확인, 조건을 만족하는 건을 하나라도 찾으면 검색 중지
-select절에 사용된 서브쿼리는 단일행 연관 서브쿼리로 스칼라 서브쿼리라고 하며, join으로 변경 가능
- from절의 서브쿼리는 동적 뷰 라고도 하며, SQL문장 중 테이블 명이 올 수 있는 곳에서 사용 가능

🎈고급집계
1.rollup: 소그룹 별 소계 계산 추가
select dname, job, count(*)직원수, sum(sal)급여합
from 
group by rollup (dname,job)
>> 부서이름별 직업별 직원수의 합과, 급여합의 소계

2. grouping sets : 여러 칼럼 각각에 대해 반복적으로 그룹화
select dname, job, count(*)직원수, sum(sal)급여합
from 
group by grouping sets (dname, job)
>>> 부서이름별 작원수합과, 급여합 + 직업별 직원수합과 급여합 (각 반대의 컬럼은 NULL)

3. cube : 다차원 소계 계산 추가(순서무관), 모든 조합의 집계계산(시스템 부하)
select dname, job, count(*)직원수, sum(sal)급여합
from 
group by cube (dname, job)

🎈윈도우 함수
-순위 : rank(공동수상, 113), dense_rank(공동수상, 112), row_number(1,2,3)
-집계 : sum, max, min, avg, count 
-행 순서 : first_value, last_value, lag, lead
-비율 : ratio_report, percent_rank, ntitle
-통계 : corr, stddev, variance

🎈뷰 사용의 장점
1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램 변경하지 않아도 된다
2. 편리성 : 복잡함 질의를 뷰로 생성하여 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL을 자주 이용하면 편리하게 사용할 수 있다
3. 보안성 : 직원의 급여정보와 같이 숨기고자 한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성하여 사용자에게 정보를 감출 수 있다
- 인리인뷰 =. 동적인뷰 = from절 서브쿼리
:서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인것처럼 사용할 수 있다. 인라인뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당정보가 저장되지 않는다

 

1. 서브쿼리

다중컬럼 = 서브쿼리의 실행 결과로 여러 컬럼을 반환한다, 메인쿼리의 조건 절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼의 개수와 컬럼의 의치가 동일해야 한다

 

2. col1별 합계와 전체합계

#[1] cube
select col1, sum(col2)
from t1
group by cube(col1)
order by 1 asc;

#[2] rollup
select col1, sum(col2)
from t1
group by rollup(col1)
order by 1 asc;

#[3]
select col1, sum(col2) #col1별 개별 합계
from t1
group by col1
union all
select null, sum(col2) # 총합계
from t1
order by 1 asc;

 

1. 집합연산자

• union 연산자는 합집합 결과에서 중복된 행을 하나의 행으로 만든다
• union all연산자는 집합 간의 결과가 중복되지 않는 경우 union과 결과가 동일하다
• union 연산자를 사용한 SQL은 각각의 집합에 group by 절을 사용할 수 있다
• union 연산자를 사용한 SQL은 마지막으로 orderby를 한번만 사용 가능하다

 

 

2.그룹연산

select b.grade, a.job, sum(a.sal), count(*)
from emp a, sal b
where a.sal between b.losal and b.hisal
groupo by grouping sets(grade, (job, grade))
# group by grade, rollup(job)
# group by grade, cube(job)
#집계컬럼이 1개일때는 rollup과 cube는 동일

 

# HRD, MKT부서별 사원번호와 급여,  해당급여의 부서비중을 구하기

부서코드	사원번호	급여	급여비중
HRD	A004	3500000	0.52
HRD	A008	3200000	0.48
HRD	A007	4500000	0.52
MKT	A003	2340000	0.27
MKT	A002	1830000	0.21

 

#[1]
select a.부서코드, a.사원번호, a.급여,
	round(ratio_to_report(급여) over (partition by 부서코드),2) as 급여비중
from(select 사원번호, 이름, 부서코드, 급여, sum(급여) over(partition by 부서코드)as 부서급여합
	from 직원
    where 부서코드 in('MKT','HRD')a
order by a.부서코드, a.급여 desc, a.사원번호

#[2]
select a.부서코드, a.사원번호, a.급여,
	round(급여/부서급여합,2) as 급여비중
from(select 사원번호, 이름, 부서코드, 급여, sum(급여) over(partition by 부서코드)as 부서급여합
	from 직원
    where 부서코드 in('MKT','HRD')a
order by a.부서코드, a.급여 desc, a.사원번호

#[3]
select a.부서코드, a.사원번호, a.급여,
	round(급여/b.부서급여합,2) as 급여비중
from 직원a , (select 부서코드, sum(급여) as 부서급여합
			from 직원
            where 부서코드 in('MKT','HRD')
            group by 부서코드)b
where a.부서코드 = b.부서코드
and a.부서코드 in('MKT','HRD')
order by a.부서코드, a.급여 desc, a.사원번호

 

 

3. 계층형 구조

• start with 절은 계층구조의 시작점을 지정하는 구문
• order siblings by 절은 형제노드사이에서 정렬을 지정하는 구문
• 순방향전개란 부모노드로부터 자식노드방향으로 전개하는 것
• 루트노드의 level은 1이다
• SQL server에서의 계층형 질의문은 CTE(common table expression)를 재귀 호출함으로써 계층 구조를 전개한다
• SQL server에서의 계층형 질의문은 앵커맴버를 실행하여 기본결과 집합을 만들고 이루 재귀맴버를 지속적으로 실행한다
• 오라클 계층형 질의문에서 where절은 모든 전개를 진행한 후 필터조건으로서 조건을 만족하는 데이터만 추출한다
• 오라클 계층형 질의문에서 prior키워드는 connect by, select, where절에서도 사용가능하다

tab1			sql	구조
			select c3 from tab1 start with c2 is null -> c2가 널값인 A에서 시작 connect by prior c1 = c2 ->부모 = 자식(역방향) order siblings by c3 desc -> c3에서 동일 시 정렬 오름차순	a ->b    ->c            -> d
c1	c2	c3
1	null	A
2	1	B
3	1	C
4	2	d

결과

c3

A

C(C와 B는 오름차순)

B

D

 

4. 서브쿼리

• 서브쿼리의 결과가 복수 행 결과를 반환하는 경우에는 IN, ALL, ANY등의 복수 행 비교 연산자와 사용해야 한다
• -> in,all,any등의 복수행 비교 연산자는 단일행 서브쿼리에서도 사용 가능하다
• 다중칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 비교되는데 SQL server에서는 현재 지원하지 않는다
• 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용할 수 있다
• 서브쿼리는 select, from, having, order by절 등에서 사용 가능하다
• 연관 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리다
• 비연관 서브쿼리가 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용
• 메인쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리의 결과가 메인쿼리의 결과로도제공될 수 있다

5. 윈도우함수

• percent-rank = 파티션별 윈도우를 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다(파티션별 0~1)
• dense_rank = 순위를 구하는 함수로 동일한 순위를 하나의 등수로 본다(1,1,2,3)
• cume_dist = 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율
• rank = 파티션내의 orderby에 의한 순위를 구하는 함수로, 동일한 순위에 대해서는 동일한 순위를 부여(1,1,3,4)
• ratio_to_report = 파티션 내의 주어진 칼럼의 값의 합계에 대한 백분율을 소수점으로 나타냄
• ntile = 파티션별 전체 건수를 주어진 인자로 n등분한 결과

6. 뷰

• 뷰는 단지 정의만 가지고 있으며, 실행시점에서 질의를 재작성 한다
• 뷰는 테이블구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다
• 뷰는 보안을 강화하기 위한 목적으로 활용된다
• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다

 

7. revoke와 grant

• 어떤 사용자가 with grant option과 함께 권한을 허가받았으면 그 사용자는 해당 권한을 with grant option유무와 관계없이 다른 사용자에게 허가할 수 있다
• public을 사용하면 자신에게 허가된 권한을 모두 사용자들에게 허가할 수 있다
• revoke문을 사용하여 권한을 취소하면 권한을 취소당한 사용자가 with grant option을 통해서 다른 사용자에게 허가했던 권한들도 모두 연쇄적으로 취소된다
• revoke문을 사용하여 권한을 취소할 때 그 권한을 허가한 사용자가 권한을 취소할 수 있다

B_user가 하단의 작업을 수행하기 위해서는

- GRANT SELECT, UPDATE ON A_user.TB_A to user_B

update A_user.TB_A # update
set col1 = 'AAA' 
where col2=3 # where + select 사용

 

8.

select empno, sal
from emp
where sal >= (select max(sal) # 다중행
	from emp
    grouop by deptno)

• 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용이 가능하다. 단일 행 비교연산자는 서브쿼리의 결과가 반드시 1이하여야 하고, 복수행 연산자는 서브쿼리의 결과 건수와 상관없다

 

select 상품분류코드,
	avg(상품가격) as 상품가격,
    count(*) over(order by avg(상품가격) range between 1000 preceding and 1000 folwing) as유사개수
from 상품
group by 상품분류코드

• group by절의 집합을 원본으로 하는 데이터를 window function 과 함께 사용한다면 group by절과 window를 함께 사용해도 오류가 발생하지 않는다
• 유사개수 컬럼은 상품분류코드로 grouping 된 집합을 원본집합으로하여 상품분류코드별 평균상품가격을 서로 비교하여 현재 읽힌 상품분류코드의 평균가격 대비 -1000 ~ 1000사이에 존재하는 상품분류코드의 개수를 구한 것

9. 순위함수

• 순위함수에 order by를 사용해서 순위를 기준으로 오름차순 혹은 내림차순으로 출력할 수 있다
• rank() : 중복값은 중복등수, 등수건너뜀
• dense_rank() : 중복값은 중복등수, 등수 안 건너뜀, 동일등수 순위에 영향이 없다
• row_number() : 중복값이 있어도 고유 등수 부여

10. 조인

1) NL 조인

• 좁은범위에 유리
• 유리순차적 처리하며, random access위주
• 후행테이블에는 조인을 위한 인덱스가 생성되어야 함

 

2) sort merge join

• 해당테이블에 인덱스가 없을 때 수행
• 정렬한 후에 정렬된 데이블을 병합하면서 조인을 수행
• 조인 연결고리의 비교 연산자가 범위연산인 경우 유리
• 두 결과 집합의 크기 차이가 많이 나는 경우 비효율

 

3) hash

• 대량이 데이터처리가 필요하고, 쿼리수행 시간이 오래 걸리는 대용량에 적합
• nl과 sort의 조인문제점 해결
• 오버헤드 발생 가능