🔍 함수

✍ 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특별한 명령어, 크게 내장형 함수 와 사용자 정의 함수 로 나뉜다.

✍ 내장 함수 : 오라클 내부에서 기본으로 제공하는 함수, 단일행 함수와, 다중행 함수 로 나뉜다.
✍ 단일행 함수(Single-row-function) : 데이터가 한 행씩 입력되고, 입력된 각 행별로 결과가 하나씩 나오는 함수
✍ 다중행 함수(Multiple-row-function): 여러 행이 입력되어, 하나의 행으로 결과가 반환되는 함수

✍ 이 글에선 오라클의 분석함수를 알아보겠습니다.

🔍 순위 함수(RANK, DENSE_RANK, ROW_NUMBER)

✍ RANK와 DENSE_RANK 함수는 값의 순위를 출력하는 함수이다. 차이점은 중복 순위의 계산 여부에 있다. ROW_NUMBER 함수는 행의 번호을 출력해주고 일련번호를 생성한다.

RANK(), DENSE_RANK(), ROW_NUMBER()
OVER (PARTITION BY 컬럼명
ORDER BY 컬럼명 [ASC|DESC] Windowing)

-- RANK() = 값의 순위를 결정(중복순위 계산)
-- DENSE_RANK() = 값의 순위를 결정(중복순위 계산X)
-- ROW_NUMBER() = 모든 행의 번호를 제공, 일련번호를 생성 
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
    RANK() OVER(ORDER BY SALARY DESC) SAL_RANK, 
    DENSE_RANK() OVER(ORDER BY SALARY DESC) SAL_DENSE_RANK,
    ROW_NUMBER() OVER(ORDER BY SALARY DESC) SAL_NUMBER
FROM EMPLOYEES
ORDER BY SAL_NUMBER;

🔍 가상순위와 분포(CUME_DIST, PERCENT_RANK)

✍ CUME_DIST은 최댓값 1을 기준으로 분산된 값을 제공, PERCENT_RANK는 최댓값 1을 기준으로 백분율 순위를 제공(시작 위치는 각각 다르다)

CUME_DIST(), PERCENT_RANK()
OVER (PARTITION BY 컬럼명
ORDER BY 컬럼명 [ASC|DESC] Windowing)

-- CUME_DIST 시작 위치 row 1/행수
-- PERCENT_RANK 시작 위치 0
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
    CUME_DIST() OVER(ORDER BY SALARY DESC) SAL_CUME_DIST, 
    PERCENT_RANK() OVER(ORDER BY SALARY DESC) SAL_PERSENT_RANK
FROM EMPLOYEES;

🔍 비율(RATIO_TO_REPORT) 함수

✍ 해당 컬럼의 백분율을 소수점으로 제공한다. 그룹 내에서 해당 값의 백분율을 구할 수 있다. 결과 값은 0보다 크고 1.0보다 작거나 같은 값이 출력된다.

RATIO_TO_REPORT()
OVER (PARTITION BY 컬럼명
ORDER BY 컬럼명 [ASC|DESC] Windowing)

-- RATIO_TO_REPORT = 해당 컬럼 값의 백분율을 소수점으로 제공
SELECT FIRST_NAME, SALARY,
    ROUND(RATIO_TO_REPORT(SALARY) OVER (), 4) AS SALARY_RATIO
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';

🔍 분배(NTILE) 함수

✍ 전체 데이터의 분포를 n개의 구간으로 나누어 표시해 줍니다. 만일 row가 균등하게
나눠지지 않을 경우에는 위에서부터 추가됩니다.

NTILE()
OVER (PARTITION BY 컬럼명
ORDER BY 컬럼명 [ASC|DESC] Windowing)

-- NTILE() = 전체 데이터 분포를 n개의 구간으로 나누어 표시 (1, 2, 3, 4, 5 5개씩 25개 6, 7, 8, 9, 10 4개씩 총 20개 합해서 총 45개)
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
    NTILE(10) OVER (ORDER BY SALARY DESC) AS SAL_QUART_TILE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;

🔍 LAG, LEAD 함수

✍ 윈도우 이전(LAG), 이후(LEAD)의 행 값을 가져올 수 있는 함수

LAG(), LEAD()
OVER (PARTITION BY 컬럼명
ORDER BY 컬럼명 [ASC|DESC] Windowing)

-- LAG() = 윈도우의 이전 n번째 행의 값을 가져올 수 있음 
-- LEAD() = 윈도우의 이후 n번째 행의 값을 가져올 수 있음
SELECT EMPLOYEE_ID,
    LAG(SALARY, 1, 0) OVER (ORDER BY SALARY) AS LOWER_SAL, -- 2100 이전은 없기 때문에 0부터 출력
    SALARY,
    LEAD(SALARY, 1, 0) OVER (ORDER BY SALARY) AS HIGHER_SAL -- 2100 이후 시작이기 때문에 2200부터 출력
FROM EMPLOYEES
ORDER BY SALARY;

🔍 LISTAGG 함수

✍ LISTAGG 함수는 ORDER BY 표현식에 따라 쿼리 내 각 그룹의 행 순서를 지정한 다음, 값을 연결하여 문자열 하나를 만듭니다.()

LISTAGG(표현식, ‘구분자 default - '' ’)
WITHIN GROUP(ORDER BY 컬럼명)

-- LISTAGG() - 하나의 컬럼의 다중 값을 출력할 때 사용 ex) 부서 별 속한 사원들의 이름
SELECT DEPARTMENT_ID,
    LISTAGG(FIRST_NAME, ',') WITHIN GROUP(ORDER BY HIRE_DATE) AS NAMES
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID; -- 부서별 입사일로 정렬한 이름들이 컬럼의 다중값으로 출력된다.(구분자는 ,)

📖 reference

인프런 오라클 데이터베이스 강의
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_LISTAGG.html