실무 SQL 공부 3-3 - 데이터 가공 SQL

하나의 테이블에 대한 조작

데이터 집계

SQL에서는 집계 함수라고 부르는 여러 함수를 제공한다.

레코드의 수를 세주거나, 합계, 평균, 최대, 최소를 계산하는 함수부터 통계 처리를 사용하여 통계 지표를 출력하기도 한다.

데이터 가공

테이블을 기반으로 데이터를 처리하는 방법을 알아본다.

만약 테이블의 형식이 집계에 적합하지 않은 경우, 어떻게 테이블을 가공해야하는지에 대한 방법도 알아보자.

1. 그룹의 특징 잡기

• 집계함수는 여러 레코드를 기반으로 하나의 값을 리턴하는 함수
  count, sum함수 등

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

  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

• 그루핑한 데이터의 특징량 계산하기

  SELECT
    user_id
    , COUNT(*) AS total_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 구문에서 지정한 컬럼을 unique key로 하여 새로운 테이블을 만들게 된다. 이 과정에서 group by 구문에 지정하지 않은 컬럼은 사라져버린다.

• 집계함수값과 집계 전 값을 동시에 다루기

  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를 사용하고 여기에 윈도우 함수를 지정한다. 만약 매개 변수를 지정하지 않으면 테이블 전체에 집계함수를 적용한 값이 리턴된다.

매개변수에 PARTITION BY <컬럼이름>를 사용하면 해당 컬럼 값을 기준으로 그룹화하고 집계 함수를 적용한다.

2. 그룹 내부 순서

• 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

  위 코드를 해석해보면, 컬럼들을 score의 높은 순서대로 정렬할 수있다. ROW_NUMBER함수는 순서에 대한 unique id를 붙이는
  함수이다.

RANK와 DENSE_RANK함수는 순위를 붙이는 함수인데, 후자는 같은 순위의 레코드가 있을 때 같은 순위의 번호를 붙일때 순위 번호를 건너뛰지 않는다.

LAG와 LEAD함수는 현재 행을 기준으로 앞 또는 뒤의 행을 추출하는 함수이다.

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

  SELECT
    product_id, score,
    --점수 순서로 Unique id 붙임
    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,
    AVG(score) OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS local_avg,
    FIRST_VALUE(product_id) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    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

• 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(PARTITON BY category ORDER BY score DESC) AS dense_rank
FROM popular_products
ORDER BY category, row

* 각 카테고리의 상위 n개 추출
```SQL
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

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

SQL은 기본적으로 row 기반으로 처리한다.

• 행/열 변환

  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

• 행을 쉼표로 구분한 문자열로 집계하기
  redshift에서는 listagg, postgreSQL에서는 string_agg를 사용한다.

  SELECT purchase_id,
  listagg(product_id, ',') AS product_ids,
  SUM(price) AS amount
  FROM purchase_detail_log
  GROUP BY purchase_id
  ORDER BY purchase_id

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

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

• 임의의 길이를 가진 배열을 행으로 전개하기
  PostgreSQL, BigQuery는 unnest, Hive와 SparkSQL은 explode 함수를 쓰면 된다.

PostgreSQL에서 쉼표로 구분된 문자열을 한 번에 행으로 전개하는 방법
regexp_split_to_table(컬럼명, ',')을 사용한다.

• Redshift에서 문자열을 행으로 전개하기
  Redshift는 배열 자료형이 지원되지 않아서 소개했던 방법을 사용할 수 없다.

  • 피벗 테이블로 문자열을 행으로 전개

    SELECT *
    FROM (
      SELECT 1 AS idx
      UNION ALL SELECT 2 AS idx
      UNION ALL SELECT 3 AS idx
    ) AS pivot

    idx
    1
    2
    3

위 형태의 테이블이 생성된다. 이어서, `split_part` 함수를 사용하여 문자열을 쉼표 등의 구분자로 분할하여 n번쨰 요소를 추출한다.
```sql
SELECT 
    split_part('A001,A002, A003', ',', 1) AS part_1,
    split_part('A001,A002,A003', ',', 2) AS part_2,
    split_part('A001, A002, A003', ',', 3) AS part_3
```