2024-06-07

안녕하세요 오늘은 금요일이에요 !!!
얼른 하루 마무리하고 주말도 푹 쉬어봅시다 🤗

---

♾️ SQL 베이직 강의 학습

🌟 Window 함수

✅ 순위 매기기 ROW_NUMBER() RANK() & DENSE_RANK() LAG() & LEAD()

✅ 백분위수 계산 NTILE()

✅ 특정열 반환 FIRST_VALUE() & LAST_VALUE()

사실 저는
윈도우함수는 사용하지 않고 있었답니다,,
그동안 안 쓰고 버텼는데 쓰면서 굉장히 편한 걸 알았네요 ,,

사용한 테이블은
부서 별 직원의 급여에 관한 테이블이었습니다 !

기본적으로 ROW_NUMBER()를 사용해서
서브쿼리를 열어 급여가 높은 순으로
순서를 매기는 쿼리를 만들어보았어요 !!

위 함수는 순서를 매겨줬지만
이번엔 순위를 매겨보았는데요 !!
동순위 발생 시 RANK는 순위 건너뛰고, DENSE_RANK는 건너뛰지 않습니다

그래서 세번째 RANK를 보시면
12 - 14 - 14 - 16순이지만,
네번째 DENSE_RANK는
12 - 13 -13 - 14로 하나의 숫자도 빠짐없이 매겨줍니다 !

그렇기 때문에 제일 마지막 120번째 사원의 순위를 보면

DENSE_RANK는 숫자의 공백이 없기 때문에
120이 아닌 78위로 마무리 되는 걸 알 수 있네요 ㅎㅎ

SELECT name, >> 사원의 이름
       salary, >> 급여
       LAG(salary, 2)>> 현재 행의 전 OVER (ORDER BY salary) as prev_salary,
       LEAD(salary, 2)>> 현재 행의 후 OVER (ORDER BY salary) as next_salary
       >> 급여가 낮은 순으로
FROM sys.employees

LAG() & LEAD() 의 경우,
해당 행을 기준으로 해당 기준 전, 후를 보여주는 함수인데요
그래서 급여가 낮은 순으로 order by 했는데

저는 괄호에 2라고 적어줬기 때문에
= 해당 기준보다 2번째 전, 후 로 보시면 됩니다

그렇기 때문에 첫번째와 두번째는
LAG행이 출력 되지 않았구요..

차례를 쭉쭉 아래로 보시면 2만큼 건너띄고 있다는 걸 알 수 있습니다 ㅎㅎ

📌 고급 문제 : 각 부서에서 가장 높은 급여와 가장 낮은 급여 차이 계산하기

---

문제
각 부서(department) 내에서 가장 높은 급여와 가장 낮은 급여의 차이를 계산하세요.

힌트
FIRST_VALUE, LAST_VALUE 함수를 사용하세요.

예시

예시를 보시면
일단 SELECT에 4개는 출력해야겠구나 감은 왔는데

이걸 어떻게 써야하지 싶어서 머리를 굴러본 결과
2회차에서 정답 확인했답니다

WITH dept_salaries AS ((서브쿼리)
SELECT department,
       salary,     
       FIRST_VALUE(salary) OVER >> 제일 큰 급여 값 하나 추출
       (PARTITION BY department ORDER BY salary DESC) as highest_salary,
       >> 부서 별로 묶어버리기 / 급여 내림차순=제일 큰 급여 값
       LAST_VALUE(salary) OVER >> 제일 적은 급여 값 하나 추출
       (PARTITION BY department
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
       >> 각 부서별로 급여를 행의 범위를 지정하여 최저 급여 추출
FROM employees
)
(메인쿼리)
SELECT department, >> 부서 출력
       MAX(highest_salary) AS highest_salary, >> 급여 제일 높은 값
       MAX(lowest_salary) AS lowest_salary, >> 급여 제일 낮은 값
       MAX(highest_salary) - MAX(lowest_salary) AS salary_diff
     >> 급여 제일 높은 값 - 급여 제일 낮은 값 = 차액 출력
FROM dept_salaries
GROUP BY department >> 부서 별 묶어주기

4개의 칼럼이 출력되어
부서 별 제일 높은 급여 - 제일 적은 급여 - 차액 이렇게 결과가 나옵니다 !

FIRST_VALUE는 order by desc를 해줘서
범위가 정해져있는데
LAST_VALUE는 order by도 안쓰고

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

이뭔씹
이게 뭐냐면요

LAST_VALUE 함수는 기본적으로 지정된 창의 마지막 값을 반환해서
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING는 창을 전체 파티션으로 설정해서 해당 파티션(department)의 모든 행을 포함해요
결과적으로, 이 설정은 파티션 내의(부서 별) 마지막 행의 값을 반환하게 됩니다.

그렇기 때문에 기본적으로 마지막 행이었던 급여가 낮은 값이 자동적으로 출력이 될 수 있었던 거였습니다 !!!!

그치만,,, 데이터 순서가 그렇게 호락호락하지 않다면 ,,,?

만약 데이터가 특정 순서로 정렬되어 있지 않다면 ?!
마지막 행의 급여가 반드시 최저 급여일 것이라는 보장은 없어요

그렇기에 각 부서의 최저 급여를 정확히 구하려면,
MIN 함수를 사용하는 것이 안전합니다 !
MIN 함수는 각 부서 내에서 급여의 최소 값을 정확히 반환해요

WITH dept_salaries AS (
    SELECT department,
           salary,
           FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary,
           MIN(salary) OVER (PARTITION BY department) as lowest_salary
    FROM employees
)
SELECT department,
       MAX(highest_salary) AS highest_salary,
       MAX(lowest_salary) AS lowest_salary,
       MAX(highest_salary) - MAX(lowest_salary) AS salary_diff
FROM dept_salaries
GROUP BY department;

이렇게 써도
똑같이 결과를 출력되네요 😇

---

오늘은 SQL을 집중적으로 파봤어요 !!
윈도우 함수를 알게돼서 코테에서 만일 특정 값을 뽑는 문제를 풀 때
유용하게 사용할 것 같네요 ㅎㅎ

금요일 하루 마무리 잘 하시고
주말 다들 잘 보내세용 !!

다들 고생 많으셨습니다 🍀🍀🍀🍀🍀