[group by 이후 집계함수 사용] 질문있습니다. 0 2 244

by 부산광어 [2020.05.22 11:38:10]


HR schema.gif (17,664Bytes)

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번을 사용하는 것으로 습관을 들여야겠군요

정말루 감사합니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입