[PostgreSQL] CTE (WITH 절)

SQL문이 복잡해지면 서브쿼리의 중첩이 많아지는데 이렇게 되면 가독성이 많이 떨어지게 됩니다. 이때 공통 테이블 식(CTE : common table expression)을 사용하면 임시로 테이블을 정의하고 재활용할 수 있습니다. 이때 사용하는 것이 WITH문입니다.

 

WITH문

WITH문의 구조는 다음과 같습니다.

WITH (테이블 이름) AS (SELECT ~ FROM ~) SELECT ~

 

DATASET QUERY

DROP TABLE IF EXISTS categories_sales;

CREATE TABLE categories_sales (
    category_id integer
  , name        varchar(255)
  , sales_amount  integer
);

INSERT INTO categories_sales
VALUES
    (1, 'dvd', 850000 )
  , (3, 'book', 444444)
  , (2, 'tape', 300000)
;
DROP TABLE IF EXISTS sales_ranking;
CREATE TABLE sales_ranking(
    category_id integer
  , product_id  varchar(255)
  , sales       integer
);

INSERT INTO sales_ranking
VALUES
    (1, 'D001', 50000)
  , (1, 'D002', 20000)
  , (1, 'D003', 10000)
  , (2, 'B001', 60000)
  , (2, 'B002', 30000)
  , (2, 'B003', 40000)
  , (3, 'A001', 60000)
  , (3, 'A002', 30000)
  , (3, 'A003', 40000)
;

 

WITH절의 활용

categories_sales와, sales_ranking을 카테코리 안의 랭킹을 정하면서 결합한다음, 카테고리별로 랭크가 가장 높은 것을 뽑는 것을 해보겠습니다.

with ranking as (
	select m.category_id 
		,m.name 
		,m.sales_amount 
		,s.product_id 
		,s.sales 
		,ROW_NUMBER() OVER(PARTITION BY name order by sales desc) as rank
	from categories_sales as m 
		JOIN sales_ranking as s 
			ON m.category_id = s.category_id)

select name, product_id, rank
from ranking
Where rank = 1;

 

 

With 문밑에 임시 테이블을 여러개 만들 수 있습니다.(with문은 맨 앞에 하나만 씁니다.)

위의 예제와 같이 두개의 테이블을 합치되, 각 카테고리안에서 매출 비중을 추출한 후, 매출비중이 높은 것들을 출력하는 쿼리입니다. 해당 쿼리를 통해 각 품목이 해당 카테고리 안에서의 매출 영향력을 알 수 있습니다.

with ranking as (
	select m.category_id 
		,m.name 
		,m.sales_amount 
		,s.product_id 
		,s.sales 
		,round(100.0 * s.sales / m.sales_amount,2)  as rate
	from categories_sales as m 
		JOIN sales_ranking as s 
			ON m.category_id = s.category_id)
, sales_rate as(
	select *,
		RANK() over(order by rate DESC) as ranks
	from ranking
	)
	
SELECT name, product_id, ranks
FROM sales_rate
Where ranks <=3;
		

 

해당 링크에서 PostgreSQL CTE에 대해 더 많은 정보를 알 수 있습니다.

wiki.postgresql.org/wiki/CTEReadme

CTEReadme - PostgreSQL wiki