[SQLD]「2」(2.6) RANK, MAX/AVG/SUM, WINDOWING 절

[SQLD]「2」(2.6) RANK, MAX/AVG/SUM, WINDOWING 절

「1」 데이터 모델링의 이해

> 「2」 SQL 기본 / 활용

「3」 관리 구문

 

1. 서브쿼리

2. 뷰

3. 집합 연산자

4. 소계(총계) 함수

> 5. 윈도우 함수

6. Top-N 쿼리

7. 셀프 조인

8. 계층 쿼리

 

5. 윈도우 함수

윈도우 함수

⸰ OVER 키워드와 함께 사용

 

√.PARTITION BY: 기존 행의 데이터와 집계된 값을 함께 볼 수 있음, OVER( ) / 윈도우 함수와 함께 사용

√.GROUP BY: 기존의 행이 하나로 합쳐져 집계된 값 반환하여 기존 행의 데이터 함께 볼 수 없음

 

⸰ 역할에 따라 분류함

  - 순위 함수 :RANKDENSE_RANKROW_NUMBER

  - 집계 함수 :SUMMAXMINAVGCOUNT

  - 행 순서 함수 :FIRST_VALUELAST_VALUELAGLEAD

  - 비율 함수 :CUME_DISTPERCENT_RANKNTILERATIO_TO_REPORT

 

순위 함수

⸰RANK: 순위를 매기며 같은 순위는 존재하는 수만큼 다음 순위를 건너뛰어 부여

  ‣1,2,2,4,5,5,7...

SELECT 칼럼명1, 
       칼럼명2,
       칼럼명3,
       RANK() OVER(PARTITION BY 칼럼명2 ORDER BY 칼럼명3 DESC) AS RANK
  FROM 테이블명;

 

⸰DENSE_RANK: 순위를 매기며 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 부여

  ‣1,2,2,3,4,4,5...

SELECT 칼럼명1, 
       칼럼명2,
       칼럼명3,
       DENSE_RANK() OVER(PARTITION BY 칼럼명2 ORDER BY 칼럼명3 DESC) AS DENSE_RANK
  FROM 테이블명;

 

⸰ROW_NUMBER: 순위를 매기며 동일한 값이라도 각기 다른 순위를 부여

  ‣1,2,3,4,5,6,7...

SELECT 칼럼명1, 
       칼럼명2, 
       칼럼명3,
       ROW_NUMBER() OVER(PARTITION BY 칼럼명2 ORDER BY 칼럼명3 DESC) AS ROW_NUMBER
  FROM 테이블명;

 

집계 함수

⸰WINDOWING 절: 집계하려는 데이터의 범위를 지정할 수 있음

 

[RANGE|ROWS] BETWEEN [UNBOUNDED PRECEDING|CURRENT ROW|n PRECEDING]

AND [UNBOUNDED FOLLOWING|CURRENT ROW|n FOLLOWING]

 

  -RANGE: 행이 가지고 있는 데이터 값이 기준, 같은 데이터 값까지 계산 

  -ROWS: 행 자체가 기준, 행 별로 계산

 

  -UNBOUNDED PRECEDING: 위쪽 끝 행 

  -CURRENT ROW: 현재 행 

  -n PRECEDING: 현재 행에서 위로 n 만큼 이동

 

  -UNBOUNDED FOLLOWING: 아래쪽 끝 행

  -CURRENT ROW: 현재 행

  -n FOLLOWING: 현재 행에서 아래로 n 만큼 이동

 

  ‣RANGEUNBOUNDED PRECEDING이 기본값

  (=RANGEBETWEENUNBOUNDED PRECEDINGANDCURRENT ROW)

 

  -RANGEBETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING   

    ‣ (현재 데이터 값을 기준으로) 위쪽 끝 행부터 아래쪽 끝 행까지 

  -RANGEBETWEEN10 PRECEDINGANDCURRENT ROW   

    ‣ (현재 데이터 값을 기준으로) 10 만큼 적은 행부터 현재 행까지, =RANGE 10 PRECEDING 

  -ROWSBETWEENCURRENT ROWANDUNBOUNDED FOLLOWING   

    ‣ (현재 행을 기준으로) 현재 행부터 아래쪽 끝 행까지 

  -ROWSBETWEENCURRENT ROWAND5 FOLLOWING   

    ‣ (현재 행을 기준으로) 현재 행부터 아래로 5 만큼 이동한 행까지

 

① ORDER BY 절이 있으면,

    WINDOWING 절은 기본적으로RANGEBETWEENUNBOUNDED PRECEDINGANDCURRENT ROW를 생성

 

② 만약, ORDER BY 절이 없고 PARTITION BY 절만 존재한다면,

    WINDOWING 절은 해당 PARTITION BY 절의 모든 ROW 를 대상으로 생성

 

③ 만약, PARTITION BY 절조차 없다면,

    WINDOWING 절은 전체 데이터의 ROW 를 대상으로 생성

 

⸰SUM: 데이터의 합계를 구하는 함수, 인자값으로는 숫자형만 가능

SELECT 칼럼명1,
       칼럼명2,
       칼럼명3,
       SUM(칼럼명3) OVER(PARTITION BY 칼럼명1
                             ORDER BY 칼럼명2 [ASC|DESC]
                             RANGE UNBOUNDED PRECEDING) AS 새로운칼럼명3
  FROM 테이블명;

칼럼명1	칼럼명2	칼럼명3	새로운칼럼명3
데이터1_A	데이터2_A	AA	AA
데이터1_A	데이터2_B	AB	AA+AB
데이터1_B	데이터2_A	BA	BA
데이터1_B	데이터2_B	BB	BA+BB
데이터1_C	데이터2_A	CA	CA
데이터1_C	데이터2_B	CB	CA+CB

‣RANGE이므로AA와AB가 같다면, 첫 번째 행의SUM이 두 번째 행SUM과 같은AA+AB이 됨

 

√. OVER 절에서 ORDER BY 절을 명시해주면, 기본값인RANGEUNBOUNDED PRECEDING구문 없이도 누적합 집계 가능

SELECT 칼럼명1,
       칼럼명2,
       칼럼명3,
       SUM(칼럼명3) OVER(ORDER BY 칼럼명3 [ASC|DESC]) AS 새로운칼럼명3
  FROM 테이블명;
 WHERE 칼럼명2 = 조건;

칼럼명1	칼럼명2	칼럼명3	새로운칼럼명3
데이터1_A	데이터2_B	AB	AB
데이터1_B	데이터2_B	BB	AB+BB
데이터1_C	데이터2_B	CB	AB+BB+CB

 

⸰MAX: 데이터의 최댓값을 구하는 함수

SELECT 칼럼명1,
       칼럼명2,
       MAX(칼럼명2) OVER(PARTITION BY 칼럼명1) AS 새로운칼럼명2
  FROM 테이블명;

 

⸰MIN: 데이터의 최솟값을 구하는 함수

SELECT 칼럼명1,
       칼럼명2,
       MIN(칼럼명2) OVER(PARTITION BY 칼럼명1) AS 새로운칼럼명2
  FROM 테이블명;

 

⸰AVG: 데이터의 평균값을 구하는 함수

SELECT 칼럼명1,
       칼럼명2,
       ROUND(AVG(칼럼명2) OVER(PARTITION BY 칼럼명1)) AS 새로운칼럼명2
  FROM 테이블명;

 

⸰COUNT: 데이터의 건수를 구하는 함수

SELECT 칼럼명1,
       칼럼명2,
       COUNT(*) OVER(PARTITION BY 칼럼명1
                         ORDER BY SCORE [ASC|DESC]
                         RANGE UNBOUNDED PRECEDING) AS COUNT
  FROM 테이블명;

‣ OVER 절에서 ORDER BY 절을 명시했으므로, 기본값인RANGEUNBOUNDED PRECEDING구문이 없어도 결과는 같음

칼럼명1	칼럼명2	COUNT
데이터1_A	A	1
데이터1_A	AA	2
데이터1_A	AAA	3
데이터1_B	B	1
데이터1_B	BB	2
데이터1_B	BBB	3

 

 

 

 

유선배 SQLD 과외노트