#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;