- StartUP SQL 교육 슬라이드를 책으로 작성 중인 페이지입니다.
- StartUP SQL이란? 데이터 분석가, 개발자, 기획자를 위한 SQL 입문 교육입니다.
- 전체 교육 슬라이드는 무료 오픈되어 있습니다: https://sweetquant.tistory.com/497
SELECT 기초 - 함수
RDBMS는 SQL 작성에 유용한 다양한 함수(FUNCTION)를 제공합니다.
함수는 값을 입력 받아 계산이나 처리를 한 후에 결과 값을 돌려줍니다.
아래 SQL은 ABS라는 절대값을 구하는 함수의 사용예입니다. SQL을 실행하면 -100의 절대값인 100이 출력됩니다.
SELECT ABS(-100) FUN_ABS FROM DUAL;
아래는 UPPER라는 함수입니다. 입력된 문자를 모두 대문자로 변환해 돌려주는 함수입니다.
SELECT UPPER('abcD') FUN_UPPER FROM DUAL;
Tip. DUAL
위 SQL에서는 FROM 절에 DUAL이라는 특별한 테이블을 사용했습니다.
DUAL은 데이터를 입력할 수는 없는 특별한 기능의 테이블입니다.
SELECT 시에 한 레코드로 이루어진 값을 출력하거나 돌려 받고 싶을때 주로 사용합니다.
오라클에서 주로 사용하는 테이블입니다. MySQL의 경우 FROM DUAL을 생략하고 SELECT 절만 사용해도 같은 기능이 가능합니다. 예를 들어 아래와 같이 SQL을 실행해도 FROM DUAL을 사용한 것과 같은 결과가 나옵니다.
SELECT UPPER('abcd') FUN_UPPER;
간단하게 ABS와 UPPER 함수를 통해 함수란 어떤 것인지 살펴봤습니다. 이처럼 함수는 값을 입력받아 값에 대한 처리나 연산을 수행한 결과를 출력해줍니다.
DBMS는 다양한 기본 함수를 제공하며, 필요하다면 사용자가 함수를 추가 개발할 수 도 있습니다.
DBMS가 제공하는 기본 함수의 사용법을 모두 알 필요는 없습니다. 다만 아래의 레퍼런스를 참고해 어떠한 함수들이 있는지 살펴볼 필요는 있습니다. 어떤 함수가 있는지 대략적으로 기억한 후에 필요할 때 사용법을 찾아보면 됩니다. 아래는 MySQL의 함수가 정리된 레퍼런스입니다.
DBMS 별로 비슷한 기능의 함수가 있습니다. 다만 DBMS 별로 함수 명이나 사용법은 다를 수 있습니다. 아래는 오라클의 함수가 정리된 레퍼런스입니다.
여기서는 자주 사용하게 되는 함 수 몇 개만 간단히 살펴보겠습니다.
아래는 REPLACE라는 함수입니다. 특정 문자열을 특정 값으로 치환(대체 변환)하기 위해 사용합니다. Item 테이블에서 ItemNm이 아이스인 경우 영어로 치환하고 있습니다.
SELECT T1.ItemId ,T1.ItemNm ,REPLACE(T1.ItemNm,'아이스','ICE') NewItemNm
FROM startdb.Item T1
WHERE T1.ItemCat = 'COF'
ORDER BY T1.ItemID;
ItemId ItemNm NewItemNm
------ ------------------- ----------------
AMB 아메리카노(B) 아메리카노(B)
AMR 아메리카노(R) 아메리카노(R)
CLB 카페라떼(B) 카페라떼(B)
CLR 카페라떼(R) 카페라떼(R)
EINR 아인슈페너(R) 아인슈페너(R)
FLTR 플랫화이트(R) 플랫화이트(R)
IAMB 아이스아메리카노(B) ICE아메리카노(B)
IAMR 아이스아메리카노(R) ICE아메리카노(R)
ICLB 아이스카페라떼(B) ICE카페라떼(B)
ICLR 아이스카페라떼(R) ICE카페라떼(R)
IEINR 아이스아인슈페너(R) ICE아인슈페너(R)
IFLTR 아이스플랫화이트(R) ICE플랫화이트(R)
SUBSTR은 문자열을 정해진 위치에서 정해진 길이만큼 잘라내는 함수입니다. 아래와 같이 사용해볼 수 있습니다.
-- 문자열을 정해진 길이만큼 잘라내는 함수,
-- SUBSTR(문자열, 시작위치, 자를길이)
SELECT T1.MobileNo
,SUBSTR(T1.MobileNo,6,4) 핸드폰번호뒷자리
FROM startdb.Member T1
WHERE T1.MemberId = 'M0015';
CONCAT은 여러 문자열을 결합하기 위해 사용하는 함수입니다. MySQL의 CONCAT 함수 안에 결합할 문자열을 차례대로 콤마(,)로 구분해 입력해주면 됩니다. 오라클은 CONCAT 대신에 문자열 사이에 ||(파이프 두개 연속)를 사용해 문자열을 결합합니다. (오라클도 CONCAT을 제공하지만, 오라클의 CONCAT은 두 개의 문자열만 결합할 수 있습니다.)
-- 문자열을 결합하는 함수, CONCAT(문자열1, 문자열2, 문자열3, ...)
SELECT T1.ItemId ,T1.ItemNm
,CONCAT(T1.HotColdCd,'-',T1.ItemSizeCd,'-',T1.ItemId) NewItemId
FROM startdb.Item T1
WHERE T1.ItemCat = 'COF'
ORDER BY T1.ItemID;
ItemId ItemNm NewItemId
------ ------------------- --------------
AMB 아메리카노(B) HOT-BIG-AMB
AMR 아메리카노(R) HOT-REG-AMR
CLB 카페라떼(B) HOT-BIG-CLB
CLR 카페라떼(R) HOT-REG-CLR
EINR 아인슈페너(R) HOT-REG-EINR
FLTR 플랫화이트(R) HOT-REG-FLTR
IAMB 아이스아메리카노(B) COLD-BIG-IAMB
IAMR 아이스아메리카노(R) COLD-REG-IAMR
ICLB 아이스카페라떼(B) COLD-BIG-ICLB
ICLR 아이스카페라떼(R) COLD-REG-ICLR
IEINR 아이스아인슈페너(R) COLD-REG-IEINR
IFLTR 아이스플랫화이트(R) COLD-REG-IFLTR
SQL을 작성할 함수는 중첩해서 사용할 수 있습니다. 함수의 결과를 함수의 입력값으로 다시 사용할 수가 있는 것이죠. 아래는 SUBSTR 처리 결과를 CONCAT의 입력 값으로 사용한 예입니다.
SELECT T1.MobileNo
,CONCAT('010-****-',SUBSTR(T1.MobileNo,6,4)) MaskMobile
FROM startdb.Member T1
WHERE T1.MemberId = 'M0015';
MobileNo MaskMobile
--------- -------------
0015-0015 010-****-0015
아래는 ROUND, CEIL, FLOOR라는 소수점을 처리하는 함수입니다.
CEIL이나 FLOOR는 일반적으로 잘 사용하지 않습니다. 하지만 데이터를 집계해서 분석하다 보면,
데이터를 특정 그룹으로 나누어야 하는데 이를 위해 CEIL이나 FLOOR를 적절하게 사용할 수 있습니다.
(기회가 되면 나중에 설명하도록 하게습니다. 물론, 이마저도 NTILE라는 분석함수로 더 쉽게 대체할 수 있습니다.)
SELECT ROUND(1.2,0) COL_ROUND
,CEIL(1.2) COL_CEIL
,FLOOR(1.2) COL_FLOOR
FROM DUAL;
COL_ROUND COL_CEIL COL_FLOOR
----------- ---------- -----------
1 2 1
SELECT ROUND(1.9,0) COL_ROUND
,CEIL(1.9) COL_CEIL
,FLOOR(1.9) COL_FLOOR
FROM DUAL;
COL_ROUND COL_CEIL COL_FLOOR
----------- ---------- -----------
2 2 1
마지막으로 STR_TO_DATE라는 문자를 날짜로 변환하는 함수의 사용법만 간단히 살펴보겠습니다.
(자세한 설명은 뒤에서 날짜 관련 함수와 같이 할 예정입니다.)
회원(Member) 테이블의 가입일시(JoinDtm) 컬럼은 DATETIME 자료형입니다.
DATETIME은 년월일과 같은 날짜값과 시분초와 같은 시간까지 저장되는 자료형입니다.
이와 같은 날짜 자료형 컬럼에 조건 값을 줄때는 STR_TO_DATE(String To Date) 함수를 사용해 문자 값을 날짜 형태로 변환해 처리해야 합니다.
STR_TO_DATE의 기본 사용법은 아래와 같습니다.
- STR_TO_DATE('20190108','%Y%m%d'): 20190108이라는 문자 값을 2019년 1월 8일이라는 날짜 형태로 변환
- 이때, 패턴문자인 %Y%m%d 에서 Y는 대문자로 m과 d는 소문자로 처리해야 합니다.
STR_TO_DATE를 사용해 회원(Member) 테이블에서 2019년 4월 1일 이전에 가입한 회원을 조회해봅니다. 아래와 같습니다.
SELECT T1.MemberId ,T1.NickNm ,T1.JoinDtm
FROM startdb.Member T1
WHERE T1.JoinDtm < STR_TO_DATE('20190401','%Y%m%d')
ORDER BY T1.MemberId ASC;
위 SQL은 STR_TO_DATE를 하지 않아도 정상 작동합니다.(각자 환경에 따라 작동이 안될 수도 있습니다.)
그런데 왜 STR_TO_DATE를 사용해야 하는지는 조금 복잡하면서 중요한 이야기입니다. 이와 관련해서는 뒤에서 별도로 설명드리도록 하겠습니다. 우선은 "날짜 컬럼에 조건 줄때는 STR_TO_DATE로 조건 값을 변환해야 하더라"라고 외워놓기 바랍니다.
함수에 대해 이해했다면 다음 문제를 풀어보기 바랍니다.
[StartUP SQL]
- 데이터 분석가, 개발자, 기획자를 위한 SQL 입문 교육
- 교육용 슬라이드 전체 무료 오픈: https://sweetquant.tistory.com/497
'SQL > StartUP SQL(Book-집필중)' 카테고리의 다른 글
[StartUP SQL BOOK]데이터 이해하기 - Ord 테이블 이해하기 (0) | 2024.05.18 |
---|---|
[StartUP SQL BOOK]데이터 이해하기 - Item 테이블 이해하기 (0) | 2024.05.17 |
[StartUP SQL BOOK]데이터 이해하기 - 식별자와 PRIMARY KEY (0) | 2024.05.16 |
[StartUP SQL BOOK]SELECT 기초 - 별칭 (0) | 2024.05.15 |
[StartUP SQL BOOK]SELECT 기초 - ORDER BY (0) | 2024.05.15 |
[StartUP SQL BOOK]SELECT 기초 - 특수 조건자 (0) | 2024.05.15 |
[StartUP SQL BOOK]SELECT 기초 - 문자의 크기 비교 (0) | 2024.05.14 |
[StartUP SQL BOOK]SELECT 기초 - AND와 OR (0) | 2024.05.13 |