정착률 : 등록일 기준으로 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표

WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , DATEADD(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
SELECT *
  FROM action_log_with_mst_users
  ORDER BY register_date

CAST : 데이터 형식을 다른 데이터 형식으로 변환하는 역할 DATEADD : 날짜, 시간, 타임스탬프를 지정하는 값만큼 늘리는 역할 OUTER JOIN : A, B 테이블을 조인할 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 사용한다.

WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , dateadd(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
, user_action_flag AS (
  SELECT user_id
       , register_date
       , SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
                   CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END))
         AS next_1_day_action
    FROM action_log_with_mst_users
    GROUP BY user_id, register_date
  )
SELECT *
  FROM user_action_flag
  ORDER BY register_date, user_id

지정한 날의 다음날에 액션을 했는지 0과 1의 플래그로 표현했다.

WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , dateadd(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
, user_action_flag AS (
  SELECT user_id
       , register_date
       , SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
                   CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END))
         AS next_1_day_action
    FROM action_log_with_mst_users
    GROUP BY user_id, register_date
  )
SELECT register_date
     , AVG(100.0 * next_1_day_action) AS repeat_rate_1_day
  FROM user_action_flag
  GROUP BY register_date
  ORDER BY register_date

등록 수의 추이와 경향 보기

SELECT register_date
     , COUNT(DISTINCT user_id) AS register_count
  FROM mst_users
  GROUP BY register_date
  ORDER BY register_date

WITH mst_users_with_year_month AS (
  SELECT *
       , SUBSTRING(register_date, 1, 7) AS year_month
    FROM mst_users
  )
  SELECT year_month
       , COUNT(DISTINCT user_id) AS register_count
       , LAG(COUNT(DISTINCT user_id)) OVER(ORDER BY year_month) AS last_month_count
       , 1.0 * COUNT(DISTINCT user_id) / LAG(COUNT(DISTINCT user_id)) OVER(ORDER BY year_month) - 1
         AS month_over_month_ratio
  FROM mst_users_with_year_month
  GROUP BY year_month

월별 등록수와 전월비를 구하는 쿼리이다. LAG : 명시된 값을 기준으로 이전 로우의 값을 반환

WITH mst_users_with_year_month AS (
  SELECT *
       , SUBSTRING(register_date, 1, 7) AS year_month
    FROM mst_users
  )
SELECT year_month
     , COUNT(DISTINCT user_id) AS register_count
     , COUNT(DISTINCT CASE WHEN register_device = 'pc' THEN user_id END) AS register_pc
     , COUNT(DISTINCT CASE WHEN register_device = 'sp' THEN user_id END) AS register_sp
     , COUNT(DISTINCT CASE WHEN register_device = 'app' THEN user_id END) AS register_app
  FROM mst_users_with_year_month
  GROUP BY year_month

등록 디바이스별 추이를 확인하는 쿼리이다.

WITH user_purchase_amount AS (
  SELECT user_id
       , SUM(amount) AS purchase_amount
    FROM action_log
    WHERE action = 'purchase'
    GROUP BY user_id
  )
, users_with_decile AS (
  SELECT user_id
       , purchase_amount
       , ntile(10) OVER(ORDER BY purchase_amount DESC) AS decile
    FROM user_purchase_amount
  )
, decile_with_purchase_amount AS (
  SELECT decile
       , SUM(purchase_amount) AS amount
       , AVG(purchase_amount) AS avg_amount
       , SUM(SUM(purchase_amount)) OVER(ORDER BY decile) AS cumulative_amount
       , SUM(SUM(purchase_amount)) OVER () AS total_amount
    FROM users_with_decile
    GROUP BY decile
  )

SELECT *
  FROM decile_with_purchase_amount

NTILE : 정해준 값을 ~~개로 나누어라

RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기

R : Recency 최근 구매일 F : Frequency 구매 횟수 M : Monetary 구매금액 합계

WITH purchase_log AS (
  SELECT user_id
       , amount
       , SUBSTRING(stamp, 1, 10) AS dt
    FROM action_log
    WHERE action = 'purchase'
  )
, user_rfm AS (
  SELECT user_id
       , MAX(dt) AS recent_date
       , CURRENT_DATE - MAX(dt::date) AS recency
       , COUNT(dt) AS frequency
       , SUM(amount) AS monetary
    FROM purchase_log
    GROUP BY user_id
  )
SELECT *
  FROM user_rfm

WITH user_action_flag AS (
  SELECT user_id
       , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase
       , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review
       , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
     FROM action_log
     GROUP BY user_id
     )
SELECT *
  FROM user_action_flag

사용자 단위로 purchase, review, favorite 이라는 3개의 액션을 행한 로그가 존재하는지를 0과 1의 플래그로 부여하였다. SIGN : 함수 안의 인자가 음수면 -1, 0이면 0, 양수면 1을 반환하는 함수

WITH user_action_flag AS (
  SELECT user_id
       , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase
       , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review
       , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
     FROM action_log
     GROUP BY user_id
     )
, action_venn_diagram AS (
  SELECT has_purchase
       , has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase, has_review, has_favorite

  UNION ALL
  SELECT NULL AS has_purchase
       , has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_review, has_favorite

  UNION ALL
  SELECT has_purchase
       , NULL AS has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase, has_favorite

  UNION ALL
  SELECT has_purchase
       , has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase, has_review

  UNION ALL
  SELECT NULL AS has_purchase
       , NULL AS has_review
       , has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_favorite

  UNION ALL
  SELECT NULL AS has_purchase
       , has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_review

  UNION ALL
  SELECT has_purchase
       , NULL AS has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY has_purchase

  UNION ALL
  SELECT NULL AS has_purchase
       , NULL AS has_review
       , NULL AS has_favorite
       , COUNT(1) AS users
    FROM user_action_flag
)

SELECT *
  FROM action_venn_diagram
  ORDER BY has_purchase, has_review, has_favorite

WITH action_log_with_dt AS(
  SELECT *
    , SUBSTRING(stamp, 1, 10) AS dt
    FROM action_log
  )
, action_day_count_per_user AS (
  SELECT user_id
       , COUNT(DISTINCT dt) AS action_day_count
    FROM action_log_with_dt
    WHERE dt BETWEEN '2016-11-01' AND '2016-11-07'
    GROUP BY user_id
  )

SELECT action_day_count
     , COUNT(DISTINCT user_id) AS user_count
  FROM action_day_count_per_user
  GROUP BY action_day_count
  ORDER BY action_day_count

구성비와 구성비 누계도 추가로 구할 수 있다.

WITH action_log_with_dt AS(
  SELECT *
    , SUBSTRING(stamp, 1, 10) AS dt
    FROM action_log
  )
, action_day_count_per_user AS (
  SELECT user_id
       , COUNT(DISTINCT dt) AS action_day_count
    FROM action_log_with_dt
    WHERE dt BETWEEN '2016-11-01' AND '2016-11-07'
    GROUP BY user_id
  )
SELECT action_day_count
     , COUNT(DISTINCT user_id) AS user_count
     , 100.0
       * COUNT(DISTINCT user_id)/SUM(COUNT(DISTINCT user_id)) OVER()
       AS composition_ratio
     , 100.0
       * SUM(COUNT(DISTINCT user_id)) OVER(ORDER BY action_day_count
                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
       / SUM(COUNT(DISTINCT user_id)) OVER()
       AS cumulative_ratio
  FROM action_day_count_per_user
  GROUP BY action_day_count
  ORDER BY action_day_count

mst_users 테이블이다.

WITH mst_users_with_int_birth_date AS (
  SELECT *
       , 20170101 AS int_specific_date
       , CAST(REPLACE(SUBSTRING(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
    FROM mst_users
  )
, mst_users_with_age AS (
  SELECT *
       , FLOOR((int_specific_date-int_birth_date)/10000) AS age
    FROM mst_users_with_int_birth_date
    )
SELECT user_id
     , sex
     , birth_date
     , age
  FROM mst_users_with_age

REPLACE : 바꾸려는 문자열과 바꾸려는 문자를 입력하고, 어떤 문자로 바꿀지를 입력하면 된다. CAST : 데이터 형식을 다른 데이터 형식으로 바꿔주는 함수이다. 이 두가지 함수를 사용하여 특정 날짜에 사용자의 나이를 구할 수 있다

이 WITH 구문을 사용하여 연령별 구분을 추가할 수 있다.

WITH mst_users_with_int_birth_date AS (
  SELECT *
       , 20170101 AS int_specific_date
       , CAST(REPLACE(SUBSTRING(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
    FROM mst_users
  )
, mst_users_with_age AS (
  SELECT *
       , FLOOR((int_specific_date-int_birth_date)/10000) AS age
    FROM mst_users_with_int_birth_date
    )
, mst_users_with_category AS (
  SELECT user_id
       , sex
       , age
       , CONCAT(CASE WHEN 20<=age THEN sex
                ELSE '' END
       , CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
              WHEN age BETWEEN 13 AND 19 THEN 'T'
              WHEN age BETWEEN 20 AND 34 THEN '1'
              WHEN age BETWEEN 35 AND 49 THEN '2'
              WHEN age >= 50 THEN '3'
              END) AS category
     FROM mst_users_with_age)

SELECT *
  FROM mst_users_with_category

CONCAT : 문자열을 합쳐주는 역할을 한다.

연령별 구분의 특징 추출하기

이번에는 action_log 테이블과 JOIN하여 각각 구매한 상품의 카테고리를 집계해보자.

WITH mst_users_with_int_birth_date AS (
  SELECT *
       , 20170101 AS int_specific_date
       , CAST(REPLACE(SUBSTRING(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
    FROM mst_users
  )
, mst_users_with_age AS (
  SELECT *
       , FLOOR((int_specific_date-int_birth_date)/10000) AS age
    FROM mst_users_with_int_birth_date
    )
, mst_users_with_category AS (
  SELECT user_id
       , sex
       , age
       , CONCAT(CASE WHEN 20<=age THEN sex
                ELSE '' END
       , CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
              WHEN age BETWEEN 13 AND 19 THEN 'T'
              WHEN age BETWEEN 20 AND 34 THEN '1'
              WHEN age BETWEEN 35 AND 49 THEN '2'
              WHEN age >= 50 THEN '3'
              END) AS category
     FROM mst_users_with_age)

SELECT p.category AS product_category
     , u.category AS user_category
     , COUNT(*) AS purchase_count
  FROM action_log AS p
  JOIN mst_users_with_category AS u
  ON p.user_id = u.user_id
  WHERE action = 'purchase'
  GROUP BY p.category, u.category
  ORDER BY p.category, u.category

사용자의 속성 또는 행동과 관련된 정보를 집계해서 사용자 행동을 조사하고, 서비스를 개선할 때 실마리가 될 수 있는 리포트를 만들어보자.

mst_users 테이블 action_log 테이블

사용자의 액션 수 집계하기

WITH stats AS (
  SELECT COUNT(DISTINCT session) AS total_uu
    FROM action_log
    )

SELECT l.action
     , COUNT(DISTINCT l.session) AS action_uu
     , COUNT(1) AS action_count
     , s.total_uu
     , 100.0 * COUNT(DISTINCT l.session)/s.total_uu AS usage_rate
     , 1.0 * COUNT(1) / COUNT(DISTINCT l.session) AS count_per_user
  FROM action_log AS l
  CROSS JOIN stats AS s
  GROUP BY l.action, s.total_uu

로그인 사용자와 비로그인 사용자를 구분해서 집계하기

WITH action_log_with_status AS (
  SELECT session
       , user_id
       , action
       , CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END
         AS login_status
    FROM action_log
  )
SELECT *
  FROM action_log_with_status

COALESCE : 괄호 안의 값 중에서 null이 아닌 값을 먼저 반환한다. 위 쿼리에서는 user_id가 null 값인 경우에는 ''이 출력되고, user_id가 null 값이 아닌 경우에는 user_id를 그대로 출력하게 되는 것이다.

WITH action_log_with_status AS (
  SELECT session
       , user_id
       , action
       , CASE WHEN COALESCE(MAX(user_id)
                            OVER(PARTITION BY session 
                                 ORDER BY stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                            , '') <> '' THEN 'member' ELSE 'none'
          END AS member_status
    FROM action_log
  )

SELECT *
  FROM action_log_with_status

단 한번이라도 로그인 이력이 있는 사람은 member로 처리하는 쿼리이다.

최댓값과 최솟값 사이의 차이를 구한다.

WITH stats AS (
SELECT MAX(price) AS max_price
     , MIN(price) AS min_price
     , MAX(price) - MIN(price) AS range_price
     , 10 AS bucket_num
  FROM purchase_detail_log
  )
, purchase_log_with_bucket AS (
  SELECT price
       , min_price
       , price - min_price AS diff
       , 1.0 * range_price / bucket_num AS bucket_range
       , FLOOR(1.0 * (price-min_price)/(1.0*range_price/bucket_num)) + 1 AS bucket
    FROM purchase_detail_log, stats
   )

SELECT *
  FROM purchase_log_with_bucket
  ORDER BY amount

위 쿼리를 이용하여 월별 매출과 작대비(作對比)를 구해보겠다.

WITH daily_purchase AS (
  SELECT dt
       , SUBSTRING(dt, 1, 4) AS year
       , SUBSTRING(dt, 6, 2) AS month
       , SUBSTRING(dt, 9, 2) AS date
       , SUM(purchase_amount) AS purchase_amount
       , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt)

SELECT month
     , SUM(CASE year WHEN '2014' THEN purchase_amount END) AS amount_2014
     , SUM(CASE year WHEN '2015' THEN purchase_amount END) AS amount_2015
     , 100.0 * SUM(CASE year WHEN '2015' THEN purchase_amount END)
             / SUM(CASE year WHEN '2014' THEN purchase_amount END)
             AS rate
  FROM daily_purchase
  GROUP BY month
  ORDER BY month

Z차트로 업적의 추이 확인하기

Z차트는 다음 세가지 요소로 구성된다.

월차매출 : 월별 매출 합계 매출누계 : 매출을 집계한 시점부터 누적한 매출 이동년계 : 해당 월의 매출에 과거 11개월의 매출을 합한 값

WITH daily_purchase AS (
  SELECT dt
       , SUBSTRING(dt, 1, 4) AS year
       , SUBSTRING(dt, 6, 2) AS month
       , SUBSTRING(dt, 9, 2) AS date
       , SUM(purchase_amount) AS purchase_amount
       , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt)
, monthly_amount AS (
   SELECT year
        , month
        , SUM(purchase_amount) AS amount
     FROM daily_purchase
     GROUP BY year, month)
, calc_index AS (
    SELECT year
         , month
         , amount
         , SUM(CASE WHEN year = '2015' THEN amount END)
               OVER(ORDER BY year, month ROWS UNBOUNDED PRECEDING)
               AS agg_amount
         , SUM(amount) OVER(ORDER BY year, month
                             ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
               AS year_avg_amount
      FROM monthly_amount
      ORDER BY year, month)

SELECT CONCAT(CONCAT(year, '-'), month) AS year_month
     , amount
     , agg_amount
     , year_avg_amount
  FROM calc_index
  WHERE year = '2015'
  ORDER BY year_month

다면적인 축을 사용해 데이터 집약하기

WITH sub_category_amount AS(
  SELECT category AS category
       , sub_category AS sub_category
       , SUM(price) AS amount
    FROM purchase_detail_log
    GROUP BY category, sub_category
  )
, category_amount AS (
  SELECT category
       , 'all' AS sub_category
       , SUM(price) AS amount
    FROM purchase_detail_log
    GROUP BY category
  )
, total_amount AS (
  SELECT 'all' AS category
       , 'all' AS sub_category
       , SUM(price) AS amount
    FROM purchase_detail_log)

          SELECT category, sub_category, amount FROM sub_category_amount
UNION ALL SELECT category, sub_category, amount FROM category_amount
UNION ALL SELECT category, sub_category, amount FROM total_amount

임의의 레코드를 가진 유사 테이블 만들기

WITH mst_devices AS(
          SELECT 1 AS device_id, 'PC' AS device_name
UNION ALL SELECT 2 AS device_id, 'SP' AS device_name
UNION ALL SELECT 3 AS device_id, '애플리케이션' AS device_name
)

SELECT *
  FROM mst_devices

WITH mst_devices AS(
          SELECT 1 AS device_id, 'PC' AS device_name
UNION ALL SELECT 2 AS device_id, 'SP' AS device_name
UNION ALL SELECT 3 AS device_id, '애플리케이션' AS device_name
)

SELECT u.user_id
     , d.device_name
  FROM mst_users AS u
  LEFT JOIN mst_devices AS d
  ON u.register_device = d.device_id

위 두 쿼리의 결과는 똑같다. UNION ALL은 처리가 비교적 무거우므로 레코드 수가 많아지면 성능 문제가 발생할 수 있다.

매출을 파악하기 위한 데이터 추출

SELECT dt
     , COUNT(*) AS purchase_count
     , SUM(purchase_amount) AS total_amount
     , AVG(purchase_amount) AS avg_amount
  FROM purchase_log
  GROUP BY dt
  ORDER BY dt

이동평균을 사용한 날짜별 추이 보기

SELECT dt
     , SUM(purchase_amount) AS total_amount
     , AVG(SUM(purchase_amount))
       OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
       AS seven_day_avg
     , CASE WHEN 7 = COUNT(*)
                     OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            THEN AVG(SUM(purchase_amount))
                 OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            END AS seven_day_avg_strict
  FROM purchase_log
  GROUP BY dt
  ORDER BY dt

당월 매출 누계 구하기

SELECT dt
     , SUBSTRING(dt, 1, 7) AS year_month
     , SUM(purchase_amount) AS total_amount
     , SUM(SUM(purchase_amount))
           OVER(PARTITION BY SUBSTRING(dt, 1, 7)
                ORDER BY dt ROWS UNBOUNDED PRECEDING)
            AS agg_amount
  FROM purchase_log
  GROUP BY dt
  ORDER BY dt

위 쿼리의 ROWS UNBOUNDED PRECEDING은 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 바꾸어도 된다. 위 쿼리는 가독성 측면에서 수정할 수 있는 부분이 있다.

WITH daily_purchase AS (
  SELECT dt
       , SUBSTRING(dt, 1, 4) AS year
       , SUBSTRING(dt, 6, 2) AS month
       , SUBSTRING(dt, 9, 2) AS date
       , SUM(purchase_amount) AS purchase_amount
       , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt)

SELECT *
  FROM daily_purchase
  ORDER BY dt

위 쿼리를 이용해서 당월 누계 매출을 구할 수 있다.

WITH daily_purchase AS (
  SELECT dt
       , SUBSTRING(dt, 1, 4) AS year
       , SUBSTRING(dt, 6, 2) AS month
       , SUBSTRING(dt, 9, 2) AS date
       , SUM(purchase_amount) AS purchase_amount
       , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt)

SELECT dt
     , year || '-' || month AS year_month
     , purchase_amount
     , SUM(purchase_amount)
           OVER(PARTITION BY year_month
                ORDER BY dt ROWS UNBOUNDED PRECEDING)
                AS agg_amount
  FROM daily_purchase
  ORDER BY dt

SELECT m.user_id
     , m.card_number
     , COUNT(p.user_id) AS purchase_count
     , CASE WHEN m.card_number IS NOT NULL THEN 1
            ELSE 0 END AS has_card
     , SIGN(COUNT(p.user_id)) AS has_purchased
  FROM mst_users_with_card_number AS m
  LEFT JOIN purchase_log AS p
       ON m.user_id = p.user_id
  GROUP BY m.user_id, m.card_number

SIGN : 괄호 안의 값의 음수/양수/0 여부를 -1/+1/0 으로 보여준다.

계산한 테이블에 이름 붙여 재사용하기

CTE : Common Table Expression 의 준말로, 공통 테이블 식을 뜻한다. 일시적인 테이블에 이름을 붙여 재사용할 수 있는 기능이다. 이를 활용하면 복잡한 쿼리의 가독성이 크게 높아진다.

WITH product_sales_ranking AS (
SELECT category_name
     , product_id
     , sales
     , ROW_NUMBER() OVER (PARTITION BY category_name
                          ORDER BY sales DESC) AS rank
  FROM product_sales
  )
  SELECT *
    FROM product_sales_ranking

WITH : 이름을 가진 서브쿼리를 정의한 후 사용하는 구문이다. 쿼리의 전체적인 가독성을 높이고, 재사용할 수 있는 장점이 있다. WITH 별명 AS (SUB QUERY) 와 같은 형식으로 사용한다.

WITH product_sales_ranking AS (
SELECT category_name
     , product_id
     , sales
     , ROW_NUMBER() OVER (PARTITION BY category_name
                          ORDER BY sales DESC) AS rank
  FROM product_sales
  )
, mst_rank AS (
  SELECT DISTINCT rank
    FROM product_sales_ranking
  )

SELECT *
  FROM mst_rank

WITH절에 기입된 서브쿼리의 경우, 먼저 사용된 쿼리는 뒤의 서브쿼리에서 사용될 수 있다. WITH 구문을 사용해서 여러 테이블을 정의할 떄는 쉼표를 사용해 테이블을 나열한다.

WITH product_sales_ranking AS (
SELECT category_name
     , product_id
     , sales
     , ROW_NUMBER() OVER (PARTITION BY category_name
                          ORDER BY sales DESC) AS rank
  FROM product_sales
  )
, mst_rank AS (
  SELECT DISTINCT rank
    FROM product_sales_ranking
  )
SELECT m.rank
     , r1.product_id AS dvd
     , r1.sales AS dvd_sales
     , r2.product_id AS cd
     , r2.sales AS cd_sales
     , r3.product_id AS book
     , r3.sales AS book_sales
  FROM mst_rank AS m
  LEFT JOIN
    product_sales_ranking AS r1
    ON m.rank = r1.rank
    AND r1.category_name = 'dvd'
  LEFT JOIN
    product_sales_ranking AS r2
    ON m.rank = r2.rank
    AND r2.category_name = 'cd'
  LEFT JOIN
    product_sale_ranking AS r3
    ON m.rank = r3.rank
    AND r3.category_name = 'book'
  ORDER BY m.rank

유사 테이블 만들기

이건 다음 포스트에서!

SQL은 행 기반으로 처리하는 것이 기본이다. 행 단위로 저장된 '세로기반'을 열 또는 쉼표로 구분된 문자열 등의 '가로 기반'으로 변환해보자.

다음과 같은 테이블이 있다.

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

다음과 같은 테이블에서는 위의 방법이 유효하지 않다. 열의 수를 미리 정할 수 없기 때문이다. 따라서 이 경우에는 데이터를 쉼표 등으로 구분한 문자열로 변환하는 방법을 생각해 볼 수 있다.

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

LISTAGG : 행을 문자열로 집약하는 함수. REDSHIFT에서 사용된다. PostgreSQL 에서는 STRING_AGG 함수를 사용한다.

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

가로 기반 데이터를 세로 기반으로 변환하는 것은 간단한 일이 아니다. 세로 기반 데이터를 가로 기반으로 변환해서 가공이 쉬운 데이터 형식으로 만드는 방법을 알아보자. 위 테이블은 q1부터 q4까지 행으로 전개할 데이터 수가 고정되었다. 그러한 데이터 수와 같은 수의 일련 번호를 가진 피벗 테이블을 만들고 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 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
  ORDER BY year, quarter

자세한 설명도 없이 CROSS JOIN 과 UNION 구문이 나와서 이해가 안 된 부분이다. 뒤에 챕터에서 자세히 설명이 나온다고 하니... 일단 그런가보다 하고 패스.

여러 개의 테이블 조작하기

여러 개의 테이블을 세로로 결합하기

위와 같은 2개의 테이블이 있다.

SELECT 'app1' AS app_name
     , user_id
     , name
     , email
  FROM app1_mst_users
UNION ALL
SELECT 'app2' AS app_name
     , user_id
     , name
     , NULL AS email
  FROM app2_mst_users

이처럼 비슷한 구조를 가지는 테이블의 데이터를 일괄 처리하고 싶은 경우, UNION ALL 구문을 사용해 여러 개의 테이블을 세로로 결합하면 된다. 결합할 때는 테이블의 컬럼이 완전히 일치해야 하므로, 한쪽 테이블에만 존재하는 컬럼은 SELECT구문에서 포함시키지 않거나, email 컬럼처럼 디폴트 값을 주면 된다.

UNION ALL 구문 대신 UNION 또는 UNION DISTINCT 구문을 사용하면 데이터의 중복을 제외한 결과를 얻을 수 있다.

여러 개의 테이블을 가로로 정렬하기

다음과 같은 3개의 테이블이 있다. 맨 위의 테이블이 마스터 테이블이다. 다음 코드 예는 마스터 테이블의 행 수를 유지한 상태로 여러 개의 테이블을 가로롤 정렬하는 쿼리이다.

SELECT m.category_id
     , m.name
     , s.sales
     , r.product_id AS top_sales_product
  FROM mst_categories AS m
  LEFT JOIN category_sales AS s
            ON m.category_id = s.category_id
  LEFT JOIN product_sale_ranking AS r
            ON m.category_id = r.category_id
            AND r.rank = 1

JOIN : 서로 다른 테이블을 결합해준다. ON을 이용해 JOIN 구문의 조건을 줄 수 있다.

SELECT m.category_id
     , m.name
     , (SELECT s.sales
          FROM category_sales AS s
          WHERE m.category_id = s.category_id) AS sales
     , (SELECT r.product_id
          FROM product_sale_ranking AS r
          WHERE m.category_id = r.category_id
          ORDER BY sales DESC
          LIMIT 1) AS top_sales_product
  FROM mst_categories AS m
  ORDER BY 1

위 예시는 서브 쿼리를 사용해서 이전의 코드와 같은 결과를 내는 코드를 만든 것이다. JOIN을 사용하지 않아 원래 마스터 테이블의 행 수가 변할 걱정 자체가 없으므로, 테이블의 누락과 중복을 회피할 수 있다.

ORDER BY 구문과 SUM/AVG 등의 집약함수를 조합하면, 집약 함수의 적용 범위를 유연하게 지정할 수 있다. FIRST_VALUE / LAST_VALUE : 각각 윈도 내부의 가장 첫번째 레코드와 가장 마지막 레코드를 추출해주는 함수.

프레임 지정 구문에는 여러가지 종류가 있다. 가장 기본적인 것은 ROWS BETWEEN start AND end 이다. start와 end에는 다음과 같은 키워드가 올 수 있다. CURRENT ROW : 현재의 행 n PRECEDING : n행 앞 n FOLLOWING : n행 뒤 UNBOUNDED PRECEDING : 이전 행 전부 UNBOUNDED FOLLOWING : 이후 행 전부

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

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

카테고리들의 순위 상위 2개까지의 상품을 추출하는 쿼리이다.

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 popular_products

카테고리별 순위 순서에서 상위 1개의 상품 ID를 추출할 경우, 위처럼 FIRST_VALUE 윈도 함수를 사용하고, SELECT 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

이미 한번씩 공부했던 쿼리이다

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 구문을 사용한 쿼리에서는, GROUP BY 구문에 지정한 컬럼 또는 집약 함수만 SELECT 구문의 컬럼으로 지정할 수 있다. 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 <컬럼이름>을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약 함수를 적용한다.

그룹 내부의 순서

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
  FROM popular_products
  ORDER BY row

SELECT ABS(x1-x2) AS abs
     , SQRT(POWER(x1-x2,2)) AS rms
  FROM location_1d

1차원에 있는 점 사이의 거리를 구하는 쿼리이다. ABS : 절댓값을 계산하는 함수 POWER : 제곱을 하는 함수 SQRT : 제곱근을 구할 때 사용

2차원 평면위의 두점 사이의 거리를 구해보자

SELECT SQRT(POWER(x1-x2, 2)+POWER(y1-y2,2)) AS dist
  FROM location_2d

피타고라스 공식을 응용한 유클리드 거리를 계산하는 쿼리이다.

날짜/시간 계산하기

SELECT user_id
     , register_stamp::timestamp AS register_stamp
     , DATEADD(hour, 1, register_stamp::timestamp) AS after_1_hour
     , DATEADD(minute, -30, register_stamp::timestamp) AS before_30_minutes

     , register_stamp::date AS register_date
     , DATEADD(day, 1, register_stamp::date) AS after_1_day
     , DATEADD(month, -1, register_stamp::date) AS before_1_month
  FROM mst_users_with_dates

SELECT user_id
     , CURRENT_DATE AS today
     , register_stamp::date AS register_date
     , CURRENT_DATE - register_stamp::date AS diff_days
  FROM mst_users_with_dates

회원 등록일과 현재 날짜의 차이, 회원 등록일과 생년월일과의 날짜 차이를 계산하는 쿼리이다.

IP주소에서 4개의 10진수 부분을 추출하는 쿼리

SELECT ip
     , CAST(SPLIT_PART(ip, '.', 1) AS integer) AS ip_part_1
     , CAST(SPLIT_PART(ip, '.', 2) AS integer) AS ip_part_2
     , CAST(SPLIT_PART(ip, '.', 3) AS integer) AS ip_part_3
     , CAST(SPLIT_PART(ip, '.', 4) AS integer) AS ip_part_4
  FROM (SELECT '192.168.0.1' AS ip) AS t

문자열 연결하기

다음과 같은 테이블에서 문자열을 연결하고자 한다

SELECT user_id
     , CONCAT(pref_name, city_name) AS pref_city
     , pref_name || city_name AS pref_city
  FROM mst_user_location

두번째, 세번째 행이 모두 동일한 값을 도출한다. CONCAT : 여러 문자열을 하나로 합쳐주는 역할을 한다. PostgreSQL 과 Redshift에서는 ||를 이용해서도 동일한 값을 추출할 수 있다.

여러 개의 값 비교하기

하나의 레코드에 포함된 여러 개의 값을 비교하는 방법을 소개해보자.

SELECT year
     , q1
     , q2
     , CASE WHEN q1 < q2 THEN '+'
            WHEN q1 = q2 THEN ' '
            ELSE '-'
            END AS judge_q1_q2
     , q2-q1 AS diff_q2_q1
     , SIGN(q2-q1) AS sign_q2_q1
  FROM quarterly_sales
  ORDER BY 1

CASE 함수를 이용해, 1분기/2분기의 대소비교를 하였다. SIGN 함수를 이용하면 CASE 식보다 간단하게 값의 증감 판정을 할 수 있다. SIGN 함수는 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴한다.

SELECT year
     , GREATEST(q1, q2, q3, q4) as greatest_sales
     , LEAST(q1, q2, q3, q4) as least_sales
  FROM quarterly_sales
  ORDER BY 1

GREATEST, LEAST 함수는 표준 SQL에 포함되지 않지만, 대부분의 SQL 쿼리 엔진에서 구현하고 있다.

SELECT year
     , (COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0))
       / (SIGN(COALESCE(q1, 0)) + SIGN(COALESCE(q2, 0)) + SIGN(COALESCE(q3, 0)) + SIGN(COALESCE(q4, 0))) AS average
  FROM quarterly_sales
  ORDER BY year

각 연도의 평균 분기매출을 구하는 쿼리이다. COALESCE는 Null 값을 어떤 값으로 리턴할지 지정하는 함수이다.

2개의 값 비율 계산하기

SELECT dt
     , ad_id
     , CAST(clicks AS double precision)/impressions AS ctr
     , 100.0 * clicks / impressions AS ctr_as_percent
  FROM advertising_stats
  WHERE dt = '2017-04-01'
  ORDER BY dt, ad_id

CTR(Click Through Rate)를 구하는 쿼리이다. CAST 함수를 이용해, 클릭수를 '더블 프리시전(double precision)' 자료형으로 변환하고 계산해야 결과도 소수점 둘째자리까지 나오게 된다.

SELECT dt
     , ad_id
     , CASE WHEN impressions >0 THEN 100.0 * clicks / impressions
            END AS ctr_as_percent_by_case
  FROM advertising_stats
  ORDER BY dt, ad_id

0으로 나누는 것을 방지하기 위해 CASE 함수를 사용하였다.

1. 코드 값을 레이블로 변경하는 방법 : CASE문 활용하기

데이터의 종류

업무 데이터 : 업무에 필요한 데이터 로그 데이터 : 업무에 직접적으로 필요하지는 않지만 분석을 위해 추출해야 하는 데이터

업무 데이터 : 서비스와 시스템을 운용하기 위한 목적으로 구축된 데이터베이스에 존재하는 데이터. '트랜젝션 데이터'와 '마스터 데이터'로 분류할 수 있다.

업무 데이터의 특징

1) 데이터의 정밀도가 높다 : 여러 데이터 처리를 하는 중에 문제가 발생하면, 트랜잭션과 롤백이라는 기능을 사용해 이러한 문제를 제거할 수 있다. 따라서 데이터의 정합성이 보증된다. 2) 갱신형 데이터 : 매일 다양한 데이터 추가, 갱신, 제거 등이 실행된다. 따라서 데이터를 추출하는 시점에 따라 바뀔 수 있다는 점을 의식해야 한다. 3) 다뤄야 하는 테이블의 수가 많다

트랜잭션 데이터 : 구매, 리뷰, 게임 플레이 데이터처럼 서비스와 시스템을 통해 사용자의 행동을 기록한 데이터. 마스터 데이터 : 마테고리 마스터, 상품 마스터처럼 서비스와 시스템이 정의하고 있는 데이터.

트랜잭션 데이터는 회원 ID와 상품 코드 등이 저장되어 있으므로, 트랜잭션 데이터만으로는 잘 팔리는 상품의 명칭 또는 카테고리 등을 알 수 없는 경우가 많다. 따라서 트랜잭션 데이터의 상품 ID와 마스터 데이터를 결합해서 상품이름, 상품 카테고리, 발매일 등을 명확하게 만들어야 리포트 업무의 폭을 넓힐 수 있다.

로그 데이터 : 통계 또는 분석을 주 용도로 설계된 데이터, 특정 태그를 포함해서 전송된 데이터, 특정 행동을 서버 측에 출력한 데이터. 누적형 데이터 이므로 로그 출력 이후에 가격이 변경되거나 사용자 정보가 변경되더라도 기존의 데이터를 수정하지 않는다.

로그 데이터의 특징

1) 시간, 사용자 엔드 포인트, IP, URL, 레퍼러, Cookie 등의 정보 저장하기 : 업무 데이터는 서비스와 시스템을 구축할 때 필요한 데이터이지만, 로그 데이터는 서비스의 처리에 영향이 거의 없는 정보를 저장한 것이다. 2) 로그 데이터는 추출 방법에 따라 데이터의 정밀도가 달라진다 3) 계속 기록을 추가하는 것뿐이므로 과거의 데이터가 변경되지 않음

회사의 선배가 추천해 준 책인데, 수준이 다소 높아보인다. 공부하는 과정에서 많이 막힐 것 같은데... 우선 해봐야 알지 않겠는가?

만약 책의 수준이 너무 높아, 지금 내가 하기에 버겁다면 중간에 다른 기본서를 한번 더 봐야하지 않을까싶다.

SELECT *
  FROM ( SELECT ACC_LOC_NAME 사고장소
              , ACC_CNT 사고건수
              , RANK() OVER(ORDER BY ACC_CNT DESC NULLS LAST) as 순위
           FROM ACC_LOC_DATA
           WHERE ACC_YEAR = 2017)
  WHERE 순위 <= 5

FROM절의 서브쿼리에서 교통사고 건수가 많은 순으로 순위를 부여하여 결과를 출력한다. 그리고 메인 쿼리에서 서브 쿼리의 결과 중 순위 5위까지만 제한을 걸어 출력한다.

SELECT 년도 "치킨집 폐업 연도"
     , 치킨집 "건수"
  FROM (SELECT 년도
             , 치킨집
             , RANK() OVER (ORDER BY 치킨집 DESC NULLS LAST) 순위
          FROM CLOSING)
  WHERE 순위 = 1

FROM절의 서브쿼리문에서 치킨집 폐업 건수가 높은 순으로 순위를 출력한다. 그리고 메인 쿼리문의 WHERE 절에서 순위가 1위의 데이터만 출력합니다.