상세 매출 정보를 담고 있는 테이블 purchase_detail_log2에서 일별 매출액을 집계하는 쿼리는 다음과 같다.
SELECT dt, category, SUM(price) AS amount, FROM learning_club.purchase_detail_log2 GROUP BY 1, 2
제품 카테고리별 매출액 변화 추이를 보고자 하기 때문에 GROUP BY의 차원에 해당하는 컬럼에 날짜 차원인 dt와 제품 카테고리의 category 2개를 지정하여SUM(price) 집계 함수로 매출액을 산출한다.
다음은 일별 매출액에서 월별 매출액을 산출하는 쿼리이다.
-- 일별 매출액 산출WITH daily_purchase AS ( SELECT dt, category, SUBSTR(dt, 1, 4) ASyear, SUBSTR(dt, 6, 2) ASmonth, SUBSTR(dt, 9, 2) ASday, SUM(price) AS amount, FROM learning_club.purchase_detail_log2 GROUP BY1, 2)SELECT year||'-'||monthAS year_month, category, SUM(amount) AS amount, FROM daily_purchase GROUP BY1, 2
GROUP BY에 날짜가 이번에는 하루가 아닌 월 단위가 되어야 하므로 날짜를 조작하여 년/월/일을 아래와 같이 daily_purchase 서브쿼리에서 미리 전처리 시켜놓았다. 메인쿼리를 간결하게 작성하기 위해서이다.
SUBSTR(dt, 1, 4) AS year,
SUBSTR(dt, 6, 2) AS month,
SUBSTR(dt, 9, 2) AS day,
매출 이력이 여러 해에 걸쳐 수집된 경우 단순히 month로 집계를 하게 되면 2017년과 2018년의 월별 매출액이 같이 합산이 될 수 있으므로 월별 집계의 차원으로는 아래와 같이 년/월을 묶어서 사용해야 원하는 결과를 얻을 수 있다.
year || '-' || month AS year_month,
여기서 ||은 문자열 접합 연산자로 CONCAT() 함수와 동일한 역할을 한다.
-- 월별 매출액 산출 WITH daily_purchase AS ( SELECT dt, category, SUBSTR(dt, 1, 4) ASyear, SUBSTR(dt, 6, 2) ASmonth, SUBSTR(dt, 9, 2) ASday, SUM(price) AS amount, FROM learning_club.purchase_detail_log2 GROUP BY1, 2)SELECT year||'-'||monthAS year_month, category, SUM(amount) AS amount, FROM daily_purchase GROUP BY1, 2;
첫 번째 Fan Chart 마트 쿼리
월별 매출액이 산출되었으니 기준 월을 정하고 기준 월 대비 각 월의 매출 비율을 산출해 보도록 하자.
기준 월(year_month)은 매출 이력을 수집하기 시작한 첫 번째 달로 정의
WITH daily_purchase AS ( SELECT dt, category, SUBSTR(dt, 1, 4) ASyear, SUBSTR(dt, 6, 2) ASmonth, SUBSTR(dt, 9, 2) ASday, SUM(price) AS amount, FROM learning_club.purchase_detail_log2 GROUP BY1, 2),monthly_purchase AS ( SELECT year||'-'||monthAS year_month, category, SUM(amount) AS amount, FROM daily_purchase GROUP BY1, 2)SELECT -- 어떤 로직이 들어가야 할까? FROM monthly_purchase ORDER BY 1;
첫번째 관문은 원하는 지표를 산출하기 위해서는 필요한 기준 월 의 값을 어떻게 가져올 수 있을까에 대한 고민이다.
1. Self-Join 을 이용하여 기준 월의 매출액 연결
--Self-Join 사용SELECT t1.year_month, t1.category, t1.amount, t2.amount AS base_amount, ROUND(t1.amount / t2.amount *100, 1) AS rate FROM monthly_purchase t1 LEFT JOIN ( SELECT category, year_month, amount FROM monthly_purchase WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY year_month) =1 ) t2 USING (category) ORDER BY1, 2;
셀프 조인은 동일 테이블의 다른 행에 존재하는 값으로 컬럼을 늘려가는 목적으로 주로 사용된다.
t2 서브쿼리에서 각 카테고리를 날짜 순으로 정렬시켜 가장 최근의 행만 남긴 후에 조인을 통해 메인쿼리에서 기준 월의 매출액을 참조할 수 있도록 하였다.
2. 분석(윈도우) 함수를 이용한 방법
SELECT year_month, category, amount, FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY year_month ) AS base_amount, amount / FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY year_month ) *100AS rate, FROM monthly_purchase ORDER BY 1, 2;
동일 테이블의 다른 행에 위치한 값으로 컬럼을 확장할 때 셀프 조인외에 윈도우 함수의 일종인 네비게이션 함수를 이용하는 방법도 있다.
여기서 사용된 FIRST_VALUE()는 네비게이션 함수로 category로 구분된 파티션에서 year_month을 오름차순으로 정렬한 후 기준 월에 해당하는 첫번째 행에서 매출값을 가져온다.
첫번째 마트 쿼리
셀프 조인보다는 가급적 윈도우 함수를 쓰는 것이 성능상 유리하다. 윈도우 함수를 적용하여 첫번째 마트 쿼리를 완성해 보자.
WITH daily_purchase AS ( SELECT dt, category, SUBSTR(dt, 1, 4) ASyear, SUBSTR(dt, 6, 2) ASmonth, SUBSTR(dt, 9, 2) ASday, SUM(price) AS amount, FROM learning_club.purchase_detail_log2 GROUP BY 1, 2),monthly_purchase AS ( SELECT year||'-'||monthAS year_month, category, SUM(amount) AS amount, FROM daily_purchase GROUP BY 1, 2)SELECT year_month, category, amount, FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY year_month ) AS base_amount, amount / FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY year_month ) *100AS rate, FROM monthly_purchase ORDER BY 1, 2;
실행 결과는 다음과 같다.
두 번째 관문은 만들어진 마트 테이블로 Fan Chart를 어떻게 그릴 수 있을까이다.
이 글의 도입부에 있는 Fan Chart를 다시 살펴보자.
가로 축에는 날짜 차원(dimension)이 지정되어 있고 세로축에는 매출 비율이라는 지표(metric)가 보여지고 있다. 그런데 꺾은 선이 하나가 아니고 각 카테고리별로 하나의 꺾은 선이 그려지고 있다.
이 의미는 카테고리의 각각의 값들인 book, cd, food 등등이 하나의 지표항목이 되고 각각 지표값을 갖는다는 의미이다. (지표 자체도 하나의 차원을 이룬다)
시각화를 위해서는 우선 차원(dimension)과 지표(metric)에 대한 이해가 필요하다. 자세한 설명보다는 각 차원항목과 지표항목들은 테이블에서 각각 하나의 컬럼에 대응된다는 점만 우선 기억하자.
예를 들어, 아래와 같은 집계 쿼리가 있을 경우 col1과 col2 컬럼은 각각 차원항목이 되고, 집계함수의 결과인 metric_1과 metric_2는 각각 지표항목이 된다.
차원 항목이나 지표항목 모두 하나의 컬럼을 차지하고 있다.
-- 집계 쿼리에서의 차원항목과 지표항목SELECT col1 AS dimension_1, col2 AS dimesnion_2, SUM(col3) AS metric_1, AVG(col3) AS metric_2, FROM some_table GROUP BY1, 2
다시 첫 번째 마트 테이블로 돌아와 보자.
현재 마트 테이블은 Fan Chart로 시각화하기에는 적절하지 않은 구조를 갖는다.
앞서 설명에서처럼 Fan Chart를 그리려면 카테고리의 각 항목들이 하나의 지표항목으로 분리되어 하나의 컬럼을 차지하고 있어야 한다.
하지만 첫 번째로 만든 마트는 카테고리들이 컬럼으로 구분되어 있지 않고 Stacked Data 형태를 가지고 있어 Fan Chart 시각화에 적합한 테이블 구조로 변경할 필요가 있다.
엑셀을 다루면서 많이 듣던 Pivot 이 등장할 차례이다.
두 번째 Fan Chart 마트 쿼리
BigQuery는 비교적 최근에 Workaround Query 도움없이 PIVOT 을 직접적으로 수행할 수 있도록 기능이 제공되고 있다. PIVOT 연산자를 사용하여 시각화에 적합한 마트 구조로 Pivoting을 수행해 보자.
WITH daily_purchase AS ( SELECT dt, category, SUBSTR(dt, 1, 4) ASyear, SUBSTR(dt, 6, 2) ASmonth, SUBSTR(dt, 9, 2) ASday, SUM(price) AS amount, FROM learning_club.purchase_detail_log2 GROUP BY 1, 2),monthly_purchase AS ( SELECT year||'-'||monthAS year_month, category, SUM(amount) AS amount, FROM daily_purchase GROUP BY 1, 2),fan_chart_mart AS (SELECT year_month, category, amount, FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY year_month ROWS UNBOUNDED PRECEDING ) AS base_amount, ROUND( amount / FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY year_month ROWS UNBOUNDED PRECEDING ) *100, 1 ) AS rate, FROM monthly_purchase ORDER BY 1)SELECT * FROM (SELECT year_month, category, rate FROM fan_chart_mart) PIVOT ( SUM(rate) FOR category IN ('book', 'cd', 'dvd', 'food', 'game', 'ladys_fashion', 'mens_fashion' ) );
PIVOT 연산자의 사용은 직관적이지는 않다. 필요한 내용만 우선 살펴보자.
SUM(rate) FOR category IN ( ... ) 은 category 컬럼에 저장된 값들 중에 IN 리스트 내에 명시된 값들을 각각 하나의 컬럼으로 만들면서 해당 컬럼의 값으로 SUM(rate)를 저장하라는 의미이다.
Pivoting된 마트 테이블에서는 카테고리 각각의 항목들이 하나의 컬럼으로 분리되어 있어 Fan Chart 시각화를 할 수 있다.
Data Explore의 Combo Chart에서 Metric 에 카테고리 항목들을 모두 추가해주면 아래의 결과를 얻을 수 있다.
