[ex17] group by

ex17_group_by

 

    select 컬럼리스트  --4. 컬럼을 선택
    from 테이블        --1. 테이블로부터
    where 조건         --2. 원하는 레코드를
    group by 기준      --3. 그룹을 나눠서
    order by 정렬;     --5. 정렬한다.
    
    
    group by절
    - 레코드를 대상으로 그룹을 나누는 역할
    - 특정 컬럼을 대상으로 같은 값을 가지는 레코드들끼리 그룹을 묶는 역할
    - 그룹을 왜 나누는지? > 각각의 나눠진 그룹을 대상 > 집계 함수를 적용하기 위해서!!!(****************)
 

select
    round(avg(basicpay)),
    buseo
from tblInsa
    group by buseo;


-- 남자 몇명? 여자 몇명? > 남녀별 각각 몇명인지?
select * from tblComedian;

select count(*), gender
    from tblComedian
        group by gender;


-- 대륙별 국가수?
select 
    count(*), continent
from tblCountry
    group by continent;


select
    buseo,
    count(*) as "부서별 인원수",
    sum(basicpay) as "부서별 급여 합",
    round(avg(basicpay)) as "부서별 평균 급여",
    max(ibsadate) as "부서별 막내의 입사일",
    min(ibsadate) as "부서별 고참의 입사일"
from tblInsa
    group by buseo;


select
    gender,
    round(avg(height)) as "남녀별 평균 키",
    round(avg(weight), 1) as "남녀별 평균 몸무게",
    max(height) as "키가 가장 큰사람",
    min(height) as "키가 가장 작은사람"
from tblComedian
    group by gender;


--직업별 인원수? 많은 > 적은
select 
    job,
    count(*)
from tblAddressBook
    group by job
        --order by count(*) desc;
        order by job asc;





select
    round(avg(basicpay)), --집합값(집계함수)
    name                  --개인값(일반컬럼)
from tblInsa;

--위와 같은 상황의 에러
select
    round(avg(basicpay)),   --평균급여 > 집합값
    buseo,                  --부서명 > 표현(개인값처럼 보이지만) > 실제(그룹역할) > 집합값
    name                    --직원명 > 개인값.. 에러
from tblInsa
    group by buseo;



-- 다중 그룹
-- 1차 그룹(부서) > 2차 그룹(직위)
select
    buseo as "부서명",
    jikwi as "직위명", 
    count(*) as "인원수"
from tblInsa
    group by buseo, jikwi
        order by buseo, jikwi;

-- 성별 인원수?
select 
    count(*),
    substr(ssn, 8, 1)
from tblInsa
    group by substr(ssn, 8, 1);


-- 지역별 인원수?
-- 서울특별시 광진구 긴고랑로 13길 5 ~ 42
-- 광주광역시 서구 상무평화로 62 ~ 64
-- 경기도 화성시 봉담읍 매봉로 123번길 55

select * from tblAddressBook;

select 
    substr(address, 1, instr(address, ' ') - 1),
    count(*)
from tblAddressBook
    group by substr(address, 1, instr(address, ' ')- 1);


-- 이메일 사이트별 인원수?

select
    substr(email, instr(email, '@') + 1),
    count(*)
from tblAddressBook
    group by substr(email, instr(email, '@') + 1)
        order by count(*) desc;


-- 급여별 그룹 > 인원수?
-- 100만원 이하
-- 100만원 ~ 200만원
-- 200만원 이상
select
    basicpay,
    floor(basicpay / 1000000)
from tblInsa;

select
    (floor(basicpay / 1000000) + 1) * 100 || '만원 이하' as "급여대",
    count(*) as "인원수"
from tblInsa
    group by floor(basicpay / 1000000)
        order by floor(basicpay / 1000000) asc;



-- 한일? 안한일? 각각 몇개?
select 
    count(*),
    case
        when completedate is null then '안한일'
        when completedate is not null then '한일'
    end
from tblTodo
    group by case
        when completedate is null then '안한일'
        when completedate is not null then '한일'
    end;

---

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

 

    *** where절 vs having절
    
    having절 (group by와 커플)
    - 조건절
    - group by으로부터 나온 셋에 대한 조건(실행 순서: group by > having)
    - 집합에 대한 질문 > 집계 함수값을 조건으로 사용
    
    where절 (from과 커플)
    - 조건절
    - from으로부터 나온 셋에 대한 조건(실행 순서: from > where)
    - 개인에 대한 질문(행) > 컬럼값을 조건으로 사용
    
    select문(***)
    1. 각 절의 역할
    2. 각 절의 실행 순서

 

-- 그룹(buseo) > 인원수(count)
select
    count(*), buseo     --3. 그룹별 셀렉트(그룹별로 count세고, 부서값 가져옴)
from tblInsa            --1. 원본
    group by buseo;     --2. 부서컬럼 확인, 같은 값을 가진 애들끼리 그룹 형성



-- 서울 사는 직원 > 부서별 인원수?
select
    count(*), buseo         --4.
from tblInsa                --1. 테이블 원본
    where city = '서울'      --2. 행에 대한 조건 필터
    group by buseo;          --3. 부서로 그룹 나눔



select
    count(*), buseo            --4.
from tblInsa                   --1.
    where basicpay >= 2500000  --2. 여기서 조건 안되는 경우 탈락(부서 몇개 없어짐)
    group by buseo;            --3. 




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


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


-- where절은 개인에 대한 조건이기때문에 집계함수 사용X
-- having절에 가능
select
    buseo,
    round(avg(basicpay))                    --4. 나눠진 그룹별로 집계함수를 각각 구한다.
from tblInsa                                --1. 60명의 데이터를 가져온다.
    group by buseo                          --2. 60명을 대상으로 > 부서로 그룹을 나눈다.
        having avg(basicpay) >= 1500000;    --3. 그룹별 집계함수값을 조건으로 필터링



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

---

group by 함수

    1. rollup()

     - group by 결과에서 집계 결과를 더 자세하게 반환
    2. cube()

    - group by 결과에서 집계 결과를 더 자세하게 반환
    - rollup() 보다 좀 더 자세하게 표현
    - rollup()에 비해 좀 더 다양한 기준으로 중간 집계 추가
    

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



select
    buseo,
    jikwi,
    city,
    count(*),
    round(avg(basicpay))
from tblInsa
    group by rollup(buseo, jikwi, city);
    
    
    
--cube()
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);