場所分雷-復習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グラフは、次の3つの要素で構成されています.
月収:月収合計
収益累計:収益統計から累積された収益
移動年表:過去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