SQL에서 시간 데이터 다루는 법

Photo by Lukas Blazek on Unsplash

시계열 데이터는 무엇인가요?

SQL을 다루다 보면 날짜와 시간과 관련된 데이터를 다루게 되는 것은 일상적인 일이 됩니다. 시간과 관련된 데이터들을 활용해 시간에 따른 변화를 계산할 수 있고, 데이터 속 트렌드를 파악할 수 있고, 시간적인 간격 또한 계산할 수 있습니다. 이는 모두 비즈니스 문제 속에 내재된 시사점을 파악하기 위함입니다.

"시계열 데이터"는 시간의 흐름에 따라 똑같은 것을 측정하여 시간 순으로 저장된 데이터를  일컫는 말입니다.

시계열 데이터에는 주식 가격, 일별 온도와 같이 똑같은 지표를 매일 측정해 시간 순으로 저장하는 데이터도 있고, 자전거 대여 및 반납처럼 특정한 행위를 함으로써 시간과 관련된 데이터를 남기는 시계열 데이터의 종류도 있습니다.

 

시작해 볼까요?

이번 글에서는 여러분들이 기본적인 SQL 지식을 갖췄다는 가정하에 설명하도록 하겠습니다. 우리는 시계열 데이터를 다룰 때 자주 쓰이는 3가지 함수를 알아볼 것입니다. 이 글은 PostsgreSQL 데이터 베이스 기준으로 작성되었습니다.

1. CAST 함수
2. EXTRACT 함수
3. DATE_TRUNCT 함수

위 함수들은 다량의 데이터를 데이터 세트로 쪼개어 볼 때 상당히 유용하게 쓰입니다. 밑에서 예시와 함께 각 함수의 이점을 살펴보도록 하죠.

 

CAST 함수

CAST 함수는 데이터의 종류를 다른 데이터의 종류로 바꿀 때 사용합니다. 꽤나 명확한 기능이죠? CAST 함수를 이용해 특정 데이터의 종류를 여러분이 희망하는 데이터 종류로 변환할 수 있습니다. CAST 함수의 문법은 아래와 같습니다.

CAST(expresssion AS datatype)
[번역] CAST(기존 데이터 AS 바꾸고 싶은 데이터의 종류)

아래는 CAST 함수가 날짜 및 시간 데이터에 어떤 식으로 사용되는지 보여줍니다.

SELECT
 NOW(),
 CAST(NOW() AS timestamp),
 CAST(NOW() AS date),
 CAST(NOW() AS time),
 CURRENT_DATE,
 CURRENT_TIME

위 쿼리를 실행하면 우리는 총 6개의 열을 결과물로 받아 볼 수 있습니다. NOW(), CURRENT_DATE, CURRENT_TIME과 친숙하지 않은 분들을 위해 설명드리자면, 이 함수들은 현재 시간과 날짜를 반환하는 함수들입니다. 위 쿼리문을 실행하면 순서대로 아래와 같은 값을 얻어 볼 수 있습니다. (참고: 아래 적힌 시간들은 이 글이 작성될 시점 기준으로 반환해 낸 값이기 때문에 여러분이 현재 쿼리를 실행해서 얻어낸 시간과 다를 수밖에 없습니다.

2020-03-28 23:18:20.261879+00:00
2020-03-28 23:18:20.261879
2020-03-28
23:18:20.261879
2020-03-28
23:18:20.261879+00:00

첫 번째 NOW 함수를 통해 얻어낸 값을 살펴보면 타임존을 포함한 전체 타임스탬프의 값이 반환된 것을 확인할 수 있습니다. 그다음, CAST 함수를 통해 오직 타임스탬프만 얻어내고자 한 2번째 값을 살펴보면 NOW 함수를 통해 얻은 데이터 중 타임존(+00:00)을 제외한 부분만 반환된 것을 확인할 수 있습니다. CAST 함수가 어떤 식으로 작동하는지 아시겠나요? CAST 함수에게 우리가 변환하고 싶은 값을 던져준 후 어떤 식으로 바꿔달라고 말하기만 하면 됩니다.

쿼리문에서 4번째 문장을 보도록 하겠습니다. NOW 함수로 얻어낸 값을 CAST 함수를 통해 'date'라는 데이터 종류로 바꾸고 싶습니다. 이를 통해 반환된 값을 살펴보면 타임스탬프가 연도-월-일 형식으로 구성된 것을 확인할 수 있습니다. 쿼리문에서 5번째 문장은 CAST 함수를 통해 NOW의 값을 'time'만 볼 수 있도록 바꿔달라고 하네요. 그래서 해당 결과값을 살펴보면 날짜는 어디에도 없고 시간 데이터만 남아있는 것을 확인할 수 있습니다.

방금 살펴본 것처럼 CAST 함수는 날짜 및 시간 데이터를 내가 원하는 데이터의 종류로 바꿀 수 있습니다. 위에서 살펴본 값 모두 시간과 관련된 데이터지만 NOW 함수로 얻은 타임스탬프 데이터를 한 번은 연/월/일로만 구성된 날짜 데이터로, 다른 한 번은 시간과 타임존으로만 구성된 시간 데이터로 '바꾸었다'라는 점을 잊지 마세요!

 

CAST 함수는 시간 및 날짜 데이터를 다룰 때만 사용하나요?

아니요. 일반적인 숫자 데이터를 다룰 때도 사용할 수 있습니다.

SELECT
 CAST(1.34 AS INT),
 CAST(1 AS BOOLEAN),

위 쿼리의 결과는 다음과 같습니다.

1 → INT(정수)는 소수점을 가질 수 없기 때문에, 가장 가까운 정수 값으로 반올림했습니다
true → BOOLEAN(참과 거짓을 숫자 1과 0만을 이용해 나타내는 방식)에서 1은 참을 나타내므로 true라는 값이 반환되었습니다.

 

INTERVAL

SQL에선 INTERVAL이란 표현을 사용해 기존 시간 데이터에다가 내가 원하는 만큼 시간을 더해주거나 빼줄 수 있습니다. INTERVAL을 사용할 땐 CAST가 필수는 아닙니다. 그저 예시의 숫자를 깔끔하게 만들고 싶어서 타임스탬프를 날짜 데이터를 바꾸고자 사용한 것이니 겁먹지 마세요.

SELECT
 CAST(NOW() AS date) AS today_date,
 CAST((NOW() + INTERVAL '3 DAYS') AS date) AS three_days,
 CAST((NOW() + INTERVAL '3 WEEKS') AS date) AS three_weeks,
 CAST((NOW() + INTERVAL '3 MONTHS') AS date) AS three_months,
 CAST((NOW() + INTERVAL '3 YEARS') AS date) AS three_years

today_date	three_days	three_weeks	three_months	three_years
2020-03-28	2020-03-31	2020-04-18	2020-06-28	2023-03-28

INTERVAL를 적고 그 옆에 시간의 길이(i.e. 3일, 3주, 3개월, 3년 등)를 함께 적으면 내가 기존에 갖고 있던 시간 데이터에 시간을 맘껏 편하게 더해줄 수 있습니다. 이 예시에서 우리가 기존에 갖고 있던 시간 데이터는 NOW 함수를 통해 얻은 '2020-03-28'이라는 데이터였고, 거기에 3일/3주/3개월/3년을 더해줘서 새로운 날짜 데이터를 만들어 내었습니다.

내가 까먹지 않으려고 적은 MySQL에서의 Interval 이야기

사칙 연산자를 활용하는 PostgreSQL과는 달리 MySQL 내 Interval은 DATE_ADD 또는 DATE_SUB 함수와 함께 쓰인다.

- 문법 : DATE_ADD(date, INTERVAL value)
- 예시 : DATE_ADD(date, INTERVAL 15 MINUTE) / DATE_ADD(date, INTERVAL -3 HOUR)
- value에 들어갈 수 있는 시간의 종류 : (링크에서 확인하기)

 

EXTRACT  함수

다음으로, 우리는 시간 데이터 중 특정 부분만 추출하는 방법을 배워볼 것입니다. EXTRACT 함수의 목적은 타임스탬프 중 일정 부분만으로 추출해내는 것인데요. 예를 들어서, 우리가 2018-12-10이라는 날짜 데이터에서 '월'만 추출해내고 싶다고 가정해봅시다. EXTRACT 함수를 이용해서 우리는 월을 나타내는 12만 추출해 낼 겁니다.

EXTRACT 함수의 문법을 살펴보시죠.

EXTRACT(part FROM date)
[번역] EXTRACT (추출하고 싶은 데이터 종류 FROM 기존 데이터)

우리가 추출하고 싶은 시간 데이터의 기준은 part 부분에 적고 기존 데이터를 date 부분에 기재합니다. EXTRACT 함수는 시계열 데이터 분석에 있어서 상당히 중요한 함수입니다. 이 함수를 통해서 시계열 데이터를 특정 시간을 기준으로 집계할 때 상당히 유용합니다. 예를 들어, 자동차 대여 가게가 매해 5월의 매주 월요일 중 가장 바쁜 시간대를 알아보고 싶다고 할 때도 EXTRACT 함수를 쓸 수 있습니다. 세부 정보를 파헤쳐 보다 가치 있는 인사이트를 얻어낼 수 있습니다.

NOW 함수를 이용해 얻은 2020-03-29 00:27:51.677318+00:00란 타임스탬프를 가지고 EXTRACT 함수를 통해 여러 종류의 날짜 데이터를 추출해 보려고 합니다.

SELECT
 EXTRACT(MINUTE FROM NOW()) AS mintue,
 EXTRACT(HOUR FROM NOW()) AS hour,
 EXTRACT(DAY FROM NOW()) AS day,
 EXTRACT(WEEK FROM NOW()) AS day,
 EXTRACT(MONTH FROM NOW()) AS month,
 EXTRACT(YEAR FROM NOW()) AS year,
 EXTRACT(DOW FROM NOW()) AS day_of_week,
 EXTRACT(DOY FROM NOW()) AS day_of_year,
 EXTRACT(QUARTER FROM NOW()) AS quarter,
 EXTRACT(TIMEZONE FROM NOW()) AS timezone

minute	hour	day	week	month	year	day_of_week	day_of_year	quarter	timezone
27	0	29	13	3	2020	0	89	1	0

EXTRACT 함수를 활용해 어떤 값을 추출하는지에 따라 상당히 다양한 세부 정보를 얻어낼 수 있다는 점을 살펴볼 수 있는데요. 기존 데이터인 타임스탬프(2020-03-29 00:27:51.677318+00:00)만을 가지고도 분, 시간, 주, 요일, 분기 등 다양한 정보를 알 수 있습니다. 참고로 day_of_week은 일요일(0)부터 토요일(6)까지를 숫자로 표현한 것입니다.

아까 한 자동차 대여 가게의 매해 5월의 매주 월요일 중 가장 바쁜 시간대를 알아보고 싶다고 했었죠? 어떤 식으로 구할 수 있는지 한 번 살펴보도록 하겠습니다.

SELECT
 EXTRACT(HOUR FROM rental_date) AS hour,
 COUNT(*) AS rentals
FROM rental
WHERE
 EXTRACT(DOW FROM rental_date) = 1 AND EXTRACT(MONTH FROM rental_date) = 5
GROUP BY 1
ORDER BY 2 DESC;

우리가 알고 싶은 것은 가장 바쁜 '시간대'입니다. 따라서 SELECT문을 보면 EXTRACT 함수를 통해 각 대여 날짜에서 hour 데이터만 가져오고, 그 시간대에 발생한 대여의 개수를 셉니다. 그러고 난 후, 매년 5월의 매주 월요일이라는 조건을 달기 위해서 WHERE절에다가 월요일 나타내는 DOW = 1과 5월을 나타내는 MONTH = 5 라고 적어주었습니다. 위 쿼리를 실행하면 아래와 같은 테이블이 나옵니다. 결과를 확인해보니 오전 11시가 매해 5월의 매주 월요일 중 가장 바쁜 시간대인 것을 확인할 수 있네요. 

hour	rentals
11	11
10	10
1	10
15	10
12	9

 

DATE_TRUNC 함수

truncate [동사] : 길이를 줄이다 혹은 짧게 하다

DATE_TRUNC 함수를 통해 타임스탬프 데이터를 여러분이 희망하는 시간 기준으로 반올림할 수 있습니다. SQL에서 타임스탬프를 자르는 이유는 지저분한 타임스탬프를 하나의 절대적인 시간 기준으로 맞추기 위함입니다. 그 시간 기준이라 함은 EXTRACT 함수에서 사용했던 시간 데이터 기준과 같은 것인데요. 분, 시간, 일, 주, 월, 연도 등의 기준을 말하는 것입니다. 다만 EXTRACT 함수를 사용했을 때는 내가 요청한 시간 기준의 숫자를 반환해주지만 (예를 들어, EXTRACT(year FROM '2021-01-09 16:09:23' 이면 2021이라는 숫자를 반환함) DATE_TRUNC 함수는 내가 요청한 시간 기준의 타임스탬프를 반환할 것입니다. (예를 들어, DATE_TRUNC('DAY' FROM '2021-01-09 16:09:23') 이면 2021-01-09 00:00:00이라는 타임스탬프를 반환함)

DATE_TRUNC 함수의 문법을 살펴볼까요? time_column은 여러분이 반올림하길 원하는 시간 데이터를 포함한 열의 이름을 적어주고, interval 은 해당 시간 데이터를 어떤 시간 기준까지 반올림하길 원하는지 명시해줍니다. 

DATE_TRUNC('[interval]', time_column)
[번역] DATE_TRUNC('[시간 기준]', 시간 데이터가 있는 열의 이름)

예시를 통해 더 자세히 알아보도록 합시다. NOW 함수를 통해 2020-03-29 00:27:51.677318+00:00란 타임스탬프가 생겼고, DATE_TRUNC 함수를 통해 지저분한 타임스탬프를 그 타임스탬프가 어떤 날짜에 속해 있는지, 어떤 주에 속해 있는지, 어떤 월에 속해 있는지, 어떤 연도에 속해 있는지 살펴볼 것입니다.

SELECT
 CAST(DATE_TRUNC('DAY', NOW()) AS date) AS day,
 CAST(DATE_TRUNC('WEEK', NOW()) AS week) AS week,
 CAST(DATE_TRUNC('MONTH', NOW()) AS month) AS month,
 CAST(DATE_TRUNC('YEAR', NOW()) AS year) AS year

day	week	month	year
2020-03-29	2020-03-23	2020-03-01	2020-01-01

DATE_TRUNC 함수를 사용해 해당 타임스탬프가 어떤 시간 간격에 위치하고 있는지를 얻어낸다고 생각하시면 됩니다. 그리고 그 시간 간격이 길고 지저분한 타임스탬프를 간략하게 보일 수 있도록 잘라내는 기준이 되고요. 참고로, 위 쿼리문에서 굳이 CAST 함수를 사용할 필요는 없었지만 분석을 위해 깔끔한 날짜 형식을 갖추고자 사용했습니다.

아까 자동차 대여 예시를 다시 가져와서 사용해볼게요. 시간대는 상관없이 어떤 날짜에 가장 많은 대여가 발생했는지 살펴보도록 하겠습니다. 

SELECT
 CAST(DATE_TRUNC('DAY', rental_date) AS date) AS rental_day,
 COUNT(*) AS rentals
FROM rental
GROUP BY 1
ORDER BY 2;

rental_day	rentals
2019-03-28	679
2019-06-18	650
2019-01-23	632
2019-11-03	584
2019-08-13	523

cf. DATE_TRUNC에 대해 더 자세히 알고 싶다면 DATE_TRUNC : 기댈 수 있는 SQL 타임스탬프 함수를 읽어보세요.

 

글을 마무리하며

SQL 내에서 타임스탬프를 다룰 때 이전보다는 편해지셨길 바랍니다. 지금까지 CAST 함수, EXTRACT 함수, INTERVAL, DATE_TRUNC 함수에 대해 알아보았습니다. 부디 이 글이 시간 흐름에 따른 트렌드 분석을 하거나 이전에 찾지 못했던 인사이트를 찾는데 도움이 되었길 바랍니다. 

 

원문: Date/Time Functions in SQL