[열정페이 - SQL] 3장 시계열 분석 기초 정리

PostgreSQL를 사용하여 진행

사용하는 교재

2024.10.2

열정페이 SQL 데이터 분석 3주차 기초 정리입니다.

데이터를 이용한 실습은 따로 올릴 예정

 

---

• 시계열 분석이란?

시간  순으로 재정렬된 데이터의 배열을 의미하며 일정한 시간 간격을 두고 저장한다.

시간에 따른 데이터 변화를 이해하고 정량화 하는 것이 목적

 

• 날짜 및 타임스탬프 형식 변환

날짜 및 시간 데이터는 시계열 분석에 중요한 요소이다. 시계열 분석에서 날짜 형식 변환은 피할 수 없는 숙명이다.

---

 

 

• 현재 날짜나 시간을 가져오는 방법

SELECT current_date;
SELECT current_timestamp;
SELECT now();

서버의 시스템 기준으로 결정되며 current_date는 시간은 반환하지 않는다.

 

 

날짜 또는 타임스탬프 값을 그대로 사용하기보다는 특정 단위 값만 추출해 사용할 수 있다.

SELECT date_part('day',current_timestamp);
SELECT date_part('month',current_timestamp);
SELECT date_part('year',current_timestamp);

이를 이용해 매출을 월별,요일별,시간별로 비교 분석이 가능하며 나중에 노이즈를 제거할때도 자주 사용된다.

 

 

• 날짜 계산

age함수를 이용한 인터벌 계산

SELECT age(date('2024-10-2'),date('2024-1-1'));
SELECT age(date('2024-01-01'),date('2024-10-2'));

 

이 결과로 2024년이 시작된지 9 mons 1 day가 지났음을 알 수 있다. 아래 코드처럼 두 날짜의 순서를 바꾸면 음수값이 나오니 순서를 신경써야한다.

또한 date_part를 이용해서 몇년 혹은 몇달이 지났는지도 손쉽게 계산할 수 있다.

 

추가로 인터벌을 사용해 특정 날짜에 더하기 연산도 가능하다.

SELECT date('2024-10-2') + interval '7 days' as new_date;
SELECT date('2024-10-2') + interval '7 months' as new_date;
SELECT date('2024-10-2') + interval '7 years' as new_date;

인터벌을 사용하지 않고 숫자만 입력하여도 작동하지만 인터벌과 단위를 명시해서 코드에 직관성을 높일 수 있다.

또한 - 연산도 가능하다.

(곱하기 연산은 불가)

 

활용

WHERE event_date < current_date - interval '3 months'

3개월이 지난 이벤트를 조회해서 비활성 사용자를 찾거나 할때 사용할 수 있다.

 

참고로 시간계산도 동일하게 가능한데 날짜와 달리 곱하기연산도 가능하다.

 

 

 

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

시스템마다 날짜와 시간을 다른 형식으로 저장할 수 있고,  서로 다른 타임 스탬프를 사용하는 경우도 있다.

따라서 형식을 맞추고 표준화하는 작업이 필요하다.

어떻게?

1. 서버의 타임스탬프와 사용자의 타임스탬프 값이 다른 데이터 찾기

2. 시간차이가 특정값보다 크다 > 삭제 

3. 시간차이가 특정값보다 작다 > 보정 후 사용

 

차이로 인해서 단위가 바뀌거나 분석 결과가 달라질 수 있다면 삭제한다. BETWEEN 절과 날짜 계산을 활용할 수 있다.