[6] 데이터 가공을 위한 SQL - 시계열 기반으로 데이터 집계하기 : 날짜 별 매출 집계, 이동 평균, 당월 매출 누계, 월별 매출의 작대비, Z차트

---

목차

3. 데이터 가공을 위한 SQL

 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출

  4-1. 시계열 기반으로 데이터 집계하기

4-2. 다면적인 축을 사용해 데이터 집약하기
5. 사용자를 파악하기 위한 데이터 추출
  5-1. 사용자 전체의 특징과 경향 찾기
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

※ Mysql 기준

 

9강 - 시계열 기반으로 데이터 집계하기

9-1. 날짜별 매출 집계하기

그냥 따라 치니 기억에 안남아서 결과 보고 쿼리 치기..

테이블 purchase_log 생성

 

Q1.

날짜 별 매출과 평균 구매액을 집계하는 쿼리 작성하기

-- 날짜 별 구매 건수, 총 합계 금액, 평균 금액 집계
select dt,
	count(order_id) count,
    sum(purchase_amount) total,
    avg(purchase_amount) avg
from purchase_log
group by dt
order by dt
;

- 신청 건수 등과 같이 평균 대상이 없는 경우에는, count를 보고 '추세'확인 가능

---

9-2. 이동 평균을 사용한 날짜별 추이 보기

over(order by~), 이동평균

- 예를 들어 일주일 중 주말에 매출이 크게 변동하는 서비스의 경우, 7일 간의 평균 매출을 사용한 이동 평균으로 표현하는 것이 좋다.

Q2.

날짜별 매출과 7일 이동평균을 집계하는 쿼리 작성하기, strict는 case when으로 확실하게 계산하기

1. seven_day_avg_amount

: 과거 7일분의 데이터를 추출할 수 없는 첫 번째 6일간에 대해 해당 6일만을 가지고 평균을 구함

- n preceding 이용

2. seven_day_avg_amount_strict

: 7일분의 데이터가 없는 경우, 7일 간의 이동 평균을 구할 수 없기에 null로 표시되도록 작성

- case when 이용

-- 7일 이동평균 계산
select dt, 
	sum(purchase_amount) total_amount,
    avg(sum(purchase_amount)) over(
    order by dt rows between 6 preceding and current row ) seven_day_avg_amount,
    case when
	7=count(*) over( --count(*)=7로 했다가 안됐었음
    order by dt rows between 6 preceding and current row)
    then avg(sum(purchase_amount)) over(
    order by dt rows between 6 preceding and current row)
    end seven_day_avg_amount_stict
from purchase_log
group by dt
order by dt;

---

9-3. 당월 매출 누계 구하기

Over(Partition by ~ Order by)

누계를 구할 때에는 윈도 함수를 사용해야 한다.

Q3.

연과 월을 추출하고, 총 합계 구하고, 날짜 순서대로 누계 매출 구하기, 위와 같은 테이블 purchase_log 사용

select dt,
	substring(dt, 1, 7) month, --왜 이름을 year_month로 하면 안되지 ..
    sum(purchase_amount) total_amount,
    sum(sum(purchase_amount)) over ( --total_amount에 한번 더 sum
    partition by substring(dt, 1, 7) 
    order by dt rows between unbounded preceding and current row
    ) agg_amount
from purchase_log
group by dt
order by dt;

- mysql은 substr, substring 모두 가능하며, mid함수와도 동의어이다.

---

Q4.

날짜 분할하고, 날짜 별 합계 금액,주문 수를 계산한 일시테이블 daily_purchase 생성하기

with daily_purchase as
(select dt,
	mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 10) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
from purchase_log
group by dt)
select * from daily_purchase;

- 이전 시작 부분의 쿼리와 결과가 동일하지만, 일시 테이블로 만들어 select 구문 내부 컬럼 의미를 쉽게 이해할 수 있다는 장점이 있음

---

Q5.

당월 누계 매출 구했던 예제와 동일, 앞서 만든 일시 테이블 활용하기

with daily_purchase as
(select dt,
	mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 10) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
from purchase_log
group by dt)
select dt,
	concat(year, '-', month) month, -- year과 month concat해서 연월 생성
    purchase_amount, --일시 테이블에서 sum group by dt되어 있어 바로 사용
    sum(purchase_amount) over(
    partition by year, month order by dt
    ) agg_amount
from daily_purchase
order by dt;

- SQL은 성능이 조금 떨어지더라도 '가독성'과 '재사용성'을 중시해서 작성하는 경우가 많다.

---

9-4. 월별 매출의 작대비 구하기

Sum(case when~)

Q6.

같은 테이블 purchase_log로 일시 테이블 daily_purchase를 생성하고, 월 별 매출과 작년 매출, 올해 매출을 구해서  작대비(올해/작년*100) 계산하기

with daily_purchase as (
	select dt, 
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
from purchase_log
group by dt)
select month,
	sum(case year when '2014' then purchase_amount end) amount_2014,
    sum(case year when '2015' then purchase_amount end) amount_2015,
    sum(case year when '2015' then purchase_amount end) / sum(case year when '2014' then purchase_amount end) * 100 rate
from daily_purchase
group by month
order by month;

왜 값이 다르지

---

9-5. Z 차트로 업적의 추이 확인하기 

Sum(case when~), Over(order by~), z차트

 

Z차트는 '월차 매출', '매출 누계', '이동년계'라는 3개의 지표로 구성되어, 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법이다.

1. 월차 매출

: 매출 합계를 월 별로 집계

2. 매출 누계

: 해당 월의 매출에 이전 월 까지의 매출 누계를 합한 누적 값

• 월차 매출이 일정하면, 매출 누계는 직선이 될 것
• 오른쪽으로 갈 수록 기울기가 급해지는 곡선이라면, 최근 매출이 상승하고 있다는 의미
• 오른쪽으로 갈 수록 기울기가 완만해지는 곡선이라면, 최근 매출이 감소하고 있다는 의미

3. 이동 년계

: 해당 월의 매출에 과거 11개월의 매출을 합한 값 (전년 동월 이후부터~)

• 작년과 올해의 매출이 일정하다면, 이동년계는 직선이 될 것
• 오른쪽 위로 올라간다면 매출이 오르는 경향이 있다는 뜻
• 오른쪽 아래로 내려간다면 매출이 감소하는 경향이 있다는 뜻

매출이 일정한 상태

 

기간 말에 매출이 성장한 상태

작년에 매출이 성장했지만, 올해 성장을 멈추고(이동년계가 내려감) 2년 전과 같은 수준이 된 상태

Z차트를 작성하기 위한 지표 집계하기

2015년의 월차 매출, 매출 누계, 이동 년계 계산하기

누계 매출 - Sum 함수 내부에서 Case 식을 사용해 15년도 매출만 압축하고, Sum 윈도 함수를 사용해서 계산

이동년계 - Sum 윈도 함수 사용, rows btw 11 preceding 활용, 현재 행에서 11행 이전까지의 데이터 합계 구하기

**16년의 모든 날에 매출이 존재할 것이라는 전제로 함 (만약 존재하지 않는 월이 있다면, case식으로 집계 대상 압축 필요)

동일한 원본 테이블 사용, purchase_log

1. daily_purchase 생성 - 일자 별 집계

-- 1. daily_purchase
with daily_purchase as /*일시 테이블 1. daily_purchase*/
	( select dt,
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
    from purchase_log
    group by dt)
select * from daily_purchase;

1. daily_purchase, 일자 별 합계 (앞 예제와 동일)

2. monlthy_amount 생성 - 연도/월 별로 집계

-- monthly_amount
with daily_purchase as /*일시 테이블 1. daily_purchase*/
	( select dt,
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
    from purchase_log
    group by dt),
monthly_amount as /*일시 테이블 2. monthly_amount*/
    ( select year,
    month,
    sum(purchase_amount) amount
    from daily_purchase
    group by year, month)
select * from monthly_amount;

 

2. monthly_amount, 연도-월 별로 집계된 테이블 생성

3. calc_index 생성 - 이동년계 계산

-- 3. calc_index
with daily_purchase as /*일시 테이블 1. daily_purchase*/
	( select dt,
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
    from purchase_log
    group by dt),
monthly_amount as /*일시 테이블 2. monthly_amount*/
    ( select year,
			month,
			sum(purchase_amount) amount
		from daily_purchase
		group by year, month),
calc_index as /*일시 테이블 3. calc_index(이동년계)*/
	( select year,
			month,
			amount,
            sum(case year when '2015' then amount end) over(
            order by year, month rows unbounded preceding) agg_amount, /*이동년계 구하기 이전에 15년도 데이터만 집계하기*/
            sum(amount) over (order by year, month rows between 11 preceding and current row) year_avg_amount
		from monthly_amount /*monthly amount에 이미 월 별로 집계가 되어 있으므로 윈도 함수 내에서 partition by 할 필요 없움*/
        order by year, month) 
select * from calc_index;

3. calc_index, 2015년도만 필터링하고, 전년도 11개월부터 현재까지의 이동년계 계산

4. 최종 쿼리 - 이동년계까지 계산한 테이블에서 2015년으로 필터링하고, 세 가지 지표 값 확인

with daily_purchase as /*일시 테이블 1. daily_purchase*/
	( select dt,
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
    from purchase_log
    group by dt),
monthly_purchase as /*일시 테이블 2. monthly_purchase*/
    ( select year,
			month,
			sum(purchase_amount) amount
		from daily_purchase
		group by year, month),
calc_index as /*일시 테이블 3. calc_index(이동년계)*/
	( select year,
			month,
			amount,
            sum(case year when '2015' then amount end) over(
            order by year, month rows unbounded preceding) agg_amount, /*이동년계 구하기 이전에 15년도 데이터만 집계하기*/
            sum(amount) over (order by year, month rows between 11 preceding and current row) year_avg_amount
		from monthly_purchase /*monthly purchase에 이미 월 별로 집계가 되어 있으므로 윈도 함수 내에서 partition by 할 필요 없움*/
        order by year, month) 
select 
	concat(year, '-', month) date,
    amount,
    agg_amount,
    year_avg_amount
from calc_index
where year = '2015'
order by date;

최종 일시 테이블 calc_index에서 2015년 데이터만 압축, 15년 매출에 대한 z차트 지표 집계가 된 모습

---

9-6. 매출을 파악할 때 중요 포인트

매출과 관련된 지표를 집계해서 매출 변화의 '이유'를 안다면, 관련 분석을 통해 확인하고 개선할 수 있다.

매출 관련 지표 집계하기

Steps: monthly_purchase까지 일시 테이블 생성 -> 합계/누계합 구하기 -> 12개월 전 매출 구하기 -> 작년비 구하기 

1. monthly_purchase 까지 일시 테이블 생성하기

with daily_purchase as (
	select dt,
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
	from purchase_log
    group by dt),
    monthly_purchase as (
    select 
	year,
    month,
	sum(orders) as orders,
    avg(purchase_amount) as avg_amount,
    sum(purchase_amount) as monthly
	from daily_purchase
    group by year, month)
select * from monthly_purchase;

monthly_purchase

2. 아래와 같은 지표 집계하기

with daily_purchase as (
	select dt,
    mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    sum(purchase_amount) purchase_amount,
    count(order_id) orders
	from purchase_log
    group by dt),
    monthly_purchase as (
    select 
	year,
    month,
    sum(orders) orders,
    avg(purchase_amount) avg_amount,
    sum(purchase_amount) monthly
	from daily_purchase
    group by year, month)
select
	concat(year, '-', month) date,
    orders,
    avg_amount,
    monthly,
    sum(monthly) over(
    partition by year order by month rows unbounded preceding) agg_amount, /*연도 별 월 별 누계*/
    lag(monthly, 12) over(
    order by year, month) last_year,
    (monthly / lag(monthly, 12) over(order by year,month)) * 100 rate
from monthly_purchase
order by date;

- 앞서 전년동월 매출을 구할 때, case식 내부에서 대상 년도를 검색했지만, 여기에서는 더 일반적인 방법인 lag함수를 사용함

- 같은 결과를 도출하기 위해 윈도 함수 없이 지표마다 select구문으로 만들고 결과를 하나의 테이블로 결합하는 방법이 있지만, select구문을 여러 개 사용하여 데이터를 여러 번 읽어들이기 때문에 성능적으로 바람직하지 않다.

- BigQuery의 경우 데이터를 읽어들일 때 과금이 발생하여 불필요한 데이터를 읽어 들이는 것을 줄여야 한다.

- monthly_purchase 테이블을 만들면 가독성이 향상되나, 꼭 with 구문으로 만들 필요 없이 하나의 select구문으로 결과 계산이 가능하다.