데이터베이스 질문과 답변
[group by 이후 집계함수 사용] 질문있습니다.
0
2
554
by 부산광어 [2020.05.22 11:38:10]
Q. 각 부서별 최대 급여자의 아이디(employee_id), 이름(first_name), 급여(salary)를 출력하시오.
단, 최대 급여자가 속한 부서의 평균급여를 마지막으로 출력하여 평균급여와 비교할 수 있게 할 것.
select max(d.department_name), max(e.employee_id), max(e.first_name),
max(e.salary), round(avg(e.salary),0)
from employees e, departments d
where e.department_id = d.department_id
group by d.department_id;
위의 질문에 대해서 제가 짠 쿼리입니다. 아무 생각없이 집계함수를 사용하다가,
문득 궁금한 점이 떠올라서 질문을 하게 되었습니다.
max(e.salary)와 같이 상수값의 max, min은 말 그대로 최대, 최소값을 반환하지만
max(e.first_name)와 같은 값을 가져올때는 어떻게 가져오나요?
단순히 블록에 튜플별로 저장하기 때문에, e.salary가 max인 값의 e.first_name 값을 가져온다고 보아야할까요?
by 마농
[2020.05.22 14:07:20]
MAX(salary) 와 MAX(first_name) 을 함께 사용시
- 최대 급여자의 이름이 출력되지 않습니다. 그냥 이름이 큰 사람 출력됩니다.
부서별 최대 급여자가 1명 뿐이라는 보장은 없습니다. 여러명일 수 있습니다.
1명 뿐이라면
- 집계함수 MAX() KEEP(DENS_RANK FIRST/LAST) 방법을 이용할 수 있습니다.
- 분석함수 ROW_NUMBER() 를 이용해도 되구요.
하지만 1명 이상일 수 있으니 다른 방법을 찾아야죠.
- 집계결과를 인라인뷰로 해서 다시 사원과 조인하면 됩니다.
- 분석함수 RANK() 를 이용해도 되구요.
-- 1. 최대 급여자는 무조건 1명 뿐이라는 가정하에 --
SELECT d.department_id
, d.department_name
, MIN(e.employee_id) KEEP(DENSE_RANK FIRST ORDER BY e.salary DESC) employee_id
, MIN(e.first_name ) KEEP(DENSE_RANK FIRST ORDER BY e.salary DESC) first_name
, MAX(e.salary) max_sal
, ROUND(AVG(e.salary), 2) avg_sal
FROM employees e
, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY department_id
;
-- 2. 집계결과와 사원 조인
SELECT a.department_id
, a.department_name
, b.employee_id
, b.first_name
, b.salary
, a.avg_sal
FROM (SELECT d.department_id
, d.department_name
, MAX(e.salary) max_sal
, ROUND(AVG(e.salary), 2) avg_sal
FROM employees e
, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
) a
, employees b
WHERE a.department_id = b.department_id
AND a.max_sal = b.salary
ORDER BY department_id
;
-- 3. RANK 분석함수 이용
SELECT department_id
, department_name
, employee_id
, first_name
, salary
, avg_sal
FROM (SELECT d.department_id
, d.department_name
, e.employee_id
, e.first_name
, e.salary
, ROUND(AVG(e.salary) OVER(PARTITION BY d.department_id), 2) avg_sal
, RANK() OVER(PARTITION BY d.department_id ORDER BY e.salary DESC) rk
FROM employees e
, departments d
WHERE e.department_id = d.department_id
)
WHERE rk = 1
ORDER BY department_id
;
by 부산광어
[2020.05.22 16:16:11]
이때까지 오답을 정답으로 알고 있었다니 ㅠㅠ..
이렇게 깨닫게 해주셔서 감사합니다!
최대 급여자가 1명이라는 보장이 없을테니, 2, 3번을 사용하는 것으로 습관을 들여야겠군요
정말루 감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.