13. CASE

1. CASE 표현식
    1) 조건 연산자
    2) IF/ELSE문과 같은 로직을 구사할 수 있음
    3) 조건문 처리를 할 수 있음
    4) 문법
        SELECT                                      <설명<
                CASE WHEN 조건식1 THEN 결과1        WHEN은 IF와 유사
                     WHEN 조건식2 THEN 결과2        WHEN은 ELSE IF와 유사
                     ELSE 결과3                     ELSE는 ELSE와 유사
                END

 

-- 1. 원하는 형식으로 컬럼 가공
-- 	a) 숫자를 문자열로 바꾸기 (컬럼 간 서로 데이터 타입을 맞춰줘야 하는 경우)
SELECT DT 
FROM ONLINE_ORDER oo ;

SELECT DT, CAST (DT AS varchar2(8)) AS yyyymmdd
FROM ONLINE_ORDER oo ;


--	b) 문자열 컬럼에서 일부만 잘라내기
SELECT DT, SUBSTR(CAST (DT AS varchar2(8)), 1, 4) AS yyyy
FROM ONLINE_ORDER oo ;

SELECT DT, SUBSTR(CAST (DT AS varchar2(8)), 1, 4) AS yyyy,
		SUBSTR(CAST (DT AS varchar2(8)), 5, 2) AS mm
FROM ONLINE_ORDER oo ;

SELECT DT, SUBSTR(CAST (DT AS varchar2(8)), 1, 4) AS yyyy,
		SUBSTR(CAST (DT AS varchar2(8)), 5, 2) AS mm,
		SUBSTR(CAST (DT AS varchar2(8)), 7, 2) AS dd 
FROM ONLINE_ORDER oo ;


-- 	c) yyyy-m-dd 형식으로 이어주기
SELECT DT, 
		CONCAT(SUBSTR(CAST (DT AS varchar2(8)), 1, 4),SUBSTR(CAST (DT AS varchar2(8)), 5, 2)) || 
		SUBSTR(CAST (DT AS varchar2(8)), 7, 2) AS yyyymmdd
FROM ONLINE_ORDER oo ;

-- 2021-06-01
SELECT DT, 
		CONCAT(SUBSTR(CAST (DT AS varchar2(8)), 1, 4), '-') || 
		SUBSTR(CAST (DT AS varchar2(8)), 5, 2) || '-' || 
		SUBSTR(CAST (DT AS varchar2(8)), 7, 2) AS yyyymmdd
FROM ONLINE_ORDER oo ;

SELECT DT, 
		SUBSTR(CAST (DT AS varchar2(8)), 1, 4) || '-' || 
		SUBSTR(CAST (DT AS varchar2(8)), 5, 2) || '-' || 
		SUBSTR(CAST (DT AS varchar2(8)), 7, 2) AS yyyymmdd
FROM ONLINE_ORDER oo ;


-- d) null 값인 경우 임의의 값으로 바꿔주기
SELECT oo.USERID 
FROM "ONLINE_ORDER" oo 
LEFT JOIN "USER_INFO" ui ON oo.USERID = ui.USERID ;
-- WHERE ui.USERID IS NULL;

SELECT oo.USERID , NVL(oo.USERID, 0)
FROM "ONLINE_ORDER" oo 
LEFT JOIN "USER_INFO" ui ON oo.USERID = ui.USERID ;
-- WHERE ui.USERID IS null;


--	d-2) null 값인 경우 임의의 값으로 바꿔주기
SELECT ui.GENDER 
FROM ONLINE_ORDER oo 
LEFT JOIN USER_INFO ui ON oo.USERID = ui.USERID 
WHERE ui.GENDER IS NULL ;

SELECT ui.GENDER , NVL(ui.GENDER, 'NA') AS gender 
FROM ONLINE_ORDER oo 
LEFT JOIN USER_INFO ui ON oo.USERID = ui.USERID 
WHERE ui.GENDER IS NULL ;

SELECT NVL(ui.GENDER, 'NA') AS gender
		, NVL(ui.AGE_BAND, 'NA') AS age_band
		, SUM(oo.GMV) AS GMV 
FROM ONLINE_ORDER oo 
LEFT JOIN USER_INFO ui ON oo.USERID = ui.USERID 
GROUP BY gender, age_band
ORDER BY 1, 2;