###### tags: SQL

> rows between unbounded preceding and current row
> https://localcoder.org/what-is-rows-unbounded-preceding-used-for-in-teradata
# 第四章:

計算過去最多七日間的平均營收金額
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_ave,
       --嚴謹計算過去七日間的平均營收金額
       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)
FROM purchase_log
GROUP BY dt
ORDER BY dt

---

--將每日營收轉換成鄰時的資料表,並在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
  	FROM purchase_log
    GROUP BY dt
)

SELECT dt,
	   concat(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

---

--計算每月營收和年成長率
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
  	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

---

--建立2015年營收的Z圖表
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
  	FROM purchase_log
    GROUP BY dt
), 
	monyhly_amount AS (
	--彙總每月營收
    SELECT year, 
      	   month,
      	   SUM(purchase_amount) AS amount
    FROM daily_purchase
    GROUP BY year, month
),
	calc_index AS (
	SELECT year,
      	   month,
      	   amount,
    	   --計算2015年的累計營收
      	   SUM(CASE WHEN year = '2015' THEN amount END) OVER(ORDER BY year, month ROWS UNBOUNDED PRECEDING) AS agg_amount, 
      	   --計算從當月開始到11個月之前的合計營收(移動年營收)
      	   SUM(amount) OVER(ORDER BY year, month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS year_avg_amount
    FROM monyhly_amount
    ORDER BY year, month
)

--最後只擷取2015年的數據
SELECT concat(year, '-', month) AS year_month,
	   amount,
       agg_amount,
       year_avg_amount
FROM calc_index
WHERE year = '2015'
ORDER BY year_month

 

---

--彙總營收相關指標
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
  	FROM purchase_log
    GROUP BY dt
), monthly_purchase AS (
	SELECT year,
  		   month,
  		   --SUM(orders) AS orders,
  		   AVG(purchase_amount) AS avg_amount,
  	 	   SUM(purchase_amount) AS monthly
  	FROM daily_purchase
  	GROUP BY year, month
)

SELECT concat(year, '-', month) AS year_month,
       avg_amount,
       monthly,
       SUM(monthly) OVER(PARTITION BY year ORDER BY month ROWS UNBOUNDED PRECEDING) AS agg_amount,
       --取得前12個月營收
       LAG(monthly, 12) OVER(ORDER BY year, month) AS last_year, 
       --計算當月營收和12個月前營收的比值
       100.0 * monthly / LAG(monthly, 12) OVER(ORDER BY year, month) AS rate
FROM monthly_purchase
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 AS 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 cast(category as text), cast(sub_category as text), cast(amount as text)
FROM sub_category_amount
UNION ALL
SELECT cast(category as text), cast(sub_category as text), cast(amount as text)
FROM category_amount
UNION ALL
SELECT cast(category as text), cast(sub_category as text), cast(amount as text)
FROM total_amount

---

--使用ROLLUP 同時取得類別銷售金額和小計
SELECT COALESCE(category, 'all') AS category,
	   COALESCE(sub_category, 'all') AS sub_category,
       SUM(price) AS amount
FROM purchase_detail_log
GROUP BY ROLLUP(category, sub_category)
 

---

--使用 GROUPING SETS
SELECT
    category,
    sub_category, 
    SUM (price) amount
FROM
    purchase_detail_log
GROUP BY
    GROUPING SETS(
        (category,sub_category),
        (category),
        (sub_category) 
    ); 

---

--計算營收組成的累計百分比與ABC Rank
WITH monthly_sales AS (
	SELECT category,
  		   --計算各類別的營收
  		   SUM(price) AS amount
    FROM purchase_detail_log
  	--將數據範圍限制在目標月份
  	WHERE dt BETWEEN '2015-12-01' AND '2015-12-31'
    GROUP BY category
), sales_composition_ratio AS (
	SELECT category,
  		   amount,
  		   --所佔百分比: 100.0 * 類別營收 / 全體營收
  		   100.0 * amount / SUM(amount) OVER() compositon_ratio,
  		   --累計百分比: 100.0 * 類別累計營收 / 全體營收
  		   100.0 * SUM(amount) OVER(ORDER BY amount DESC) / SUM(amount) OVER() AS cumulative_ratio
  
  	FROM monthly_sales
)

SELECT *,
	   --根據累計百分比進行分級
       CASE
       		WHEN cumulative_ratio BETWEEN 0  AND 70  THEN 'A'
            WHEN cumulative_ratio BETWEEN 70 AND 90  THEN 'B'
            WHEN cumulative_ratio BETWEEN 90 AND 100 THEN 'C'
       END
FROM sales_composition_ratio
ORDER BY amount DESC

---

--取得建立 Fan chart 所需之數據
--注意重點:採取哪個時間點的交售額作為基礎
WITH daily_category_amount AS (

   SELECT dt,
  		  category,
  		  substring(dt, 1, 4) AS year,
  		  substring(dt, 6, 2) AS month,
  		  substring(dt, 9, 2) AS date,
  		  price
   FROM purchase_detail_log
   GROUP BY dt, 
  			category,
  			price
	
), monthly_category_amount AS (

	SELECT concat(year, '-', month) AS year_month,
  	       category,
  		   SUM(price) AS price
    FROM daily_category_amount
  	GROUP BY year, 
  			 month,
  			 category
  
)

SELECT year_month,
	   category,
       price,
       FIRST_VALUE(price) OVER (PARTITION BY year_month, category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS base_amount,
       100.0 * price / FIRST_VALUE(price) OVER (PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS rate
FROM monthly_category_amount
ORDER BY year_month, 
 		 category

---

--計算各數據所屬階級
WITH stats AS (

	SELECT MAX(price) + 1 AS max_price, -- +1 是為了能將所有數據都納入所指定的階級中
  		   MIN(price) AS min_price,
  		   MAX(price) + 1 - 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(標準化價格/階級範圍)，由於index是從1開始所以加上1
  		   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
 

---

--取得建立直方圖所需之數據
WITH stats AS (

	SELECT MAX(price) + 1 AS max_price, -- +1 是為了能將所有數據都納入所指定的階級中
  		   MIN(price) AS min_price,
  		   MAX(price) + 1 - 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(標準化價格/階級範圍)，由於index是從1開始所以加上1
  		   FLOOR(1.0 * (price - min_price) / (1.0 * range_price / bucket_num)) + 1 AS bucket 
    FROM purchase_detail_log, stats
  
)
  
SELECT bucket,
	   --計算階層的上限與下限
	   min_price + bucket_range * (bucket - 1) AS lower_limit,
       min_price + bucket_range * bucket AS upper_limit,
       --計算次數
       COUNT(price) AS num_purchase,
       --計算各階層的合計金額
       SUM(price) AS total_amount
FROM purchase_log_with_bucket
GROUP BY bucket, 
		 min_price, 
         bucket_range
ORDER BY bucket
 

---

--手動調整直方圖的上限與下限

WITH stats AS (
	
	SELECT 5000 AS max_price, --價格的最大值
  		   0 AS min_price, --價格的最小值
  		   5000 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(標準化價格/階級範圍)，由於index是從1開始所以加上1
  		   FLOOR(1.0 * (price - min_price) / (1.0 * range_price / bucket_num)) + 1 AS bucket 
    FROM purchase_detail_log, stats
  
)

SELECT bucket,
	   --計算階層的上限與下限
	   min_price + bucket_range * (bucket - 1) AS lower_limit,
       min_price + bucket_range * bucket AS upper_limit,
       --計算次數
       COUNT(price) AS num_purchase,
       --計算各階層的合計金額
       SUM(price) AS total_amount
FROM purchase_log_with_bucket
GROUP BY bucket, 
		 min_price, 
         bucket_range
ORDER BY bucket