[Big Query] SQL을 활용해 Z차트 준비하기

 Today's Topic

• Z차트 개요
• Z차트 분석 포인트
• Z차트의 형태
• SQL을 활용하여 Z차트 작성 준비하기

- Used Function

• WITH 구문
• CASE 구문
• OVER (ORDER BY ~) / OVER (PARTITION BY~ ROWS) 구문
• CONCAT()함수

---

1.  Z차트의 개요

Z차트란, 월차매출, 매출누계, 이동년계라는 3개의 지표를 하나의 차트 안에 표시해 넣은 것입니다.

계절(시즌제품의 판매량의 증가나 분기적 요인 등)의 영향을 배제하고 트렌드를 분석하는 기법이라고 보시면 됩니다.

 

월차매출

매출 합계를 월별로 집계하는 것입니다.

예를 들어, 2023년의 1분기의 월차매출을 구하고자 한다면 2023년 1월, 2월, 3월 각각의 월 매출을 말합니다.

월차매출의 예시

매출누계

해당 달의 매출에다가 이전월 까지의 매출의 누계를 더한 값입니다.

예를 들어 2023년 1분기의 매출 누계는 2023년 1월부터 3월까지 매출의 합계라고 볼 수 있습니다.

매출누계의 예시

이동년계

이동년계는 해당 월의 매출에 과거 11개월의 매출을 합한 값입니다.

예를 들어 2023년 1월의 이동년계는 2022년 2월부터 2023년 1월까지의 매출의 합계라고 보면 되겠습니다.

2023년 1울의 이동년계

 

---

2.  Z차트의 분석 포인트

1)  매출누계에서의 분석 포인트

월차매출이 일정하다면 매출누계는 직선이 됩니다. 오른쪽으로 갈수록 기울기가 급해진다면 최근 매출이 상승하고 있다는 의미이며,

반대로 완만해진다면 최근 매출이 하락하고 있다는 의미입니다.

 

 

2)  이동년계에서의 분석 포인트

작년과 올해의 매출이 일정하게 나타낸다면 이동년계는 직선이 됩니다. 이동년계가 우상향한다면 매출은 상승하는 경향을 보인다고 볼 수 있고,반대로 우하향한다면 매출은 감소하고 있는 경향을 보인다고 볼 수 있습니다.

 

---

3.  Z차트의 형태

Z차트의 형태는 다음과 같이 3가지의 형태로 나눌 수 있습니다.

 

먼저 매출이 일정할 경우입니다.

매출이 일정하다는 것은 좋게 말하면 매우 안정적인 상태, 조금 안좋게 말하면 정체기로 생각할 수 있습니다.

월별매출(월차매출)과 이동합계는 매출이 일정하므로 변함이 없고, 매출누계 역시 일정한 월 매출을 기록하고 있으므로 일정하게 증가하는 모습을 보입니다. 즉, 매우 반듯한 Z모양에 가까운 모습을 보입니다.

 

다음으로 매출이 증가하는 형태입니다.

이동합계 그래프의 형태는 상승하고 있는 형태이고, 

월별매출(월차매출)과 매출누계의 그래프 역시 점점 증가하는 모습입니다.

 

마지막으로 매출이 감소하는 형태입니다.

앞서 보신 차트보다 조금 더 확실하게 하기 위해 폭을 조금 더 키웠습니다.

이동합계는 점점 더 감소하고 있으며, 매출누계 역시 기울기가 점점 줄어드는 모습입니다.

월별(차)매출 역시 점점 감소하는 추세를 보여,  위 매출 증가 테이블과 반대의 형태를 보이고 있음을 알 수 있습니다.

 

---

4.  SQL을 활용하여 Z차트 준비하기

월차매출, 매출누계, 이동합계가 모두 주어진 데이터셋이라면 바로 엑셀이나 파이썬 등을 이용해 분석에 들어갈 수 있지만

실제로는 연월과 매출합계와 같은 간단한 정보만 주어진 경우가 많습니다.

그렇기 때문에 이것을 Z차트로 활용하려면 데이터를 가공하여야 하는데 이 작업을 SQL을 사용해 하게 됩니다.

 

그렇다면 SQL을 활용하여 일반적인 데이터셋으로 어떻게 Z차트를 준비할 수 있는지 학습해 보도록 하겠습니다.

 

9강_z차트.csv

0.00MB

0) 테이블 정보

2021년 1월부터 2022년 12월까지의 판매정보를 나타낸 테이블입니다.

본 테이블의 내용은 임의로 작성한 것입니다.

dt: 고객이 물건을 구매한 일자입니다.

id: 고객 id를 나타낸 것입니다.

price: 고객이 구매한 물건의 가격을 나타낸 것입니다.

 

원래의 테이블은 일자, 고객id, 가격만 나와 있습니다.

하지만 이 테이블로 z차트를 만들기 위해서는 다음 순서로 진행할 수 있습니다.

 

1) 먼저 dt칼럼에서 연월을 추출하고,

2) 2021년과 2022년의 월별 구매가격의 합계(월차매출)를 구한 뒤,

3) 2022년만의 누계매출을 구하고

4) 2022년의 이동 누계를 집계하고,

(2022년 1월부터 12월까지의 이동 누계를 구하는 것이므로 2021년 2월부터의 데이터가 필요할 것입니다.)

5) 당해연도 데이터만 추출하는 순서로 진행됩니다.

 

step 1) 테이블의 dt칼럼에서 연월만 추출하기

먼저 dt칼럼을 한번에 사용하기는 힘든 상황입니다.

나중에 누계매출과 이동년계를 집계할 때 rows구문을 사용하여 행을 추출하게 되는 상황이 있기 때문에, 이 경우를 대비해서 dt칼럼을 연월일로 분리할 필요가 있습니다.

with구문을 사용해 물리 테이블이 아닌 임시 테이블에 저장하는 방법을 사용하겠습니다.  물론, 해당 쿼리를 물리 테이블로 저장시켜도 되나, 추가 수정이 이루어질 예정이기 때문에 일단 임시 테이블로 저장하는 것이 좋을 것 같습니다.

--1) dt칼럼을 연월일로 분리하기 (dt칼럼이 date타입이므로, string타입으로 변환한 후 분리하여야 함.)
with daily_price as (
  select dt
  , substr(cast(dt as string), 1, 4) as year
  , substr(cast(dt as string), 6, 2) as month
  , substr(cast(dt as string), 9, 2) as date
  , sum(price) as tot_price
  , count(id) as order_count
  from inlaid-lane-373607.sqlr202301.9_z_chart
  group by dt
)
select * from daily_price
order by dt;

위 쿼리에서 sum(price) as tot_price쿼리는 같은 일자에 대해 구매가격의 합계를 구한 것입니다.

또한, order_count칼럼은 같은 일자에 몇 번 구매되었는지 나타내는 것입니다.

 

step 2)2021년과 2022년의 월차매출 구하기

그 다음으로는 2021년과 2022년의 월차매출을 구하는 단계입니다.

여기서는 일시 테이블을 사용했기 때문에 임시 테이블을 또 적어주면서 중첩해서 나가야 하는 것이 중요합니다.

--1) dt칼럼을 연월일로 분리하기 (dt칼럼이 date타입이므로, string타입으로 변환한 후 분리하여야 함.)
with daily_price as (
  select dt
  , substr(cast(dt as string), 1, 4) as year
  , substr(cast(dt as string), 6, 2) as month
  , substr(cast(dt as string), 9, 2) as date
  , sum(price) as tot_price
  , count(id) as order_count
  from inlaid-lane-373607.sqlr202301.9_z_chart
  group by dt
)
--2) daliy_price 임시 테이블을 활용하여 2021년과 2022년의 월별 매출 (월차매출) 구하기
, monthly_amount as (
  select year, 
  month,
  sum(tot_price) as amount
  from daily_price
  group by year, month
 )
select * from monthly_amount;

월차매출을 구하는 테이블의 임시 테이블 이름은 monthly_amount로 지정하였고,

위 daily_price테이블에서 year, month칼럼과 구매가격을 연도와 월별로 합한 값인 amount칼럼을 추출하였습니다.

이렇게 된다면 2021년 1월부터 2022년 12월까지의 매출을 월별로 확인할 수 있겠네요.

그러므로 'monthly_amount'테이블에는 위와 같은 정보가 저장되어 있을 것입니다.

여기서의 포인트는 group by절이라고 할 수 있습니다.

group by year, month를 사용함으로써, 연도별로 묶고 월별로 묶어 해당 월에 대한 매출의 합계를 구할 수 있었습니다.

만일 group by 절을 사용하지 않았다면 연월을 그룹화할 수 없었기 때문에 월별 매출을 구할 수 없었을 것입니다.

 

step 3) 2022년의 누계매출 구하기

세 번째 단계는 누계매출을 구하는 단계입니다.

누계매출이란 해당 년도 1월부터 달마다 매출을 점점 누적해 나가는 값입니다.

1월에 1000원, 2월에 2000원의 매출을 기록했다면 누계매출은 3000원이 되는 것이지요.

2021년(전년도)의 누계매출은 Z차트를 작성하는데 필요없는 데이터이므로, 그 부분은 제욓

이것을 위 테이블을 사용해 sql로 쿼리를 작성해 보겠습니다.

--1) dt칼럼을 연월일로 분리하기 (dt칼럼이 date타입이므로, string타입으로 변환한 후 분리하여야 함.)
with daily_price as (
  select dt
  , substr(cast(dt as string), 1, 4) as year
  , substr(cast(dt as string), 6, 2) as month
  , substr(cast(dt as string), 9, 2) as date
  , sum(price) as tot_price
  , count(id) as order_count
  from inlaid-lane-373607.sqlr202301.9_z_chart
  group by dt
)
--2) daliy_price 임시 테이블을 활용하여 2021년과 2022년의 월별 매출 (월차매출) 구하기
, monthly_amount as (
  select year, 
  month,
  sum(tot_price) as amount
  from daily_price
  group by year, month
 )
--3) monthly_amount 임시 테이블을 활용하여 2022년의 매출누계 구하기 
, calc as (
  select year, month, amount,
  sum(case when year = '2022' then amount end)
  over (order by year, month rows unbounded preceding) as agg_amount
  from monthly_amount)
 select * from calc
 order by year, month;

 'calc'라는 임시테이블은 바로 2022년의 누계매출을 구한 테이블이라고 보시면 됩니다.

그리고 앞서 구했던 2021년~2022년월차매출 테이블인 'monthly_amount' 테이블에서year, month칼럼을 뽑아왔고, amount(월차매출)칼럼까지 뽑아왔습니다.

 

그렇다면 이 월차매출 칼럼은 매출누계를 구해기 위해 어떻게 이용할 수 있을까요?바로 sum함수와 over()구문을 활용하면 됩니다.먼저 z차트를 그리는데 올해의 매출누계만 필요하므로 전년도 매출누계는 삭제해도 무방합니다.

그래서 case구문으로 2022년의 데이터만 sum(amount)를 해준 것입니다. 

또한,  연도와 월별로 정렬한 후 rows구문을 활용해 unbounded preceding(이전 행 전체)에 대한 합계를 구할 수 있도록 구문을 지정하였습니다.

이렇게 되면 연월별로 값이 계속 누적되어 나가는 그런 형태가 되지 않을까요?

위 결과를 보시면 2021년에 대한 누계매출은 null값으로 표시된 반면 2022년에 대한 누계매출은 값이 계속 누계되어 나가고 있는 것을 볼 수 있습니다.

 

step 4) 2022년의 이동누계 구하기

네 번째 작업은 2022년의 이동 누계를 구하는 작업입니다.

이때까지는 2022년의 단순 누계의 합계를 구하는 것이었다면,

이제는 2022년 당월부터 11개월 전까지의 이동누계를 구함으로써 최근 1년동안 매출이 얼마나 증가/감소했는지 확인해볼 수 있습니다.

 

* 코드가 길어 보이실 수 있으나, 이는 임시 테이블을 활용한 쿼리이기 때문에 이전의 데이터와 함께 쓰여야 하기 때문입니다.

--4) 이후의 쿼리가 새로 추가된 쿼리이므로 참고하시기 바랍니다.

--1) dt칼럼을 연월일로 분리하기 (dt칼럼이 date타입이므로, string타입으로 변환한 후 분리하여야 함.)
with daily_price as (
  select dt
  , substr(cast(dt as string), 1, 4) as year
  , substr(cast(dt as string), 6, 2) as month
  , substr(cast(dt as string), 9, 2) as date
  , sum(price) as tot_price
  , count(id) as order_count
  from inlaid-lane-373607.sqlr202301.9_z_chart
  group by dt
)
--2) daliy_price 임시 테이블을 활용하여 2021년과 2022년의 월별 매출 (월차매출) 구하기
, monthly_amount as (
  select year, 
  month,
  sum(tot_price) as amount
  from daily_price
  group by year, month
 )
--3) monthly_amount 임시 테이블을 활용하여 2022년의 매출누계 구하기 
, calc as (
  select year, month, amount,
  sum(case when year = '2022' then amount end)
  over (order by year, month rows unbounded preceding) as agg_amount
 --4) monthly_amount 임시 테이블을 활용하여 이전 1년간(현재 달부터 11개월전까지)의 이동년계 구하기 
  ,sum(amount)
  over(order by year, month rows between 11 preceding and current row) as moving_amount
  from monthly_amount
  order by year, month
  )
select * from calc
order by year, month;

이동누계는 calc테이블에 작성하였습니다.

즉, agg_amount칼럼 옆에 이동누계 칼럼 'moving_amount'칼럼을 추가한 것이라 보면 됩니다.

 

calc테이블은 monthly_amount테이블에서 칼럼을 뽑아옵니다.

그러므로 여기서도 monthly_amount테이블에 있는 월별 매출데이터를 활용할 수 있겠습니다.

먼저 sum함수와 over(order by ~ rows ~)구문을 이용해, 월별 매출의 합계를 연월별로 정렬하여 11행 전부터 현재 행까지 뽑아옵니다. 이것을 moving_amount칼럼이라고 지정하였고, 이것이 바로 이동 누계입니다.

 

이렇게 되면 다음과 같은 결과를 볼 수 있습니다.

거의 완성이 다 된 것 같지만, 뭔가 거슬리는 것이 있습니다.

거슬리는 것 중에는 2021년 중간의 null값도 있지만 이는 step3에서 필요 없는 데이터이므로 일부러 null을 처리한 것입니다.

null을 처리한 이유가 무엇이었을까요?

2022년의 z차트를 그릴 때 필요한 것은 2022년의 데이터만 필요하기 때문 아닐까요?

그러므로 2021년의 데이터는 지워야 합니다.

 

step 5) 당해연도 데이터만 추출하기

드디어 마지막입니다. 그렇다면 이제 당해연도(2022년도) 데이터만 추출해 보겠습니다.

쿼리가 길어 보이실 수 있으나, 이 역시 step1~4에서 진행했던 내용을 누적한 것입니다.

임시 테이블로 생성했기 때문에 바로 calc테이블을 호출하면 인식을 하지 못하는 에러가 발생하기 때문입니다. 

--1) dt칼럼을 연월일로 분리하기 (dt칼럼이 date타입이므로, string타입으로 변환한 후 분리하여야 함.)
with daily_price as (
  select dt
  , substr(cast(dt as string), 1, 4) as year
  , substr(cast(dt as string), 6, 2) as month
  , substr(cast(dt as string), 9, 2) as date
  , sum(price) as tot_price
  , count(id) as order_count
  from inlaid-lane-373607.sqlr202301.9_z_chart
  group by dt
)
--2) daliy_price 임시 테이블을 활용하여 2021년과 2022년의 월별 매출 (월차매출) 구하기
, monthly_amount as (
  select year, 
  month,
  sum(tot_price) as amount
  from daily_price
  group by year, month
 )
--3) monthly_amount 임시 테이블을 활용하여 2022년의 매출누계 구하기 
, calc as (
  select year, month, amount,
  sum(case when year = '2022' then amount end)
  over (order by year, month rows unbounded preceding) as agg_amount
 --4) monthly_amount 임시 테이블을 활용하여 이전 1년간(현재 달부터 11개월전까지)의 이동년계 구하기 
  ,sum(amount)
  over(order by year, month rows between 11 preceding and current row) as moving_amount
  from monthly_amount
  order by year, month
  )
--5) 2022년 데이터만 출력하기
select concat(year, '-', month) as year_month
, amount
, agg_amount
, moving_amount
from calc
where year='2022'
order by year_month;

먼저 concat()함수를 사용한 점이 눈에 띕니다.

해당 함수를 사용해 연월을 결합할 수 있습니다. 2022 | 02로 분리되었던 것이 2022-02와 같이 '-'기호를 사이에 두고 이어지게 한 것입니다.

그다음 표시할 월차누계, 매출누계, 이동년계를 같이 표시하도록 합니다.

당연히 모든 데이터가 전부 들어있는 calc테이블에서 뽑아오도록 해야겠지요.

단, 저희들은 2022년만의 데이터를 활용해 z차트를 그릴 것이므로, where절을 활용해 조건을 지정해 줍니다.

그리고 앞서 지정한 연월별로 오름차순(기본값)정렬을 해 줍니다.

 

이렇게 되면 다음과 같은 결과가 나올 것입니다.

이렇게 2022년의 매출 이동년계까지 모두 구해보았습니다.

이 테이블을 저장해서 Phython, excel, r, Tableau등으로 z차트를 그릴 수 있습니다.

과연 해당 테이블을 시각화하면 어떤 인사이트를 얻을 수 있을까요?

이 데이터를 시각화하는 포스팅도 기회가 되면 진행해 보도록 하겠습니다.

 

처음에는 단순 연월, ID, 구매가격만 나와있었지만

이렇게 데이터를 조합해서 새로운 의미 있는 테이블을 만든다는 것이 

처음에는 어렵지만 계속 해 본다면 익숙해 질 것 같습니다.

 

이것으로 'Z차트 준비하기'에 대한 포스팅을 마무리하겠습니다.

이상입니다.

 

---

본 내용은 "데이터 분석을 위한 SQL레시피" 교재를 참고하여 작성되었습니다.