06.Oracle DB, 그룹화 (distinct, group by, 집계함수, having, case when )

sungjuk테이블 및 입력 데이터

○ distinct

    - 칼럼에 중복내용이 있으면 대표값 1개만 출력
    - 형식) distinct 칼럼명

select addr from sungjuk;		--모든 칼럼의 addr 선택
select distinct addr from sungjuk;	--중복하는 addr 한번만 선택

○ group by

    - 칼럼의 동일 내용끼리 그룹화 시킴
    - 형식) group by 칼럼1, 칼럼2, 칼럼3 ...

select addr from sungjuk group by addr;

    - 그룹시키고 나올수 있는 값은 1개만 조회 가능

select addr, uname      
from sungjuk
group by addr;	--ORA-00979: GROUP BY 표현식이 아닙니다.

 

○ 집계함수

    - 칼럼에 중복내용이 있으면 대표값 1개만 출력

        ① count(칼럼) : 행 개수

        ② max(칼럼) : 최대값

        ③ min(칼럼) : 최소값

        ④ sum(칼럼) : 합

        ⑤ avg(칼럼) : 평균

    - round(값, 소수점) : 반올림

select addr, count(*), max(kor), min(kor), sum(kor), avg(kor)
from sungjuk
group by addr;

 

○ 2차 그룹

-- 주소별(1차)로 그룹화하고, 주소가 같다면 국어점수(2차)로 그룹화하기    
select addr, kor, count(*)
from sungjuk
group by addr, kor
order by addr, kor desc;

 

○ having 조건절

    - group by 와 같이 사용하는 조건절
    - 형식) having 조건

-- 주소별 인원수가 2인 행을 조회하시오
select addr, count(*)
from sungjuk
group by addr
having count(*)=2;

 

○ case when ~ then end 구문

    - group by 와 같이 사용하는 조건절
    - 형식) case when 조건1 then 조건만족시 값1
                     when 조건2 then 조건만족시 값2
                     when 조건3 then 조건만족시 값3
                       ...
                     else 값
              end 결과칼럼명

-- 이름, 주소를 조회하시오 (단, 주소는 한글로 바꿔서 조회)
select uname, addr, case when addr='Seoul' then '서울'
                         when addr='Jeju'  then '제주'
                         when addr='Busan' then '부산'
                         when addr='Suwon' then '수원'
                    end as juso
from sungjuk;

● PRACTICE 연습문제

    Q1) 주소별 인원수를 조회하시오

--문1)
select addr, count(*) as cnt    -- 칼럼명 임시 부여
from sungjuk
group by addr;    

select addr, count(*) cnt       -- 칼럼명 임시 부여 (as 생략가능)
from sungjuk
group by addr;

    Q2) 주소별 국어점수에 대해서 집계하시오

          (단, 평균을 소수점없이 반올림하고 그 값으로 내림차순 정렬해서 조회

--문2)
select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor), 0)
from sungjuk
group by addr
order by avg(kor) desc;

 

    Q3) 평균(aver)이 80점 이상인 행을 대상으로 주소별 인원수를 인원수순으로 조회하시오

--문3)
select addr, count(*) as cnt
from sungjuk                   
where aver>=80 
group by addr 
order by count(*);

 

    Q4) 주소별 국어평균값이 50점 이상인 행을 조회하시오

          (단, 평균값은 소수점없이 반올림)

--문4)
select addr, round(avg(kor),0) as avg_kor
from sungjuk
group by addr
having round(avg(kor),0)>=50
order by round(avg(kor),0) desc;

 

    Q5) 평균(aver)이 70점 이상인 행을 대상으로 주소별 인원수를 구한 후,

           그 인원수가 2미만인 행을 인원수 순으로 조회하시오

--문5)
select addr, count(*)
from sungjuk
where aver>=70
group by addr
having count(*)<2
order by count(*);

 

    Q6) 이름, 국어, 학점을 조회하시오

           (학점: 국어점수 90이상 'A학점', 80이상 'B학점', 70이상 'C학점', 60이상 'D학점', 나머지 'F학점')

--문6)
select uname, kor, case when kor>=90 then 'A학점'
                       when kor>=80 then 'B학점'
                       when kor>=70 then 'C학점'
                       when kor>=60 then 'D학점'
                       else 'F학점'
                  end as 국어학점
from sungjuk;  

select uname, kor, case when kor between 90 and 100 then 'A학점'
                       when kor between 80 and 89  then 'B학점'
                       when kor between 70 and 79  then 'C학점'
                       when kor between 60 and 69  then 'D학점'
                       else 'F학점'
                  end as grade
from sungjuk;