[SQL 레시피] 하나의 테이블에 대한 조작

7강. 하나의 테이블에 대한 조작

1. 그룹의 특징 잡기

• 집약 함수
  • COUNT, SUM, AVG, …
  • GROUP BY
  • 윈도우 함수 over(partition by column)

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 sqlrecipe.review
limit 100;

2. 그룹 내부의 순서

• ORDER BY 구문으로 순서 정의하기
  • row_number(1,2,3), rank (1-1-3), dense_rank (1-1-2)
  • 현재 행보다 앞에 있는 행의 값 추출하기
    • lag(product_id) over(order by score desc)
  • 현재 행보다 뒤에 있는 행의 값 추출하기
    • lead(product_id) over(order by score desc)
  • 점수 순서로 유일한 순위 붙이기
    • row_number() over(order by score desc)

  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 sqlrecipe.popular_products
  order by row
  limit 100;
  

• 순위 상위부터의 누계 점수 계산하기
  • sum(score) over(order by score desc rows between unbounded preceding and current row)
  • 이전 행 전부와 지금 행 사이의 값

  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 -- 순위 상위부터의 누계 점수 계산하기
        , 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 -- 순위가 높은 상품 ID 추출하기
        , last_value(product_id) over(order by score desc rows between unbounded preceding and unbounded following) AS last_value -- 순위가 낮은 상품 ID 추춣하기
  from sqlrecipe.popular_products
  order by row
  limit 100;
  

💡 윈도 프레임 지정

• rows between start and end
• current row (햔재의 행)
• n preceding (n행 앞)
• n following (n행 뒤)
• unbounded preceding (이전 행 전부)
• unbounded following (이후 행 전부)

 

• 범위 내부의 상품 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 sqlrecipe.popular_products
where category = 'action'
order by row
limit 100;

• 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 sqlrecipe.popular_products
order by category, row
limit 100;

• 각 카테고리의 상위 n개 추출하기
  • 윈도 함수에서 WHERE 구문에 작성할 수 없으므로, SELECT 구문에서 윈도 함수를 사용한 결과를 서브쿼리로 만들고 외부에서 WHERE 구문을 적용한다.
  • 카테고별 최상위 상품 추출 (DISTINCT 활용)

  select distinct category
        , first_value(product_id) over(partition by category order by score desc rows between unbounded preceding and unbounded following) AS product_id
  from sqlrecipe.popular_products
  limit 100;
  

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 sqlrecipe.popular_products
where category = 'action'
order by row
limit 100;

3. 세로 기반 데이터를 가로 기반으로 변환하기 (피벗)

행 단위로 저장된 ‘세로 기반’을 열 또는 쉼표로 구분된 ‘가로 기반’으로 변환

• 행을 열로 변환하기
  • GROUP BY
  • MAX (CASE~) 활용
  💡 CASE 표현식의 결과는 리스트이므로, MAX/MIN 함수 등을 활용하여 스칼라 값으로 추출해야함.

  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 sqlrecipe.daily_kpi
  group by dt
  order by dt
  limit 100;
  

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

select purchase_id
      , string_agg(product_id, ',') AS product_ids
      , sum(price) AS amount
from sqlrecipe.purchase_detail_log
group by purchase_id
order by purchase_id
limit 100;

 

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

이미 데이터가 쉼표로 구분된 열 기반의 형식으로 저장되어서, 이를 분석하기 위해 어쩔 수 없이 변환해야하는 경우에 사용된다.

• 열로 표현된 값을 행으로 변환하기
  • 행으로 전개할 데이터 수가 고정되었다면, 데이터 수와 같은 수의 일련 번호를 가진 피벗 테이블을 만들고 CROSS JOIN 하면 된다.

  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 sqlrecipe.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
  limit 100;
  

• 임의의 길이를 가진 배열을 행으로 전개하기
  • unnest 함수와 같은 테이블 함수를 사용한다. (FROM구문에서 사용가능)
  • 매개 변수로 배열을 받고, 배열을 레코드 분할해서 리턴해줌.
  • Bigquery에서는 스칼라 값과 테이블을 동시에 다룰 수 없기때문에 FROM절에서 사용해야함.
  • 테이블 함수를 FROM 내부에 작성하고, JOIN 구문을 사용해 원래 테이블과 테이블 함수의 리턴 값을 결합해야 함.

  select purchase_id
        , product_id
  from sqlrecipe.purchase_log AS p
  cross join unnest(split(product_ids, ',')) AS product_id
  limit 100;