웅진 STARTERS 부트캠프
10주 5일차 TIL 정리
WoodenStella
2023. 4. 14. 17:39
10주 4일차에는 preceding과 case문을 활용한 실습을 진행하고, 그룹 함수에 대해 학습했다.
※ Z 차트 ※
1. cte 테이블 생성
--1. cte 테이블 생성
with cte_order as (
select o.order_date , od.unit_price ,od.quantity
, od.discount, c.category_name, c.category_id
, to_char(o.order_date,'yyyy') as year
, to_char(o.order_date,'mm') as month
, to_char(o.order_date,'dd') as day
, od.unit_price * od.quantity * (1-od.discount) as 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 c.category_id = p.category_id
)
2. year와 month를 concat으로 합쳐주고, 1997년 6월부터 1998년 4월까지의 데이터 외엔 전부 0으로 표시하도록 한다.
--1. cte 테이블 생성
with cte_order as (
/*(생략)*/
)
-- 2. year와 month합, 기준월매출 생성
select *, concat (year, month) as year_month
, case when concat (year, month) between '199706' and '199804' then sales else 0 end as 기준월매출
from cte_order
where category_id = 6
order by year, month
이후 이 또한 cte테이블로 생성한다.
--1. cte 테이블 생성
with cte_order as (
/*(생략)*/
)
-- 2. year와 month합, 기준월매출 생성
, cte_ym as(
select *, concat (year, month) as year_month
, case when concat (year, month) between '199706' and '199804' then sales else 0 end as 기준월매출
from cte_order
where category_id = 6
order by year, month
)
3. 월별매출과 기준월매출로만 진행한 누계, between - preceding을 활용한 이동년계를 각각 컬럼으로 생성한다.
--1. cte 테이블 생성
with cte_order as (
/*(생략)*/
)
-- 2. year와 month합, 기준월매출 생성
, cte_ym as(
/*(생략)*/
)
-- 3. 월별매출, 매출누계, 이동년계 한 테이블에 정리
select year_month
-- , sum(기준월매출)
, round(sum(sales)::numeric) as 월별매출
, round(sum(sum(기준월매출)) over (order by year_month)::numeric) as 매출누계
, round(sum(sum(sales)) over (order by year_month rows
between 10 preceding and current row)::numeric) as 이동년계
from cte_ym
group by year_month
limit 11 offset 11
이를 csv로 추출해 시각화하자.
4. 태블로 시각화
year_month를 열에,
나머지 컬럼 중 하나를 행에 놓은 후, 나머지 두 개를 행의 헤더에 드랍한다.
축에다가 끌어넣는 방법으로 태블로에서 z chart 시각화 가능
※ 그룹 함수 ※
1. GROUPING SETS
1) cte 테이블 생성
-- 0. cte테이블 생성
with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date
, to_char(o.order_date,'yyyy') as year
, to_char(o.order_date,'q') as quarter
, to_char(o.order_date,'mm') as month
, to_char(o.order_date,'dd') as day
, 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_id , p.product_name , p.unit_price as 마스터단가, p.discontinued as 단종여부
, s.supplier_id , s.company_name , s.country , s.city
from northwind.products p , northwind.categories c ,northwind.orders o
, northwind.order_details od , northwind.suppliers s
where c.category_id = p.category_id and s.supplier_id = p.supplier_id
and p.product_id = od.product_id and od.order_id = o.order_id
)
2) grouping sets를 사용한 cte 테이블을 하나 만든다.
-- 0. cte테이블 생성
with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date
, to_char(o.order_date,'yyyy') as year
, to_char(o.order_date,'q') as quarter
, to_char(o.order_date,'mm') as month
, to_char(o.order_date,'dd') as day
, 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_id , p.product_name , p.unit_price as 마스터단가, p.discontinued as 단종여부
, s.supplier_id , s.company_name , s.country , s.city
from northwind.products p , northwind.categories c ,northwind.orders o
, northwind.order_details od , northwind.suppliers s
where c.category_id = p.category_id and s.supplier_id = p.supplier_id
and p.product_id = od.product_id and od.order_id = o.order_id
)
-- 1. 제품 속성별 매출액 (grouping sets)
select category_name, product_name, company_name, country, city, sum(sales) as 매출액
from cte_products_sale
group by grouping sets (1,2,3,4,(4,5),())
-- 각각이 그룹핑되어 각각에 대한 결과가 한 번에 보인다
-- 비어있는 괄호를 넣으면 전체에 대한 집계
-- 요소를 괄호로 묶으면 grouping sets 사용하지 않은 것 같이 나온다
cte 테이블로 담아준다.
-- 0. cte테이블 생성
with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date
, to_char(o.order_date,'yyyy') as year
, to_char(o.order_date,'q') as quarter
, to_char(o.order_date,'mm') as month
, to_char(o.order_date,'dd') as day
, 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_id , p.product_name , p.unit_price as 마스터단가, p.discontinued as 단종여부
, s.supplier_id , s.company_name , s.country , s.city
from northwind.products p , northwind.categories c ,northwind.orders o
, northwind.order_details od , northwind.suppliers s
where c.category_id = p.category_id and s.supplier_id = p.supplier_id
and p.product_id = od.product_id and od.order_id = o.order_id
),
-- 1. 제품 속성별 매출액 (grouping sets)
cte_grouping_sets as(
select category_name, product_name, company_name, country, city, sum(sales) as 매출액
from cte_products_sale
group by grouping sets (1,2,3,4,(4,5),())
-- 각각이 그룹핑되어 각각에 대한 결과가 한 번에 보인다
-- 비어있는 괄호를 넣으면 전체에 대한 집계
-- 요소를 괄호로 묶으면 grouping sets 사용하지 않은 것 같이 나온다
)
3) 사용할 그룹을 각각 cte 테이블로 만들어준다.
-- 0. cte테이블 생성
with cte_products_sale as (
/* (생략) */
),
-- 1. 제품 속성별 매출액 (grouping sets)
cte_grouping_sets as(
select category_name, product_name, company_name, country, city, sum(sales) as 매출액
from cte_products_sale
group by grouping sets (1,2,3,4,(4,5),())
)
-- 2. 카테고리별 매출액
, cte_category_sales as(
select category_name, 매출액
from cte_grouping_sets
where category_name is not null
)
-- 2-1. 제품별 매출액
, cte_product_sales as(
select product_name, 매출액
from cte_grouping_sets
where product_name is not null
)
-- 2-2. 공급사별 매출액
, cte_company_sales as(
select company_name, 매출액
from cte_grouping_sets
where company_name is not null
)
-- 2-3. 공급국가별 매출액
, cte_country_sales as(
select country, 매출액
from cte_grouping_sets
where country is not null and city is null
)
-- 2-4. 공급국가 및 도시별 매출액
, cte_country_city_sales as(
select country, city, 매출액
from cte_grouping_sets
where country is not null and city is not null
)
-- 2-4. 전체 매출액
, cte_total_sales as(
select 'all' as 전체, max(매출액) as "전체 매출액"
from cte_grouping_sets
)이때, select 문으로 선택만 할 경우 해당 테이블이 null값으로 존재하는 다른 행 또한 집계되지 않은 채 등장할 것이므로, where 조건으로 (그룹핑한 테이블) IS NOT NULL 을 달아준다
괄호를 두 개 써준 그룹이 있다면, 다음과 같이 해야 한다.
eg) GROUP BY GROUPING SETS (1, (1,2))
↓
- 1 호출 시: WHERE 1 IS NOT NULL AND 2 IS NULL
- (1, 2) 호출 시: WHERE 1 IS NOT NULL AND 2 IS NOT NULL
+ TOTAL을 작성할 때엔 가장 큰 값을 호출하면 되므로, MAX( )함수를 사용해준다.
4) 마지막으로 원하는 그룹을 호출하기 위해 주석으로 코드를 달아준 후,
SELECT *
FROM /* (CTE명) */다음과 같은 형식으로 호출해준다.
-- 0. cte테이블 생성
with cte_products_sale as (
/* (생략) */
),
-- 1. 제품 속성별 매출액 (grouping sets)
cte_grouping_sets as(
/* (생략) */
)
-- 2. 카테고리별 매출액
, cte_category_sales as(
/* (생략) */
)
-- 2-1. 제품별 매출액
, cte_product_sales as(
/* (생략) */
)
-- 2-2. 공급사별 매출액
, cte_company_sales as(
/* (생략) */
)
-- 2-3. 공급국가별 매출액
, cte_country_sales as(
/* (생략) */
)
-- 2-4. 공급국가 및 도시별 매출액
, cte_country_city_sales as(
/* (생략) */
)
-- 2-4. 전체 매출액
, cte_total_sales as(
/* (생략) */
)
-- 3. 결과
select *
-- 카테고리별: cte_total_sales
-- 상품별: cte_product_sales
-- 공급사별: cte_company_sales
-- 공급국가별: cte_country_sales
-- 공급국가 및 도시별: cte_country_city_sales
-- 전체: cte_total_sales
from cte_total_sales
order by 1 asc;
2. ROLLUP
GROUP BY ROLLUP(그룹1, 그룹2) 와 같은 형태로 사용, 소계와 총계를 구해준다.
/* 그룹핑 함수 2 - rollup 함수 (단계별 소계) */
with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date
, to_char(o.order_date,'yyyy') as year
, to_char(o.order_date,'q') as quarter
, to_char(o.order_date,'mm') as month
, to_char(o.order_date,'dd') as day
, 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_id , p.product_name , p.unit_price as 마스터단가, p.discontinued as 단종여부
, s.supplier_id , s.company_name , s.country , s.city
from northwind.products p , northwind.categories c ,northwind.orders o
, northwind.order_details od , northwind.suppliers s
where c.category_id = p.category_id and s.supplier_id = p.supplier_id
and p.product_id = od.product_id and od.order_id = o.order_id
)
-- 카테고리, 제품 별 매출액 소계
--, cte_category_product_rollup as(
SELECT category_name, product_name, sum(sales)
FROM cte_products_sale
GROUP BY ROLLUP(category_name, product_name)
ORDER BY 1,2
3. CUBE: 각 컬럼들의 가능한 모든 조합을 기준으로 그룹핑해 보여준다.
eg) year, quarter, month → year, quarter, month, year-quarter, year-month, quarter-month, year-quarter-month