SQL 활용(서브쿼리, 집합연산자, 그룹함수, 윈도우 함수)

1. 서브쿼리(Subquery)

하나의 쿼리 안에 존재하는 또다른 쿼리로 위치에 따라 아래와 같이 나눌 수 있다.

• SELECT 절 : 스칼라 서브쿼리
• FROM 절 : 인라인 뷰
• WHERE절, HAVING 절 : 중첩 서브쿼리

2. 집합 연산자

1) UNION ALL

QUERY1의 결과와 QUERY2의 결과를 그대로 합하는 것으로 중복된 행도 그대로 출력된다.

2) UNION

QUERY1의 결과와 QUERY2의 결과를 합한 후 중복을 제거하여 출력한다.

 

3) INTERSECTION

QUERY1의 결과와 QUERY2의 결과에서 공통된 부분만 중복을 제거하여 출력한다.

4) MINUS / EXCEPT

QUERY1의 결과에서 QUERY2의 결과를 제거하고 출력한다.

 

3. 그룹 함수

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

 

1) ROLLUP

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

2) CUBE

ROLLUP이 일방향으로 그룹핑하여 소계를 구했다면 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로 그룹핑, 총합계

3) GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수로, 인자값으로 ROLLUP이나 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(B)) : A로 그룹핑, B로 그룹핑, C로 그룹핑, 총합계

4. 윈도우 함수

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

1) 순위 함수

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

• DENSE_RANK : 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다.
• ROW_NUMBER : 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.

2) 집계 함수

• SUM : 데이터의 합계를 구하는 함수이다. 인자값으로는 숫자형만 올 수 있다.
• MAX : 데이터의 최댓값을 구하는 함수이다.
• MIN : 데이터의 최솟값을 구하는 함수이다.
• AVG : 데이터의 평균값을 구하는 함수이다.
• COUNT : 데이터의 건수를 구하는 함수이다.

3) 행 순서 함수

• FIRST_VALUE : 파티션별 가장 선두에 있는 데이터를 구하는 함수이다. SQL Server(MSSQL)에서는 지원하지 않는다.
• LAST_VALUE : 파티션별 가장 끝에 위치한 데이터를 구하는 함수이다.
• LAG(A,Num) : 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수이다. (Default = 1)
• LEAD : 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수이다.

4) 비율 함수

• RATIO_TO_REPORT : 파티션 별 합계에서 차지하는 비율을 구하는 함수이다.
• PERCENT_RANK : 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수이다.
• CUME_DIST : 해당 파티션에서의 누적 백분을을 구하는 함수이다. 결과값은 0보다 크고 1보다 작거나 같다.
• NTILE : 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수이다.