SQL 공부 일지 10일차 230829

## 공부정리

과목2 2장 SQL 활용 3편

4) 서브쿼리

5) 그룹함수

6) 윈도우함수

 

 

 

#4 서브쿼리

1 서브쿼리

• 하나의 SQL문 안의 SQL문
• 단일행 또는 복수행 비교 연산자와 함께 사용 가능
• 서브쿼리에선 ORDER BY 사용 불가(메인쿼리의 마지막 부분에만 위치 가능)
• 서브쿼리는 메인쿼리의 테이블의 칼럼 사용 가능(메인쿼리에선 서브쿼리의 칼럼 사용불가)

이미지 출처 : https://hoon93.tistory.com/30

 

* 서브쿼리가 SQL 문에서 사용 가능한 곳

- SELECT, FROM, WHERE, HAVING, ORDER BY절

- INSERT문의 VALUES절

- UPDATE문의 SET절

- DELECT문 사용 불가

 

 

2 동작방식에 따른 분류

서브쿼리의 종류	설명
Un-Correlated(비연관) 서브쿼리	서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리이다. 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용한다.
Correlated(연관) 서브쿼리	서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리이다. 일반적으로 메인쿼리가 먼저 수행되어 얽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용된다.

 

 

3 반환되는 데이터 형태에 따른 분류

서브쿼리 종류	설명
Single Row 서브쿼리 (단일 행 서브쿼리)	서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미한다. 단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용된다. 단일 행 비교 연산자에는 =, <, <=, >, >=, <> 이 있다.
Multi Row 서브쿼리 (다중 행 서브쿼리)	서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다. 다중 행 서브쿼리는 다중 행 비교 연산자와 함께 사용된다. 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
Multi Column 서브쿼리 (다중 칼럼 서브쿼리)	서브쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

 

 

4 다중 행 서브쿼리

- 다중 행 비교연산자

다중 행 연산자	설명
IN (서브쿼리)	서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다. (Multiple OR 조건)
비교연산자 ALL (서브쿼리)	서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. 비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 모든 결과 값을 만족해야 하므로, 서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족한다.
비교연산자 ANY (서브쿼리)	서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. 비교연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로, 서브쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족한다. (SOME은 ANY와 동일함)
EXISTS (서브쿼리)	서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않는다.

 

- 다중 행 서브쿼리 예제

SQL>>
"선수들 중에서 '정현수' 라는 선수가 소속되어 있는 팀 정보를 출력하라!"

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM
WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME='정현수')
ORDER BY TEAM_NAME;

 

 

5 그 밖의 위치에서 사용하는 서브쿼리

① SELECT 절에 서브쿼리 사용 -> 스칼라 서브쿼리(Scalar Subquery)

- 한 행, 한 칼럼만을 반환

- 값 하나를 반환하는 서브쿼리, SELECT 절에 사용하는 서브쿼리

- 스칼라 서브쿼리 대신 JOIN으로 동일한 결과 추출 가능

 

② 뷰

- 가상의 테이블, FROM절에 사용하는 뷰는 인라인 뷰 라고 한다. (실제 데이터는 X)

- SQL이 실행될 때만 임시적으로 생성되는 동적 뷰(일회성)

- 일반 뷰가 정적 뷰, 인라인 뷰는 동적 뷰

 

* 장점

- 독립성 : 테이블 구조 변경 자동 반영

- 편리성 : 쿼리를 단순하게 작성 가능

- 보안성 : 뷰를 생성할 때 칼럼을 제외할 수 있음

 

 

#5 그룹함수

1 데이터분석 개요

- ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 정의

- AGGREGATE FUNCTION(집계), GROUP FUNCTION(그룹), WINDOW FUNCTION(윈도우)

 

 

2 ROLLUP 함수

• ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용
• Grouping Columns의 수를 N이라고 했을 때 N + 1 Level의 Subtotal이 생성
• GROUP BY로 묶인 칼럼의 소계 계산, 계층 구조
• GROUP BY 칼럼 순서가 바뀌면 결과 값 바뀜
• GROUP By의 확장된 형태

 

* GROUP BY ROLLUP(A) : 전체 합계, 칼럼 A소계

* GROUP BY ROLLUP(A,B) : 전체 합계, 칼럼 A 소계, 칼럼 (A,B) 조합 소계

* GROUP BY ROLLUP(A,B,C) : 전체 합계, 칼럼 A소계, 칼럼 (A,B) 조합 소계, (A,B,C) 조합 소계

* GROUP BY ROLLUP(A,(B,C)) : 전체 합계, 칼럼 A소계, 칼럼 (A,(B,C)) 조합 소계

* GROUP BY ROLLUP(B) : A그룹별 집계, A그룹 내부에서 B칼럼별 집계

 

SQL>>
"부서명과 업무명을 기준으로 사원수과 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행"

SELECT DNAME, JOB, COUNT(*)"Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO GROUP BY DNAME, JOB;

 

 

3 CUBE 함수

- 결합 가능한 모든 값에 대한 다타원 집계

 

* GROUP BY CUBE(A) : 전체 합계, 칼럼 A 소계

* GROUP BY CUBE(B) : 전체 합계, 칼럼 A 소계, 칼럼 B 소계, 칼럼 (A,B) 소계

 

SQL>>
"부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행"

SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs'
ELSE JOB END AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY CUBE(DNAME, JOB);

 

 

 

4 GROUPING SETS 함수

- 특정 항목에 대한 소계 계산, GROUP BY 칼럼 순서와 무관하게 개별적으로 처리

- 내가 보고싶은 것만 소계를 생성

 

* GROUP BY GROUPING SETS(A) : 칼럼 A 소계

* GROUP BY GROUPING SETS(A, B) : 칼럼 A 소계, 컬럼 B 소계

* GROUP BY GROUPING SETS((A, B) : 컬럼 (A, B) 소계

 

 

 

 

#6 윈도우 함수

1 윈도우 함수(WINDOW FUNCTION)

- 여러 행 간의 관계 정의 함수, 중첩 불가

 

 

2 윈도우 함수 (WINDOW FUNCTION)의 종류

① 순위 함수

- RANK : 중복 순위 포함

- DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음)

- ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여

 

SQL>>
"사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다."

SELECT JOB, ENAME, SAL, RANK()
OVER (ORDER BY SAL DESC) ALL_RANK, RANK()
OVER (PARTTTION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

 

② 윈도우 일반 집계 (AGGREGATE) 함수

- SUM(합), MAX(최대값), MIN(최소값), AVG(평균값) 등

 

③ 행 순서 함수

- FIRST_VALUE / LAST_VALUE 함수 : 첫 값 / 끝 값

- LAG / LEAD : 이전 값 / 이후 값

- LEAD(E,A)는 E에서 A번째 행의 값을 호출하는 형태로도 쓰임 (A의 기본값은 1)

 

④ 비율 함수

- RATIO_TO_REPORT : 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점 반환

- PERCENT_RANK : 제일 먼저 나오는 것 0, 제일 늦게 나오느 것 1, 행의 순서별 백분율

- CUME_DIST : 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율

- NTILE : 전체 건수를 ARGUMENT 값으로 N 등분한 결과