[sql] 날짜별 이동평균, 월간 누적매출, 매출 z차트 만들기

 

 

날짜별 이동평균, 월간 누적매출

 

DROP TABLE IF EXISTS purchase_log;
CREATE TABLE purchase_log(
dt varchar(255)
, order_id integer
, user_id varchar(255)
, purchase_amount integer
);

INSERT INTO purchase_log
VALUES
('2014-01-01', 1, 'rhwpvvitou', 13900)
, ('2014-01-01', 2, 'hqnwoamzic', 10616)
, ('2014-01-02', 3, 'tzlmqryunr', 21156)
, ('2014-01-02', 4, 'wkmqqwbyai', 14893)
, ('2014-01-03', 5, 'ciecbedwbq', 13054)
, ('2014-01-03', 6, 'svgnbqsagx', 24384)
, ('2014-01-03', 7, 'dfgqftdocu', 15591)
, ('2014-01-04', 8, 'sbgqlzkvyn', 3025)
, ('2014-01-04', 9, 'lbedmngbol', 24215)
, ('2014-01-04', 10, 'itlvssbsgx', 2059)
, ('2014-01-05', 11, 'jqcmmguhik', 4235)
, ('2014-01-05', 12, 'jgotcrfeyn', 28013)
, ('2014-01-05', 13, 'pgeojzoshx', 16008)
, ('2014-01-06', 14, 'msjberhxnx', 1980)
, ('2014-01-06', 15, 'tlhbolohte', 23494)
, ('2014-01-06', 16, 'gbchhkcotf', 3966)
, ('2014-01-07', 17, 'zfmbpvpzvu', 28159)
, ('2014-01-07', 18, 'yauwzpaxtx', 8715)
, ('2014-01-07', 19, 'uyqboqfgex', 10805)
, ('2014-01-08', 20, 'hiqdkrzcpq', 3462)
, ('2014-01-08', 21, 'zosbvlylpv', 13999)
, ('2014-01-08', 22, 'bwfbchzgnl', 2299)
, ('2014-01-09', 23, 'zzgauelgrt', 16475)
, ('2014-01-09', 24, 'qrzfcwecge', 6469)
, ('2014-01-10', 25, 'njbpsrvvcq', 16584)
, ('2014-01-10', 26, 'cyxfgumkst', 11339)
;

/* 날짜별 매출 이동 평균 */

select dt
,sum(a.purchase_amount)
, avg(sum(purchase_amount))
over(order by dt rows between 6 preceding and current row) -- 일주일간 행 간격 정해주기
,case
when 7 = count(*)
over(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 as seven
from purchase_log a with (nolock)
group by
dt


/* 날짜별 매출 월간 누계 매출 */

select dt
,convert(char(7), dt, 10) as year_month
,sum(purchase_amount)
,sum(sum(purchase_amount))
over(partition by convert(char(7), dt, 10) order by dt asc)
from purchase_log a with (nolock)
group by
dt

 

매출 z차트 만들기

 

/* 매출 z차트 만들기 */

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

--- select * from daily_purchase ;

, 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 concat(year, '-', month) as year_month
,orders
,avg_amount as avg_amount
,monthly
,sum(monthly)
over(partition by year order by month rows unbounded preceding) -- 12개월
,lag(monthly, 12)
over(order by year, month) as last_year
from monthly_purchase