📍 참고 사항
💡 1.이동평균을 구할 때
◾참고자료
◾주의점
특정기간의 이동평균을 구하는 방법, over을 이용하자
- 윈도우 함수와 창을 이용해서 n일 이동평균을 구해야한다.
◾코드
select *, avg(컬럼) over (order by date rows between n-1 preceding and current row) as moving_average
from 테이블명;
◾예시: 7일 이동평균을 구할 때
select * , sum(total_amount) over (order by date rows between 6 preceding and current row) as moving_average_7days
from purchase_log;
◾주의: 1-6일차때도 값이 출력된다. 삭제하고 싶다면 다음과 같이 진행해야한다.
case when 구문을 사용해서 count가 7일 때만 null값이 아니도록 출력하면 된다.
select * ,
case when count(purchase_amount) over(order by dt rows between 6 preceding and current row)=7
then sum(purchase_amount) over(order by dt rows between 6 preceding and current row)
else null
end as moving_average_7days
from purchase_log;
💡 2.매출 누계를 구할 때
◾lag 함수
: 특정 시점의 이전 값을 구하고 싶다면 lag 함수를 활용해야한다.
- partition by : 지정시 group 별로 행 값을 가져온다.
◾참고자료
◾예제
12번째 이전값 구하기
select lag(a_value,12) over(partition by a_type order by a_date asc| desc)
from a
📍 실습 ) 138 P - 160p: 시계열 기반으로 데이터 집게하기
💡 1.날짜별 매출 집계하기
코드
select dt ,sum(purchase_amount) as total_purchase , round(avg(purchase_amount),2) as avg_purchase
from purchase_log group by dt order by 1 asc;
💡 2.이동평균을 사용한 날짜별 추이 보기
개념
-- 이동평균(moving average)이란, 말 그대로 어떤 것이 방향성을 가지고 움직일 때, 이동하면서 구해지는 평균
-- 매출이 상승한 경향이 있는지? 하락한 경향이 있는지 확인하기 위해 사용한다. (7일동안 평균 매출을 사용한 7일 이동평균으로 표현하기)
◾코드
-- https://kimsyoung.tistory.com/entry/SQL%EC%97%90%EC%84%9C-%EC%9D%B4%EB%8F%99%ED%8F%89%EA%B7%A0-%EA%B5%AC%ED%98%84%ED%95%98%EB%8A%94-%EB%B2%95
select * ,sum(purchase_amount) over(order by dt rows between 6 preceding and current row) as 7_moving_average
from purchase_log;
select * ,
case when count(purchase_amount) over(order by dt rows between 6 preceding and current row)=7
then sum(purchase_amount) over(order by dt rows between 6 preceding and current row)
else null
end as moving_average_7days
from purchase_log;
💡 3.당월 매출 누계 구하기
-- 모르겠음
💡 4.월별 매출의 작대비 구하기
◾개념
-- 작대비란? 작년 대비 비율을 의미한다.
◾코드
with
cte_1(month,year_ago,purchase_amount) as (select date_format(dt, '%Y-%m') as month ,date_format(date_add(dt,interval -1 year) ,'%Y-%m') as year_ago, purchase_amount from purchase_log),
cte_2(month,purchase_amount) as (select date_format(dt, '%Y-%m') as month , purchase_amount from purchase_log)
select month, concat(round(a_purchase_amount *100/ b_purchase_amount,2),'%') as rate
from (select a.month, a.purchase_amount as a_purchase_amount , b.purchase_amount as b_purchase_amount from cte_1 a inner join cte_2 b on a.year_ago= b.month) t;
💡 5.z 차트 작성하기 (역시 매출 누계가 있어 다음번에 진행하기..)
◾개념
-- 참고: z 차트) 계절 트렌드를 제외하고 매출의 성자 또는 쇠퇴를 다양한 각도에서 살펴본다면, z 차트를 사용하는 것이 좋다.
💡 6. 매출을 파악할 때 중요 포인트
◾개념
-- tip: 주변 데이터를 함께 고려해서 리포트를 작성해야한다.
-- ex: 판매 횟수에 변화가 있다면? 방문 횟수, 상품 횟수, 회원등록수를 확인하기
매출 데이터 만들기
◾개념
-- 판매 월 | 판매 횟수 | 평균 구매액 | 매출액 | 누계 매출액 | 작년 매출액 | 작년비
-- tip :
-- 1. lag 함수를 사용해서 n 개월 전의 값을 구하기 (값이 없다면 자동 null이 된다.)
-- 2. with 구문을 통해서 가독성 있게 최종결과물을 확인하기
◾코드
-- 판매 월 | 판매 횟수 | 평균 구매액 | 매출액 | 누계 매출액 | 작년 매출액 | 작년비
with cte_1(month, count_order,avg_amount, total_purchase) as (select date_format(dt, '%Y-%m') as month, count(order_id) as count_order ,avg(purchase_amount) as avg_amount,
sum(purchase_amount) as total_purchase
from purchase_log
group by date_format(dt, '%Y-%m')),
cte_2( year_ago, total_purchase_1) as (select
date_format(date_add(dt, interval +1 year),'%Y-%m') as year_ago,sum(purchase_amount)
from purchase_log
group by year_ago),
cte_3(판매월,판매횟수,평균구매액,매출액,작년매출액,작년비) as (select month as 판매월, count_order as 판매횟수, round(avg_amount,2) as 평균구매액,
total_purchase as 매출액,ifnull(total_purchase_1,0) as 작년매출액 ,round((( total_purchase - ifnull(total_purchase_1,0)) 100/(ifnull(total_purchase_1,0))),2) as 작년비
from (select from cte_1 a left join cte_2 b on a.month = b.year_ago) t)
select 판매월,
판매횟수, round(평균구매액,0) as 평균구매액, 매출액, 작년매출액 , concat(작년비, '%') as 작년비
from cte_3;
