📌 1️⃣ 1997년 6월 ~ 1998년 4월 Z차트 📌
-- order_details : unit_price, quantity, discount, 월별매출, 매출누계, 이동연계
-- orders : order_date, year, month
with cte_z_sales as (
select to_char(o.order_date, 'yyyy-mm') order_ym
, sum(od.quantity * od.unit_price * (1-od.discount)) sales
from orders o, order_details od
where o.order_id = od.order_id
group by order_ym
order by 1
)
✅ 이동연계 산출
, cte_z_moving_sum as (
select *
, round(sum(sales) over(order by order_ym rows between 10 preceding and current row)::NUMERIC, 2) as 이동연계
from cte_z_sales
)
✅ 매출누계 산출
, cte_z_chart as (
select order_ym
, sales as 월별매출
, sum(sales) over(ORDER BY order_ym) as 매출누계
, 이동연계
from cte_z_moving_sum
where order_ym >= '1997-06' and order_ym <= '1998-04'
)
select * from cte_z_chart;
📌 2️⃣ 카테고리 별 1997년 6월 ~ 1998년 4월 Z차트 📌
-- order_details : unit_price, quantity, discount, 월별매출, 매출누계, 이동연계
-- orders : order_date, year, month
-- products
-- categories : category_id, category_name
with cte_z_sales as (
select c.category_id , c.category_name
, to_char(o.order_date, 'yyyy-mm') order_ym
, sum(od.quantity * od.unit_price * (1-od.discount)) sales
from orders o, order_details od, products p , categories c
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
group by order_ym, c.category_id , c.category_name
order by c.category_id , order_ym
)
✅ 이동연계 산출
, cte_z_moving_sum as (
select *
, round(sum(sales) over(partition by category_id order by order_ym rows between 10 preceding and current row)::NUMERIC, 2) as 이동연계
from cte_z_sales
)
✅ 매출누계 산출
, cte_z_chart as (
select category_id , category_name , order_ym
, sales as 월별매출
, sum(sales) over(partition by category_id order by order_ym) as 매출누계
, 이동연계
from cte_z_moving_sum
where order_ym >= '1997-06' and order_ym <= '1998-04'
)
select * from cte_z_chart;
GROUPING SETS
📌 1️⃣ 제품 속성 별 grouping sets 📌
-- order_details : unit_price, quantity, discount, amount
-- products : product_name, unit_price, discontinued
-- categories : category_id, category_name
-- suppliers : supplier_id, company_name, country, city
with cte_products_sales as (
select c.category_id , c.category_name
, od.quantity , od.unit_price , od.discount
, od.quantity * od.unit_price * (1-od.discount) as amount
, p.product_name , p.unit_price as master_unit_price, p.discontinued
, s.supplier_id , s.company_name , s.country , s.city
from order_details od, products p , categories c , suppliers s
where d.product_id = p.product_id
and p.category_id = c.category_id
and p.supplier_id = s.supplier_id
)
✅ 제품 속성 별 매출액 : grouping sets
, cte_grouping_sets as (
select category_name, product_name, company_name, country, city, sum(amount) as sales
from cte_products_sales
group by grouping sets(category_name, product_name, company_name, country, city, (country, city), ())
)
✅ 카테고리 별 매출액 : cte_category_amount
, cte_category_amount as (
select category_name, sales
from cte_grouping_sets
where category_name is not null
)
✅ 제품 별 매출액 : cte_product_amount
, cte_product_amount as (
select product_name, sales
from cte_grouping_sets
where product_name is not null
)
✅ 공급사 별 매출액 : cte_company_amount
, cte_company_amount as (
select company_name, sales
from cte_grouping_sets
where company_name is not null
)
✅ 공급 국가 별 매출액 : cte_country_amount
, cte_country_amount as (
select country, sales
from cte_grouping_sets
where country is not null and city is null
)
✅ (공급 국가, 도시) 별 매출액 : cte_country_city_amount
, cte_country_city_amount as (
select country, city, sales
from cte_grouping_sets
where country is not null and city is not null
order by country, city
)
✅ 전체 매출액 : cte_all_amount
, cte_all_amount as (
select 'all' as 전체 , sales
from cte_grouping_sets
order by sales desc
limit 1
)
-- 카테고리 별 매출액 : cte_category_amount
-- 제품 별 매출액 : cte_product_amount
-- 공급사 별 매출액 : cte_company_amount
-- 공급 국가 별 매출액 : cte_country_amount
-- (공급 국가, 도시) 별 매출액 : cte_country_city_amount
-- 전체 매출액 : cte_all_amount
select * from cte_all_amount;
1️⃣ 카테고리 별 매출액 : cte_category_amount / 2️⃣ 제품 별 매출액 : cte_product_amount / 3️⃣ 공급사 별 매출액 : cte_company_amount
4️⃣ 공급 국가 별 매출액 : cte_country_amount / 5️⃣ (공급 국가, 도시) 별 매출액 : cte_country_city_amount 5️⃣ 전체 매출액 : cte_all_amount
📌 2️⃣ 시기별 grouping sets 📌
-- order_details : unit_price, quantity, discount, amount
-- orders : order_date, year, month, day, quarter, order_id
with cte_products_sales 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
, o.order_id
, od.quantity , od.unit_price , od.discount
, od.quantity * od.unit_price * (1-od.discount) as amount
from orders o, order_details od
where o.order_id = od.order_id
)
✅ 시기 별 매출액 : grouping sets
, cte_grouping_sets as (
select year, quarter, month, day, sum(amount) as sales, count(distinct order_id) as order_count
from cte_products_sales
group by grouping sets(year, (year, quarter), (year, month), (year, month, day), ())
order by year, quarter, month, day
)
✅ 연도 별 매출액 : cte_year_amount
, cte_year_amount as (
select year, sales, order_count
from cte_grouping_sets
where year is not null
and quarter is null
and month is null
and day is null
)
✅ 분기 별 매출액 : cte_quarter_amount
, cte_quarter_amount as (
select year, quarter, sales, order_count
from cte_grouping_sets
where year is not null
and quarter is not null
and month is null
and day is null
)
✅ 월 별 매출액 : cte_month_amount
, cte_month_amount as (
select year, month, sales, order_count
from cte_grouping_sets
where year is not null
and quarter is null
and month is not null
and day is null
)
✅ 일 별 매출액 : cte_day_amount
, cte_day_amount as (
select year, month, day, sales, order_count
from cte_grouping_sets
where year is not null
and quarter is null
and month is not null
and day is not null
)
-- 연도 별 매출액 : cte_year_amount
-- 분기 별 매출액 : cte_quarter_amount
-- 월 별 매출액 : cte_month_amount
-- 일 별 매출액 : cte_day_amount
select * from cte_year_amount;
1️⃣ 연도 별 매출액 : cte_year_amount / 2️⃣ 분기 별 매출액 : cte_quarter_amount
3️⃣ 월 별 매출액 : cte_month_amount / 4️⃣ 일 별 매출액 : cte_day_amount
ROLLUP
📌 (카테고리, 제품), 시기 별 매출액 rollup 📌
-- order_details : unit_price, quantity, discount, amount
-- products : product_name, unit_price, discontinued
-- categories : category_id, category_name
-- suppliers : supplier_id, company_name, country, city
with cte_products_sales as (
select c.category_name ,p.product_name
, 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
, od.unit_price * od.quantity *(1-discount) as amount
, od.quantity , od.order_id
, s.company_name , s.country , s.city
from orders o , order_details od , products p , categories c, suppliers s
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
and s.supplier_id = p.supplier_id
)
✅ 카테고리, 제품 별 매출액 소계 : cte_category_product_rollup
, cte_category_product_rollup as (
select category_name, product_name, sum(amount) as sales
from cte_products_sales
group by rollup(category_name, product_name)
order by 1, 2
)
✅ 시기 별 매출액 소계 : cte_period_rollup
, cte_period_rollup as (
select year, quarter, month, sum(amount) as sales
from cte_products_sales
group by rollup(year, quarter, month)
order by 1, 2, 3
)
-- 카테고리, 제품 별 매출액 소계 : cte_category_product_rollup
-- 시기 별 매출액 소계 : cte_period_rollup
select * from cte_period_cube;
1️⃣ 카테고리, 제품 별 매출액 소계 : cte_category_product_rollup / 2️⃣ 시기 별 매출액 소계 : cte_period_rollup
CUBE
📌 (카테고리, 제품), 시기 별 매출액 cube 📌
-- order_details : unit_price, quantity, discount, amount
-- products : product_name, unit_price, discontinued
-- categories : category_id, category_name
-- suppliers : supplier_id, company_name, country, city
with cte_products_sales as (
select c.category_name ,p.product_name
, 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
, od.unit_price * od.quantity *(1-discount) as amount
, od.quantity , od.order_id
, s.company_name , s.country , s.city
from orders o , order_details od , products p , categories c, suppliers s
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
and s.supplier_id = p.supplier_id
)
✅ 시기 별 매출액 집계 : cte_period_cube
, cte_period_cube as (
select year, quarter, month, sum(amount) as sales
from cte_products_sales
group by cube(year, quarter, month)
order by 1, 2, 3
)
-- 시기 별 매출액 집계 : cte_period_cube
select * from cte_period_cube;
1️⃣ 시기 별 매출액 집계 : cte_period_cube
📝 오늘의 과제
과제 : 고객 속성, 구매 이력에 대한 집계 (그룹함수 이용)
📌 과제 : 고객 속성, 구매 이력에 대한 집계 📌
-- customers : customer_id, company_name, contact_name, contact_title, country, city
-- orders : order_id
-- order_details : unit_price, quantity, discount, amount
with cte_customers_sales as (
select c.customer_id , c.company_name , c.contact_title , c.country , c.city
, o.order_id
, od.quantity * od.unit_price * (1-od.discount) as amount
from orders o , order_details od , customers c
where o.order_id = od.order_id
and o.customer_id = c.customer_id
)
✅ 고객 속성 별 매출액 : grouping sets
, cte_grouping_sets as (
select company_name, contact_title, country, city, sum(amount) as sales
from cte_customers_sales
group by grouping sets(company_name, contact_title, country, city, (country, city), ())
order by 1,2,3
)
✅ 회사 별 매출액 : cte_company_sale
, cte_company_sale as (
select company_name, sales
from cte_grouping_sets
where company_name is not null
)
✅ 직급 별 매출액 : cte_title_sale
, cte_title_sale as (
select contact_title, sales
from cte_grouping_sets
where contact_title is not null
)
✅ 국가 별 매출액 : cte_country_sale
, cte_country_sale as (
select country, sales
from cte_grouping_sets
where country is not null
and city is null
)
✅ (국가, 도시) 별 매출액 : cte_country_city_sale
, cte_country_city_sale as (
select country, city, sales
from cte_grouping_sets
where country is not null
and city is not null
)
✅ 직급 별 매출액 소계 : cte_title_rollup
, cte_title_rollup as (
select contact_title, sum(amount) as sales
from cte_customers_sales
group by rollup(contact_title)
order by 1
)
✅ 국가 별 매출액 소계 : cte_country_rollup
, cte_country_rollup as (
select country, sum(amount) as sales
from cte_customers_sales
group by rollup(country)
order by 1
)
✅ (국가, 도시) 별 매출액 소계 : cte_country_city_rollup
, cte_country_city_rollup as (
select country, city, sum(amount) as sales
from cte_customers_sales
group by rollup(country, city)
order by 1,2
)
-- 회사 별 매출액 : cte_company_sale
-- 직급 별 매출액 : cte_title_sale
-- 국가 별 매출액 : cte_country_sale
-- (국가, 도시) 별 매출액 : cte_country_city_sale
-- 직급 별 매출액 소계 : cte_title_rollup
-- 국가 별 매출액 소계 : cte_country_rollup
-- (국가, 도시) 별 매출액 소계 : cte_country_city_rollup
select * from cte_country_city_rollup;
1️⃣ 회사 별 매출액 : cte_company_sale / 2️⃣ 직급 별 매출액 : cte_title_sale
3️⃣ 국가 별 매출액 : cte_country_sale / 4️⃣ (국가, 도시) 별 매출액 : cte_country_city_sale
5️⃣ 직급 별 매출액 소계 : cte_title_rollup / 6️⃣ 국가 별 매출액 소계 : cte_country_rollup / 7️⃣ (국가, 도시) 별 매출액 소계 : cte_country_city_rollup
