[TIL] SQL 챌린지 - 5회차

1. 윈도우 구문

 Postrgres SQL 등 엄격한 ANSI SQL을 제공하는 경우 SELF JOIN 필수이다.

 윈도우 구문은 이동평균, 누적 합계, 순위 계산이 가능하여 일종의 서브쿼리 역할을 한다. 윈도우 구문을 사용하면 가독성이 올라간다.

-- 일반적인 구문
SELECT 집계함수() OVER ( [PARTITION BY {컬럼명1}], [ORDER BY {컬럼명2}]) 
FROM {테이블};

 

2. 윈도우 구문 함수

- ORDER BY 구문

 함수 내에서 데이터를 정렬하는 데 사용된다. 일반적인 ORDER BY 구문과 달리 윈도우 함수가 적용되는 범위 내에서만 정렬된다.

-- 전체 직원을 대상으로 월급을 많이 받는 순위 매기기
SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM 
    hr_employees;

 

- PARTITION BY 구문

 데이터를 특정 기준으로 그룹화하여 별도로 윈도우 함수를 적용하는 방법이다. GROUP BY 구문은 전체 데이터를 기준으로 그룹화 하는 것과 대조적이다.

-- 부서별로 누가 돈을 평균적으로 잘 받는지 확인하기
SELECT 
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_dept
FROM 
    hr_employees
ORDER BY avg_salary_by_dept;

 

- ORDER BY + PARTITION BY

-- 부서별로 나눴을 때 월급 받는 순위 매겨보기
SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank_by_dept
FROM 
    hr_employees
ORDER BY EMPLOYEE_ID;

 

3. 윈도우 함수

- 집계 함수

함수	설명
SUM()	더하기
AVG()	평균
COUNT()	갯수
MIN()	최소
MAX()	최대

 

- 순위 함수

함수	설명
RANK()	동인한 값에 동일한 순위를 매기고 다음 순위는 건너 뜀
DENSE_RANK()	동일한 값에 동일한 순위를 매기지만 다음 순위를 건너뛰지 않음
ROW_NUMBER()	동일한 값이 있어도 유일한 순위를 부여
PERCENT_RANK()	백분율 순위 계산
NTILE(N)	파티션 내의 행을 N 등분 (기본적으로 ORDER BY가 있어야 작동)

-- NTILE 함수
SELECT EMPLOYEE_ID , DEPARTMENT_ID ,salary,
NTILE(5) OVER 
(PARTITION BY DEPARTMENT_ID ORDER BY salary desc) AS ntile2
FROM HR_EMPLOYEES HE 
ORDER BY EMPLOYEE_ID ASC ;

-- 모든 직원을 대상으로 급여 백분위 IQR로 상한이상치, 정상범위, 하한이상치 찾아보기
WITH salary_ranks AS (
    SELECT 
        salary,
        PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
    FROM hr_employees
),
quartiles AS (
    SELECT 
        MIN(CASE WHEN pct_rank >= 0.25 THEN salary END) AS Q1,
        MIN(CASE WHEN pct_rank >= 0.75 THEN salary END) AS Q3
    FROM salary_ranks
),
iqr_calc AS (
    SELECT 
        Q1,
        Q3,
        (Q3 - Q1) AS IQR,
        (Q1 - 1.5 * (Q3 - Q1)) AS lower_bound,
        (Q3 + 1.5 * (Q3 - Q1)) AS upper_bound
    FROM quartiles
)
-- 2. 이상치 판별
SELECT
    e.first_name, 
    e.last_name,
    e.salary
    ,
    CASE 
        WHEN e.salary < iqr_calc.lower_bound THEN 'LOWER_OUTLIER'
        WHEN e.salary > iqr_calc.upper_bound THEN 'UPPER_OUTLIER'
        ELSE 'ORDINARY' 
    END AS outlier_chk
FROM 
    hr_employees e, IQR_CALC;

 

- 값 반환 함수

 행을 기준으로 이전, 이후 값 혹은 윈도우 내 첫번째, 마지막 값을 가져와야할 수 있다.

함수	설명
FIRST_VALUE()	윈도우 프레임 내 첫 번째 값 반환
LAST_VALUE()	윈도우 프레임 내 마지막 값 반환
LAG()	윈도우 프레임 내 이전 행 값 반환
LEAD()	윈도우 프레임 내 다음 행 값 반환

-- 부서별 최고 연봉자 찾아보기
SELECT 
    employee_id,
    department_id,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary_in_dept
FROM 
    hr_employees
ORDER BY employee_id ASC;
-----------------------------------------------------------------------------------------------
-- 사번을 기준으로 선배의 월급 정보 가져오기
SELECT 
    employee_id,
    manager_id,
    salary,
    LAG(salary, 1) OVER (ORDER BY EMPLOYEE_ID ASC) AS previous_salary
FROM 
    HR_employees
ORDER BY employee_idASC;

 

4. 윈도우 구문2 - 범위 지정

- Frame Specification

 프레임은 윈도우의 하위 집합이다.

구문	설명
UNBOUNDED PRECEDING	윈도우 내 첫 번째 부터(기본 옵션)
UNBOUNDED FOLLOWING	윈도우 내 마지막 행 까지(기본 옵션)
CURRENT ROW	현재행 포함
N PRECEDING	현재 행에서 이전 N행 포함
N FOLLOWING	현재 행에서 다음 N행 포함

-- 일반적인 구문
SELECT 집계함수() OVER ( [PARTITION BY {컬럼명1}], [ORDER BY {컬럼명2}] [ROWS|RANGE {범위}]) 
FROM {테이블};
-- ROWS: 물리적인 행 단위 프레임 설정
-- BETWEEN: 논리적인 값을 범위로 지정
-----------------------------------------------------------------------------------------------
-- 기본 구문
SELECT 
    employee_id,
    department_id,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department_id)) AS avg_salary_by_dept
FROM 
    hr_employees
ORDER BY EMPLOYEE_ID ASC
-----------------------------------------------------------------------------------------------
-- RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
SELECT 
    employee_id,
    department_id,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS avg_salary_by_dept
FROM 
    hr_employees
ORDER BY EMPLOYEE_ID ASC;

-- 직원 입사순으로 누적 월급 구하기(부서별이 아닌 전체를 기준으로)
-- SUM() + ORDER BY
SELECT 
    employee_id,
    department_id,
    salary,
    ROUND(SUM(salary) OVER (ORDER BY EMPLOYEE_ID ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS avg_salary_by_dept
FROM 
    hr_employees
ORDER BY EMPLOYEE_ID ASC
-----------------------------------------------------------------------------------------------
-- 직원 입사순으로 부서별 누적 월급 구하기
-- SUM() + ORDER BY + PARTITION BY
SELECT 
    employee_id,
    department_id,
    salary,
    ROUND(SUM(salary) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS avg_salary_by_dept
FROM 
    hr_employees
ORDER BY EMPLOYEE_ID ASC