윈도우 함수
- OVER키워드와 함께 사용.
- 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER
- 집계 함수 : SUM, MAX, MIN, AVG, COUNT
- 행 순서 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD
- 비율 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
1. 순위 함수
1) RANK : 1, 2, 2, 4, 5, 5, 7 ...
- 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뜀.
SELECT ORDER_DT,
COUNT(*),
RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT;2) DENSE_RANK : 1, 2, 2, 3, 4, 4, 5 ...
- 같은 순위가 존재하더라도 다음 순위를 건너뛰지않음.
- DENSE가 '밀집한'이라는 뜻을 가짐. '순위가 밀집되어 있다'로 기억.
SELECT ORDER_DT,
COUNT(*),
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS DENSE_RANK
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT;3) ROW_NUMBER : 1, 2, 3, 4, 5, 6, 7 ...
- 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여.
SELECT ORDER_DT,
COUNT(*),
ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS ROW_NUMBER
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT;2. 집계 함수
1) SUM
//↓모든 사람들의 점수의 합계 구하는 쿼리
SELECT SUM(SCORE) AS TOTAL_SCORE FROM SQLD;
//↓개인별 총점수를 구하는 쿼리
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
SUM(SCORE) OVER(PARTITION BY STUDENT_NAME) AS TOTAL_SCORE
FROM SQLD;2) MAX
//↓모든 점수를 대상으로 최댓값을 반환
SELECT MAX(SCORE) AS MAX_SCORE FROM SQLD;
//↓과목별 최대 점수를 구하는 쿼리
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
MAX(SCORE) OVER(PARTITION BY SUBJECT) AS MAX_SCORE
FROM SQLD;3) MIN
//↓모든 점수를 대상으로 최솟값 반환
SELECT MIN(SCORE) AS MIN_SCORE FROM SQLD;
//↓과목별 최소 점수를 구하는 쿼리
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
MIN(SCORE) OVER(PARTITION BY SUBJECT) AS MIN_SCORE
FROM SQLD;4) AVG
//↓모든 점수를 대상으로 평균값 반환
SELECT AVG(SCORE) AS AVG_SCORE FROM SQLD;
//↓과목별 평균 점수를 구하는 쿼리
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
ROUND(AVG(SCORE) OVER(PARTITION BY SUBJECT)) AS AVG_SCORE
FROM SQLD;5) COUNT
//↓모든 점수를 대상으로 건수를 반환
SELECT COUNT(*) AS MAX_SCORE FROM SQLD;
//↓과목별 PASS한 건수를 구하는 쿼리
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
COUNT(*) OVER(PARTITION BY SUBJECT) AS PASS_COUNT
FROM SQLD
WHERE RESULT = 'PASS';참고) 윈도우 함수 사용 옵션
WINDOWING절을 이용하여 집계하려는 데이터의 범위를 지정할 수 있음.
- UNBOUNDED PRECEDING : 위쪽 끝 행
- UNBOUNDED FOLLOWING : 아래쪽 끝 행
- CURRENT ROW : 현재 행
- n PRECEDING : 현재 행에서 위로 n만큼 이동
- n FOLLOWING : 현재 행에서 아래로 n만큼 이동
- ROWS : 행 자체가 기준이 됨.
- RANGE : 행이 가지고 있는 데이터 값이 기준이 됨.
예시)
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
: 처음부터 현재 행까지, RANGE UNBOUNDED PRECEDING과 같은 의미
RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
: 현재 행이 가지고 있는 값볻 100만큼 적은 행부터 현재 행까지, RANGE 10 PRECEDING 과 같은 의미
RANGE GETWEEN CURRENT ROW AND UNBOUDED FOLLOWING
: 현재 행부터 끝까지
ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
: 현재 행부터 아래로 5만큼 이동한 행까지3. 행 순서 함수
1) FIRST_VALUE
- 파티션 별 가장 처음에 위치한 데이터를 구하는 함수.
// ↓ 모든 점수를 대상으로 오름차순 후 첫번째에 위치하는 점수를 출력.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
FIRST_VALUE(SCORE) OVER(PARTITION BY SUBJECT
ORDER BY SCORE DESC) AS FIRST_VALUE
FROM SQLD;2) LAST_VALUE
- 파티션 별 가장 끝에 위치한 데이터를 구하는 함수.
// ↓ 모든 점수를 대상으로 오름차순 후 마지막에 위치하는 점수를 출력.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
LAST_VALUE(SCORE) OVER(PARTITION BY SUBJECT
ORDER BY SCORE DESC) AS LAST_VALUE
FROM SQLD;
// 이와 같이 사용하면 원하는대로 출력이 안된다.
// WINDOWING절의 default가 RANGE UNBOUNDED PRECEDING이어서 파티션의 범위가 맨 위 끝 행부터 현재행까지로 지정되었기 때문에 원하는대로 출력이 안되는 것.
// 아래와 같이 WINDOWING절을 명시해줘야함.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
LASST_VALUE(SCORE) OVER(PARTITION BY SUBJECT
ORDER BY SCORE DESC
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS LAST_VALUE
FROM SQLD;3) LAG
- 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수
//과목별로 본인보다 2만큼 앞에 있는(높은) 점수를 구하는 쿼리.
//LAG의 두번째 인자(여기서는 2)를 생략하면 default 1이 됨.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
LAG(SCORE,2) OVER(PARTITION BY SUBJECT
ORDER BY SCORE DESC) AS LAG
FROM SQLD;4) LEAD
- 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수.
//과목별로 본인보다 2만큼 뒤에 있는(낮은) 점수를 구하는 쿼리.
//LEAD의 두번째 인자(여기서는 2)를 생략하면 default 1이 됨.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
LEAD(SCORE, 2) OVER(ORDER BY SCORE) AS LEAD
FROM SQLD;4. 비율 함수
1) RATIO_TO_REPORT ( 0<= X <=1 )
- 파티션 별 합계에서 차지하는 비율을 구하는 함수.
// 과목별 SCORE 합계에서 차지하는 비율을 구하는 쿼리.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
SUM(SCORE) OVER(PRTITION BY SUBJECT) AS SUM,
SCORE/SUM(SCORE) OVER (PARTITION BY SUBJECT) AS "SCORE/SUM",
RATIO_TO_REPORT(SCORE) OVER(PARTITION BY SUBJECT) AS RATIO_TO_REPORT
FROM SQLD;2) PERCENT_RANK ( 0<= X <=1 )
- 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수.
// PERCENT_RANK 함수의 결과가 RANK순위 값에서 1을 뺀 값을 총 COUNT에서 1을 뺀 값으로 나눈 값과 동일함.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
RANK() OVER(ORDER BY SCORE) AS RANK,
COUNT(*) OVER() AS COUNT,
(RANK() OVER (ORDER BY SCORE) -1) /(COUNT(*) OVER()-1) AS "(RANK-1)/(COUNT-1)",
PERCENT_RANK() OVER(ORDER BY SCORE) AS PERCENT_RANK
FROM SQLD;
// 과목별로 나눈 파티션에서 해당 SCORE가 차지하는 백분위 순위를 구하는 쿼리.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS RANK,
COUNT(*) OVER(PARTITION BY SUBJECT) AS COUNT,
(RANK() OVER (PARTITION BY SUBJECT ORDER BY SCORE) -1) /(COUNT(*) OVER()-1) AS "(RANK-1)/(COUNT-1)",
PERCENT_RANK() OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS PERCENT_RANK
FROM SQLD;3) CUME_DIST ( 0<= X <=1 )
- 해당 파티션에서의 누적 백분율을 구하는 함수. 결과값은 0보다 크고 1보다 작거나 같은 값을 가짐.
// CUME_DIST 함수의 결과가 현재 행까지의 누적 건수에서 전체 건수로 나눈 값과 동일함.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
COUNT(*) OVER(ORDER BY SCORE) AS COUNT,
COUNT(*) OVER() AS TOTAL_COUNT,
COUNT(*) OVER(ORDER BY SCORE)/COUNT(*) OVER() AS "COUNT/TOTAL_COUNT",
CUME_DIST() OVER(ORDER BY SCORE) AS CUME_DIST
FROM SQLD;
// 과목별로 나눈 파티션에서 해당 SCORE에 해당하는 누적 백분율을 구하는 쿼리
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
COUNT(*) OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS COUNT,
COUNT(*) OVER(PARTITION BY SUBJECT ) AS TOTAL_COUNT,
COUNT(*) OVER(PARTITION BY SUBJECT ORDER BY SCORE)/COUNT(*) OVER() AS "COUNT/TOTAL_COUNT",
CUME_DIST() OVER(PARTITION BY SUBJECT ORDER BY SCORE) AS CUME_DIST
FROM SQLD;4) NTILE: 인자값에 주어진 수대로 등급이 할당.
- 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수.
SELECT STUDENT_NAME,
SUBJECT,
SCORE,
NTILE(1) OVER(ORDER BY SCORE DESC) AS NTILE1,
NTILE(3) OVER(ORDER BY SCORE DESC) AS NTILE3,
NTILE(5) OVER(ORDER BY SCORE DESC) AS NTILE5,
FROM SQLD;TOP-N 쿼리
- 탑100처럼 100위까지를 조회하는 방식. TOP-N의 데이터를 추출하기 위한 몇 가지 방식들이 있음.
1. ROWNUM
- 엑셀 작성 시 순번이 필요한 상황이 종종 생기는데 열을 만들어서 순번을 자동번호를 매기는 경우가 있음. 이와 같이 SELECT 절에 ROWNUM 컬럼을 하나 추가하면 됨.
- 행이 추가될 때 ROWNUM이 하나씩 추가되는 것이므로 WHERE ROWNUM = 5과 같은 건너뛰기 조건은 성립되지 않음. 항상 <, <=조건으로 사용해야함.
SELECT ROWNUM,
이름,
국어,
수학,
영어
FROM EXAM_SCORE;2. 윈도우 함수의 순위 함수
- 순위 함수를 이용하여 TOP-N 쿼리를 작성할 수 있음.
1) ROW_NUMBER 함수
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY 국어 DESC, 영어 DESC, 수학 DESC) AS RNUM,
이름,
국어,
영어,
수학
FROM EXAM_SCORE)
WHERE RNUM <=5;2) RANK 함수
SELECT * FROM (
SELECT RANK() OVER(ORDER BY 국어 DESC, 영어 DESC, 수학 DESC) AS RANK,
이름,
국어,
영어,
수학
FROM EXAM_SCORE)
WHERE RANK <=5;3) DENSE_RANK 함수
SELECT * FROM (
SELECT DENSE_RANK() OVER(ORDER BY 국어 DESC, 영어 DESC, 수학 DESC) AS DR,
이름,
국어,
영어,
수학
FROM EXAM_SCORE)
WHERE DR <=5;셀프조인(SELF JOIN)
- 나 자신과의 조인. FROM절에 같은 테이블이 두 번이상 등장하기 때문에 꼭 ALIAS를 이용해야함.
- 카테고리의 경우 셀프 조인이 반복될 수 있음.(대분류-중분류-소분류)
SELECT A.CATEGORY_TYPE,
A.CATEGORY_NAME,
B.CATEGORY_TYPE,
B.CATEGORY_NAME,
C.CATEGORY_TYPE,
C.CATEGORY_NAME
FROM CATEGORY A,
CATEGORY B,
CATEGORY C
WHERE A.CATEGORY_NAME = B.PARENT_CATEGORY
AND B.CATEGORY_NAME = C.PARENT_CATEGORY;계층 쿼리
- 카테고리의 깊이가 깊어질수록 셀프 조인이 반복되는데 이때 계층 쿼리를 사용하면 간단하게 쿼리를 사용할 수 있음.
- 테이블에 계층구조를 이루는 컬럼이 존재할 경우 계층 쿼리 이용하여 데이터를 출력할 수 있음.
// 위의 셀프조인의 쿼리를 계층 쿼리로 변경하면 아래와 같다.
SELECT LEVEL,
SYS_CONNECT_BY_PATH('['||CATEGORY_TYPE||']'||CATEGORY_NAME,'-') AS PATH
FROM CATEGORY
START WITH PARENT_CATEGORY IS NULL,
CONNECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY;- LEVEL : 현재의 DEPTH를 반환. 루트 노드는 1.
- SYS_CONNECT_BY_PATH(컬럼, 구분자): 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수.
- START WITH: 경로가 시작되는 루트 노드를 생성해주는 절.
- CONNECT BY: 루트로부터 자식 노드를 생성해주는 절. 조건에 만족하는 데이터가 없을 때까지 노드를 생성.
- PRIOR: 바로 앞에 있는 부모 노드의 값을 반환.
- ORDER SIBLINGS BY절 : 정렬할 수 있음 (ORDER BY절 사용 대신)
시간이 걸릴 뿐 내가 못할 건 없다.