Q1.
각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.
처음에는 max()와 group by를 사용해서 부서별 최고 월급 직원을 뽑고 emp 테이블이랑 적당히 합치려고 했는데
문제는 group by를 쓰는 순간 최고 월급 직원의 name만 추출할 수가 없었다
SELECT DEPARTMENT
, max(salary)
FROM EMPLOYEES
GROUP BY DEPARTMENT어떻게든 식을 간단하게 해보고 싶었던 자의 발악
결국에는 row_number() 함수로 파티션(group by 대용)별 1행에서 name, department, salary를 추출한 다음,
이걸 emp 테이블과 join해서 필요한 칼럼을 빼낼 수 있었다
SELECT emp.NAME
, emp.DEPARTMENT
, emp.SALARY
, bb.name 'Top_Earner'
, bb.salary 'Top_Salary'
FROM EMPLOYEES emp INNER JOIN (
SELECT *
FROM (
SELECT name
, SALARY
, DEPARTMENT
, row_number() OVER (PARTITION BY department ORDER BY salary desc) rownum
FROM EMPLOYEES
) aa
WHERE rownum <= 1
) bb
ON emp.DEPARTMENT = bb.department
;오히려 처음에 너무 쉽게 가려고 했다가 더 돌아가버린 기분
Q2.
부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요
처음에는 쉽다고 생각했는데, 내가 지금 머리가 굳은건지, 어거지로 서브쿼리 중첩해서 코딩하는 게 아니면 정답이 보이질 않는다...
처음에는 avg(salary) 계산하는 서브쿼리를 하나 만들고,
이걸 max() 계산해서 where절에 넣으면 되겠거니...했었는데
생각해보니 월급이 가장 높은 부서를 찾는 것이고, 그럼 where절은 당연히 아니었고...
SELECT department
, avg_sal
FROM (
SELECT department
, row_number() OVER (order BY avg_sal desc) AS 'rownum'
, avg_sal
FROM (
SELECT department
, avg(salary) avg_sal
FROM EMPLOYEES
GROUP BY DEPARTMENT
) aa
) bb
WHERE rownum <= 1
;어찌됐든 최종 결과물
첫번째 서브쿼리(aa)에서는 dep별로 avg(sal)을 구하는 용도이고,
두번째 서브쿼리(bb)에서는 avg(sal)을 내림차순으로 해서 rownum을 반환해준다
(row_number() 함수에서 partition값을 null로 하면 파티션을 나누지 않고 전체 데이터에서 rownum을 수행한다)
그리고 마지막 메인쿼리에서 rownum이 1인 값만 출력하도록 했다
