DAY-24. Oracle group by, having

🔐 2022-04-04 - 1

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

1️⃣ group by

• 그룹함수 단 한개의 결과값을 도출하고 싶을 때(그룹별 집계)
• 그룹함수가 적용될 그룹 기준으로 해서 group by 절에 해당 그룹을(컬럼) 기술해 사용
• 그룹으로 나누어서 결과 도출한다고 생각

🔎
//부서별 인원 조회
select nvl(dept_code,'인턴') "부서코드" , count(*) from employee
    group by dept_code;

🔎
// 부서별 인원 조회 -> 부서코드가 null 인 경우에는 count를 하지 않음
select dept_code "부서코드", count(*) from employee
    where dept_code is not null
    group by dept_code;

🔎
//부서코드, 부서별 인원, 부서별 월급 총합, 부서별 월급 평균 조회
select nvl(dept_code,'인턴') "부서코드" ,
       count(*) "부서별 인원",
       to_char(sum(salary),'L999,999,999') "부서별 월급 총합",
       to_char(round(avg(salary)),'L999,999,999') "부서별 월급 평균"
from employee
       group by dept_code
       order by 1;

🔎
//부서코드, 보너스를 지급받는 사원의 수(보니스율이 있는 사원)를 조회 -> 부서코드 순으로 정렬

select nvl(dept_code,'인턴')"부서코드",
       count(*) || '명' "보너스 받는 사원수"
from employee
    where bonus is not null
    group by dept_code
    order by 1;

🔎
// 성별, 성별별 급여의 평균(정수처리_소수점 버림), 성별별 급여의 합계, 성별별 인원수 -> 인원수를 기준으로 내림차순
select
       decode(substr(emp_no, 8, 1),'1','남','여') "성별",
       to_char(floor(avg(salary)),'L999,999,999')"급여 평균",
       to_char(sum(salary),'L999,999,999') "급여 합계 ",
       count(*) "인원"
from employee
    group by substr(emp_no, 8, 1)
    order by 4 desc;

• group by에 extract를 안쓰고 hire_date로 하면 년도는 같은데 날짜가 다르면 다른 그룹으로 바뀌어버린다 그래서 extract사용 필수

group by 안에 1개 이상의 조건

• 그룹 안의 그룹 ⇒ 그룹을 먼저 하나의 조건으로 나눈뒤, 그 안에서 또 다른 조건으로 나눔

🔎
//각 부서안의 직급별로 부서코드, 직급코드, 직급별 사원수, 평균급여

select
    nvl(dept_code,'인턴') "부서코드",
    job_code "직급코드",
    count(*) "사원수",
    round(avg(salary)) "평균급여"
from employee
    group by dept_code,job_code
    order by 1,2;

🔎
//문제3. 부서코드, 성별, 부서별 성별 사원수를 구하세요.
 (부서코드, 성별 기준 오름차순 정렬)

select nvl(dept_code,'인턴') "부서코드",
       decode(substr(emp_no,8,1),'1','남','여') "성별",
       count(*)
from employee
       group by dept_code, substr(emp_no,8,1) -- 먼저 부서로 나누고 , 그 다음 성별로 나눔
       order by dept_code ,2 ;

2️⃣ Having

• where절에서 group함수를 사용 할 수 없을 때
• group by를 이용해서 그룹함수로 값을 구해올 때 그 값에 대한 조건을 걸고 싶다면 사용하는 절
• 즉 having은 group by를 통해 나온 값에 다시 조건을 줄 때 사용

🔎
// 급여 평균이 300만원 이상인 부서에 대한 부서코드, 평균급여 조회
// 부서별로 그룹화 -> 각 부서별 급여 평균 -> 그 평균이 300만원 이상 -> 부서코드, 평균급여 조회
// 부서코드가 D6라면 제외

select nvl(dept_code,'인턴') "부서코드",
	    floor(avg(salary)) "평균급여"

from employee
	where dept_code not in ('D6')
		group by dept_code
	having floor(avg(salary)) >= 3000000;

🔎
//인원이 3명이 미만인 직급코드는 인원을 보충
select nvl(job_code,'인턴') "직급코드",
    count(*) "인원"
from employee
    group by job_code
    having count(*) < 3; -- group by의 결과 값에 조건을 거는거다

💡 where VS having

• where은 기본적인 조건절로서 우선적으로 모든 필드를 조건에 둘 수 있다. 하지만 having은 group by 된 이후 특정한 필드로 그룹화 되어진 새로운 테이블에 조건을 줄 수 있다.

1) select dept_code, salary, from employee where salary > 4000000 group by dept_code;
2) select dept_code, avg(salary) from employee where salary group by dept_code having avg(salary)> 4000000;

1)의 경우는 where 조건절에 맞는 정보를 우선적으로 들고와서 group by로 그룹화 한다

2)의 경우는 group by로 그룹화 한 상태에서 having 조건절에 맞는 정보를 들고 온다