10주 3일차 TIL 정리

10주 3일차에는 SQL의 CTE절, 윈도우 함수 구문에 대해 학습했다.

---

※ 복잡한 데이터셋을 다루는 방법 ※

---

1. ETL 스냅샷테이블: 쿼리 결과를 테이블에 저장 (테이블 컨트롤 권한 필요)

2. VIEW: 쿼리 결과를 가상테이블(VIEW)로 저장하여 사용 (테이블 컨트롤 권한 필요)

3. TEMPORARY TABLE: 현재 세션동안 유지되는 임시테이블에 저장 (테이블 컨트롤 권한 필요)

4. 공통 테이블 표현식(CTE): 쿼리 결과에 이름을 붙여 테이블처럼 사용 (테이블 컨트롤 권한 불필요)

 

---

※ TEMPORARY TABLE 생성 ※

---

create temporary table temp_order_details
as
select o.order_id, o.customer_id, 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
       , od.product_id, od.unit_price, od.quantity, od.discount 
       , od.unit_price * od.quantity * (1-od.discount) as sales
       from northwind.orders o , northwind.order_details od 
where o.order_id = od.order_id

 

다음과 같이

CREATE TEMPORARY TABLE ~~ 로 임시테이블을 생성하면, 이를 그대로 활용할 수 있다.

-- 임시테이블 이용해 월별 매출액, 주문건수, 건당 평균 주문액, 주문자수, 주문고객 평균주문액 추출
select year, month, sum(sales), count(distinct order_id), sum(sales)/count(distinct order_id)
                              , count(distinct customer_id), sum(sales)/count(distinct customer_id)
from temp_order_details
group by year, month;

 

---

※ 공통 테이블 표현식(CTE) ※

---

with
cte_order_details as(
select o.order_id, o.customer_id, 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
       , od.product_id, od.unit_price, od.quantity, od.discount 
       , od.unit_price * od.quantity * (1-od.discount) as sales
       from northwind.orders o , northwind.order_details od 
where o.order_id = od.order_id
)

다음과 같이 WITH 테이블이름 AS ( 쿼리구문 )으로 CTE테이블을 생성해줄 수 있다.

여기서 쿼리를 실행하기 위해선, 괄호에 바로 이어지도록 쿼리를 작성하면 된다. (주석 제외 줄바꿈 하면 안 됨!)

-- 임시테이블 이용해 월별 매출액, 주문건수, 건당 평균 주문액, 주문자수, 주문고객 평균주문액 추출
with
cte_order_details as(
select o.order_id, o.customer_id, 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
       , od.product_id, od.unit_price, od.quantity, od.discount 
       , od.unit_price * od.quantity * (1-od.discount) as sales
       from northwind.orders o , northwind.order_details od 
where o.order_id = od.order_id
)
select year, month, sum(sales), count(distinct order_id), sum(sales)/count(distinct order_id)
                              , count(distinct customer_id), sum(sales)/count(distinct customer_id)
from cte_order_details
group by year, month;

 

---

※ 윈도우 함수 ※

---

1. 윈도우 함수 구문

함수( ) over( 윈도우 프레임 지정 )

 

- SUM(SALES)를 기준으로 한 계산들

-- 매출에 대한 총계 계산
SUM (SALES) OVER ()

-- CATEGORY별 총계 구하기
SUM (SALES) OVER (PARTITION BY CATEGORY)

-- CATEGORY별 누계 구하기 (마지막은 일괄에 대한 총계)
SUM (SALES) OVER (ORDER BY CATEGORY)

-- CATEGORY별 누계 및 총계 구하기(일괄총계 없음, 카테고리별 분류)
SUM (SALES) OVER (PARTITION BY CATEGORY ORDER BY CATEGORY)

 

2. LAG함수: 이전 행의 데이터를 가져와서 계산

with
cte_order_details as(
select o.order_id, o.customer_id, 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
       , od.product_id, od.unit_price, od.quantity, od.discount 
       , od.unit_price * od.quantity * (1-od.discount) as sales
       from northwind.orders o , northwind.order_details od 
where o.order_id = od.order_id
)
, cte_pre_amount as(
select year,month,day
       , sum(sales) as amount
from cte_order_details
group by 1,2,3
order by 1,2,3
)
select *,lag(amount,2) over (order by year,month, day) as 전일매출 from cte_pre_amount;

3. LAG함수의 반대기능(이후 행) 으로 LEAD함수 존재.