[데이터 분석을 위한 SQL 레시피] 7장. 하나의 테이블에 대한 조작

💡 대규모의 데이터 처리시, 각 레코드 하나하나를 다루기는 쉽지 않음

→ 대량의 데이터를 집계하고 몇가지 지표를 사용해 데이터 전체의 특징 파악하기!

 

- 데이터 집약 : 평균, 최대 최소,,, 통계 지표 출력 → 윈도 함수를 사용해 순서를 고려하는 처리, 여러개의 레코드를 대상으로 하는 처리

- 데이터 가공 : 테이블 형식이 집계에 적합하지 않은 경우 어떻게 테이블을 가공해야 하는지!

 

1. 그룹의 특징 잡기

집약 함수 : 여러 레코드를 기반으로 하나의 값을 리턴하는 함수

ex) COUNT(), SUM()

상품 평가 테이블

 

테이블 전체의 특징량 계산하기

COUNT : 지정한 컬럼의 레코드 수 리턴

DISTINCT : 중복을 제외하고 수를 세워줌

SELECT
  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 구문을 이용해 데이터를 조금 더 작게 분할

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
;

‼️ GROUP BY를 사용했을 때, SELECT 구문에는 집약함수 또는 Group by구문에 지정한 컬럼만이 올 수 있음! ( Group by를 사용하면 지정컬럼을 유니크 키로 새로운 테이블을 만들기 때문!)

 

집약 함수를 적용한 값과 집약 전의 값을 동시에 다루기

윈도 함수를 이용해 집약함수의 결과와 원래값을 조합할 수 있음!

💡 윈도우 함수란? : Group By와 유사하게 데이터를 그룹화하여 집계해주는 함수!

but, Group By는 집계된 결과만 보여주는 반면, 윈도우 함수는 기존 데이터에 집계된 값을 추가해서 나타내준다!

# 윈도우 함수 기본 형태
함수(함수_적용_열)OVER (PARTITIONBY 그룹열 ORDERBY 순서열)

PARTITION BY : Group By와 같은 기능

ORDER BY : Order By와 같은 기능 (DESC : 내림차순)

# 개별 리뷰 점수와 사용자 평균 리뷰 점수의 차이를 구하기
SELECT
user_id, product_id, 
-- 개별 리뷰 점수
score,
-- 전체 평균 리뷰 점수
AVG(score) OVER() as avg_score,
-- 사용자의 평균 리뷰 점수
AVG(score) OVER(PARTITION BY user_id) AS user_avg_score,
-- 개별 리뷰 점수와 사용자 평균 리뷰 점수의 차이 
score - AVG(score) OVER(PARTITION BY user_id) AS user_avg_score_diff
FROM review; 

• 집약함수로 윈도 함수를 사용하려면, 집약 함수 뒤에 OVER 구문을 붙이고, 여기에 윈도함수를 지정
• OVER 구문에 매개변수를 지정하지 않으면 테이블 전체에 집약함수를 적용한 값이 리턴됨!
• 매개변수에 PARTITION BY <컬럼 이름>을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약함수 적용

2. 그룹 내부의 순서

윈도함수가 등장하면서, SQL로 순서를 다루는 것이 굉장히 쉬워짐!

ORDER BY 구문으로 순서 정의하기

윈도 함수란, 테이블 내부에 ‘윈도’라고 부르는 범위를 지정하고, 해당 범위 내부에 포함된 값을 특정 레코드에서 자유롭게 사용하려고 도입한 것!

→ 이때, 해당 값의 위치를 명확하게 지정해야함

→ OVER구문 내부에 ORDER BY 구문사용하면, 윈도 내부에 있는 데이터의 순서 정의 가능!

SELECT
  product_id
  , score
  -- 점수 순서로 유일한 순위
  , ROW_NUMBER()  OVER(ORDER BY score DESC) AS row
  -- 같은 순위 허용, 순위
  , RANK()        OVER(ORDER BY score DESC) AS rank
  -- 같은 순위 허용, 순위 숫자는 건너뜀
  , DENSE_RANK()  OVER(ORDER BY score DESC) AS dense_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
;

• ORDER BY score DESC 로 테이블 내부의 상품을 스코어가 높은 순으로 정렬
• ROW_NUMBER : 순서에 순위번호를 붙이기
• RANK 와 DENSE_RANK 함수는, 같은 순위의 레코드가 있을 때, 동일한 순위 번호를 붙임
  • RANK의 경우, 같은 순위의 레코드 뒤의 순위 번호를 건너 뜀
  • 1, 1, 3, 4, ...
  • DENSE_RANK :순위 번호를 건너뛰지 않음
  • 1, 1, 2, 3, ...
• LAG와 LEAD 함수는
  • 현재 행을 기준으로 앞의 행 또는 뒤의 행을 추출하는 함수
  • 두번째 매개변수에 숫자를 지정하여 n번째 값 추출

ORDER BY 구문과 집약함수 조합하기

• ORDER BY와 SUM/AVG 같은 집약함수를 조합하면, 집약 함수의 적용 범위를 유연하게 지정할 수 있음!

# ORDER BY 구문에 이어지는 ROWS 구문은 윈도 프레임 지정 구문 쿼리
SELECT
  product_id
  , score
	-- 점수 순서로 유일한 순위를 붙임
  , ROW_NUMBER()  OVER(ORDER BY score DESC) AS row

  -- 순위 상위에서 현재 행까지의 스코어를 모두 더한 값 계산
  , SUM(score)
      OVER(ORDER BY score DESC
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
      AS cum_score
  
  -- 현재 행 기준 전/후 총 3개행의 평균점수 계산
  , AVG(score)
      OVER(ORDER BY score DESC
          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
      AS local_avg
  
  -- FIRST_VALUE : 순위가 높은 상품 ID(윈도 내부의 첫 레코드)
  , FIRST_VALUE(product_id)
      OVER(ORDER BY score DESC
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
      AS first_value
  
  -- LAST VALUE : 순위가 낮은 상품 ID(윈도 내부의 마지막 레코드)
  , LAST_VALUE(product_id)
      OVER(ORDER BY score DESC
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
      AS last_value

FROM popular_products
ORDER BY row
;

윈도우 프레임 지정

• 프레임 지정이란? 현재 레코드 위치를 기반으로 상대적인 윈도를 정의하는 구문 - 3가지 종류
• ROWS BETWEEEN {start} AND {end}
  • start와 end에는
    • CURRENT ROW : 현재 행
    • n PRECEDING : n행 앞
    • n FOLLOWING : n행 뒤
    • UNBOUNDED PRECEDING : 이전 행 전부
    • UNBOUNDED FOLLOWING : 이후 행 전부
• array_agg : 다수의 row들의 특정 칼럼값을 하나의 배열에 담고 싶을 때 사용하는 함수

# 범위 내부 상품 ID를 집약하는 쿼리
SELECT
  product_id
  , ROW_NUMBER()  OVER(ORDER BY score DESC) AS row

  -- 가장 앞 순위부터, 뒷 순위까지의 범위를 대상으로 상품 ID 집약
  , array_agg(product_id)
      OVER(ORDER BY score DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  AS whole_agg

  -- 가장 앞 순위부터 현재 순위까지의 범위를 대상으로 상품 ID 집약
  , array_agg(product_id)
      OVER(ORDER BY score DESC
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  AS cum_agg

  -- 순위 하나 앞/뒤까지의 범위를 대상으로 상품 ID 집약
  , array_agg(product_id)
      OVER(ORDER BY score DESC
          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  AS local_agg
FROM popular_products
WHERE category='action'
ORDER BY row
;

• 윈도 함수에 프레임 지정을 하지 않으면, ORDER BY 구문이 없는 경우 모든 행, ORDER BY 구문이 있는 경우 첫 행에서 현재 행까지가 디폴트로 지정!

PARTITION BY와 ORDER BY 조합하기

• PARTITION BY와 ORDER BY 구문을 조합해 카테고리들의 순위를 계산하는 쿼리

SELECT
	category, product_id, score,
	-- 카테고리 별로 점수 순서로 정렬하고, 유일한 순위를 붙임
	ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS row
	-- 카테고리 별로 같은 순위를 허가하고 순위를 붙임
	, RANK() OVER (PARTITION BY category ORDER BY score DESC) AS rank
	
	-- 카테고리별로 같은 순위가 있을 때
	-- 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
	, DENSE_RANK()
	OVER (PARTITION BY category ORDER BY score DESC) AS dense_rank
	FROM popular_products ORDER BY category, row;

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

윈도함수를 WHERE구문에 작성할 수 없음

→ 윈도함수를 사용한 결과를 서브쿼리로 만들고, WHERE구문 적용하기!

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;

• 각 카테고리별 순위 순서에서 상위 1개의 상품 ID만을 추출하기

→ FIRST_VALUE 윈도우 함수를 사용하고, SELECT DISTINCT 구문으로 결과 집약하기!

-- DISTINCT 구문을 사용해 중복 제거하기!
SELECT DISTINCT category
	-- 카테고리별로 순위 최상품 상품 ID 추출하기
, FIRST_VALUE(product_id)
	OVER (PARTITION BY category ORDER BY score DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS product_id
FROM popular_products;

• DISTINCT 안 쓸 경우!

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

• 행 단위로 저장된 ‘세로 기반’을, 열 또는 쉼표로 구분된 문자열 등의 ‘가로기반’으로 변환하기!
• SQL에선 행(레코드) 기반으로 처리하는 것이 기본이므로, 데이터를 저장할 때 최대한 데이터를 행으로 분할하는 것이 좋음! but, 최종 출력에선 데이터를 열로 전개해야 가독성이 높은 경우 많다

행을 열로 변환하기

SQL에서 열은 고정적이어야 함! → 열로 전개할 데이터의 종류 또는 수를 명확하게 알고 있어야 함!

# 행으로 저장된 지표를 열로 변환하는 쿼리
# Group by dt로 날짜를 1개의 레코드로 집약하게 함!
SELECT dt, MAX(CASE WHEN indicator = 'impressions' THEN val END) AS impressions
, MAX(CASE WHEN indicator = 'sessions' THEN val END) AS sessions,
 MAX(CASE WHEN indicator = 'users' THEN val END) AS users
FROM daily_kpi
GROUP BY dt ORDER BY dt;

• 이때, MAX를 써준 이유 -> SELECT 문에 GROUP BY 문에 사용된 컬럼이나 집계함수만이 들어갈 수 있기 때문에!

행을 쉼표로 구분한 문자열로 집약하기

• 열의 종류와 수를 모를 경우에는 행을 열로 변환하는 방법을 쓸 수 없음!

한번의 주문으로 여러개의 상품을 구매했을 때 → 이를 상품별로 레코드를 나누어 저장하는 테이블! 구매id를 기반으로 레코드를 하나로 집약하고 싶어도 상품을 몇 개 주문했는지 미리 알 수 없음! → 열로 집계가 어렵다!

→ 행을 집약해서 쉼표로 구분된 문자열로 변환하기! string_agg 함수 이용

SELECT purchase_id
-- 상품 id를 배열에 집약하고 쉼표로 구분된 문자열로 변환하기
, string_agg(product_id, ',') AS product_ids
, SUM(price) as amount
FROM purchase_detail_log GROUP BY purchase_id ORDER BY purchase_id;

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

• 가로 기반을 세로 기반으로 변환하는 것은 간단한 일이 아님!
• 이미 데이터가 쉼표로 구분된 열 기반의 형식으로 저장된 경우 많음! → 변환해야함!

열로 표현된 값을 행으로 변환하기

데이터의 수가 고정되어 있는 데이터!

• 컬럼으로 표현된 가로기반 데이터의 특징 : 데이터의 수가 고정되어 있다는 것!

→ 위에 데이터를 보면, q1 ~ q4까지 모두 4개의 데이터 → 데이터 수 만큼의 일련번호를 가진 피벗테이블을 만들고 CROSS JOIN 하기!

# 피벗테이블을 결합하고, CASE 식으로 레이블 이름과 매출값을 추출해서 열을 행으로 변환!
SELECT
	q.year, 
-- Q1에서 Q4까지의 레이블 이름 출력하기
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
-- Q1에서 Q4까지의 매출 출력하기
, 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;

	

임의의 길이를 가진 배열을 행으로 전개하기

데이터 길이가 확정되지 않은 경우에는 조금 복잡!

# 피벗테이블을 결합하고, CASE 식으로 레이블 이름과 매출값을 추출해서 열을 행으로 변환!
SELECT
	q.year, 
-- Q1에서 Q4까지의 레이블 이름 출력하기
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
-- Q1에서 Q4까지의 매출 출력하기
, 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;

쉼표로 구분된 상품 id를 포함한 레코드!

테이블 함수를 구현하기! unnest() 함수

• 테이블 함수: 리턴값이 테이블인 함수! 매개변수로 배열을 받고, 배열을 레코드 분할해서 리턴해줌!

SELECT unnest(ARRAY['A001','A002','A003']) AS product_id;

SELECT purchase_id, product_id
	FROM purchase_log AS p CROSS JOIN unnest(string_to_array(product_ids, ',')) AS product_id
-- string_to_array 함수로 문자열을 배열로 변환하고, unnest 함수로 테이블로 변환하기!

• regexp_split_to_table: 문자열을 구분자로 분할해서 테이블화하는 함수

SELECT purchase_id, regexp_split_to_table(product_ids, ',') AS product_id
FROM purchase_log;