SQL에서 시간 데이터 분석하는 법

시간 차례대로 값을 정렬한 것을 시계열이라고 부릅니다. 이 글에서는 분석에서 자주 등장하는 시계열 패턴에 관해 살펴볼 것입니다. 그리고 해당 패턴들을 SQL 내에서 윈도우 함수를 사용하여 구하는 방법을 다룰 것입니다. 

여러분은 이미 시간과 관련된 데이터를 분석을 해보신 적이 있나요? 좀전에 설명하긴 했지만, 시간의 차례대로 정렬된 값을 우리는 시계열 데이터라고 일컫습니다. 시계열 데이터는 실제 비즈니스 상황에서 정말 자주 사용되므로 해당 데이터를 분석 시 사용하는 몇 가지 분석 패턴을 익혀두는 것이 중요합니다.

 

시계열 데이터의 예시

시계열 데이터는 어떤 값에 시간이라는 부가적인 요소가 붙어있는 데이터를 말합니다. 다시 말해, 어떤 값이 날짜 혹은 시간과 연결되어 있다는 뜻입니다. 대표적인 시계열 데이터를 나열해보자면 다음과 같습니다.

- 특정 회사의 일별 주식 가격 (각각의 주식 가격이 특정 날짜와 연결되어 있을 것임)
- 한 달에 걸친 특정 웹사이트 방문 수 (날마다 웹사이트 방문 수가 다를 것임)
- 일별 신규 사용자 등록 수
- 주별 매출
- 지난 10년 간의 연도별 매출 매입 (해당 시계열 데이터는 연도가 기준임)
- 지난 2개월 간의 일별 어플 로그인 수 (해당 시계열 데이터는 하루가 기준임)

이 글에서 우리는 가상 웹사이트 2개의 인기도를 분석해 볼 것인데요. 인기도 기준은 일별 방문 횟수라는 시계열 데이터를 가지고 분석할 것입니다. 지금부터 살펴볼 SQL 쿼리문들은 여러분이 진행할 시계열 분석에서도 충분히 응용할 수 있는 부분이니, 어려워 보이더라도 잘 따라와 주세요. 자, 우리는 2019년 7월 1일부터 2019년 12월 31일 사이의 데이터를 살펴볼 것입니다. 아래는 daily_visit 테이블입니다. 어떤 데이터가 저장되어 있는지 살펴보도록 하겠습니다.

date	visits	weekend	website
2019-07-01	2805	N	www.sqlanalysts.com
2019-07-02	4398	N	www.sqlanalysts.com
2019-07-03	6744	N	www.sqlanalysts.com
2019-07-04	6925	N	www.sqlanalysts.com
...	...	...	...
...	...	...	...
2019-12-25	3591	N	www.sqlanalysts.com
2019-12-26	4988	N	www.sqlanalysts.com
2019-12-27	7061	N	www.sqlanalysts.com
2019-12-28	2286	Y	www.sqlanalysts.com
2019-12-29	2462	Y	www.sqlanalysts.com
2019-12-30	3216	N	www.sqlanalysts.com
2019-12-31	4752	N	www.sqlanalysts.com
2019-07-01	3087	N	www.sqldevelopers.com
2019-07-02	5157	N	www.sqldevelopers.com
2019-07-03	8207	N	www.sqldevelopers.com
...	...	...	...
...	...	...	...
2019-12-26	5924	N	www.sqldevelopers.com
2019-12-27	8619	N	www.sqldevelopers.com
2019-12-28	1730	Y	www.sqldevelopers.com
2019-12-29	1913	Y	www.sqldevelopers.com
2019-12-30	3621	N	www.sqldevelopers.com
2019-12-31	5618	N	www.sqldevelopers.com

해당 테이블에 관해 부연 설명을 하겠습니다. 이 테이블은 총 4개의 열이 있습니다. 첫 번째는 date 열로, 2019년 7월 1일부터 2019년 12월 31일까지 저장되어 있습니다. 두 번째는 visits 열로, 각 날짜에 발생한 총 웹사이트 방문 수를 나타냅니다. 세 번째는 weekend 열로, 만약 값이 N이면 그 날은 주중을 의미하고, 만약 Y 라면 주말을 의미합니다. 마지막으로 website 열은 웹사이트 이름입니다. 우리는 2개의 가상 웹사이트 인기도를 분석할 것이기에, 여기에 저장된 웹사이트의 개수는 2개밖에 없습니다 (www.sqlanalysts.com 랑 www.sqldevelopers.com)

이미 눈치를 채셨을 것 같은데, 위 테이블의 각 행은 시간과 연관되어 있고, 또한 시간의 차례대로 값이 기록되어 있습니다. 이게 바로 지금까지 우리가 이야기한 시계열 데이터라고 말할 수 있겠습니다.

 

윈도우 함수를 이용해 누적 합계 구하기

누적 합계 개념을 사용해 우리의 분석을 시작해보도록 합시다. 누적 합계란 이전 값을 누적하여 구한 합을 말합니다. 아래 그림은 7월 1일부터 어떤 특정 날짜까지의 누적 방문 수를 구하고 있습니다. 그림에서 주의하여 살펴볼 점은 누적 합계가 각 사이트마다 구해졌다는 점입니다.

7월 1일의 누적 합계는 2805 입니다. 그 숫자는 7월 1일의 방문 수와 똑같습니다. 그 이유는 7월 1일은 이전 데이터가 없기 때문입니다. 우리는 이전 데이터가 없는 7월 1일을 누적 합계의 첫 시작점으로 삼고 누적 합계를 구해나갈 겁니다. 그다음 날인 7월 2일의 누적 합계를 보면 전날 누적 방문 수인 2805 와 당일 방문 수인 4398 을 더한 7203 인 것을 볼 수 있습니다. 7월 3일의 누적 합계의 경우 당일 방문 수인 6744 에다가 이전 누적 방문 수인 7203 을 더한 13947 입니다. 그다음도 다 이런 식으로 계산됩니다.

이는 누적 합계가 사용될 수 있는 여러 비즈니스 케이스 중 하나입니다. 이외에도 월초부터 현재까지의 특정 물품 판매량, 지난 분기 혹은 지난 연도의 체크카드 및 신용카드 거래 내역의 누적 액수, 연초부터 지금까지의 누적 일별 매출 또한 지금 우리가 공부하고 있는 누적 합계가 사용된 예시입니다. 

누적 합계가 무엇을 의미하는지 알았으니 이를 SQL로 구현하는 방법을 알아보겠습니다. 누적 합계는 윈도우 함수를 사용하여 계산합니다. 윈도우 함수는 기존의 행을 그대로 남겨둔 채 열을 가로질러 계산할 수 있는 아주 유용한 SQL 함수입니다. 윈도우 함수는 GROUP BY 와 유사하게 느껴지지만, 막상 둘의 결과값을 비교하면 GROUP BY 절울 사용한 것과 윈도우 함수를 사용한 것 사이에는 분명한 차이가 존재합니다. GROUP BY 절을 사용할 경우, 행들이 하나로 합쳐집니다, 하지만 윈도우 함수를 사용하면, 행들이 하나로 합쳐지지 않습니다. 기존의 행들은 사라지지 않은 채 그대로 남아있고, 우리가 구하고자 했던 새로운 값은 기존의 행 옆에 놓입니다. 누적 합계도 바로 그런 상황입니다. 기존의 행에다가 새로 구한 합계들을 놓아주어야 누적해서 더해갈 수 있겠지요. 만약 지금까지의 내용이 이해가 안 된다면 GROUP BY vs. PARTITION BY : 유사점과 차이점을 읽어보시길 추천 드립니다. 

아래는 daily_visit 데이터에 누적 합계를 계산하는 SQL 쿼리문입니다.

SELECT
 *,
 SUM(visits) OVER(PARTITION BY website ORDER BY date) AS running_total
FROM daily_visits;

이 쿼리를 실행하면, 모든 행들이 조회됨과 동시에 running_total 이라는 새로운 열이 하나 생깁니다. 어떤 상황인지 간략히 설명드릴게요.

첫째, OVER절과 PARTITION BY를 통해 이 문장이 윈도우 함수를 사용한다는 것을 알 수 있습니다. 둘째, PARTITION BY는 행을 구분할 기준을 제공합니다. 우리 예시에서는 행을 웹사이트로 구분지었습니다. 셋째, ORDER BY는 행의 순서를 결정합니다. 우리는 행을 날짜 기준으로 정렬하라고 요청했습니다. 넷째, 이 예시에서는 ROW/RANGE를 사용함으로써 따로 윈도우 함수가 실행될 행의 범위를 지정해 주진 않았습니다. 즉, 윈도우 함수가 실행되어야 할 행의 범위를 특이하게 정해줘야 할 상황이 아니라면(예를 들어 특정 행의 바로 앞 행과 바로 뒷 행의 값을 더하고 싶음), 괄호 안에 ROW/RANGE 와 관련된 표현을 따로 적지 않는 이상 누적 합계는 디폴트 범위는 행의 제일 첫 번째 행부터 현재 행까지 입니다.

윈도우 함수는 다른 시계열 분석 패턴에도 사용할 SQL 개념입니다. 하지만, 이 글에서는 윈도우 함수의 구문에 관해선 자세히 다루지 않겠습니다. 만약 윈도우 함수를 더 자세히 알고 싶으시다면 대표적인 윈도우 함수 6가지 알아보기를 읽어보시길 추천드립니다.

이번에는 증감률과 같은 비율 변화와 이동 평균에 관해 살펴보도록 하겠습니다.

 

일별 웹사이트 방문의 비율 변화

분석을 하다 보면 시간에 따라 사업이 변화하는 모습을 설명해야 할 때가 있습니다. 이를 잘 나타낼 수 있는 방법 중 하나가 바로 증감률을 사용하는 것입니다. 증감률을 사용하면 아래와 같은 질문에 답할 수 있습니다.

1. 지난달에 비해 이번 달의 매출은 어떠합니까? 증가했나요 아니면 감소했나요?
2. 웹사이트 신규 사용자의 수가 지난 분기에 비해 증가했나요 아니면 감소했나요?
3. 지난주에 비해 오늘 더 많은 상품을 팔았나요?
4. 지난 연도에 비해 올해 판매량이 늘고 있나요 아니면 줄고 있나요? 

이제부턴 SQL을 통해 증감률을 구하는 법을 보여드리도록 하겠습니다. 아래는 웹사이트의 총 방문수 증감률을 구하는 방법입니다. 기본적인 원리만 이해한다면, 여러분의 비즈니스 상황에도 충분히 적용할 수 있답니다. 

 

LAG 함수를 이용해 이전 행의 값을 가져오기

증감률을 구하기 위해 가장 먼저 해야 할 일은 이전 행의 값을 가져오는 것입니다. 왜 이전 행의 값이 필요하냐고요? 왜냐하면 증감률을 구하는 수식이 다음과 같기 때문입니다.

(현재 값 - 이전 값) / 이전 값 * 100

이 수식을 보면 두 값의 변화를 구하기 위해서는 한 줄에 현재 값과 이전 값을 나란히 놓아주어야 합니다. 그래서 우리가 쿼리문을 작성할 때 제일 먼저 할 일은 현재 행의 값과 이전 행의 값을 불러오는 것입니다. 그래야 위의 수식을 통해 증감률을 계산할 수 있을 테니까요. 해당 작업은 윈도우 함수 중 하나인 LAG 함수를 사용하면 손쉽게 해낼 수 있습니다. LAG 함수는 현재 행보다 앞에 있는 행의 값을 불러올 때 사용하는 함수입니다. 

아래는 LAG 함수를 사용하여 작성한 쿼리문입니다. 날짜 순으로 정렬한 후 바로 직전 행의 값을 반환하라고 요청했습니다. 즉, 이전 날의 데이터일테니 새로 만드는 열의 이름을 previous_day_visits 라고 지었습니다.

SELECT
 *,
 LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits; 

LAG 함수가 사용된 문장을 부연 설명을 하자면, 이 문장은 윈도우 함수의 가장 기본적인 구문 형식입니다. LAG 함수는 필수적으로 1개의 매개변수를 요구하고, 그 매개변수는 우리가 필요로 하는 값을 담고 있는 열 이름입니다. 따라서 괄호 안에는 우리가 필요로 하는 값(즉, 방문 수)을 담고 있는 열인 visits 을 적었습니다. 이를 통해 각 행마다 이전 행의 값을 나란히 붙여줄 수 있게 되었습니다.

LAG 함수는 바로 전 행의 값 말고도 훨씬 앞에 있는 행의 값들도 불러올 수 있습니다. 예를 들어, 오늘 데이터를 일주일 전의 데이터와 비교하고 싶다고 가정해봅시다. 그 경우, 각 행마다의 7일 전 데이터를 가져와 나란히 붙여줘야 하겠지요. 이를 수행하기 위해선, LAG 함수 괄호 안에 우리가 원하는 값이 들어있는 열 이름에다가, 해당 값이 N개의 행 앞에 있다고 말해주는 매개변수를 추가하여 적습니다. 쿼리를 한 번 보시죠.

SELECT
 *,
 LAG(visits, 7) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

LAG 함수의 기본 범위는 1이라서 열 이름 뒤에 아무것도 적지 않는 이상은 바로 앞 행의 값을 가져옵니다. 하지만 여러분이 몇 번째 앞에 있는 값을 가져오고 싶은지에 따라 숫자를 바꿔서 적어주면 됩니다. 우리 예시에서는 일주일 전의 값이 궁금한 것이기 때문에 숫자 7을 넣은 것뿐입니다. 

 

일별 증감률 구하기

증감률을 계산하기 위해 필요한 값이 다 모였으니 이젠 일별 증감률을 구해보도록 합시다.

WITH daily_visits_lag AS(
  SELECT
     *,
     LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
  FROM daily_visits)

SELECT
 *,
 COALESCE(ROUND((visits - previous_day_visits)/previous_day_visits * 100), 0) AS percent_change
FROM daily_visits_lag;

구문이 꽤나 복잡해 보이지만, 실제로는 그렇지 않습니다. 어떤 내용인지 한 번 뜯어서 살펴보겠습니다.

첫째, LAG 함수를 통해 이전 행에서 값을 가져오는 쿼리문은 WITH 구문을 사용하여 상단에 위치해주었습니다. WITH 구문은 사용자가 임시로 만든 테이블을 말합니다. 둘째, 해당 임시 테이블 이름은 daily_visits_lag 라고 지었습니다. 보시다시피 해당 테이블은 증감률을 구할 때 필요한 값을 갖고 있습니다 (특정 날짜와 그 전날의 웹사이트 방문 수). 셋째, 임시로 만든 테이블인 daily_visits_lag 을 메인 쿼리에서 사용하고 있습니다. 메인 쿼리 부분을 보면, 모든 행과 열을 요청한 후(*) 한 개의 열을 추가했습니다. 바로 증감률을 구하는 식입니다.

참고로, 해당 쿼리문이 실행되고 나면 임시로 생성한 테이블 daily_visits_lag 는 더 이상 존재하지 않습니다. 이 글에서는 임시 테이블을 생성하는 WITH 구문, 즉 CTE 에 관해서는 다루지 않을 것입니다. 만약 해당 개념을 공부하고 싶으시다면, SQL CTE를 잘 활용하려면? 편을 읽어보시길 추천드립니다.

위의 쿼리문을 실행하면, 아래와 같은 결과를 얻을 수 있습니다.

 

주별 증감률 구하기

일별 증감률을 구하는 법을 알았으니 7일 증감률, 즉 주별 증감률도 구해보도록 하겠습니다. 

WITH  daily_visits_lag AS(
   SELECT
      *,
      LAG(visits, 7) OVER(PARTITION BY website ORDER BY date) AS previous_7day_visits)
    FROM daily_visits)

SELECT
 *,
 COALESCE(ROUND((visits - previous_7day_visits)/previous_7day_visits *100),0) AS percent_change
FROM daily_visits_lag;

이전 쿼리와 비교했을 때 달라진 점은 LAG 함수의 괄호 안에 몇 번째 앞의 열을 가져오라는 추가 매개변수를 적어준 것 밖에 없습니다. 쿼리를 실행하면 결과는 다음과 같습니다.

여기서 사용한 쿼리문은 다른 비즈니스 상황에도 충분히 사용할 수 있습니다. 여러분의 상황에 맞게 테이블명과 열 이름만 수정하여 사용하시면 원하는 증감률을 손쉽게 구하실 수 있습니다.

 

단순 이동 평균 (7일 기준)

시계열 분석에서 자주 언급되는 분석 패턴 중 하나는 단순 이동 평균(Simple Moving Average)입니다. 단순 이동 평균이란 특정 값과 그것의 이전 N개 값의 평균을 말합니다. 테이블을 가지고 설명하자면, 각 행에 저장된 값마다 이동 평균이 계산됩니다. 오직 집계 함수 AVG만 사용한다면 테이블 내의 전체 행의 평균을 구할 것입니다. 하지만 단순 이동 평균은 각 행마다 가지는 특정 범위의 평균을 구합니다.

단순 이동 평균은 종종 주식 가격이나 가상화폐의 트렌드를 분석할 때 사용됩니다. 왜냐하면 이동 평균은 여러 변수들의 행동을 이해하는 데 있어 유용한 잣대를 제공하기 때문입니다. 전체에 관한 평균값 딱 1개만 제공하는 것이 아니라, 특정 지표의 여러 평균값들을 관찰하기에 보다 나은 예측을 내놓을 수 있습니다. 들쭉날쭉한 값을 가지고 인사이트를 도출하기보다는 상대적으로 부드러운 여러 개의 평균값들을 비교해 봄으로써 데이터의 전반적인 흐름을 파악할 때 도움이 됩니다.

아래 그래프는 각 날짜마다 2개의 값을 가지므로 2개의 그래프로 나타내고 있습니다. 막대 그래프는 해당 날짜에 발생한 방문 수를, 선 그래프는 해당 날짜와 이전 6일들의 평균값을 나타냅니다.

ㅜ

단순 이동 평균을 구할 때도 윈도우 함수를 사용합니다. 이전 예시에서는 LAG 함수를 사용해 이전 행들의 값을 가져왔지만,  이번에는 OVER절 안에 ROW/RANGE 매개변수를 적어줌으로써 평균값을 구할 범위를 제한해 줄 것입니다.

SELECT
 *,
 AVG(visits) OVER(PARTITION BY website ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS SMA7
FROM daily_visits;

이 글의 초반에서 ROW와 RANGE에 관해 아주 간략히 언급했었는데요. OVER절 안에 ROW 매개변수를 사용하면 윈도우 함수가 실행될 특정 범위를 제한해줍니다. 누적 합계를 구할 때는 가장 첫 행부터 현재 행까지의 합을 구해야 했기 때문에 따로 범위를 제한할 필요가 없었지만, 7일 단순 이동 평균과 같이 매 행마다 윈도우 함수(여기서는 AVG 함수)가 실행되어야 할 범위가 달라질 경우 범위를 구체적으로 제한해 줄 필요가 있습니다. 지금 쿼리문에서는 ROW가 제한한 행의 범위는 총 7개의 행입니다. 바로 현재 행과 이전 6개의 행입니다. 이를 쿼리로는 어떻게 표현했는지 주의깊게 살펴보세요. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW라고 적었습니다. 

쿼리를 실행해보면, 각 행마다의 고유한 평균값을 얻게 됩니다. 한 번 직접 보시죠.

 

RANK 함수를 이용해 최다 방문 날짜 찾기

마지막으로 살펴볼 분석 패턴은 순위입니다. '순위'라는 단어에 이미 눈치채셨을 수도 있지만, 해당 분석은 특정 변수의 크기에 따라 값의 정렬 순서가 결정됩니다. 예를 들어, 각 웹사이트 별로 방문 수가 높았던 날짜순으로 데이터를 가공하여 살펴보고 싶다고 가정해 보겠습니다. 이를 구하려면, 우리는 방문 수를 가지고 테이블에 순위를 매길 필요가 있습니다. 이를 해결할 수 있는 방법은 RANK 함수입니다. 이것도 윈도우 함수입니다.

SELECT
 *,
 RANK() OVER(PARTITION BY website ORDER BY visits DESC) AS rank
FROM daily_visits;

쿼리문을 볼까요?

첫째, 순위는 visits 열의 값으로 결정됩니다. 그걸 쿼리문으로 어떻게 표현했냐면 ORDER BY를 사용해 visits 열 안의 값으로 전체 행을 정렬하라고 요청했습니다. 또한 ORDER BY문 끝에 DESC를 적어서 내림차순으로 정렬될 것이고, 이는 제일 높은 방문 수를 가진 행이 1위를 가져갈 것입니다. 둘째, PARTITION BY를 통해 각 웹사이트마다의 순위를 매겨달라고 요청했습니다. 마지막으로, RANK 함수를 사용해서 똑같은 값을 가진 행들은 똑같은 순위를 부여받게 됩니다. 

그래서 이 쿼리를 실행하면, 여러분의 결과 테이블에는 rank 라는 새로운 열이 추가되어 있을 것입니다. 이제는 각 웹사이트마다 어떤 날짜에 방문 수가 높았는지 한눈에 확인할 수 있습니다. PARTITION BY를 통해 각 웹사이트마다 순위를 매길  수 있도록 행의 구역을 나누었기에 데이터가 섞이지 않고 깔끔하게 순위가 부여될 수 있었습니다. 참 용이한 기능이지요!

date	visits	weekend	website	rank
2019-08-23	11993	N	www.sqldevelopers.com	1
2019-08-28	11334	N	www.sqldevelopers.com	2
2019-10-04	10998	N	www.sqldevelopers.com	3
2019-09-20	10812	N	www.sqldevelopers.com	4
2019-10-23	10737	N	www.sqldevelopers.com	5

이건 sqldevelopers.com 의 랭킹입니다.

date	visits	weekend	website	rank
2019-10-12	10895	N	www.sqlanalysts.com	1
2019-07-06	10595	N	www.sqlanalysts.com	2
2019-07-13	10558	N	www.sqlanalysts.com	3
2019-12-22	10327	N	www.sqlanalysts.com	4
2019-10-20	10290	N	www.sqlanalysts.com	5

이건 sqlanalysts.com 의 랭킹입니다.

 

글을 마무리하며

지금까지 시계열 분석을 하기 위한 기본적인 윈도우 함수와 이를 활용한 분석 패턴을 배워 보았습니다. 개념과 글에서만 그치지 말고 실제 데이터를 통해 직접 분석을 해보면서 해당 개념에 더 익숙해지시길 바랍니다.

 

원문 : How to Analyze a Time Series in SQL