10. 내장함수

1. SQL 함수는 DBMS가 제공하는 내장 함수(built-in function)와 사용자가 직접 만드는 사용자 정의 함수로 나뉨

2. 숫자 함수
    1) ABS(숫자)    : 숫자의 절대값 계산
    2) CEIL(숫자)   : 숫자보다 크거나 같은 최소의 정수
        - ex) CEIL(4.1) = 5
    3) FLOOR(숫자)  : 숫자보다 작거나 같은 최소의 정수
        - ex) FLOOR(4.1) = 4
    4) ROUND(숫자, m) : m자리를 기준으로 숫자를 반올림
        - ex) ROUND(5.36, 1) = 5.4
    
3. 문자 함수
    1) REPLACE(s1, s2, s3)  : 대상 문자열을 지정한 문자로 변경 (문자열을 치환하는 함수)
    2) LENGTH(s)    : 대상 문자열의 글자 수를 반환 (글자의 수를 세어주는 함수)
    3) SUBSTR(s, n, k)  : 대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환 

    4) CONCAT(s1, s2) : 두 문자열을 연결
                        ex) CONCAT('이젠','IT') = '이젠IT'

4. 날짜/시간 함수
    1) to_date(char, datetime)  : 문자형(char) 데이터를 날짜형(DATE)으로 변환
    2) to_char(date, datetime)  : 날짜형(DATE) 데이터를 문자형(char)으로 변환
        - datetime의 주요 인자
            - d         요일(1~7)
              day       요일(월요일~일요일)
              dy        요일의 약자(월~일)
              dd        1달 날짜(1~32)
              ddd       1년 날짜(1~365)
              hh, hh12  12시간(1~12)
              hh24      24시간(0~23)
              mi        분(0~59)
              mm        월(01~12)
              mon       월이름 약어(Jan~Dec)
              month     월이름 (January~December)
              ss        초(0~59)
              yyyy      4자리 연도
              yyy,yy,y  4자리 연도의 3, 2, 1자리
    3) SYSDATE  : 오라클의 현재 날짜와 시간을 반환하는 함수

 

5. NULL 값을 확인하는 방법 : is null, is not null
    1) null 값을 찾을 때는 '=' 연산자가 아닌 'is null'을 사용함
    2) null이 아닌 값을 찾을 때는 '<>' 연산자가 아니고 'is not null'을 사용함

6. NVL : NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
    1) NVL(속성, 값) : 속성 값이 NULL이면 '값'으로 대치함

7. ROWNUM
    1) 내장 함수는 아니지만 자주 사용함
    2) 내부적으로 생성되는 가상 컬럼
    3) SQL 조회 결과의 순번을 나타냄
    4) 자료를 일부분만 확인하여 처리할 때 유용함

 

8. CAST 표현식
    1) 데이터 값을 특정 데이터 타입으로 형변환이 가능하도록 함
    2) 각종 데이터 값을 CAST 표현식을 이용해 적절하게 형변환 함

SELECT ABS(-78), ABS(+78) FROM DUAL; -- 78, 78 

SELECT CEIL(4.1) FROM DUAL; -- 5

SELECT FLOOR(4.1) FROM DUAL; -- 4 

SELECT ROUND(4.875, 1) FROM DUAL; -- 4.9

SELECT ROUND(5.51, 1) FROM DUAL; -- 5.5

SELECT ROUND(5.5) FROM DUAL; -- 6


-- 고객 중 주문 내역이 있는 고객의 고객 정보를 추출하시오
-- 고객번호, 평균 금액 
SELECT CUSTID "고객번호", ROUND(SUM(SELLPRICE)/COUNT(*), -2) "평균금액" 
FROM ORDERS o GROUP BY CUSTID;


-- 제목에 축구가 포함된 도서를 농구로 변경한 후 도서 목록을 추출하시오
SELECT BOOKID, REPLACE(BOOKNAME, '축구', '농구') BOOKNAME, PUBLISHER, price FROM BOOK;


-- '굿스포츠'에서 출판한 도서의 제목과 제목의 글자 수를 확인하시오
SELECT BOOKNAME "제목", LENGTH(BOOKNAME) "글자 수", LENGTHB(BOOKNAME) "바이트 수"
FROM BOOK WHERE PUBLISHER = '굿스포츠';


-- 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원 수를 구하시오
-- 성	인원

SELECT SUBSTR(NAME, 1, 1) "성", COUNT(*) "인원" FROM CUSTOMER c GROUP BY SUBSTR(NAME, 1, 1);


-- 2021년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 출력하시오
-- 단, 주문일은 'yyyy-mm-dd 요일' 형태로 표시하시오
SELECT ORDERID "주문번호", TO_CHAR(ORDERDATE, 'yyyy-mm-dd dy') "주문일", CUSTID "고객번호", BOOKID "도서번호"
FROM ORDERS o WHERE ORDERDATE = TO_DATE('20210707', 'yyyymmdd');

SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyy/mm/dd dy hh24:mi:ss') "SYSDATE_1" FROM DUAL;

SELECT SYSTIMESTAMP FROM DUAL;


-- 고객 테이블에서 이름, 전화번호가 포함된 고객목록을 출력하시오
-- 단, 전화번호가 없는 고객은 '연락처없음'으로 표시하시오.
SELECT NAME "이름", NVL(PHONE, '연락처없음') FROM CUSTOMER c;


-- 고객목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오
SELECT ROWNUM "순번", CUSTID, NAME, PHONE FROM CUSTOMER c WHERE ROWNUM <= 2;

-- 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;

-- 날짜 관련 함수 활용하기

--	a) 오늘을 나타내는 기본구문
SELECT SYSDATE FROM dual;

SELECT CURRENT_DATE FROM DUAL ;

SELECT CURRENT_TIMESTAMP FROM DUAL ;


--	b) 날짜 형식에서 문자 형식으로 변환하기
SELECT TO_CHAR(SYSDATE, 'yyyymmdd') FROM DUAL ; 

SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM DUAL ; 

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL ; 


-- c) 날짜 더하기/빼기
SELECT SYSDATE FROM DUAL ;

SELECT SYSDATE + (INTERVAL '1' MONTH) add_month FROM DUAL ;
SELECT SYSDATE + (INTERVAL '2' MONTH) add_month FROM DUAL ;

SELECT SYSDATE - (INTERVAL '1' MONTH) minus_month FROM DUAL ;
SELECT SYSDATE - (INTERVAL '2' MONTH) minus_month FROM DUAL ;

SELECT SYSDATE + (INTERVAL '1' day) add_day FROM DUAL ;
SELECT SYSDATE - (INTERVAL '1' day) minus_day FROM DUAL ;

SELECT SYSDATE + (INTERVAL '1' YEAR) add_year FROM DUAL ;
SELECT SYSDATE - (INTERVAL '1' YEAR) minus_year FROM DUAL ;


-- d) 날짜로부터 연도, 월, 주 확인하기
SELECT TO_CHAR(SYSDATE, 'month') FROM DUAL ;
SELECT TO_CHAR(SYSDATE, 'yyyy') FROM DUAL ;
SELECT TO_CHAR(SYSDATE, 'd') FROM dual;	-- 주 중 일로 표시 (일요일:1) 
SELECT TO_CHAR(SYSDATE, 'dd') FROM dual; -- 월 중 일로 표시
SELECT TO_CHAR(SYSDATE, 'ddd') FROM dual; -- 연 중 일로 표시

-- ex) 최근 1년 혹은 최근 1년 포함 언제까지의 매출액을 확인해 주시오
SELECT *
FROM GMV_TREND gt 
WHERE CAST('yyyy' AS varchar(4)) || CAST('mm' AS varchar(2)) >= 
		CAST(SYSDATE - (INTERVAL '1' YEAR) AS varchar(30));
		
SELECT *
FROM GMV_TREND gt 
WHERE CAST('yyyy' AS varchar(4)) || CAST('mm' AS varchar(2)) >= 
		CAST(SYSDATE - (INTERVAL '1' YEAR) AS varchar(30)) || 
		CAST(SYSDATE - (INTERVAL '1' MONTH) AS varchar(30))
ORDER BY 2 DESC, 3;