SQLD(SQL 활용)

참고자료 : https://blog.naver.com/ekf1121_?Redirect=Log&logNo=223481107906&from=postView&trackingCode=blog_bloghome_searchlist

 

□ 서브쿼리

1. 연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가짐
2. 비연관 서브쿼리 : 메인쿼리에 값을 제공하기 위한 목적으로 사용됨
3. 단일 행 서브쿼리 : 실행결과가 항상 1건 이하, 단일 행 비교 연산자와 사용
4. 다중 행 서브쿼리 : 실행결과가 어려건인 서브쿼리, 당중행 비교 연산자와 함께 사용
   • IN : 결과에 값이 포함되는지 확인
   • ANY : 결과중 하나라도 조건을 만족하는지 확인
   • ALL : 모든 값이 조건을 만족하는지 확인
   • EXISTS : 결과가 존재하는지 여부를 확인
5. 다중 컬럼 서브쿼리 : 여러 컬럼 반환, 메인쿼리 조건절에 따라 여러 컬럼 동시에 비교 가능
6. 스칼라 서브쿼리 : SELECT 절에서 사용, 한 행, 한 컬럼만을 반환
7. 인라인 뷰(동적 뷰) : FROM 절에서 사용, 서브쿼리를 임시 테이블처럼 사용
8. HAVING 절, ORDER BY 절에서도 사용 가능

□ 집합 연산자

1. UNION(중복 제거)
2. UNION ALL(결과 전부 합침)
3. INTERSECT(교집합, 중복 제거)
4. EXCEPT(차집합, 중복 제거)

□ 그룹 함수

1. ROLLUP(1,2) : 1과 2별 소계, 1별 소계, 총 합계(계층 구조, 순서가 바뀌면 결과밧이 바뀜)
2. CUBE(1,2) : 1과 2별 소계, 1별 소계, 2별 소계, 총 합계(순서 무관)
3. GROUPING SETS(1,2) : 1별 소계, 2별 소계(순서 무관)

empno	ename	deptno	job	sal
101	Kim	10	MANAGER	3000
102	Lee	10	CLERK	1000
103	Park	20	MANAGER	2500
104	Choi	20	CLERK	1200

 

- ROLLUP(deptno, job)

deptno	job	total_sal
10	MANAGER	3000
10	CLERK	1000
10	NULL	4000
20	MANAGER	2500
20	CLERK	1200
20	NULL	3700
NULL	NULL	7700

 

- CUBE(deptno, job)

deptno	job	total_sal
10	MANAGER	3000
10	CLERK	1000
10	NULL	4000
20	MANAGER	2500
20	CLERK	1200
20	NULL	3700
NULL	MANAGER	5500
NULL	CLERK	2200
NULL	NULL	7700

 

- GROUPING SETS(deptno, job)

deptno	job	total_sal
10	NULL	4000
20	NULL	3700
NULL	MANAGER	5500
NULL	CLERK	2200

□ 윈도우 함수

범주	함수	설명
순위	ROW_NUMBER RANK DENSE_RANK	파티션 내에서 행의 순위를 매김 RANK는 동일 값이면 건너뛰기 DENSE_RANK는 건너뛰지 않음
집계	SUM AVG MAX MIN COUNT	파티션 내 행들에 대한 합계 파티션 내 행들에 대한 평균 파티션 내 행들에 대한 최대 값 파티션 내 행들에 대한 최소 값 파티션 내 행들에 대한 개수 계산
행 순서	FIRST_VALUE LAST_VALUE LAG LEAD	특정 순서 기준 첫 번째값 가져오기 마지막 값 가져오기 이전 행 값 참조 다음 행 값 참조
비율	PERCENT_RANK CUME_DIST NTILE	파티션 내 상대적 순위 누적 분포 구간 나누기 계산

 

- 문법

SELECT 
    윈도우함수(A) 
    OVER (
        PARTITION BY 컬럼
        ORDER BY 컬럼
        ROWS | RANGE 윈도잉 절
    ) AS 별칭
FROM 테이블명;

 

• BETWEEN a AND b : 프레임 범위 지정
• UNBOUNDED PRECEDING/FOLLOWING : 프레임 시작/끝을 현재 윈도우 그룹의 첫 번째/마지막 행으로 설정
• N PRECEDING/FOLLOWING : 현재 행을 기준으로 지정 된 수만큼 이전/이후의 행을 나타냄
• CURRENT ROW : 현재 행을 기준으로 윈도우 프레임을 설정

□ Top N 쿼리

• ORDER BY : 데이터 정렬
• LIMIT : 정렬된 결과에서 상위 N개 행 선택
• FETCH : 결과 집합에서 상위 N개 행 선택
• TOP(n) WITH TIES : 값이 동일한 경우 함께 출력

□ 계층형 질의와 셀프 조인

• CONNECT BY : 트리형태의 구조로 쿼리 수행
• START WITH : 계층 구조 전개의 시작 위치(최상위 행) 지정
• CONNECT_BY_ROOT/ISLEAF : 최상위/하위 계층 값
• SYS_CONNECT_BY_PATH : 계층 구조의 전개 경로
• ORDER BY SIBLINGS BY : 형제 노드 사이에서 정렬

1. SQL Server에서 계층형 질의문은 CTE(Common Table EXPRESSION)를 재귀호출함으로써 계층구조를 전개한다.
2. "" 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.
3. 오라클의 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용한다.
4. "" PRIOR 키워드는 CONNECT BY 절 뿐만 아니라 SELECT, WHERE 절에서도 사용할 수 있다.

- 셀프조인 : 동일 테이블 사이의 조인, 식별을 위해 반드시 테이블 명칭(Alias) 사용

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

 

- 뷰 사용의 장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램을 변경하지 않아도 된다.
2. 편리성 : 복잡한 질의 단순하게 작성할 수 있다.
3. 보안성 : 숨기고 싶은 정보 빼고 생성할 수 있다.