[데브코스][데이터 분석] 다양한 SQL 함수 다루기

< 3주차 SQL 쿼리 문법을 활용하여 데이터 분석 진행하기 (2) / (3) >

4. 다양한 SQL 함수 다루기

 

4-1. 타임스탬프 함수

: SQL에서 날짜와 시간 다루기

ex. 현재 시점에서 고객이 얼마전에 구매했는지 알 수 있음

-- STR_TO_DATE : 문자열을 날짜형식으로 바꾸는 함수
-- DATEDIFF : 날짜간 차이를 구하는 함수
SELECT user_name, DATEDIFF(NOW(), STR_TO_DATE(date, '%Y%m%d')) as dist
FROM orders

 

데이터 타입

- STRING : ‘yyyy-mm-dd’, ‘yyyy-mm-dd HH:MM:SS’

- DATE : yyyy-mm-dd

   (저장 공간이 적음)

- DATETIME : YYYY-MM-DD HH:MM:SS

- TIMESTAMP : YYYY-MM-DD HH:MM:SS UTC

   (DB 종류에 따라 표기 방식이 다름. 위치정보가 표시됨. DATE 타입과 유사. 저장할 수 있는 시간 범위가 제한적이기 때문에 DATE 타입    보다 저장소 용량이 작음. 시간과의 연산이 용이)

 

현재 시간

• NOW() : UTC 기준으로 현재 시간을 가져오는 함수(한국시간보다 9시간 느림)
  • CURRENT_TIMESTAMP()
  •  CURTIME() : 현재 시간 반환 (DB에 따라 다름)
  •  CURRENT_DATE() = CURDATE() : yyyy-mm-dd 형식으로 반환
• SYSDATE() : 함수가 호출된 시간을 반환
• YEAR() : 날짜에서 연도 추출 / MONTH() : 날짜에서 월 추출 / DAY() : 날짜 추출
• HOUR() / MINUTE() / SECOND()
• WEEKDAY() : 요일 반환
• MONTHNAME() / DAYNAME()

 

날짜 형식화

• STR_TO_DATE : 문자열 타입을 날짜 타입으로 변경
• DATE_FORMAT : 지정된 형식으로 날짜를 출력
  • %Y : 연도 (2023)
  • %y : 연도 (23)
  • %m : 월 (11)
  • %d : 일 (20)
  • %H : 시 (05), 24시간 형태
  • %T : hh:mm:ss
  • %s : 초

 

날짜 연산

• ADDDATE() : 특정 interval 만큼 시간을 더함
  • = DATE_ADD()
• SUBDATE() : 특정 interval 만큼 시간을 뺌
  • = DATE_SUB()
• CONVERT_TZ() : 타임존 변경하여 출력
• DATEDIFF() : 두 날짜 간의 차이를 반환
• TIMEDIFF() : 두 시간 간의 차이를 반환
• TIME_TO_SEC() : 시간을 초 단위로 반환

 

활용 예시

SELECT ADDDATE('2023-11-01', 3) as added
-- 결과: 2023-11-04

SELECT DATE_ADD('2023-11-01', INTERVAL 3 SECOND) as added
-- 20231101에서 3초만 더함
결과: 2023-11-01 00:00:03

SELECT DATE_ADD('2023-11-01', INTERVAL -1 DAY) as added
-- 20231101에서 1일을 뺌
결과: 2023-10-31

SELECT CONVERT_TZ(NOW(), '+00:00', '+09:00')
-- CONVERT_TZ(시간, 현재 타임존, 바꿀 타임존)
-- 현재 시간이 UTC 기준이니 현재 타임존은 +00:00이고, 한국시간은 +09:00을 해야함

SELECT DATEDIFF('2023-11-04', '2023-11-01') as diff
-- 결과 : 3

SELECT TIMEDIFF('2023-11-04 11:05:05', '2023-11-04 11:00:00') as diff
-- 결과 : 00:05:05

 

4-2. 타입 변환

ex. 정수타입으로 저장된 데이터를 문자열 타입으로 바꾸어 다른 테이블과 결합할 수 있음

- 데이터 타입 불일치로 인한 연산/비교 오류를 피하기 위함

- 다양한 데이터 소스 간의 호환성을 유지하기 위함

(같은 데이터를 서로 다른 조직에서 다룰 때 데이터 타입 불일치의 문제가 나올 수 있음)

 

CAST

SELECT CAST('20231014' AS SIGNED INTEGER) as int_date

CONVERT

SELECT CONVERT('20231101', SIGNED INTEGER) as int_date

 

=> cast, convert 함수는 사용방법만 다르고, 도출되는 결과는 유사함. 

 

4-3. 조건절

 

4-4. 그 외 유용한 함수