1. SQL 연습
1.1 Z 차트
– ABC 분석은 집중할 제품을 식별합니다. 시계열 분석이 아님
월매출(단기 추이)
'추천 관련글,
누적매출(단기추세)
: 당월부터 전월까지의 누적 매출 합계
이동 연도(장기 추세)
: 해당 월 매출 + 지난 11개월 매출(최근 1년간 누적 합계)
→ 그래프에 표시되지 않는 지난 1년간의 매출 추이도 읽을 수 있습니다.
+) Z차트 해석 예시
사례 1: 정상 상태. 정지 기간. 추가 성장을 위해 필요한 조치
CASE 2 : 매출 성장 추이
CASE 3: 판매 감소 추세. 장단기 쇠퇴 추세, 성장으로 전환해야
11개월 Z차트 도면(1997-06 ~ 1998-04)
0) CTE 테이블 생성
with cte_products_sale as (
select
o.order_date
, to_char( o.order_date, 'yyyy') as year
, to_char( o.order_date, 'mm') as month
, to_char( o.order_date, 'dd') as day
, to_char( o.order_date, 'q') as quarter
, o.order_id, o.customer_id
, od.product_id, od.unit_price as 판매단가, od.quantity, od.discount
, od.unit_price * od.quantity * (1 - od.discount) as sales
, c.category_id, c.category_name
, p.product_name, p.unit_price as 마스터단가, p.discontinued
, s.supplier_id, s.company_name, s.country, s.city
from orders o, order_details od, categories c, products p, suppliers s
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
and p.supplier_id = s.supplier_id
)1) 월 매출 계산
, cte_amount as (
select year, month, category_id, category_name, sum(sales) as 월매출
from cte_products_sale
group by 1,2,3,4
)2) 기준 월 매출 계산
1997.06~1998.04만 0으로 처리
, cte_base_amount as (
select *
, case
when year||month between '199706' and '199804' then 월매출
else 0
end as 기준월매출
from cte_amount
order by 1,2
)3) 누적매출
, cte_agg as (
select *, sum(기준월매출) over(order by year, month) as 매출누계
from cte_base_amount
)
4) 전월 대비 10개월 이체 연결
, cte_pre10_sum as (
select *
, sum(월매출) over(order by year, month rows between 10 preceding and current row) as 이동합계
from cte_agg
)
5) 최종 결과
select category_id, category_name, year||month as 연일, 월매출, 매출누계, 이동합계
from cte_pre10_sum
where 기준월매출 != 0
1.2 그룹 기능 – 그룹화 세트
지표 한번에 추출
– 카테고리별 매출 / 제품별 매출 / 공급자별 매출 / 국가별 매출 / (국가, 도시)별 매출 / 총 매출
0) CTE 쓰기
with cte_products_sale as (
select
o.order_date
, to_char( o.order_date, 'yyyy') as year
, to_char( o.order_date, 'mm') as month
, to_char( o.order_date, 'dd') as day
, to_char( o.order_date, 'q') as quarter
, o.order_id, o.customer_id
, od.product_id, od.unit_price as 판매단가, od.quantity, od.discount
, od.unit_price * od.quantity * (1 - od.discount) as sales
, c.category_id, c.category_name
, p.product_name, p.unit_price as 마스터단가, p.discontinued
, s.supplier_id, s.company_name, s.country, s.city
from orders o, order_details od, categories c, products p, suppliers s
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
and p.supplier_id = s.supplier_id
)1) 카테고리별, 상품명, 공급사명, 공급국명, 공급국 도시명별로 그룹핑하여 집계
, cte_grouping_sets as (
select category_name, product_name, company_name, country, city, sum(sales) 매출액
from cte_products_sale
group by grouping sets (category_name, product_name, company_name, country, city, (country, city), ())
)2) 지정된 컬럼의 그룹별 집계
-- 카테고리별 매출액
, cte_category_amount as (
select category_name, 매출액
from cte_grouping_sets
where category_name is not null
)
-- 제품별 매출액
, cte_product_amount as (
select product_name, 매출액
from cte_grouping_sets
where product_name is not null
)
-- 공급사별 매출액
, cte_company_amount as (
select company_name, 매출액
from cte_grouping_sets
where company_name is not null
)
-- 공급국가별 매출액
, cte_country_amount as (
select country, 매출액
from cte_grouping_sets
where country is not null and city is null
)
-- (공급국가, 도시)별 매출액
, cte_country_city_amount as (
select country, city, 매출액
from cte_grouping_sets
where country is not null and city is not null
)
-- 전체 매출액
, cte_all_amount as (
select 'all' as 전체, 매출액
from cte_grouping_sets
order by 매출액 desc
limit 1
)
-- 카테고리별 매출액 : cte_category_amount
-- 제품별 매출액 : cte_product_amount
-- 공급사별 매출액 : cte_company_amount
-- 공급국가별 매출액 : country
-- (공급국가, 도시)별 매출액 : cte_country_city_amount
-- 전체 매출액 : cte_all_amount
select * from cte_category_amount;
+) 연간, 분기별, 월별, 일별 매출, 그룹별 주문 수
, cte_grouping_sets as (
select
year as 연도
, concat(year,'-',month) as 월
, concat(year,'-',month,'-',day) as 일
, concat(year,'-',quarter) as 분기
, sum(sales) as 매출액
, count(distinct order_id) as 주문건수
from cte_products_sale
group by grouping sets (연도, 월, 일, 분기, ())
)
1.3 그룹 기능 – ROLLUP
카테고리 및 제품별 매출 소계
– 순서 문제
– 대분류, 중분류, 소분류별 순서
, cte_category_product_rollup as (
select category_name, product_name, sum(sales) as 매출액
from cte_products_sale
group by rollup (category_name, product_name)
order by 1,2
)
1.4 그룹 기능 – CUBE
지정된 열 그룹별로 모든 조합에 대해 집계
, cte_cube as (
select year, quarter, sum(sales) as 매출액
from cte_products_sale
group by cube(year, quarter)
)
2. 개인 과제
고객 속성 및 구매 내역 집계(그룹 기능 사용)
with cte_customers_sale as (
select
to_char( o.order_date, 'yyyy') as year
, to_char( o.order_date, 'mm') as month
, to_char( o.order_date, 'dd') as day
, to_char( o.order_date, 'q') as quarter
, c.contact_title, c.country, c.city
, o.order_id, o.customer_id
, od.unit_price * od.quantity * (1 - od.discount) as sales
from customers c, orders o, order_details od, products p, categories c2
where c.customer_id = o.customer_id
and o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c2.category_id
)
-- 고객 속성별 그룹화
, cte_grouping_sets as (
select country, city, contact_title
, concat(year,'-',quarter) as 분기
, sum(sales) as 매출액
, count(distinct order_id) as 주문건수
, count(distinct customer_id) as 주문자수
from cte_customers_sale
group by grouping sets (분기, country, city, (분기, country), (country, city), contact_title, ())
)
-- 1) 분기별 매출액, 주문건수, 주문자수
, cte_quarter_info as (
select 분기, 매출액, 주문건수, 주문자수
from cte_grouping_sets
where 분기 is not null and country is null
)
-- 2) 국가별 매출액, 주문건수, 주문자수
, cte_country_info as (
select country, 매출액, 주문건수, 주문자수
from cte_grouping_sets
where country is not null and city is null
)
-- 3) 도시별 매출액, 주문건수, 주문자수
, cte_city_info as (
select city, 매출액, 주문건수, 주문자수
from cte_grouping_sets
where city is not null and country is null
)
-- 4) (분기, 국가)별 매출액, 주문건수, 주문자수
, cte_quarter_country_info as (
select 분기, country, 매출액, 주문건수, 주문자수
from cte_grouping_sets
where 분기 is not null and country is not null
)
-- 5) (국가, 도시)별 매출액, 주문건수, 주문자수
, cte_country_city_info as (
select country, city, 매출액, 주문건수, 주문자수
from cte_grouping_sets
where country is not null and city is not null
)
-- 6) 직급별 매출액, 주문건수, 주문자수
, cte_title_info as (
select contact_title, 매출액, 주문건수, 주문자수
from cte_grouping_sets
where contact_title is not null
)
-- 7) 전체 매출액, 주문건수, 주문자수
, cte_all_info as (
select 'all' as 전체, 매출액, 주문건수, 주문자수
from cte_grouping_sets
order by 매출액 desc
limit 1
)
-- 분기별 매출액, 주문건수, 주문자수 : cte_quarter_info
-- 국가별 매출액, 주문건수, 주문자수 : cte_country_info
-- 도시별 매출액, 주문건수, 주문자수 : cte_city_info
-- (분기, 국가)별 매출액, 주문건수, 주문자수 : cte_quarter_country_info
-- (국가, 도시)별 매출액, 주문건수, 주문자수 : cte_country_city_info
-- 직급별 매출액, 주문건수, 주문자수 : cte_title_info
-- 전체 매출액, 주문건수, 주문자수 : cte_all_info
select * from cte_quarter_country_info;
