[SQL로 시작하는 데이터 분석] (3) - 시계열 분석 2

이전 포스팅에서는 시계열 분석의 기초 개념과 함께, 날짜·시간 데이터의 처리와 시간대 변환, 주요 SQL 함수들을 중심으로 정리해보았다.

 

이번 글에서는 본격적으로 날짜 계산, 시간 간격(interval) 활용, 그리고 서로 다른 날짜 데이터를 결합하는 방법 등 실전에서 자주 마주하게 될 시계열 데이터 조작 기법들을 다뤄보겠다.

 

단순히 날짜를 더하고 빼는 것을 넘어서, 어떻게 하면 분석 목적에 맞게 정확한 시간 데이터를 구성할 수 있을지 하나씩 예제를 통해 살펴보자!

 

1. 날짜 계산

시계열 분석에서 날짜 간 차이를 계산하거나 날짜를 더하고 빼는 연산은 매우 기본적이면서도 자주 사용되는 작업이다. 이번 섹션에서는 날짜 계산에 유용한 SQL 문법들을 소개한다!! 

 

(1)  📆 두 날짜 간의 차이 (Interval 계산)

가장 기본적인 방식은 날짜 간 뺄셈을 사용하는 것이다.

SELECT date('2020-06-30') - date('2020-05-31') AS days;

>>>
days
-----
30

 

+) 날짜의 순서를 바꾸면 음수값이 나온다

SELECT date('2020-05-31') - date('2020-06-30') AS days;

>>> 
days 
-----
-30

 

 

(2) ➕ 날짜 더하기 (Interval 활용)

날짜에 값을 더할 때는 + 연산과 함께 interval을 사용해야 한다.

SELECT date('2020-06-01') + interval '7 days' AS new_date;

>>>
new_date
---------------------
2020-06-08 00:00:00

+) 단순히 +7 처럼 정수를 더하는 것은 불가능하기 때문에 꼭 interval을 사용한다는 것을 기억하자

 

(3) 📌 DATEDIFF 함수 사용하기

DATEDIFF()는 특정 단위로 날짜 간 차이를 계산할 수 있게 해주는 함수로, MySQL, SQL Server 등에서 자주 사용된다.

--- 일 단위 차이 계산 방법 
SELECT datediff('day', date('2020-05-31'), date('2020-06-30')) AS days;

>>>
days
-----
30

---- 월 단위 차이 
SELECT datediff('month', date('2020-01-01'), date('2020-06-30')) AS months;

>>>
months
--------
5

+) 단위는 'day', 'month', 'year' 등으로 다양하게 지정할 수 있다. 

 

 

---

2. 시간 계산

날짜뿐 아니라 시간 단위의 연산도 가능하다. 예를 들어 시간끼리 더하거나 빼는 것도 interval을 활용하면 쉽게 처리할 수 있다.

 

-- 시간 더하기
SELECT time '08:00:00' + interval '2 hours' AS result;

-- 시간 빼기
SELECT time '08:00:00' - interval '90 minutes' AS result;

시간 연산도 날짜와 마찬가지로 interval 단위를 명확히 지정해주는 것이 핵심이다! 

 

3. 서로 다른 출처의 날짜 합치기 

시계열 분석을 하다 보면 여러 출처의 데이터셋을 병합해야 하는 경우가 많다. 이때 가장 흔한 문제가 바로 날짜 형식(format)이 서로 다르다는 것! 날짜가 같더라도 형태가 다르면 JOIN, MERGE, GROUP BY가 제대로 되지 않는다.

 

예시 상황

A 데이터: '2020-06-01' (문자열 형식, 'YYYY-MM-DD')

B 데이터: '20200601' (정수 혹은 문자열, 'YYYYMMDD')

C 데이터: '01/06/2020' (영국식 날짜 형식)

D 데이터: '2020.06.01 00:00:00' (타임스탬프 포함)

 

(1) 날짜 형식 통일하기

문자열을 날짜로 변환할 땐 to_date() 또는 str_to_date() 함수를 사용해서 공통된 포맷으로 통일해주는 게 우선이다.

-- 문자열 '20200601'을 날짜로 변환 (MySQL)
SELECT str_to_date('20200601', '%Y%m%d') AS formatted_date;

>>> 
formatted_date
-----------
2020-06-01

-- '01/06/2020'을 표준 날짜로 변환 (PostgreSQL)
SELECT to_date('01/06/2020', 'DD/MM/YYYY') AS formatted_date;

>>>
formatted_date
---------------
2020-06-01

 

타임스탬프가 붙은 경우엔 ::date 또는 cast()로 날짜만 추출할 수 있다.

-- 타임스탬프에서 날짜만 추출 (PostgreSQL)
SELECT timestamp '2020-06-01 12:34:56'::date;

>>>
2020-06-01

 

 

(2) 공통 포맷으로 통일한 후 조인

날짜 포맷이 모두 DATE형으로 변환되었다면, 이제는 JOIN이나 UNION으로 자연스럽게 결합할 수 있다.

-- 두 데이터셋의 날짜를 기준으로 JOIN
SELECT *
FROM sales_data A
JOIN match_data B
  ON A.sale_date = B.match_date;

→ 이때 sale_date, match_date는 모두 동일한 형식(예: DATE)으로 되어 있어야 한다!

 

(3) 타임스탬프 정밀도 통일

한쪽은 '2020-06-01 00:00:00', 다른 쪽은 '2020-06-01'인 경우, 정밀도 차이 때문에 JOIN이 안 되기도 한다. 이럴 땐 ::date 또는 date_trunc()를 활용해서 정밀도를 통일하자.

-- 타임스탬프를 날짜만 남기고 비교
SELECT *
FROM table1 A
JOIN table2 B
  ON A.event_time::date = B.reference_date;

또는 아래 쿼리로도 정밀도 통일이 가능하다

-- 시간 단위까지 맞추고 싶다면 date_trunc 사용
SELECT date_trunc('day', timestamp_column)

 

 

+팁)  날짜 포맷 비교하기 이전에 컬럼 타입부터 확인!

-- PostgreSQL에서 컬럼 타입 확인
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name';

 

서로 다른 날짜 포맷이 섞여 있을수록 분석 단계에서 혼란이 생기기 쉬우니, 초기에 날짜 포맷 정리 작업을 해두는 것이 시계열 분석의 핵심 준비 단계라고 할 수 있다.

다음 포스팅에서는 이렇게 정리된 날짜를 기준으로 이동 평균, 계절성 분석, 추세 파악 등 본격적인 시계열 분석 기법들을 다뤄볼 예정이다. 이제 진짜 분석 맛 좀 보자...🔥