🖥️ 들어가며
📌 Rows와 Range
: Rows는 실제 행의 수를 기준으로, Range는 Order By한 컬럼 값을 기준으로 범위를 지정합니다.
✏️ 1. 누적 매출 구하기
- 기존 쿼리
- 자체 조인을 사용하여 각 지점의 매월 매출액, 누적 매출액을 함께 표시합니다.
- T2.판매월 <= T1.판매월 조건으로 현재 월과 이전 월의 데이터를 모두 가져옵니다.
- 변경 쿼리
- GROUP BY를 사용하여 결과를 요약합니다.
- MIN(T1.매출액): 현재 월의 매출액
- SUM(T2.매출액): 현재 월까지의 누적 매출액
- 큰 쪽 테이블 T1을 GROUP BY, MIN에서 사용 / 작은 쪽 테이블 T2은 SUM 누적액에서 유일하게 사용합니다.
- 윈도우 함수 활용
- 윈도우 함수(SUM OVER)를 사용하여 누적액을 계산합니다.
- PARTITION BY 지점: 각 지점별로 별도 누적을 계산합니다.
- ORDER BY 판매월: 판매월 순서대로 누적을 계산합니다.
- ROW BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 첫 행부터 현재 행까지의 범위를 지정합니다.
-- 기존
SELECT T1.지점, T1.판매월, T1.매출액, T2.판매월, T2.매출액
FROM 누적매출 T1, 누적매출 T2
WHERE T2.지점 = T1.지점
AND T2.판매월 <= T1.판매월
ORDER BY T1.지점, T1.판매월, T2.판매월;
-- 변경
SELECT T1.지점, T1.판매월,
MIN(T1.매출액) AS 매출액,
SUM(T2.매출액) AS 누적액
FROM 누적매출 T1, 누적매출 T2
WHERE T2.지점 = T1.지점
AND T2.판매월 <= T1.판매월
ORDER BY T1.지점, T1.판매월;
-- 윈도우 함수 활용
SELECT 지점, 판매월, 매출액,
SUM(매출액) OVER (PARTITION BY 지점 ORDER BY 판매월
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적액,
FROM 누적매출;
✏️ 2. 윈도우 함수
👉🏻 단일행 vs. 다중행 함수 비교
- 단일행: TO_CHAR, SUB_STRING
- 다중행: 윈도우 함수, 집계함수 (집계함수 + OVER => 윈도우 함수)
- 윈도우 함수 종류
| 함수 구분 | 함수 | 설명 | 비고 |
| 순위 | RANK | 순위 | 1,2,2,4,5 |
| DENSE_RANK | 동일 순위 1건으로 산정 | 1,2,2,3,5 | |
| ROW_NUMBER | 동일 순위 미인정 | 1,2,3,4,5 | |
| 집계 | SUM, MAX, MIN, AVG, COUNT | 합계, 최대값, 최소값, 평균, 건수 | |
| 행순서 | FIRST_VALUE, LAST_VALUE | 파티션 내의 첫번째 값, 최종값 | |
| LAG, LEAD | 이후 레코드의 값, 이전 레코드의 값 | ||
| 비율 | CUME_DIST | 파티션 내 데이터 값에 대한 누적 비율 | 결과값 0~1 사이 |
| PERCENT_RANK | 파티션 내 row 개수에 대한 비율 | ||
| RATO_TO_REPORT | 파티션 내 비율 | ||
| NTILE | 인자 값으로 전체 로우 개수를 나누고 인자 값의 순서를 지정 | ntile(4) over(order by sal) sal 컬럼순으로 정렬 → 전체 로우별 4등분 → 1~4 값 설정 |
- 윈도우 함수 사용법
- Between 사용 시
| rows |
unbounded preceding (맨 위에서부터) |
current row | ||
| between | 10 preceding (위에서 10개) |
and | unbounded following (맨 밑까지) |
|
| range | current row | 10 following (밑에서 10개) |
(... ORDER BY C1
RANGE BETWEEN 10 PRECEDING AND CURRENT ROW)
-- Order By한 컬럼 값을 기준으로 범위 지정| C1 | C2 | 결과 |
| 10 | 10 | 10 |
| 20 | 20 | 10+20 |
| 30 | 30 | 10+20+30+40 |
| 30 | 40 | 10+20+30+40 |
- Between 미사용
| rows | unbounded preceding: 맨 위 |
| 10 preceding: 위에서 10개 | |
| range | current row: 현재 행까지 |
| unbounded following: 맨 아래 | |
| 10 following: 밑에서 10개 |
📒 정리하면
- 누적 매출 구하기: 기존의 자체 조인 방식, GROUP BY를 활용한 변경 방식, 윈도우 함수를 활용한 방식 중 윈도우 함수 방식이 가장 효율적입니다. (SUM OVER)
- 윈도우 함수 종류: 순위(RANK, DENSE_RANK, ROW_NUMBER), 집계(SUM, AVG 등), 행순서(LAG, LEAD 등), 비율(NTILE 등)
- 윈도우 함수는 PARTITION BY로 그룹화, ORDER BY로 정렬 순서를 지정할 수 있습니다.
- ROWS: 실제 행의 수를 기준 으로 / RANGE: ORDER BY한 컬럼 값을 기준으로 범위를 지정합니다.
- UNBOUNDED PRECEDING은 처음부터, CURRENT ROW 현재 행까지, UNBOUNDED FOLLOWING은 마지막까지, n PRECEDING/FOLLOWING: 위에서 n개/밑에서 n개
반응형
