[프로그래머스] 4. IS NULL

NULL값에 대한 AVG 처리

AVG() : 평균을 구하는 함수, 모든 집계함수는 NULL값을 제외하므로, 잘못된 평균 결과가 반환된다

sum(~) / count(*) : count(*)는 NULL 값을 포함한 개수를 반환하므로, 올바른 평균 결과가 반환된다.

 

따라서 AVG함수를 사용할 때에는, NVL을 이용해 NULL을 0으로 치환하는 작업이 우선되어야 한다.

 AVG(NVL(comm , 0))
 
 SUM(comm) / COUNT(*)

---

NULL인 결과도 추가하기

WHERE NVL(job, 'NONE') != 'MANAGER

LNNVL(job = 'MANAGER')

WHERE (job != 'MANAGER' OR job IS NULL)

---

NULL 처리

① job IS NULL (NULL을 조건으로 사용할 때는 IS NULL을 사용한다)
② job IS NOT NULL (NULL을 조건으로 사용할 때는 IS NOT NULL을 사용한다)
③ job IS NULL (빈 문자열은 NULL을 사용한다)
④ sal + NVL(NULL, 0) (수치값 또는 컬럼은 NULL이 존재할 경우 NVL로 치환한다)

---

NULLIF

-> 2개의 값이 같으면 NULL 다르면 첫 번쨰 값을 반환

NULLIF(칼럼명, '확인값') -> 해당 칼럼에서의 값이 확인값과 같으면 NULL, 다르면 그대로 값을 출력

---

NULL 값을 치환할 때는 NVL, NVL2, COALESCE 함수

1. NVL          : NVL("값", "지정값") 

: 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다.

2. NVL2         : NVL2("값", "지정값1", "지정값2") // NVL2("값", "NOT NULL", "NULL") 

NVL2 함수는 NULL이 아닌 경우 지정값1을  출력하고, NULL인 경우 지정값2를 출력한다.

3. COALESCE   : 여러 개의 칼럼의 NULL 값을 판별해야 할 경우 NULL이 아닌 첫 번째 칼럼의 값을 반환

COALESCE('A', 'B', NULL)      			--'A'
COALESCE(NULL, 'B', 'C', 'D') 			--'B'
COALESCE(NULL, NULL, NULL, NULL, 'E')           --'E'
COALESCE(NULL, NULL, NULL, NULL) 		--'NULL

NVL함수를 중첩으로 사용할 때 대체 가능

NVL(tel_no1, NVL(tel_no2, tel_no3))

COALESCE(tel_no1, tel_no2, tel_no3)

 

->DECODE 함수로 대체 가능

DECODE         : DECODE("값", NULL, "지정값1", "지정값2")

DECODE (COMM, NULL, 0, COMM)
DECODE (COMM, NULL, 'N', 'Y')

 

DECODE

DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3..........) 

 

DECODE (GENDER, 'M', '남자', 'F', '여자', '기타')

--1. if (GENDER =='M') return '남자'
--2. else if(GENDER=='F') return '여자'
--3. else return '기타'

DECODE (GENDER, 'M', '남자', 'F', '여자')

--1. if (GENDER =='M') return '남자'
--2. else if(GENDER=='F') return '여자'
--3. else return NULL

 

DECODE 활용 1. DECODE 내부에 또다른 DECODE 함수

-> 직업 별로 구분할 때

DECODE (JOB, 'CLERK', DECODE(MGR, '7788', 'SCOTT'
                                  '7698', 'BLAKE'
                                  '7782', 'CLARK')
       , 'ANALYST', DECODE(MGR, '7756', 'JONES'
                                '7698', 'BLAKE')

DECODE 활용 2. 집계함수를 이용한 DECODE 함수

-> 월별, 일별 통계를 산출하거나, 행을 열로 바꿀 때

SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '01', 1)) "1월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '02', 1)) "2월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '03', 1)) "3월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '04', 1)) "4월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '05', 1)) "5월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '06', 1)) "6월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '07', 1)) "7월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '08', 1)) "8월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '09', 1)) "9월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '10', 1)) "10월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '11', 1)) "11월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM',), '12', 1)) "12월"

 

DECODE를 대체할 수 있는 CASE 함수

-> DECODE이 등가연산만 가능하지만 CASE는 많은 비교연산을 할 수 있다.

-> NULL끼리 비교시 DECODE는 참, CASE는 거짓을 리턴

CASE WHEN deptno = '10' THEN 'New York'
	 WHEN deptno = '20' THEN 'Dallas'
     ELSE 'Unknown'
END

--1. if deptno =10이면 return New York
--2. else if deptno = 20이면 return Dallas
--3. else return Unknown


CASE WHEN deptno = '10' THEN 'New York'
	 WHEN deptno = '20' THEN 'Dallas'
END

--1. if deptno =10이면 return New York
--2. else if deptno = 20이면 return Dallas
--3. else return NULL

 

CASE의 활용

1. 비교연산자, 범위연산자 사용

CASE WHEN sal >= 2900 THEN '1등급'
	 WHEN sal >= 2700 THEN '2등급'
	 WHEN sal >= 2000 THEN '3등급'
END AS sal_grade

 

2. WHERE절에서 사용

SELECT CASE WHEN sal >= 2900 THEN '1등급'
	    WHEN sal >= 2700 THEN '2등급'
	    WHEN sal >= 2000 THEN '3등급'
	    END AS sal_grade
FROM emp a
WHERE job ='MANAGER'
AND (CASE WHEN sal >=2900 THEN 1
          WHEN sal >=2700 THEN 2
          WHEN sal >=2000 THEN 3
          END) =1

 

3. 오라클 내장 함수를 조건으로 사용

SELECT CASE WHEN TO_CHAR(hiredate, 'q') ='1' THEN '1분기'
            WHEN TO_CHAR(hiredate, 'q') ='2' THEN '2분기'
            WHEN TO_CHAR(hiredate, 'q') ='3' THEN '3분기'
            WHEN TO_CHAR(hiredate, 'q') ='4' THEN '4분기'
            END AS hire_quarter
FROM emp a
WHERE job = 'MANAGER'

 

4. THEN 절에서 중첩 case문 사용

CASE WHEN deptno = '10' THEN
        WHEN sal >= 2000 THEN '1등급'
	    WHEN sal >= 1500 THEN '2등급'
	    WHEN sal >= 1000 THEN '3등급'
END
WHEN deptno = '20' THEN
        WHEN sal >= 3000 THEN '1등급'
	    WHEN sal >= 2500 THEN '2등급'
	    WHEN sal >= 2000 THEN '3등급'
END
WHEN deptno = '30' THEN
        WHEN sal >= 2500 THEN '1등급'
	    WHEN sal >= 2000 THEN '2등급'
	    WHEN sal >= 1500 THEN '3등급'
END

---

1. 이름이 없는 동물의 아이디

-- 코드를 입력하세요
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

 

2. 이름이 있는 동물의 아이디

-- 코드를 입력하세요
SELECT ANIMAL_ID ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ID

 

3. NULL 처리하기

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, NVL(NAME, 'No name') NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID