데.분.레 - 복습 9

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

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

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