테이블에 대한 조작하기

목차

1. 그룹핑한 데이터 특징량 계산하기

 

2. ORDER BY 구문을 통해 테이블 내부의 순서를 다루는 쿼리

 

3. 각 카테고리의 상위 n개 추출하기

 

4. 가로 기반 데이터를 세로로 변환하기

 

 

실습에 필요한 테이블

review 테이블

popular_products 테이블

quarterly_sales 테이블

 

그룹핑한 데이터 특징량 계산하기

• GROUP BY 뒤에 집약 함수는 SELECT 구문 컬럼만 지정 가능
• SELECT 구문 내부에서 product_id, score 동시 지정은 불가능
• SUM, AVG 등 집계 함수를 이용해서 결과값 도출

SELECT 
	user_id 
	, COUNT(*) AS total_count
        , COUNT(DISTINCT user_id) AS user_count
        , COUNT(DISTINCT product_id) AS product_count
        , SUM(score) AS sum
        , AVG(score) AS avg
        , MAX(score) AS max
        , MIN(score) AS min
FROM 
	review
GROUP BY 
	user_id;​

 

 

ORDER BY 구문을 통해 테이블 내부의 순서를 다루는 쿼리

• 순위 함수 : ROW_NUMBER, DENSE_RANK, RANK를 통해 순위 집계
• LAG, LEAD를 통해 product_id의 전행과 후행을 찾아 score 내림차순으로 정렬
• OVER(ORDER BY)를 통해 각각의 열들을 테이블 내부에서 score를 내림차순으로 정렬한다.
• ORDER BY 절에서 row열로 정렬

SELECT 
    product_id
    , score
    , ROW_NUMBER() OVER(ORDER BY score DESC) AS row
    , DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank
    , RANK() OVER(ORDER BY score DESC) AS rank
    , LAG(product_id) OVER(ORDER BY score DESC) AS lag1
    , LAG(product_id, 2) OVER(ORDER BY score DESC) AS lag2
    , LEAD(product_id) OVER(ORDER BY score DESC) AS lead1
    , LEAD(product_id, 2) OVER(ORDER BY score DESC) AS lead2
FROM 
	popular_products
ORDER BY 
	row;

 

각 카테고리의 상위 n개 추출하기

• 서브 쿼리를 이용, ROW_NUMBER() OVER PARTITION BY로 category에 대한 집계된 값을 score 내림차순으로 정렬
• 이 쿼리의 경우 상위 2개를 추출하는 쿼리

SELECT *
FROM ( SELECT category, product_id, score, ROW_NUMBER() OVER(PARTITION BY category ORDER BY score DESC) 
	  AS rank FROM popular_products
	 ) AS popular_products_with_rank
WHERE rank <= 2
ORDER BY category, rank;

 

가로 기반 데이터를 세로로 변환하기

• 일련 번호를 가진 피벗 테이블을 사용해 행으로 변환하는 쿼리

SELECT 
	q.year
	, CASE
        WHEN p.idx = 1 THEN 'q1'
        WHEN p.idx = 2 THEN 'q2'
        WHEN p.idx = 3 THEN 'q3'
        WHEN p.idx = 4 THEN 'q4'
	END AS quarter
	, CASE
        WHEN p.idx = 1 THEN q.q1
        WHEN p.idx = 2 THEN q.q2
        WHEN p.idx = 3 THEN q.q3
        WHEN p.idx = 4 THEN q.q4
	END AS sales
FROM quarterly_sales AS q
	CROSS JOIN
	-- 행으로 전개하고 싶은 열의 수만큼 순번 테이블 만들기
	(SELECT 1 AS idx
	UNION ALL SELECT 2 AS idx
	UNION ALL SELECT 3 AS idx
	UNION ALL SELECT 4 AS idx
	) AS p;