Big Bro's Studying Archive

SQL by Orcale # 4. decode, case문, group by절과 having절 본문

Big Data展

SQL by Orcale # 4. decode, case문, group by절과 having절

빅브로오 2020. 4. 8. 13:33

어떤 마음가짐으로 공부할지

decode와 case문의 특성 이해
상황에 따라 좀 더 적합한 문법 활용 가능하도록
그룹함수는 그룹별로 출력되는 결과값이니
그룹별로 분류되지 않는 데이터를 선택할 때 주의

조건문 이어서

1. decode함수

decode(대상, 조건1, 치환1, else값)
deptno가 10인 직원중 job이 clerk인 직원은 A부서로 나머지 직원은 B부서로
20인 직원은 C부서로
30인 직원은 D 부서로 이동

select deptno, ename, decode(deptno, 10, decode(job, 'CLERK', 'A', 'B'),
                                     20, 'C',
                                     30, 'D')
  from emp;

2. case 문

예제_ deptno가 10이면 A, 20이면 B, 30이면 C로 치환&응용

select ename, deptno, job,
       case when deptno = 20 then 'B'
            when deptno = 30 then 'C'
            when deptno = 10 and job = 'CLERK' then 'A'
                            else 'A2'
       end as 새부서명,

       case deptno when 10 then 'A' 
                   when 20 then 'B'
                   when 30 then 'C'
        end as 새부서명2, 
        -- 비교 컬럼을 앞에 높을 경우
        -- when 뒤에 들어가는 데이터값은 컬럼 데이터 형식과 일치해야한다.

       case when sal > 3000 then 'A'
            when sal <= 3000 then 'B'
        end as 등급
  from emp;

연습문제1) student 테이블에서 성별컬럼 생성(남자, 여자)

select studno, name, jumin,
               case when substr(jumin, 7, 1) = '1' then '남자'
               else '여자' end as 성별
from student;

연습문제2) emp 테이블에서 sal별 등급 부여
sal이 3000 초과인 경우 'A', 그 외 'B'

select ename, case when sal > 3000 then 'A'
              when sal <= 3000 then 'B'
              end as 등급,
       decode(sign(sal-3000), 1, 'A', 'B') as 등급2
  from emp; 
-- decode는 대소 비교를 통한 치환이 불가능하므로, sign함수를 이용해 대소비교가 가능하게 만들어서 하게 하면 된다.
-- 그러나 별로 좋은 쿼리는 아니다... case 써주는게 성능상 나음

select name, jumin, decode( substr(jumin,7,1), 1, '남자', '여자')
  from student;
-- 이런 식으로 일치 불일치 비교가 오는 것이 더 좋다.

아래는 개인적으로 연습해본 것들이므로 참고

select deptno, count(empno), max(sal), round(avg(sal), 1) 
  from emp
 group by deptno
 order by avg(sal);

select job, count(empno), round(avg(sal), 1)
  from EMP
 group by job
 order by job;

select job, deptno, count(empno), round(avg(sal+nvl(comm, 0)))
  from emp
 group by job, deptno
 order by round(avg(sal));

select job, avg(sal), sum(sal)
  from emp
 group by job
   having avg(sal) >= 3000;

그룹함수 (복수행함수) : 여러행 데이터 입력, 한 건 출력

1. count

select count(*), count(empno), count(comm)
  from emp;
-- *를 쓰면 가장 정확하게 조회되나 모든 테이블의 데이터를 조회하다보니 성능상 좋지 않다.
-- not null(NN)인 컬럼을 쓰는 게 가장 좋다.

2. sum

select sum(sal), sum(comm)
  from emp;

3. avg

select avg(sal),
       avg(comm), -- not null인 4명에 대한 평균
       sum(comm)/count(empno), -- 14명에 대한 평균
       avg(nvl(comm, 0)) -- 14명에 대한 평균
  from emp;

avg comm과 전체 평균 결과가 다름... avg는 null을 제외하기 때문

4. min / max

select min(sal), max(sal), sal 
       -- 그룹함수는 그룹함수가 아닌 컬럼과는 동시에 사용할 수 없다.
  from emp;

group by 구문

: 특정 그룹별 그룹함수의 연산결과 출력

예제) emp 테이블에서 부서별 최대연봉 출력

select dept.deptno, dname, max(sal)
  from emp, DEPT
 where emp.deptno = dept.DEPTNO
 group by dept.deptno, dname;
-- 출력되는 테이블의 결과값을 상상해보며 쿼리를 짜보자.
-- 뒤에서 배울 join을 활용하여 부서명 까지 나오도록 쿼리를 짜보았다.

아래는 개인적으로 연습해본 쿼리들이다.

select job, max(sal)
  from emp
 group by job;

select ename, sal, decode(sal, (select max(sal) from emp where job = 'CLERK'), '최고연봉')
  from emp;

 select grade, avg(height)
   from student
  group by grade;

 select deptno, max(sal), (select ename from emp where sal = (select max(sal) from emp group by deptno)) from emp group by deptno;)
   from emp
  group by deptno;

  select deptno, SAL, (select ename from emp)
    from emp;

연습문제) student 테이블에서 학년별, 성별 키의 평균

 select grade, case when substr(jumin, 7, 1) = '1' then '남'
                                                    else '여'
                                                    end as SEX, 
               round(avg(height)) 키평균
   from STUDENT
  group by grade, substr(jumin, 7, 1)
  order by grade;

having 절 : group by 연산 결과에 조건 추가시

참고 : 쿼리문의 실행 순서

select     --5
  from     --1
 where     --2
 group by  --3
 having    --4 
 order by -- 6

예제) 부서별 평균연봉이 2500이상인 부서 출력

select deptno, round(avg(sal),1)
  from EMP
 group by deptno
having avg(sal) >= 2500;

예제2) 부서별 평균연봉 출력, 10번 제외

select deptno, round(avg(sal))  
from EMP  
group by deptno  
having job = 'CLERK';
Comments