DATABASE STEP 18 - Having 절

★ Having 절

• 조건절
• group by로 부터 나온 셋에 대한 조건
• 집합에 대한 질문 > 집계 함수값을 조건으로 사용
• 조합

[WITH <Sub Query>]                                  - WITH 절
SELECT column_list                                     - SELECT 절 
FROM table_name                                     - FROM 절
[WHERE search_condition]                            - WHERE 절
[GROUP BY group_by_expression]                  - GROUP BY 절
[HAVING search_condition]                          - HAVING 절
[ORDER BY order_expresstion [ASC|DESC]];      - ORDER BY 절

select 컬럼리스트  -- 5. 컬럼을 선택
from 테이블         -- 1. 테이블로부터
where 조건          -- 2. 원하는 레코드를
group by 기준      -- 3. 그룹을 나눠서
having 절            -- 4. 그룹에 대한 조건
order by 정렬조건 -- 6. 정렬한다.

 

■ having 절 사용 ex)

• 1. group by절만 사용

select 
    buseo,
    round(avg(basicpay))      -- 3. 나눠진 그룹별로 집계함수를 각각 구한다.
from tblInsa                     -- 1. 60명의 데이터를 가져온다.
    group by buseo;           -- 2. 60명을 대상으로 > 부서로 그룹을 나눈다.

• 2. where 절 복합사용

select 
    buseo,
    round(avg(basicpay)) as "평균 급여"         -- 4. 나눠진 그룹별로 집계함수를 각각 구한다.
from tblInsa                                           -- 1. 60명의 데이터를 가져온다.
    where basicpay >= 1500000                 -- 2. 60명을 대상으로 조건에 맞는 직원만 남긴다.
        group by buseo;                             -- 3. where절을 만족한 대상으로 > 부서로 그룹을 나눈다.

• 3. Having 절

select
    buseo,
    round(avg(basicpay)) as "평균 급여"      -- 4. 나눠진 그룹별로 집계함수를 각각 구한다.
from tblInsa                                        -- 1. 60명의 데이터를  가져온다.
    group by buseo                               -- 2. 60명을 대상으로 > 부서별 그룹을 나눈다.
        having avg(basicpay) >= 1500000   -- 3. 부서별 150만원 이상인 그룹만 남긴다.(그룹별 집계함수값을 조건으로 필터링)
            order by avg(basicpay) desc;


select
    buseo,
    round(avg(basicpay)) as "평균 급여"              -- 5.
from tblInsa                                                -- 1. from 절 
    where basicpay >= 1500000                      -- 2. where 절
        group by buseo                                   -- 3. 
            having avg(basicpay) >= 2200000        -- 4.
                order by avg(basicpay) desc;           -- 6.

 

■ 요구사항에 따른 ex)

-- tblZoo. 체온이 변온인 종류 중 아가미 호흡과 폐 호흡을 하는 종들의 갯수를 가져오시오.
SELECT 
    count(case
        when breath in ('lung') then 1
    end) as "변온,폐 호흡",
    count(case
        when breath in ('gill') then 1
    end) as "변온,아가미 호흡"
FROM tblzoo
    GROUP BY thermo
        HAVING thermo = 'variable';


-- tblAddressBook. 관리자의 실수로 몇몇 사람들의 이메일 주소가 중복되었다. 중복된 이메일 주소만 가져오시오.
select *
from tblAddressBook;
select 
    Email,
    count(email)
from tblAddressBook
    Group by email
    having count(email)>1;
-- tblAddressBook. 성씨별 인원수가 100명 이상 되는 성씨들을 가져오시오.
select 
    substr(name, 1, 1) as "성",
    count(substr(name, 1, 1))
from tblAddressBook
    Group by substr(name, 1, 1)
    having count(substr(name, 1, 1)) >= 100;
-- tblAddressBook. '건물주'와 '건물주자제분'들의 거주지가 서울과 지방의 비율이 어떻게 되느냐?
select
    job,
    count(*) as "총인원수",
    count(case
            when substr(address, 1, 2) = '서울' then 1
    end) as "서울 거주",
    count(case
            when substr(address, 1, 2) <> '서울' then 1
    end) as "서울 비거주",
    
    round(count(case
            when substr(address, 1, 2) = '서울' then 1
    end) / count(*) * 100, 2) as "서울 거주(%)",
    
    round(count(case
            when substr(address, 1, 2) <> '서울' then 1
    end) / count(*) * 100, 2) as "서울 비거주(%)"
    
from tblAddressBook
    group by job
        having job in ('건물주', '건물주자제분');

 

★ Group by 함수

• 1. roll up()
• 2. cube()

 

■ 1. roll up()

• group by 결과에서 집계 결과를 더 자세하게 반환
• 사용 ex)

select
    buseo,
    count(*),
    round(avg(basicpay))
from tblInsa
    group by rollup(buseo); -- rollup은 총계를 내줌.
    
select
    buseo,
    jikwi,
    count(*),
    round(avg(basicpay))
from tblInsa
    group by rollup(buseo, jikwi);
      --  order by buseo asc, jikwi asc;
      

select
    buseo,
    jikwi,
    city,
    count(*),
    round(avg(basicpay))
from tblInsa
    group by rollup(buseo, jikwi, city);

 

■ 2. cube()

• group by 결과에서 집계 결과를 더 자세하게 반환
• roll up() 보다 조금 더 자세하게 표현
• 사용 ex)

select
    buseo,
    jikwi,
    count(*),
    round(avg(basicpay))
from tblInsa
    group by cube(buseo, jikwi);
    
select
    buseo,
    jikwi,
    city,
    count(*),
    round(avg(basicpay))
from tblInsa
    group by cube(buseo, jikwi,city);