- 01 집합 연산
- 02 그룹 함수
- 03 윈도우 함수
01 집합 연산
집합 연산자란?
각 쿼리의 결과 집합을 가지고 연산을 하는 명령어
1. UNION ALL
2. UNION
3. INTERSECT
4. MINUS / EXCEPT
1. 1. UNION ALL
- 각 쿼리의 결과 집합의 합집합이다.
- 중복된 행도 그대로 출력된다.
- UNION ALL은 QUERY1의 결과와 QUERY2의 결과를 그대로 합하는 것으로 중복된 행도 그대로 출력
1. 2. UNION
- 각 쿼리의 결과 집합의 합집합이다.
- 중복된 행은 한 줄로 출력된다.
- UNION은 QUERY1의 결과와 QUERY2의 결과를 합한 후 중복을 제거하여 출력
1. 3. INTERSECT
- 각 쿼리의 결과 집합의 교집합이다.
- 중복된 행은 한 줄로 출력된다.
- INTERSECT는 QUERY1의 결과와 QUERY2의 결과에서 공통된 부분만 중복을 제거하여 출력
1. 4. MINUS / EXCEPT
- 앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합이다.
- 중복된 행은 한 줄로 출력된다.
- MINUS / EXCEPT는 QUERY1의 결과에서 QUERY* 결과를 제거하고 출력
02 그룹 함수
그룹함수란?
- 데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수
1. 집계함수
2. 소계(총계) 함수
2. 1. 집계 함수
- COUNT, SUM, AVG, MAX, MIN 등
2. 2. 소계(총계) 함수
- ROLLUP, CUBE, GROUPING SETS 등
2. 2. 1. ROLLUP
소그룹 간의 소계 및 총계를 계산하는 함수
ROLLUP (A)
- A로 그룹핑
- 총합계
ROLLUP (A, B)
- A, B로 그룹핑
- A로 그룹핑
- 총합계
ROLLUP (A, B, C)
- A, B, C로 그룹핑
- A, B로 그룹핑
- A로 그룹핑
- 총합계
e.g. ROLLUP 함수 이해하기
위 데이터를 이용해 ROLLUP을 이해해보자
이 쿼리는 주문 수량을 날짜별로 그룹핑하여 카운트한 쿼리이다.
위 데이터에 ROLLUP 함수를 적용하여 합계를 구했다. 날짜별로 그룹핑+총합계가 나온다.
이 쿼리는 주문 수량을 날짜와 주문음료별로 그룹핑하여 카운트한 쿼리이다.
위 데이터에 ROLLUP 함수를 적용해서 소계와 총계를 구했다. 날짜별, 주문음료별로 그룹핑 + 날짜별로 그룹핑 + 총합계의 결과가 나온다.
다음 쿼리는 주문 수량을 날짜와 주문음료, 판매사원별로 그룹핑하여 카운트한 쿼리이다.
위 데이터에 ROLLUP 함수를 적용하여 소계와 총계를 구해보자.
날짜별, 주문음료별. 판매사원별로 그룹핑 + 날짜별. 주문음료별로 그룹핑 + 날짜별로 그룹핑 + 총 합계의 결과가 나왔다.
이제 ()를 하나씩 추가해 보도록 히자.
( )가 추가됐어도 원리는 동일하다. 위 쿼리의 결과 날짜별. 주문음료별. 판매사원별로 그룹핑 + 날짜 별, 음료별로 그룹핑 + 총합계의 결과가 나왔다. GROUP BY ROLLUP(ORDER_DT, ORDER_ ITEM, REG_NAME)과 비교했을 때 날짜별로 그룹핑한 Row가 빠진 것을 알 수 있다.
GROUP BY ROLLUP(ORDER__DT, (ORDER_ITEM, REG_NAME))은 날짜별, 주문음료별, 판매사원별로 그룹핑 + 날짜별로 그룹핑 + 총합계의 결과가 나다. GROUP BY ROLLUP(ORDER_DT, ORDERJTEM, REG_NAME)과 비교했을 때 날짜별, 주문음료별로 그 룹핑한 Row가 빠진 것을 알 수 있다
2. 2. 2. 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로 그룹핑
- B, C로 그룹핑
- A로 그룹핑
- B로 그룹핑
- C로 그룹핑
- 총합계
이 쿼리는 주문 수량을 날짜별로 그룹핑하여 카운트한 쿼리이다.
위 데이터에 CUBE 함수를 적용해서 합계를 구했다. 날짜별로 그룹핑+총합계의 결과가 나온다.
왼쪽, 주문 수량을 날짜와 주문음료별로 그룹핑하여 카운트한 쿼리를 CUBE 함수를 적용하여 소계와 총계를 구해보자.
날짜별, 주문음료별로 그룹핑 + 날짜별로 그룹핑 + 주문음료별로 그룹핑 + 총합계의 결과가 나왔다.
왼쪽, 주문 수량을 날짜와 주문음료, 판매사원별로 그룹핑하여 카운트한 쿼리에 CUBE 함수를 적용하여 소계와 총계를 구해보자.
날짜별, 주문음료별, 판매사원별로 그룹핑 + 날짜별, 주문음료별로 그룹핑 + 날짜별, 판매사원별로 그룹핑 + 주문음료별, 판매사원별로 그룹핑 + 날짜별로 그룹핑 + 주문음료별로 그룹핑 + 판매사원 별로 그룹핑 + 총합계의 결과가 나왔다,
이제 ()를 하나씩 추가해 보도록 하자.
날짜별. 주문음료별, 판매사원별로 그룹핑 + 날짜별. 주문음료별로 그룹핑 + 판매사원별로 그룹핑 + 총합계의 결과가 나왔다. GROUP BY ROLLUP((ORDER_DT, ORDERJTEM), REG_NAME)과 비 교했을 때 판매사원별로 그룹핑이 더해진 것을 알 수 있다.
GROUP BY CUBE(ORDER_DT, (ORDERJTEM, REG. NAME)) 결과를 보면, 날짜별. 주문음료별. 판매사원별로 그룹핑 + 날짜별로 그룹핑 + 주문음료별. 판매사원별로 그룹핑 + 총합계의 결과가 나왔다. GROUP BY ROLLUP(ORDER_DT, (ORDERJTEM, REG_NAME))과 비 교했을 때 주문음료별. 판매사원별로 그룹핑이 더해진 것을 알 수 있다.
2. 2. 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(C))
- A로 그룹핑
- B로 그룹핑
- C로 그룹핑
- 총합계
위의 과정과 동일한 예시를 들어보겠다.
데이터에 GROUPING SETS 함수를 적용하여 소계와 총계를 구해보자.
날짜별로 그룹핑 + 주문음료별로 그룹핑한 결과가 나왔다.
위 결과에 인자값에 ( )를 추가해 총계를 더해주었다.
위 결과에 ROLLUP을 적용하여 총계를 구해주었다.
이렇게 인자값에 ( )를 추가해주거나 ROLLUP을 적용해 총계를 구해줄 수 있다.
2. 2. 4. GROUPING
GROUPING 함수 : ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 Row를 구분할 수 있게 해준다. 앞선 경우는 소계를 나타내는 Row에서 그룹핑의 기준이 되는 컬럼을 제외하고는 모두 NULL 값으로 표현되었지만 GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트를 출력할 수 있다.
03 윈도우 함수
OVER 키워드와 함께 사용
3. 1. 순위 함수
RANK, DENSE_RANK, ROW_NUMBER
3. 1. 1. RANK
순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다.
부서별로 급여가 높은 사워부터 순위를 매긴 쿼리이다.
3. 1. 2. DENSE_RANK
순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다.
다음 쿼리는 부서별로 급여가 높은 사원부터 순위를 매긴 것이다.
3. 1. 3. ROW_NUMBER
순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.
다음 쿼리는 부서별로 급여가 높은 사원부터 순위를 매긴 것이다.
3. 2. 집계 함수
SUM, MAX, MIN. AVG, COUNT
3. 2. 1. SUM
데이터의 합계를 구하는 함수이다. 인자값으로는 숫자형만 올 수 있다.
3. 2. 2. MAX
데이터의 최댓값을 구하는 함수
3. 2. 3. MIN
데이터의 최솟값을 구하는 함수
3. 2. 4. AVG
데이터의 평균값을 구하는 함수
3. 2. 5. COUNT
데이터의 건수를 구하는 함수
3. 3. 행 순서 함수
FIRST_VALUE, LAST_VALUE, LAG, LEAD
3. 3. 1. FIRST_VALUE
파티션별 가장 선두에 위치한 데이터를 구하는 함수
SQL Server(MSSQL)에서는 지원하지 않는다.
3. 3. 2. LAST_VALUE
파티션별 가장 끝에 위치한 데이터를 구하는 함수
SQL Server(MSSQL)에서는 지원하지 않는다.
3. 3. 3. LAG
파티션별로 특정 수만큼 앞선 데이터를 구하는 함수
SQL Server(MSSQL)에서는 지원하지 않는다.
3. 3. 4. LEAD
파티션별 특정 수만큼 뒤에 있는 데이터를 구하는 함수
SQL Server(MSSQL)에서는 지원하지 않는다.
3. 4. 비율 함수
RATlO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE
3. 4. 1. RATlO_TO_REPORT
파티션별 합계에서 차지하는 비율을 구하는 함수
SQL Server(MSSQL)에서는 지원하지 않는다
3. 4. 2. PERCENT_RANK
해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
SQL Server(MSSQD에서는 지원하지 않는다.
3. 4. 3. CUME_DIST
해당 파티션에서의 누적 백분율을 구하는 함수
결과값은 0보다 크고 1보다 작거나 같은 값을 가진다.
SQL Server(MSSQL)에서는 지원하지 않는다.
3. 4. 4. NTILE
주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수