SQL활용 - 2(윈도우 함수, TOP N 쿼리)

윈도우 함수

1. 윈도우 함수행과 행간의 관계를 쉽게 정의하기 위한 함수GROUP BY랑 윈도우 함수 함께 사용 가능

   • 종류

     • 그룹 내 순위 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
     • 그룹 내 집계 관련 함수: SUM, MAX, MIN, AVG, COUNT
     • 그룹 내 행 순서 관련 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD
     • 그룹 내 비율 관련 함수: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
     • 통계 분석 관련 함수: CORP, COVAR_POP, STDDEV, VARIANCE, REGR_SLOPE

   • OVER

     • SELECT 윈도우함수(arguments) OVER ([PARTITION BY 칼럼] [ORDER BY] [WINDOWING]) FROM 테이블;

     • partition by:

       • 소그룹으로 나누기
       • partition by를 생략하면 모든 데이터를 대상으로 한다

     • windowing

       • 함수 대상이 되는 행 기준의 범위를 강력하게 지정

       • SQL 서버는 미지원

       • 기본값: RANGE UNBOUNDED PRECEDING
         현재 행 기준으로 파티션 내의 첫번째 행까지를 범위로 지정한다

       • ROWS는 현재 행의 앞뒤 건수
         RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위

       • ROWS/RANGE UNBOUNDED PRECEDING/CURRENT_ROW/VALUE_EXPR PRECEDING
         
         ROWS/RANGE 
         BETWEEN UNBOUNDED PRECEDING/CURRENT_ROW/VALUE_EXPR PRECEDING/FOLLOWING
         AND
         UNBOUNDED FOLLOWING/CURRENT_ROW/VALUE_EXPR PRECEDING/FOLLOWING

   • 윈도우 함수의 필수 문구

2. 데이터 웨어하우스에서 발전

3. = 분석함수, 순위함수

4. 그룹 내 순위 함수

   1. RANKPARTITION으로 만든 특정 범위 내 혹은 전체 데이터에서 순위 구함

      SELECT JOB, ENAME, SAL,
          RANK() OVER (ORDER BY SAL DESC) AS ALL_RK,
          RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_RK
      FROM EMP;

      JOB_RK의 partition by와 ALL_RK의 order by가 충돌되어 JOB별로 정렬되지 않고 ALL_RK의 order by절대로 정렬된다

   2. 만약 ALL_RK가 없었다면 JOB으로 소그룹을 나눈 뒤에 그 안에서 정렬된다

   3. 동일한 값은 동일 순위

   4. ORDER BY를 포함한 쿼리에서 특정 항목(칼럼)에 대한 순위를 구하는 함수

  ```sql
  WHERE SAL = 
  (SELECT SAL FROM 
       (SELECT SAL, RANK() OVER (ORDER BY SAL DESC) AS RK FROM EMP
     WHERE SAL < 1300)
   WHERE RK = 1);

위 쿼리는 에러가 날 수 있다

RANK 함수는 동일 순위를 허용하므로 다중행 반환이 가능하기 때문에

1등이 여러명일 때 에러가 난다

```

2. DENSE_RANKRANK와 똑같은 문법

3. DENSE_RANK는 중간 순위를 안 비우니까1 1 2 3 4 4 4 5 이런 식으로 나온다
   그러면 순위 <= 10이라고 해도 10명보다 더 나올 수 있다

4. 동일한 순위를 하나의 건수로 취급한다: 만약 2등이 공동으로 두 명이면 랭크와 덴스랭크 둘 다 두 명을 2등으로 표시한다 하지만 랭크는 그 다음 등수를 4등이라고 하고(1 2 2 4), 덴스랭크는 3등이라고 한다(1 2 2 3)

5. ROW_NUMBERRANK와 같은 문법동일 값에 대한 순서 관리는 order by절로!
   ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME)일반 집계함수

6. 데이터베이스마다 독자적인 순위 지정법이 있다

7. 동일한 값에 고유한 순위를 분여한다

8. 일반 집계함수

   1. SUMSUM 윈도우 함수는 인자로 지정한 칼럼 / 표현식의 합계 값을 반환한다.over절에 order by를 기록하면 순서에 따라 각 행까지의 누적합을 집계하고SUM([DISTINCT|ALL] expr) over (...)이렇게 하면 동일 값 동일 윈도우로 처리한다

   2. 

   3. 윈도윙 절의 기본값은 RANGE UNBOUNDED PRECEDING: 현재 행 기준으로 파티션 내의 첫번째 행까지를 범위로 지정한다

   4. order by가 없으면 전체합을 집계한다

   5. SUM(SAL) OVER (PARTITION BY MGR) AS SAL_SUM

   6. 파티션별 윈도우의 합 구하기

   7. MAX인라인 뷰를 이용해서 파티션별 최댓값 가진 행만 추출할 수 있다

      MGR	ENAME	SAL
      7566	FORD	3000
      7566	SCOTT	3000
      7698	ALLEN	1600
      7782	MILLER	1300

      FORD, SCOTT는 동일한 급여를 받아서 WHERE절을 둘 다 충족했다FROM (SELECT ..., RANK() OVER(PARTITION BY MGR ORDER BY SAL DESC) AS SAL_RK FROM EMP) WHERE SAL_RK = 1;

   8. 다만 위 쿼리는 성능이 저하되므로 순위 함수인 RANK를 사용하는 것이 더 좋다

   9. SELECT MGR, ENAME, SAL FROM (SELECT MGR, ENAME, SAL, MAX(SAL) OVER(PARTITION BY MGR) AS MAX_SAL FROM EMP) WHERE SAL = MAX_SAL;

   10. 파티션 별 최댓값

   11. MIN

   12. AVG

       • 같은 매니저를 둔 사원들의 평균 SAL 구하기(자기 바로 앞뒤 사번!)

         MGR	ENAME	HIREDATE	SAL	AVG_SAL
         7566	C	1981-12-11	1400	...
         7698	A	1981-02-20	1600	1425
         7698	W	1981-02-22	1250	1450
         7698	T	1981-09-08	1500	1333
         7698	M	1981-09-28	1250	1233
         7698	J	1981-12-03	950	1100

         A행의 AVG_SAL이 1425인 이유는 7698 매니저 파티션 내에서 첫번째 데이터라서 앞의 데이터가 없고 본인 1600과 다음 1250을 2로 나누었기 때문이다. J행 역시 파티션의 마지막 데이터라서 1250과 950으로만 구한다

       • SELECT MGR, ENAME, HIREDATE, SAL, ROUND(AVG(SAL) OVER ( PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ) AS AVG_SAL FROM EMP;

   13. AVG와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터 통곗값

   14. COUNTCOUNT 윈도우 함수도 order by 기술시 순서에 따른 누적.

       • 급여 기준 정렬. 본인보다 급여가 50 이하로 적거나 150 이하로 많은 인원수

         ENAME	SAL	EMP_CNT
         S	800	2
         T	950	2
         A	1100	3
         W	1250	3
         M	1250	3

         S는 750~950 사이의 급여를 받는 인원수

       • A는 1050~1250 사이의 급여를 받는 인원수...

       • SELECT ENAME, SAL, COUNT(*) OVER ( ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING ) AS EMP_CNT FROM EMP;

   15. COUNT는 개수를 세는 거니까 HAVING COUNT(CASE WHEN ~ THEN 0 ELSE NULL END) >= 1해도 에러가 아니다 0인 건 개수를 세고 아닌 건 NULL이라 개수를 안 세면 되니까!

9. 그룹 내 행 순서 함수

   1. FIRST_VALUESQL서버 미지원

      SELECT 
          DEPTNO, ENAME, SAL,
          FIRST_VALUE(ENAME) OVER (
            PARTITION BY DEPTNO
            ORDER BY SAL DESC
               ROWS UNBOUNDED PRECEDING
        ) AS ENAME_FV
      FROM EMP;

      DEPTNO	ENAME	SAL	ENAME_FV
      10	k	5000	k
      10	c	2450	k
      10	m	1300	k
      20	s	3000	s
      20	f	3000	s
      20	j	2975	s
      20	a	1100	s
      20	s	800	s
      30	b	2850	b

      범위는 현재행 기준 파티션 내 첫번째 행까지

      SELECT 
          SAL, 
          COMM, 
          FIRST_VALUE(COMM IGNORE NULLS) OVER (ORDER BY SAL) AS C1, 
          FIRST_VALUE(COMM) OVER (ORDER BY SAL) AS C2
      FROM EMP;

      SAL	COMM	C1	C2
      950	N	N	N
      1250	500	500	N
      1250	1400	500	N
      1500	0	500	N
      1600	300	500	N
      2850	N	500	N

      정렬순서상 첫 행에는 NULL 외 COMM이 없어서 C1은 NULL

   2. 20 파티션 내에서 최고급여는 s와 f 둘인데, 둘 중 어떤 것이 첫번째로 나올 지는 알 수 없다 first_value는 공동 순위를 인정하지 않는다. 따라서 세부 정렬을 하고 싶으면 별도 정렬 조건을 넣은 인라인 뷰를 사용하거나 order by절에 정렬 조건 추가한다(ORDER BY SAL DESC, ENAME으로 하면 f가 최고 급여자로!)

   3. MIN과 같은 결과

   4. 파티션별 가장 먼저 나온 값

   5. LAST_VALUESQL서버는 미지원

      SELECT 
          DEPTNO, ENAME, SAL,
          LAST_VALUE(ENAME) OVER (
            PARTITION BY DEPTNO
            ORDER BY SAL DESC
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS ENAME_LV
      FROM EMP;

      범위는 현재 행 포함, 파티션 내 마지막 행까지의 범위

   6. MAX와 같은 결과

   7. 파티션별 가장 나중에 나온 값

   8. LAGSQL서버는 미지원

      LAG(칼럼[, 몇 번째 앞의 행(기본값 = 1), NULL 대체값])
      
      SELECT LAG (SAL, 2, 0) OVER (ORDER BY HIREDATE);
      /* 두 행 앞의 값을 가져온다. 1, 2행은 NULL이므로 대체값인 0 출력 */

      본인보다 입사일자가 한 명 앞선 사원의 급여 구하기

      ENAME	HIREDATE	SAL	LAG_SAL
      A	20210220	1600	N
      W	20210222	1250	1600
      T	20210908	1500	1250
      M	20210928	1250	1500

   9. SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) AS LAG_SAL FROM EMP WHERE JOB = 'SALESMAN';

   10. 인자는 3개까지 가능

   11. 파티션별 이전 몇 번째 행의 값

   12. LEADSQL서버 미지원

   13. 인자 세 개까지 받음

   14. 파티션별 이후 몇 번째의 값

10. 그룹 내 비율 함수

    1. RATIO_TO_REPORT결과값은 0 < x <= 1SQL서버 미지원

    2. SELECT ENAME< SAL< ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS PR FROM EMP WHERE JOB = 'SALESMAN'; /* ALLEN 1500 0.29 1600/5600 */

    3. 개별 ratio 합 구하면 1

    4. 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 나타낸다

    5. PERCENT_RANK값이 아닌 행의 순서별 백분율같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째일까?

       DEPTNO	ENAME	SAL	PR
       10	K	5000	0.00
       10	C	2450	0.50
       10	M	1300	1.00
       20	S	3000	0.00
       20	F	3000	0.00
       20	J	2975	0.50
       20	A	1100	0.75
       20	S	800	1.00
       30	B	2850	0.00

       파티션 내 전체 건수가 세 건이라면 구간은 2개로 나뉜다같은 값을 가지면 순서도 동일하다고 본다

    6. 그러나 0.00이 두 개이면 다음은 0.25를 건너뛰고 0.50이 된다.

    7. 0과 1 사이를 두 구간으로 나누면 0, 0.5, 1이 된다.

    8. SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS PR FROM EMP:

    9. SQL서버 미지원

    10. 파티션별 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1이라고 한다

    11. CUME_DIST결과값은 0 < x <= 1같은 부서 소속 사원 집합에서 본인 급여가 누적 순서상 몇 번째일까?

        DEPTNO	ENAME	SAL	CD
        10	K	5000	.33
        10	C	2450	0.67
        10	M	1300	1.00
        20	S	3000	0.40
        20	F	3000	0.40
        20	J	2975	0.60

        전체 건수가 세 건이면 0.3333 간격같은 값이면 동일 순위를 부여한다

    12. 이 때, 다른 윈도우 함수는 앞 행의 결과값을 따르지만 CUME_DIST는 뒤 행의 결과값을 따른다. 그래서 S와 F가 0.20이 아니라 0.40을 가진다.

    13. 전체 건수가 다섯 건이면 0.2 간격

    14. SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS CD FROM EMP;

    15. SQL서버 미지원

    16. 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수를 누적 백분율로

    17. NTILESQL서버 지원

        ENAME	SAL	NT
        K		1
        F		1
        S		1
        J		1
        B		2
        C		2
        A		2
        T		2
        M		3
        W		3
        M		3
        A		4
        J		4
        S		4

        SAL은 각 파티션별(NT의 숫자가 바뀌면 파티션도 바뀐 것) 내림차순NTILE(4)이므로 14를 4로 나눈다그럼 4개로 나눈 파티션에 3씩 분배 후 나머지는 앞의 파티션부터 하나씩 준다

    18. 그래서 파티션 1, 2는 네 명씩 3, 4는 세 명씩이다

    19. 몫은 3 나머지는 2

    20. 전체 건수는 14건이었다.

    21. SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS NT FROM EMP;

    22. 파티션별 전체 건수를 인수 값으로 N등분한 결과값

TOP N 쿼리

1. ROWNUM 슈도 칼럼SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호

   SELECT NAME FROM PLAYER
   WHERE ROWNUM <= 1; /* 1건의 행 */
   WHERE ROWNUM < 2; /* 1건의 행 */
   WHERE ROWNUM = N; /* N건의 행 */
   WHERE ROWNUM < N + 1; /* N건의 행 */

   ORDER BY와 함께 쓴다고 순위 높은 것을 추출하는 것은 아니다높은 순위를 추출하고 싶다면 인라인 뷰에서 먼저 데이터를 정렬한 후 ROWNUM을 사용

2. SELECT ENAME, SAL FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <= 3;

3. (데이터 추출 후 정렬 작업이 이루어지기 때문)

4. 원하는 만큼의 행을 가져오고 싶을 때 WHERE절에서 이용한다

5. 오라클에서 사용

6. TOP절결과 집합으로 출력되는 행의 수를 제한한다

   • expression: 반환할 행 수
   • percent: 쿼리 결과 집합에서 처음 expression%의 행만 반환(10%처럼)
   • with ties: order by절을 지정했을 때만 가능하다
   • ​ TOP N의 마지막 행과 같은 값이 있으면 추가 출력한다

   SELECT TOP(2) WITH TIES ENAME, SAL
   FROM EMP
   ORDER BY SAL DESC;

   TOP절 마지막에 컴마가 없다는 것!!

7. TOP (expression) [Percent] [with TIES]

8. SQL서버에서 사용

9. ROW LIMITING절ANSI 표준 SQL 문법ORDER BY절 다음에 작성한다(order by절이 있어야 한다)OFFSET offset ROW/ROWS

   • OFFSET offset 건너뛸 행의 갯수
   • FETCH 반환할 행의 갯수나 백분율
   • ONLY 지정된 행의 갯수나 백분율만큼 행 반환

   SELECT EMPNO, SAL
   FROM EMP
   ORDER BY SAL, EMPNO
       FETCH FIRST 5 ROWS ONLY;

   월급 낮은 순 다섯명월급 낮은 순인데 상위 5명(제일 월급 낮은 다섯명) 건너뛰고 출력