데이터 분석을 위한 SQL 레시피 4장 공부

매출을 파악하기 위한 데이터 추출

이동평균을 사용한 날짜별 추이 보기

# 1.
round(avg(sum(purchase_amount))) over(order by dt rows between 6 preceding and current row)

# 2.
round(avg(sum(purchase_amount)) over(order by dt rows between 6 preceding and current row))

# -> 2번으로 해야함. (집계 함수는 중첩되어 호출 할 수 없음)

1번으로 적어서 계속 오류를 찾아야 했다. 조심!

select dt
	-- 이 날 매출
	, sum(purchase_amount) as total_amount
    
	-- 최근 최대 7일 동안의 평균 계산
	, round(avg(sum(purchase_amount)) over(order by dt rows between 6 preceding and current row)) as seven_day_avg
	
    -- 최근 7일 동안의 평균 확실하게 계산
	, case
		when 
			7 = count(*) 
			over(order by dt rows between 6 preceding and current row) 
		then 
			round(avg(sum(purchase_amount))
			over(order by dt rows between 6 preceding and current row))
	end as seven_day_avg_strict
from purchase_log
group by dt
order by dt;

 

 

당월 매출 누계 구하기

날짜별로 매출뿐만 아니라, 해당 월의 누계도 구하기

• 가독성과 재사용성을 중시해서 작성하기

-- 날짜별 매출 임시 테이블
with daily_purchase as (
	select dt
		-- 연, 월, 일 각각 추출
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as day
		-- 이 날 매출
		, sum(purchase_amount) as purchase_amount
		-- 이 날 주문 수
		, count(order_id) as orders
	from purchase_log 
	group by dt
)

--select *
--from daily_purchase
--order by dt;

-- 당월 누계 매출 집계하기
select dt
	-- 연-월
	, concat(year, '-', month) as year_month
	-- 이 날 매출
	, purchase_amount
	-- 이 날까지 당월 누계 매출
	, sum(purchase_amount) over (partition by year, month order by dt rows unbounded preceding) as agg_amount
from daily_purchase
order by dt;

 

https://chaemi720.tistory.com/372

Z차트를 작성하는 SQL 쿼리

 

카테고리별 매출과 소계 계산하기

• 소계 : 일부를 더한 것
• 대분류, 소분류, 매출 형태의 데이터로 출력
  • 계층별로 집계한 결과를 UNION ALL 구문으로 하나의 테이블로 합치기

-- 소 카테고리의 매출 집계
with sub_category_amount as (
	select category as category
		, sub_category as sub_category
		, sum(price) as amount
	from purchase_detail_log 
	group by category, sub_category
)
-- 대 카테고리의 매출 집계
, category_amount as (
	select category
		, 'all' as sub_category
		, sum(price) as amount
	from purchase_detail_log
	group by category
)
-- 전체 매출 집계
, total_amount as (
	select 'all' as category
		, 'all' as sub_category
		, sum(price) as amount
	from purchase_detail_log 
) 

-- 데이터 결합
		  select category, sub_category, amount from sub_category_amount
union all select category, sub_category, amount from category_amount
union all select category, sub_category, amount from total_amount;

• UNION ALL 사용하면 테이블을 여러 번 불러오고, 데이터를 결합하는 비용도 발생하므로 성능이 좋지 않다.
  • ROLLUP을 사용해 구현
    • 대부분의 분석리포트는 소계(중간집계)와 전체합계가 필요 > ROLLUP이 가장효율적 
    • ROLLUP을 사용하면 소계를 계산할 때 레코드 집계 키가 NULL이 되므로, COALESCE로 해결
    • ROLLUP은 GROUP BY 뒤에 ROLLUP을 적어 사용
    • ROLLUP(A, B, C, D)사용시 다음과 같은 데이터들이 조회
      • GROUP BY 된 A+B+C+D 별 데이터
      • A+B+C별 소계 데이터
      • A+B별 소계 데이터
      • A별 소계데이터
      • 전체합계

select coalesce(category, 'all') as category
	, coalesce(sub_category, 'all') as sub_category
	, sum(price) as amount
from purchase_detail_log 
group by rollup(category, sub_category);

 

팬 차트로 상품의 매출 증가율 확인

• 팬 차트
  • 어떤 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 해주는 그래프
  • 변화가 백분율로 표시되므로, 작은 변화를 쉽게 인지하고 상황을 판단 할 수 있음
  • 주의) 어떤 시점에서의 매출 금액을 기준으로 채택할 것인가에 따라 성장 경향인지 또는 쇠퇴 경향인지 판단이 크게 달라짐

• FIRST_VALUE(컬럼명) OVER()
  • 선택된 레코드들 중 특정한 컬럼을 기준으로 시작 값(처음 값)을 출력
  • <-> LAST_VALUE(컬럼명)

with daily_category_amount as (
	select dt
		, category
		-- 연, 월, 일
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as date
		-- 이 날 카테고리별 매출
		, sum(price) as amount
	from purchase_detail_log 
	group by dt ,category
)

, monthly_category_amount as (
	select concat(year, '-', month) as year_month
		, category
		-- 당월 카테고리별 매출
		, sum(amount) as amount
	from daily_category_amount
	group by year, month, category
)

select year_month
	, category
	, amount
	-- 기준 월 매출
	, first_value(amount)
		over(partition by category order by year_month, category rows unbounded preceding) as base_amount
	-- 기준 월 매출 대비
	, 100.0*amount
	/ first_value(amount)
		over(partition by category order by year_month, category rows unbounded preceding) as rate
from monthly_category_amount
order by year_month, category;

 

히스토그램으로 구매 가격대 집계

• 최댓값, 최솟값, 범위를 구하는 쿼리

with stats as (
	select max(price) + 1 as max_price
		, min(price) as min_price
		-- 금액의 범위
		, max(price) + 1 - min(price) as range_price
		-- 계층 수
		, 10 as bucket_num
	from purchase_detail_log
)

• 데이터의 계층을 구하는 쿼리
  • SQL 관련 시스템은 대부분 히스토그램을 작성하는 함수가 표준 제공
    • PostgreSQL은 width_bucket함수

, purchase_log_with_bucket as (
	select price
		, min_price
		-- 정규화 금액 : 대상 금액에서 최소 금액을 뺀 값
		, price - min_price as diff
		-- 계층 범위 : 금액 범위를 계층 수로 나눈 것
		, 1.0*range_price/bucket_num as bucket_range
		-- 계층 판정
		, floor(1.0*(price-min_price)/(1.0*range_price/bucket_num))+1 as bucket
        -- 함수
        -- width_bucket(price, min_price, max_price, bucket_num) as bucket
	from purchase_detail_log, stats
)

select *
from purchase_log_with_bucket
order by price;

• 도수 계산

select bucket
	-- 계층의 하한과 상한 계산
	, min_price + bucket_range*(bucket-1) as lower_limit
	, min_price + bucket_range*bucket as upper_limit
	-- 도수 세기
	, count(price) as num_purchase
	-- 합계 금액 계산
	, sum(price) as total_amount
from purchase_log_with_bucket
group by bucket, min_price, bucket_range
order by bucket;

• 매출의 상승/하락의 원인을 조사하라는 지시를 받았을 때는 '최근 매출'과 '과거 매출'을 기반으로 두 개의 히스토그램을 작성하고, 두 기간 사이의 어떤 부분에 차이가 있는지 확인하기