#ods層數據倉庫

create database if not exists ods_it_shop;
#APP層數據倉庫

create database if not exists it_shop_bi;

--創建APP層的資料庫
CREATE DATABASE `it_shop_bi`；
-- 訂單支付總額/總筆數分析
CREATE TABLE IF NOT EXISTS it_shop_bi.app_order_total(
  id INTEGER  PRIMARY KEY AUTO_INCREMENT,    -- 用作唯一記錄標識的組件，無實際意義
  dt DATE,                                    -- 對應的日期
  total_money DOUBLE,                       -- 總支付金額
  total_cnt INTEGER                          -- 總訂單筆數
);


SELECT
  SUBSTRING(t1.createTime, 1, 10) AS dt,  -- 某一天
  SUM(t1.realTotalMoney) AS total_money,  -- 訂單總金額
  COUNT(1) AS total_cnt                     -- 訂單總筆數
FROM
 ods_it_orders t1
WHERE
 SUBSTRING(t1.createTime, 1, 10) = '2019-09-05'
GROUP BY  SUBSTRING(t1.createTime, 1, 10);


USE `ods_it_shop`;
INSERT INTO it_shop_bi.app_order_total
SELECT
  NULL,
  SUBSTRING(t1.createTime, 1, 10) AS dt,  -- 某一天
  SUM(t1.realTotalMoney) AS total_money,  -- 訂單總金額
  COUNT(1) AS total_cnt                     -- 訂單總筆數
FROM
 ods_it_orders t1
WHERE
 SUBSTRING(t1.createTime, 1, 10) = '2019-09-05'
GROUP BY  SUBSTRING(t1.createTime, 1, 10);

USE ``;
-- 訂單用戶分析表
CREATE TABLE IF NOT EXISTS it_shop_bi.app_order_user(
  id INTEGER  PRIMARY KEY AUTO_INCREMENT,   -- 唯一標識
  dt DATE,                       -- 統計日期
  total_user_cnt INTEGER                 -- 總用戶數
);

-- 統計每日下訂單用戶數
SELECT
  SUBSTRING(t1.createTime, 1, 10) AS dt,
  COUNT(DISTINCT t1.userId) AS user_total
FROM
  `ods_it_shop`.ods_it_orders t1
WHERE
  SUBSTRING(t1.createTime, 1, 10) = '2019-09-05'
GROUP BY  SUBSTRING(t1.createTime, 1, 10);


-- 統計每日下訂單用戶數
INSERT INTO `it_shop_bi`.app_order_user
SELECT
  NULL,
  SUBSTRING(t1.createTime, 1, 10) AS dt,
  SUM(DISTINCT t1.userId) AS user_total
FROM
  `ods_it_shop`.ods_it_orders t1
WHERE
  SUBSTRING(t1.createTime, 1, 10) = '2019-09-05'
GROUP BY  SUBSTRING(t1.createTime, 1, 10);




-- 創建支付方式訂單總額/訂單筆數分析
CREATE TABLE IF NOT EXISTS `it_shop_bi`.app_order_paytype
(
    id          INTEGER AUTO_INCREMENT PRIMARY KEY, -- 唯一標識（無意義）
    dt          DATE,                               -- 統計日期
    pay_type    VARCHAR(20),                      -- 支付方式
    total_money DOUBLE,                             -- 總支付金額
    total_cnt   INTEGER                             -- 總訂單筆數
);

-- 統計不同支付方式的訂單總金額、總筆數
SELECT
   '2019-09-05',
  CASE WHEN payType = 1 THEN '支付寶'
   WHEN payType = 2 THEN '微信'
   WHEN payType = 3 THEN '信用卡'
   ELSE '其他'
  END AS payType,
  SUM(t1.realTotalMoney) AS total_money,
  COUNT(1) AS total_cnt
FROM
 `ods_it_shop`.ods_it_orders t1
WHERE
 SUBSTRING(dt,1,10) = '2019-09-05'
GROUP BY t1.payType;



-- 統計不同支付方式的訂單總金額、總筆數
INSERT INTO `it_shop_bi`.app_order_paytype
SELECT
   NULL,
   '2019-09-05',
  CASE WHEN payType = 1 THEN '支付寶'
   WHEN payType = 2 THEN '微信'
   WHEN payType = 3 THEN '現金'
   ELSE '其他'
  END AS payType,
  SUM(t1.realTotalMoney) AS total_money,
  COUNT(1) AS total_cnt
FROM
 `ods_it_shop`.ods_it_orders t1
WHERE
 SUBSTRING(dt,1,10) = '2019-09-05'
GROUP BY t1.payType;


-- 創建下訂單用戶最多的前5名
CREATE TABLE IF NOT EXISTS `it_shop_bi`.app_order_user_top5
(
    id        INTEGER AUTO_INCREMENT PRIMARY KEY,-- 唯一標識（無意義）
    dt        VARCHAR(10),-- 統計日期
    userid    VARCHAR(20),                       -- 用戶id
    username  VARCHAR(50),                       -- 用戶姓名
    total_cnt INTEGER                            -- 總訂單筆數
);

-- 方式1
-- 統計訂單筆數TOP5用戶分析
SELECT
 '2019-09'
 , t.userId
  ,t.userName
  ,COUNT(orderId)  AS total_cnt
FROM `ods_it_shop`.ods_it_orders t
WHERE SUBSTRING(createTime,1,7) = '2019-09' # 2019-09
GROUP BY t.userName, t.userId
ORDER BY total_cnt DESC
LIMIT 5;


-- 統計訂單筆數TOP5用戶分析
INSERT INTO `it_shop_bi`.app_order_user_top5
SELECT
    NULL,
    '2019-09',
    userId,
    userName,
    COUNT(orderId) AS total_cnt
FROM `ods_it_shop`.ods_it_orders
WHERE SUBSTRING(createTime, 1, 7) = '2019-09'
GROUP BY userId,userName
ORDER BY total_cnt DESC
LIMIT 5

-- 創建商品分類訂單總額/訂單筆數分析結果表
CREATE TABLE IF NOT EXISTS `it_shop_bi`.app_order_goods_cat
(
    id          INTEGER AUTO_INCREMENT PRIMARY KEY,-- 唯一標識（無意義）
    dt          DATE,                              -- 統計日期
    cat_name    VARCHAR(50),                       -- 一級分類名稱
    total_money DOUBLE,                            -- 訂單總金額
    total_num   INTEGER                            -- 訂單總筆數
);


-- 統計不同一級商品分類訂單總額/總筆數分析
CREATE TABLE `it_shop_bi`.tmp_goods_cat
AS
SELECT t3.catId   AS cat_id_l3,   -- 3級分類id
       t3.catName AS cat_name_l3, -- 3級分類名稱
       t2.catId   AS cat_id_l2,   -- 2級分類id
       t2.catName AS cat_name_l2, -- 2級分類名稱
       t1.catId   AS cat_id_l1,   -- 1級分類id
       t1.catName AS cat_name_l1  -- 1級分類名稱
FROM `ods_it_shop`.ods_it_goods_cats t3,
     `ods_it_shop`.ods_it_goods_cats t2,
     `ods_it_shop`.ods_it_goods_cats t1
WHERE t3.parentId = t2.catId
  AND t2.parentId = t1.catId
  AND t3.cat_level = 3;

SELECT * FROM `it_shop_bi`.tmp_goods_cat;





-- 統計分析不同一級商品分類對應的總金額、總筆數
SELECT
  '2019-09-05',
  t1.cat_name_l1 AS goods_cat_l1,
  SUM(t3.payPrice * t3.goodsNum) AS total_money,
  COUNT(DISTINCT t3.orderId) AS total_cnt
FROM
  `it_shop_bi`.tmp_goods_cat t1
LEFT JOIN `ods_it_shop`.ods_it_goods t2
  ON t1.cat_id_l3 = t2.goodsCatId
LEFT JOIN `ods_it_shop`.ods_it_order_goods t3
  ON t2.goodsId = t3.goodsId
WHERE
  SUBSTRING(t3.createTime, 1, 10) = '2019-09-05'
GROUP BY
  t1.cat_name_l1;
  
-- 執行跑了2.03秒  
  
  
#性能優化
CREATE UNIQUE INDEX idx_goods_cat3 ON `it_shop_bi`.tmp_goods_cat(cat_id_l3);

CREATE UNIQUE INDEX idx_it_goods ON `ods_it_shop`.ods_it_goods(goodsId);

CREATE INDEX idx_it_order_goods ON `ods_it_shop`.ods_it_order_goods(goodsId);


-- 優化後跑了0.038秒

 SELECT
    NULL,
   '2019-09-05',
   t1.cat_name_l1 AS goods_cat_l1,
   SUM(t3.payPrice * t3.goodsNum) AS total_money,
   COUNT(DISTINCT t3.orderId) AS total_cnt
 FROM
   `it_shop_bi`.tmp_goods_cat t1
 LEFT JOIN
   `ods_it_shop`.ods_it_goods t2
   ON t1.cat_id_l3 = t2.goodsCatId
 LEFT JOIN
   `ods_it_shop`.ods_it_order_goods t3
   ON t2.goodsId = t3.goodsId
 WHERE
   SUBSTRING(t3.createTime, 1, 10) = '2019-09-05'
 GROUP BY
   t1.cat_name_l1;