SQL 활용 - 윈도우 함수, TOP-N, 셀프조인, 계층쿼리

yy·2024년 4월 2일
0

잡동산이

목록 보기
19/21

윈도우 함수

  • 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절 사용 대신)
profile
시간이 걸릴 뿐 내가 못할 건 없다.

0개의 댓글