04 Null, Case, Decode, 그룹함수, GROUP BY, HAVING

1. 데이터 형식 확인하자
2. 숫자로된 문자는 숫자형으로 변환하여 계산

---

 

[문제] 2005년도에 홀수달에 입사한 사원들의 정보를 출력.

DESC employees;                         -- 테이블 형식 체크
SELECT * FROM nls_session_parameters;   -- 클라이언트 정보 체크

SELECT *
FROM hr.employees
WHERE to_char(hire_date,'yyyy') = '2005'        -- to_char : 문자형
AND mod(to_number(to_char(hire_date,'mm')),2) = 1;    -- mod 계산을 위해 to_number 숫자형으로 변환

SELECT *
FROM hr.employees
WHERE hire_date >= to_date('2005/01/01','rrrr/mm/dd')
AND hire_date <= to_date('2005/12/31','rrrr/mm/dd')
AND mod(to_number(to_char(hire_date,'mm')),2) = 1;

hire_date >= '2005/01/01'   -> 연도 4자리 표기
   date          char       -> 암시적 데이터 변환 사용 자제 -> to_date 사용하자

 

SELECT mod(to_number(to_char(hire_date,'mm')),2), salary
FROM hr.employees;

salary                    -> 숫자    : 오른쪽정렬

to_char(hire_date,'mm')            -> 문자로 추출    : 왼쪽정렬
                    -> 암묵적 숫자형변환 -> 사용 자제

to_number(to_char(hire_date,'mm'))    -> 숫자로 바꿔 계산하자

 

---

 

■ NULL
- null은 사용할 수 없거나, 할당되지 않았거나, 알 수 없거나, 적용할 수 없는값, 계산할 수 없다. 결측값(치)
- null 0, 공백이 아니다

SELECT
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct)) annual_salary_1,
    ((salary * 12) + (salary * 12 * nvl(commission_pct,0))) annual_salary_2
FROM hr.employees;

 

---

■ nvl ★
- null값을 실제값으로 리턴하는 함수
- nvl(칼럼, 실제값) , nvl(인수, 인수)
- nvl함수 사용시에 두 인수의 타입이 일치해야 한다.

SELECT
    nvl(commission_pct,0),
    nvl(commission_pct,to_number('0')),
    nvl(to_char(commission_pct),'no comm')
FROM hr.employees;


COMMISSION_PCT : NUMBER(2,2) : 숫자형 칼럼

nvl(commission_pct,'no comm')   -> 'no comm' : 문자라 형식이 달라 오류 남
                                -> to_char(commission_pct) 숫자를 문자형으로 변환

 

■ nvl2(exp1, exp2, exp3)
- exp1이 null이 아니면 exp2를 수행하고 exp1이 null이면 exp3를 수행한다.
- exp2와 exp3의 인수가 일치해야 한다.

SELECT
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct)) annual_salary_1,
    ((salary * 12) + (salary * 12 * nvl(commission_pct,0))) annual_salary_2,
    nvl2(commission_pct, ((salary * 12) + (salary * 12 * commission_pct)), salary * 12) annual_salary_3
FROM hr.employees;


nvl2(commission_pct, ((salary * 12) + (salary * 12 * commission_pct)), salary * 12) annual_salary_3

<PL/SQL>
if commission_pct is not null then
    ((salary * 12) + (salary * 12 * commission_pct))
else :
    salary * 12
end if

 

SELECT
    nvl(commission_pct,0),
    nvl(commission_pct,to_number('0')),
    nvl(to_char(commission_pct),'no comm'),
    nvl2(commission_pct, to_char(salary * 12 * commission_pct), 'no comm'),
FROM hr.employees;


nvl2(commission_pct, salary * 12 * commission_pc, 'no comm')
                     └ 숫자형                      └ 문자형     -> 타입 불일치 오류
                       : to_char사용하여 형변환

 

■ coalesce(exp1, exp2, exp3, ..., expn)
- exp1 null이면 exp2를 수행하고, exp2 null이면 exp3를 수행하고,
exp3 null이면 다음 exp를 수행한다.
즉, null이 발생하지 않을때가지 인수를 수행한다.

- ★ 비교에 있어 인수타입이 서로 같아야 한다.

SELECT
    nvl(commission_pct,0),
    nvl(commission_pct,to_number('0')),
    nvl(to_char(commission_pct),'no comm'),
    nvl2(commission_pct, to_char(salary * 12 * commission_pct), 'no comm'),
    coalesce(to_char(commission_pct), to_char(salary * 12 * commission_pct), 'no comm')
FROM hr.employees;


SELECT
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct)) annual_salary_1,
    ((salary * 12) + (salary * 12 * nvl(commission_pct,0))) annual_salary_2,
    nvl2(commission_pct, ((salary * 12) + (salary * 12 * commission_pct)), salary * 12) annual_salary_3,
    coalesce(((salary * 12) + (salary * 12 * commission_pct)), salary * 12, 0) annual_salary_4
FROM hr.employees;

 

■ nullif(exp1, exp2)
- exp1과 exp2가 일치하면 null, 일치하지 않으면 exp1을 리턴한다.

SELECT
    length(last_name),
    length(first_name),
    nullif(length(last_name),length(first_name)),
    nullif(length(last_name),5)
FROM hr.employees;

<PL/SQL>
if exp1 = exp2 then
    null
else
    exp1
end if

 

---

 

■ 조건제어문
- SQL문에서는 IF문을 사용해서 조건제어문을 수행할 수 없다.
- CASE 표현식, DECODE 함수

- DECODE 함수는 기준값과 비교값을 내부적으로 같다(=)라는 비교연산자를 사용한다.
DECODE(기준값, 비교값, 참값) -- false면 null값으로 리턴한다.

    **DECODE(기준값, 비교값, 참값, 기본값)    -- false면 기본값을 리턴한다.**

- 오라클 전용 함수

DECODE(기준값, 비교값1, 참값1, 비교값2, 참값2, 비교값3, 참값3, ... 기본값) -- 기본값 미표기 시 null 값 리턴	IF 기준값 = 비교값 THEN 참값 ELSE null END IF

SELECT
    employee_id,
    salary,
    job_id,
    decode(job_id,
            'IT_PROG', salary * 1.1,
            'ST_CLERK', salary * 1.2,
            'SA_REP', salary * 1.3,
            salary) revised_salary
FROM hr.employees;

 

비교연산자 : =, >, >=, <, <=, <>, !=, ^=, BETWEEN AND, IN,
IS NULL, IS NOT NULL, LIKE
논리연산자 : AND, OR, NOT

 

- CASE 표현식(9i) : 기준값과 비교값의 대해서 모든 비교연산자를 사용할 수 있다.

CASE 기준값
    WHEN 비교값1 THEN 참값1
    WHEN 비교값2 THEN 참값2
    WHEN 비교값3 THEN 참값3
    ...
    ELSE 기본값
END

SELECT
    employee_id,
    salary,
    job_id,
    CASE job_id
        WHEN 'IT_PROG' THEN salary * 1.1
        WHEN 'ST_CLERK' THEN salary * 1.2
        WHEN 'SA_REP' THEN salary * 1.3
        ELSE salary            -- 생략 시 NULL값 리턴
    END revised_salary
FROM hr.employees;

 

CASE 
    WHEN 기준값 비교연산자 비교값1 [AND,OR] ... THEN 참값1
    WHEN 기준값 비교연산자 비교값2 THEN 참값2
    WHEN 기준값 비교연산자 비교값3 THEN 참값3
    ...
    ELSE 기본값
END

SELECT
    employee_id,
    salary,
    job_id,
    CASE
        WHEN job_id = 'IT_PROG' THEN salary * 1.1
        WHEN job_id = 'ST_CLERK' THEN salary * 1.2
        WHEN job_id = 'SA_REP' THEN salary * 1.3
        ELSE salary
    END revised_salary
FROM hr.employees;

 

[문제] 사원들의 급여를 기준으로 아래와 같이 출력.
      ~ 4999  : low
 5000 ~ 9999  : medium
10000 ~ 19999 : good
20000 ~       : excellent

SELECT 
    salary,
    CASE
        when salary < 5000 then 'low'
        when salary < 10000 then 'medium'
        when salary < 20000 then 'good'
        else 'excellent'
    END grade
FROM hr.employees;

 

■ DECODE 함수, CASE 표현식 null check 방법
- decode함수의 null 체크는 null 키워드를 사용한다.
- case표현식에서 null 체크는 is null, is not null 연산자를 사용한다.

SELECT
    employee_id,
    salary,
    commission_pct,

    DECODE(commission_pct, null, salary * 12, 
            ((salary * 12) + (salary * 12 * commission_pct))) annual_salary_1,

    CASE
        WHEN commission_pct is null then salary * 12
        ELSE (salary * 12) + (salary * 12 * commission_pct)
    END annual_salary_2

FROM hr.employees;

 

---

 

■ yy와 rr 차이점

예) 95-10-27
- YY : 현재 년도의 세기를 반영 : 20세기 (2095-10-27), 21세기 (2009/10/27)
- RR : 2000년부터는 표기법을 자동화로 변경해순다.

SELECT
    to_char(to_date('95-10-27','yy/mm/dd'), 'yyyy-mm-dd') yy,
    to_char(to_date('95-10-27','rr/mm/dd'), 'yyyy-mm-dd') rr
FROM dual;

(RR 타입 기준 표)

현재연도	데이터 입력 연도
0~49	50~99
0~49	반환 날짜는 현재 세기를 반영	반환 날짜는 이전 세기를 반영
50~99	반환 날짜는 이후 세기를 반영	반환 날짜는 현재 세기를 반영

예)

현재연도	데이터입력날짜	YY	RR
1994	95-10-27	1995	1995
1994	17-10-27	1917	2017
2001	17-10-27	2017	2017
2048	52-10-27	2052	1952
2051	47-10-27	2047	2147

 

---

 

■ 그룹함수
- 여러행당 하나의 결과를 반환하는 함수
- SUM, AVG, MEDIAN, VARIANCE, STDDEV, MAX, MIN, COUNT

- 그룹함수에 입력하는 값이 숫자형만 입력해야하는 그룹함수 : SUM, AVG, MEDIAN, VARIANCE, STDDEV
- 그룹함수에 입력하는 값이 모든 타입이 가능한 그룹함수 : MAX, MIN, COUNT

- 그룹함수는 NULL 을 포함하지 않습니다(=제외). 단, COUNT(*) 만 NULL 포함한 행수를 구한다.

 

- COUNT : 행의 수를 구하는 함수

SELECT count(*)                         -- null 포함 : 전체 검색 시
FROM hr.employees;

SELECT count(department_id)             -- null 제외한 건수 : 컬럼명으로 검색 시
FROM hr.employees;

SELECT count(distinct department_id)    -- 중복을 제거한 건수
FROM hr.employees;

SELECT count(unique department_id)      -- 중복을 제거한 건수
FROM hr.employees;

 

- SUM : 합

SELECT sum(salary)
FROM hr.employees;

SELECT count(commission_pct),sum(commission_pct)  -- null 값을 제외한 합
FROM hr.employees;

 

- AVG : 평균

SELECT 
    avg(commission_pct),        -- null 제외한 평균
    avg(nvl(commission_pct, 0))  -- null을 0으로 변환하여 전체 평균
FROM hr.employees;

예) 10, 20, null
sun : 10 + 20
avg : (10 + 20) / 2
전체 avg : (10 + 20 + 0) / 3

 

- MEDIAN : 중앙값

SELECT avg(salary), median(salary)
FROM hr.employees;

예) 관측개수 : 홀수
20, 30, 10, 50, 60, 90, 70, 40, 80

1.크기순으로 오름차순 정렬
10, 20, 30, 40, 50, 60, 70, 80, 90

2.건수 계산
홀수 : (관측개수 + 1) / 2 = 5 = 5번째 값 = 중앙값의 위치

 

예) 관측개수 : 짝수
20, 30, 10, 50, 60, 90, 70, 40, 80, 100
1.크기순으로 오름차순 정렬
10, 20, 30, 40, 50, 60, 70, 80, 90, 100

2.건수 계산
짝수 : 관측개수 / 2 = 5
(관측개수 / 2) + 1= 6 = 5번,6번 위치값의 평균

 

- VARIANCE : 분산, 내가 가진 자료(데이터)가 평균값을 중심으로 퍼져있는 평균적인 거리

SELECT variance(salary)
FROM hr.employees;

1. 평균
2. 편차 제곱합의 평균

(관측값 - 관측값평균)² + (관측값 - 관측값평균)² + (관측값 - 관측값평균)²  
---------------------------------------------------------------  
                        관측값의 수 - 1 (자유도)  

 

- STDDEV : 표준편차, 분산에 제곱근을 수행한 값, 이유는 단위를 마춰기 위해서

SELECT stddev(salary)
FROM hr.employees;

 

- MAX : 최대값

SELECT max(salary), max(last_name), max(hire_date)
FROM hr.employees;

 

- MIN : 최소값

SELECT min(salary), min(last_name), min(hire_date)
FROM hr.employees;

- 범위 : max - min

SELECT max(salary) - min(salary)
FROM hr.employees;

 

---

 

■ GROUP BY 절 : 데이터의 행을 작은 그룹(군집)으로 나눌 수 있는 절
- 9i R1 버전까지는 sort group by 알고리즘으로 수행되어 group by 절에 명시된 컬럼을 기준으로 정렬된 결과로 출력된다.
- 9i R2 버젼부터 hash group by 알고리즘으로 수행하기 때문에 group by 절ㅇ에 명시된 컬럼을 기준으로 정렬이 수행되지 않습니다.

 

■ 그룹함수 사용 시 주의 사항
- NULL 을 포함하지 않습니다.(단, COUNT(*) 제외)
- SELECT 절에 그룹함수에 포함되지 않은 개별 컬럼은 하나도 빠짐 없이 GROUP BY 절에 명시해야 한다.
- GROUP BY 절에는 열별칭, 위치표기법 사용할 수 없다.
- GROUP BY 절에 명시된 컬럼을 기준으로 군집화 할때 NULL 포함한다.
- 그룹함수의 결과를 제한하기위해서 WHERE 절을 사용하면 오류 발생

SELECT department_id, sum(salary)    -> 오류 : 그룹함수 사용 시 나머지 개별칼럼을 그룹으로 묶어주기
FROM hr.employees;
↓
SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id;


SELECT department_id, job_id, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id;

SELECT department_id dept_id, job_id, sum(salary)
FROM hr.employees
GROUP BY dept_id, 2;            -> 오류 : GROUP BY 절에서는 열별칭, 위치표기법 사용불가

SELECT department_id, sum(salary)
FROM hr.employees
WHERE sum(salary) >= 20000        -> 오류 : WHERE 절은 행을 제한하는 절이지
GROUP BY department_id                그룹함수의 결과를 제한하지 않는다. -> HAVING 절 사용
ORDER BY 1;

 

---

 

■ HAVING 절 : 그룹함수의 결과를 제한하는 절

SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
HAVING sum(salary) >= 20000
ORDER BY 1;

SELECT department_id, sum(salary)
FROM hr.employees
WHERE last_name LIKE '%i%'
GROUP BY department_id
HAVING sum(salary) >= 20000
ORDER BY 1;

SELECT department_id, sum(salary)
FROM hr.employees
WHERE last_name LIKE '%i%'
GROUP BY department_id
HAVING count(*) >= 5
ORDER BY 1;

- 그룹함수는 2번만 중첩할 수 있다.
- 그룹함수를 2번 중첩했을 경우 개별 컬럼을 사용할 수 없다. 오류발생.
해결방법 : 서브쿼리를 이용해야한다.

SELECT max(avg(salary))
FROM hr.employees
GROUP BY department_id;

 

---

 

[문제] 2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력.

SELECT job_id, count(*) 인원수
FROM hr.employees
WHERE to_char(hire_date,'rrrr') = '2008'
GROUP BY job_id
ORDER BY 2 desc;

 

[문제] 년도별 입사한 인원수를 출력.

SELECT to_char(hire_date,'rrrr') 입사연도, count(*) 인원수
FROM hr.employees
GROUP BY to_char(hire_date,'rrrr');

 

[문제] 월별 입사한 인원수 출력.

SELECT to_number(to_char(hire_date,'mm')) 입사월, count(*) 인원수
FROM hr.employees
GROUP BY to_number(to_char(hire_date,'mm'))
ORDER by 1 ;

 

[문제] 년도별 입사 인원수를 아래와 같이 출력. ★
total   2001년   2002년   2003년
-----   -----    -----   ------
  107       1        7       6

SELECT
    count(*) total,
    count(decode(to_char(hire_date,'yyyy'), '2001', 'x')) "2001년",
    count(decode(to_char(hire_date,'yyyy'), '2002', 'x')) "2002년",
    count(decode(to_char(hire_date,'yyyy'), '2003', 'x')) "2003년"
FROM hr.employees;

또는

SELECT
    count(*) total,
    count(case when to_char(hire_date,'yyyy') = '2001' then 'x' end) "2001년",
    count(case when to_char(hire_date,'yyyy') = '2002' then 'x' end) "2002년",
    count(case when to_char(hire_date,'yyyy') = '2003' then 'x' end) "2003년"
FROM hr.employees;


☆ SQL에서 조건문은 decode, case 사용

SELECT count(decode(to_char(hire_date,'yyyy'), '2001', 'x')) "2001년"
        -- (2001 맞으면 x)의 카운트
FROM hr.employees;

SELECT sum(decode(to_char(hire_date,'yyyy'), '2001', 1)) "2001년"
        -- (2001 맞으면 1)의 합계
FROM hr.employees;