postgresql에서 행순서 함수와 분포함수에 대해 리스트업하고 내용도 자세히 정리

PostgreSQL에서는 다양한 행순서 함수와 분포함수를 제공합니다. 이들 함수는 데이터 분석과 통계 작업에 매우 유용합니다. 아래에 주요 함수들을 리스트업하고 자세히 설명하겠습니다.

## 행순서 함수

1. ROW_NUMBER()
   - 파티션 내에서 각 행에 고유한 순차적 번호를 할당합니다.
   - 중복 값이 있어도 항상 고유한 번호를 부여합니다.

2. RANK()
   - 파티션 내에서 순위를 매깁니다.
   - 중복 값에 대해 같은 순위를 부여하고, 다음 순위는 중복된 개수만큼 건너뜁니다.

3. DENSE_RANK()
   - RANK()와 유사하지만, 중복 값에 대해 순위를 건너뛰지 않습니다.
   - 연속적인 순위를 생성합니다.

4. FIRST_VALUE()
   - 윈도우 프레임의 첫 번째 행 값을 반환합니다.

5. LAST_VALUE()
   - 윈도우 프레임의 마지막 행 값을 반환합니다.

6. LAG()
   - 현재 행을 기준으로 이전 행의 값을 참조합니다.

7. LEAD()
   - 현재 행을 기준으로 다음 행의 값을 참조합니다.

## 분포 함수

1. PERCENT_RANK()
   - 백분위 순위를 계산합니다.
   - (RANK - 1) / (전체 행 수 - 1)의 값을 반환합니다.
   - 결과 범위: 0 ≤ y ≤ 1

2. CUME_DIST()
   - 누적 분포 값을 계산합니다.
   - (현재 행보다 작거나 같은 값을 가진 행의 수) / (전체 행 수)
   - 결과 범위: 0 < y ≤ 1

3. NTILE(n)
   - 데이터를 n개의 그룹으로 균등하게 나눕니다.
   - 각 행에 1부터 n까지의 그룹 번호를 할당합니다.

## 사용 예시 및 특징

1. ROW_NUMBER(), RANK(), DENSE_RANK() 비교:
   ```sql
   SELECT 
     salary,
     ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
     RANK() OVER (ORDER BY salary DESC) as rank,
     DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
   FROM employees;
   ```
   - 동일한 salary에 대해:
     - ROW_NUMBER()는 고유한 번호를 부여
     - RANK()는 같은 순위를 부여하고 다음 순위를 건너뜀
     - DENSE_RANK()는 같은 순위를 부여하고 다음 순위를 연속적으로 부여

2. PERCENT_RANK()와 CUME_DIST() 비교:
   ```sql
   SELECT 
     salary,
     PERCENT_RANK() OVER (ORDER BY salary) as percent_rank,
     CUME_DIST() OVER (ORDER BY salary) as cume_dist
   FROM employees;
   ```
   - PERCENT_RANK()는 첫 번째 행에 대해 0을 반환할 수 있지만, CUME_DIST()는 항상 0보다 큰 값을 반환합니다.
   - CUME_DIST()는 동일한 값에 대해 같은 누적 분포 값을 반환합니다.

3. NTILE() 사용 예:
   ```sql
   SELECT 
     salary,
     NTILE(4) OVER (ORDER BY salary DESC) as quartile
   FROM employees;
   ```
   - 이 쿼리는 직원들을 급여 기준으로 4개의 그룹(사분위)으로 나눕니다.

이러한 함수들은 OVER 절과 함께 사용되며, PARTITION BY를 통해 데이터를 그룹화하고 ORDER BY로 정렬할 수 있습니다. 이를 통해 복잡한 데이터 분석 작업을 SQL 쿼리 내에서 효율적으로 수행할 수 있습니다.

출처
[1] [SQL] 순위 : RANK() , DENSE_RANK() , ROW_NUMBER() , NTILE() https://yueliang-front-end.tistory.com/31
[2] PostgreSQL Window Functions: The Ultimate Guide - Neon https://neon.tech/postgresql/postgresql-window-function
[3] Postgre Window Functions - GeeksforGeeks https://www.geeksforgeeks.org/postgre-window-functions/
[4] WINDOW 함수 - CUME_DIST, NTILE, RATIO_TO_REPORT ... https://kaymktnote.tistory.com/entry/WINDOW-%ED%95%A8%EC%88%98-CUMEDIST-NTILE-RATIOTOREPORT-PERCENTRANK-%EB%B9%84%EC%9C%A8-%ED%95%A8%EC%88%98-%EC%A0%95%EB%A6%AC
[5] 순위 함수(RANK, DENSE_RANK, ROW_NUMBER, NTILE) https://doorbw.tistory.com/221
[6] Data Processing With PostgreSQL Window Functions - Timescale https://www.timescale.com/learn/postgresql-window-functions
[7] PostgreSQL CUME_DIST Function By Practical Examples - Neon https://neon.tech/postgresql/postgresql-window-function/postgresql-cume_dist-function
[8] percent_rank(), cume_dist() and ntile() - YugabyteDB Docs https://docs.yugabyte.com/preview/api/ysql/exprs/window_functions/function-syntax-semantics/percent-rank-cume-dist-ntile/
[9] ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() 비교 (예제포함) https://tak2k.tistory.com/56
[10] [SQL]분석함수(analytic function)_순위함수(NTILE, CUME_DIST ... https://chgpky.tistory.com/10
[11] [SQL] 순위 함수 - cume_dist, percent_rank, ntile - 나의 분석일기 https://roadto-data.tistory.com/15
[12] How to make a postgres function reorder rows representing a linked ... https://stackoverflow.com/questions/61254909/how-to-make-a-postgres-function-reorder-rows-representing-a-linked-list
[13] [SQL]함수로 순위,등급,비율 출력하기(RANK, DENSE_RANK, NTILE ... https://seoyuun22.tistory.com/44
[14] Window Functions - postgresql.kr https://postgresql.kr/docs/9.6/functions-window.html
[15] 순위함수 RANK DENSE_RANK ROW_NUMBER 특징별 기억하면 좋고 https://blog.naver.com/topgunmagic/220368199517
[16] 8.15.1. 배열 타입 정의 https://postgresql.kr/docs/9.5/arrays.html
[17] [DB/postgres] 윈도우 함수(Window Function) 이해 및 활용방법 https://adjh54.tistory.com/261
[18] Choosing a Postgres distribution v5.6 - EDB https://www.enterprisedb.com/docs/pgd/latest/planning/choosing_server/
[19] 통계에 대해 알아보자 (feat. NTILE, LAG, LEAD, CUME_DIST) https://thinpig-data.tistory.com/entry/%ED%86%B5%EA%B3%84%EC%97%90-%EB%8C%80%ED%95%B4-%EC%95%8C%EC%95%84%EB%B3%B4%EC%9E%90-feat-NTILE-LAG-LEAD-CUMEDIST
[20] PostgreSQL ORDER BY - Neon https://neon.tech/postgresql/postgresql-tutorial/postgresql-order-by
[21] Documentation: 17: 3.5. Window Functions - PostgreSQL https://www.postgresql.org/docs/current/tutorial-window.html
[22] [postgresql] Window Function(윈도우함수)에 대해 알아보자! https://chiefcoder.tistory.com/35
[23] How to sort rows using ORDER BY and SELECT in PostgreSQL - EDB https://www.enterprisedb.com/postgres-tutorials/how-sort-rows-using-order-and-select-postgresql-6-examples
[24] SQL 순위함수: RANK, DENSE_RANK, ROW_NUMBER, NTILE https://proni.tistory.com/124
[25] 17: F.41. tablefunc — functions that return tables (crosstab and others) https://www.postgresql.org/docs/current/tablefunc.html
[26] pg_math -- compute statistical distribution functions in PostgreSQL https://github.com/chanukyasds/pg_math
[27] Documentation: 9.5: Aggregate Functions - PostgreSQL https://www.postgresql.org/docs/9.5/functions-aggregate.html
[28] 17: 9.3. Mathematical Functions and Operators - PostgreSQL https://www.postgresql.org/docs/current/functions-math.html
[29] [PostgreSQL] 윈도우 함수(Window Functions)의 개념, 성능 및 사용법 ... https://junhkang.tistory.com/40
[30] How can I get a list of all functions stored in the database of a ... https://stackoverflow.com/questions/1347282/how-can-i-get-a-list-of-all-functions-stored-in-the-database-of-a-particular-sch/78426906