[SQLD]「2」(2.7) FIRST_VALUE/LEAD, RATIO_TO_REPORT/CUME_DIST/NTILE

[SQLD]「2」(2.7) FIRST_VALUE/LEAD, RATIO_TO_REPORT/CUME_DIST/NTILE

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

> 「2」 SQL 기본 / 활용

「3」 관리 구문

 

1. 서브쿼리

2. 뷰

3. 집합 연산자

4. 소계(총계) 함수

> 5. 윈도우 함수

6. Top-N 쿼리

7. 셀프 조인

8. 계층 쿼리

 

5. 윈도우 함수

행 순서 함수

⸰FIRST_VALUE: 파티션별 가장 선두에 위치한 데이터를 구하는 함수

SELECT 칼럼명1,
       칼럼명2,
       FIRST_VALUE(칼럼명2) OVER(PARTITION BY 칼럼명1
                                    ORDER BY 칼럼명2 [ASC|DESC]) AS FIRST_VALUE
  FROM 테이블명;

칼럼명1	칼럼명2	FIRST_VALUE
데이터1_A	1A	1A
데이터1_A	1AA	1A
데이터1_B	1B	1B
데이터1_B	1BB	1B

 

⸰LAST_VALUE: 파티션별 가장 끝에 위치한 데이터를 구하는 함수

SELECT 칼럼명1,
       칼럼명2,
       LAST_VALUE(칼럼명2) OVER(PARTITION BY 칼럼명1
                                   ORDER BY 칼럼명2 [ASC|DESC]
                                   RANGE BETWEEN UNBOUNDED PRECEDING
                                     AND UNBOUNDED FOLLOWING) AS LAST_VALUE
  FROM 테이블명;

‣ 이때, WINDOWING 절을 명시해주지 않고 ORDER BY 절까지 작성된다면, ORDER BY 절이 WINDOWING 절의 기본값인 RANGEUNBOUNDED PRECEDING이 생성되어, 현재 ROW 와 같은 LAST_VALUE 값을 가짐

 

따라서RANGEBETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING으로 명시 해주어야 아래 결과가 나옴

칼럼명1	칼럼명2	FIRST_VALUE
데이터1_A	1A	1AA
데이터1_A	1AA	1AA
데이터1_B	1B	1BB
데이터1_B	1BB	1BB

 

⸰LAG: 파티션별 특정 수만큼 앞선 데이터를 구하는 함수

  - 두 번째 인자값을 생략하면 기본값은 1

SELECT 칼럼명1,
       칼럼명2,
       LAG(칼럼명2, 숫자) OVER(PARTITION BY 칼럼명1
                                  ORDER BY 칼럼명2 [ASC|DESC]) AS LAG
  FROM 테이블명;

‣ 숫자를 2로 가정하면,

칼럼명1	칼럼명2	FIRST_VALUE
데이터1_A	1A	NULL
데이터1_A	1AA	NULL
데이터1_A	1AAA	1A
데이터1_A	1AAAA	1AA
데이터1_B	1B	NULL
데이터1_B	1BB	NULL
데이터1_B	1BBB	1B
데이터1_B	1BBBB	1BB

 

⸰LEAD: 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수

  - 두 번째 인자값을 생략하면 기본은 1

SELECT 칼럼명1,
       칼럼명2,
       LEAD(칼럼명2, 숫자) OVER(PARTITION BY 칼럼명1
                                   ORDER BY 칼럼명2 [ASC|DESC]) AS LEAD
  FROM 테이블명;

‣ 숫자를 2로 가정하면,

칼럼명1	칼럼명2	FIRST_VALUE
데이터1_A	1A	1AAA
데이터1_A	1AA	1AAAA
데이터1_A	1AAA	NULL
데이터1_A	1AAAA	NULL
데이터1_B	1B	1BBB
데이터1_B	1BB	1BBBB
데이터1_B	1BBB	NULL
데이터1_B	1BBBB	NULL

 

비율 함수

⸰RATIO_TO_REPORT: 파티션별 합계에서 해당 값이 차지하는 비율을 구하는 함수

  - 파티션 안에서칼럼값 / SUM(칼럼값)

SELECT 칼럼명1,
       칼럼명2,
       RATIO_TO_REPORT(칼럼명2) OVER(PARTITION BY 칼럼명1) AS RATIO_TO_REPORT
  FROM 테이블명;

‣ 칼럼명2 값을 아래값으로 가정하면,

칼럼명1	칼럼명2	RATIO_TO_REPORT
데이터1_A	30	0.297029703
데이터1_A	59	0.5841584158
데이터1_A	12	0.1188118812
데이터1_B	27	0.1607142857
데이터1_B	95	0.5654761905
데이터1_B	46	0.2738095238

 

⸰PERCENT_RANK: 파티션별 맨 위끝 행을 0, 맨 아래끝 행을 1로 놓고, 행의 위치를 백분위 순위값으로 구하는 함수

  - 파티션 안에서(RANK( ) - 1) / (COUNT(*) - 1)

SELECT 칼럼명1,
       칼럼명2,
       PERCENT_RANK() OVER(PARTITION BY 칼럼명1
                               ORDER BY 칼럼명2 [ASC|DESC]) AS PERCENT_RANK
  FROM 테이블명;

‣ 칼럼명2 값을 아래값으로 가정하면,

칼럼명1	칼럼명2	PERCENT_RANK
데이터1_A	30	0
데이터1_A	59	0.25
데이터1_A	12	0.5
데이터1_A	24	0.75
데이터1_A	35	1
데이터1_B	23	0
데이터1_B	18	0.25
데이터1_B	27	0.5
데이터1_B	95	0.75
데이터1_B	46	1

 

⸰CUME_DIST: 파티션별 전체 건수에서 해당 값을 기준으로 누적 건수의 누적 백분율을 구하는 함수

  - 결과값은 0보다 크고 1보다 작은 값,RANGEUNBOUNDED PRECEDING으로 누적 건수를 구함

SELECT 칼럼명1,
       칼럼명2,
       CUME_DIST() OVER(PARTITION BY 칼럼명1
                            ORDER BY 칼럼명2 [ASC|DESC]) AS CUME_DIST
  FROM 테이블명;

‣ 칼럼명2 값을 아래값으로 가정하면,

칼럼명1	칼럼명2	CUME_DIST
데이터1_A	30	0.2
데이터1_A	59	0.1
데이터1_A	12	0.8
데이터1_A	12	0.8
데이터1_A	37	1
데이터1_B	23	0.2
데이터1_B	18	0.6
데이터1_B	18	0.6
데이터1_B	95	0.8
데이터1_B	46	1

 

⸰NTILE: 파티션별로 주어진 수만큼 N등분한 후 현재 행에 해당하는 등급을 구하는 함수

  - 동등하게 순번을 부여한 뒤에 할당할 행이 남은 경우, 맨 앞의 그룹부터 하나씩 더 채워짐

SELECT 칼럼명1,
       칼럼명2,
       NTILE(숫자) OVER(PARTITION BY 칼럼명1
                            ORDER BY 칼럼명2 [ASC|DESC]) AS NTILE
  FROM 테이블명;

‣ 숫자를 3으로 가정하면,

칼럼명1	칼럼명2	NTILE
데이터1_A	1A	1
데이터1_A	1AA	1
데이터1_A	1AAA	2
데이터1_A	1AAAA	2
데이터1_A	1AAAAA	3
데이터1_B	1B	1
데이터1_B	1BB	1
데이터1_B	1BBB	2
데이터1_B	1BBBB	2
데이터1_B	1BBBBB	3

 

 

 

 

 

 

유선배 SQLD 과외노트