1. 项目简介

1.1业务背景介绍

• 本案例围绕某个互联网小型电商的订单业务来开发。某电商公司，每天都有一些的用户会在线上采购商品，该电商公司想通过数据分析，查看每一天的电商经营情况。例如：电商公司的运营部门想要清楚的看到每天的订单笔数、订单的下单总额、不同支付类型对应的订单笔数和总额等等。
• 为了实现公司的数据分析业务，我们需要开发一套数据分析的系统以实现电商公司的需求

1.2电商的购物流程

• 这里就假设为京东购物举例

 

 

从上图可以看到，每一个用户购买商品，都会浏览商品、提交订单。所以商品、订单是电商中非常重要的。业务将主要围绕商品、订单展开。

1.3电商系统的简单介绍

 

 

• 用户打开浏览器，访问电商页面
• 用户按下回车后，浏览器发出请求，请求电商网站的Web服务器
• Web服务器从数据库取出数据，返回
• 前端页面展示数据

结论

数据是存放在数据库中，我们开展数据分析只需要能够获取到数据库中的数据即可

1.4电商的相关业务术语介绍

1. 我们去淘宝、京东上买的东西，就是一件件的商品。商品包含这些内容：

 

 

 

 

 

 

 

1. 商品的标题

 

 

2. 商品的所属分类

 

 

3. 商品的价格

 

 

4. 商品的颜色

 

 

5. 商品的版本

 

 

6. 商品的介绍

 

1.4.2商品分类

• 绝大多数的电商都有商品商品。每个商品一定是会属于某个类别。例如：电冰箱属于 家用电器 > 大家电 分类。不同的商品可能对应的分类是不一样的。

 

 

 

 

 

 1.4.3订单

• 用户购买商品是通过提交订单来完成的。用户每一次购物都会有订单，订单中包含了订单号、收货人、订单状态、支付方式、商品评价等。

 

 

 1.4.4 订单详情

• 用户可能在一次购买中，买了多个商品。订单详情指的是订单走过来包含的具体信息。例如：订单中包含的商品信息、商品金额、商品数量等。

 

 

 

1.4.5   用户

• 想要购买商品，需要先在电商网站上注册用户。用户包含了很多信息，例如：用户名、密码、性别、生日、以及手机、密码等信息。

 

 

 

 

1.4.6    区域

每个订单都有区域的概念，例如：我们可以选择配送到哪儿，是配置到北京市昌平区百善镇还是其他地方。

 

 

1.5     技术方案介绍

本项目基于MySQL数据库，使用Kettle、Apache Superset实现数据可视化。案例使用MySQL作为数据分析的存储以及查询引擎、以Kettle作为数据处理脚本执行工具、以及Apache Superset实现数据可视化展示。

案例的最终效果如下：

 

 2.项目架构说明

 

2.1  系统架构

 

 

2.2数据流程图

 

 

1.  通过Kettle将MySQL业务系统数据库中，将数据抽取出来，然后装载到MySQL数据仓库中。
2. 编写SQL脚本，对MySQL数据仓库中的数据进行数据分析（分组、聚合等），并将分析后的结果保存。
3. 使用 Superset 将保存下来的分析结果以图形的方式展示出来。

 

 

3.搭建项目环境

这里是写博客步骤又不可以少所以

第一步

1. 搭建虚拟机
2. 安装jdk
3. 安装MySQL8.0
4. 安装superset

有问题参考我博客的安装文档

第二步

1. 用SQLyog执行连接虚拟机的MySQL

 

 

 

 第三部

执行下面SQL脚本

 https://files.cnblogs.com/files/it-wp/BI%E6%A1%88%E4%BE%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E8%84%9A%E6%9C%AC.7z

创建两个伪数仓

#ods层数据仓库

create database if not exists ods_it_shop;
#APP层数据仓库

create database if not exists it_shop_bi;

4.  项目开发

4.1     表结构概览

表名	说明
it_areas	行政区域表，例如：北京市、昌平区等。
it_goods	商品表，保存了商品的基本信息。例如：商品的唯一标识、商品的名称、店铺ID、商品的分类等。
it_goods_cats	商品分类表，每一个商品都有自己的分类。例如：海尔的某个冰箱属于：家用电器 > 大家电 > 冰箱 这样的一个分类。
it_orders	订单表，用户提交的订单将保存在该表中。表中包含了：下订单的用户、订单的状态、订单的支付金额、订单所属的区域、用户所属的地址等。
it_order_goods	订单明细表，订单明细表包含了订单中的包含的商品信息。用户可以同时买多个商品，然后提交一个订单。例如：提交的订单中包含一个手机、和一个电冰箱。订单明细中包含了用户买的商品数据和订单信息。例如：该订单明细对应的订单是什么、买了几个这样的商品、商品的ID是什么等。
it_users	用户信息表，包含了用户的ID、用户名、密码等信息。

 

4.2     表字段解释

it_areas/行政区域表

列名	类型	说明
areaId	int(11)	区域ID
parentId	int(11)	父ID
areaName	varchar(100)	地区名称
areaKey	char(10)	地区首字母
areaType	tinyint(4)	级别标志1：省，2：市，3：县区
createTime	varchar(25)	创建时间

it_goods/商品表

列名	类型	说明
goodsId	bigint(11)	商品id
goodsSn	varchar(20)	商品编号
goodsName	varchar(200)	商品名称
goodsImg	varchar(150)	商品图片
shopId	bigint(11)	门店ID
marketPrice	decimal(11,2)	市场价
shopPrice	decimal(11,2)	门店价
isHot	tinyint(4)	是否热销产品
goodsCatId	int(11)	goodsCatId
saleNum	int(11)	总销售量
createTime	varchar(25)	创建时间

 

it_goods_cats/商品分类

列名	类型	说明
catId	int(11)	品类ID
parentId	int(11)	父ID
catName	varchar(20)	分类名称
dataFlag	tinyint(4)	删除标志
createTime	varchar(25)	建立时间
cat_level	tinyint(4)	分类级别，共3级

 

it_orders/订单表

列名	类型	说明
orderId	bigint(11)	订单id
orderNo	varchar(20)	订单编号
shopId	bigint(11)	门店id
userId	bigint(11)	用户id
orderStatus	tinyint(4)	订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:
goodsMoney	decimal(11,2)	商品金额
deliverMoney	decimal(11,2)	运费
totalMoney	decimal(11,2)	订单金额（包括运费）
realTotalMoney	decimal(11,2)	实际订单金额（折扣后金额）
payType	tinyint(4)	支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他
isPay	tinyint(4)	是否支付
userName	varchar(20)	收件人姓名
userAddress	varchar(255)	收件人地址
userPhone	char(20)	收件人电话
createTime	varchar(25)	下单时间
noticeDeliver	tinyint(3) unsigned	提醒发货 0:未提醒 1:已提醒
payTime	varchar(25)	支付时间
totalPayFee	int(11)	总支付金额

it_order_goods/订单明细表

列名	类型	说明
ogId	bigint(11)	订单明细ID
orderId	bigint(11)	订单ID
goodsId	bigint(11)	商品ID
goodsNum	bigint(11)	商品数量
goodsPrice	decimal(13,0)	商品价格
payPrice	decimal(13,0)	实际支付价格
goodsName	varchar(600)	商品名称
goodsImg	varchar(450)	商品图片
createtime	varchar(75)	创建时间

it_users/用户表

 

列名	类型	说明
userId	int(11)	用户ID
loginName	varchar(20)	登录名
loginSecret	int(11)	登录秘钥
loginPwd	varchar(50)	登录密码
userSex	tinyint(4)	用户性别
userName	varchar(100)	用户名
trueName	varchar(100)	用户真实姓名
brithday	date	生日
userPhoto	varchar(200)	用户照片
userQQ	varchar(20)	用户QQ
userPhone	char(11)	用户电话
userScore	int(11)	用户积分
userTotalScore	int(11)	用户总积分
userFrom	tinyint(4)	用户来源
userMoney	decimal(11,2)	用户现金
lockMoney	decimal(11,2)	账户现金
createTime	datetime	创建时间
payPwd	varchar(100)	支付密码
rechargeMoney	decimal(11,2)	充值金额

4.3.1    数据抽取业务分析

我们已经大概熟悉了上面的6张表，这6张表有时候并不是将所有数据一次性原封不动地同步到数据仓库中，而是有一些处理细节。考虑以下几个可能出现的业务场景：

　　1. 每一天都需要进行订单的分析，例如：2021年2月2日一共有多少笔订单、订单的总额是多少。

　　2. 每一天都需要进行用户的分析，例如：2021年2月2日一共注册有多少个用户。

　　3. 商品分类、区域的变化率很少，因为分类、区域几乎都是常年不变的。

　　4. 商品的数据相对变化频率较高，因为可能每天都会有商品信息的更新。

表名	说明	装载表	抽取方式	抽取周期
it_areas	行政区域表	ods_it_areas	增量同步抽取	每年
it_goods	商品表	ods_it_goods	增量同步抽取	每天
it_goods_cats	商品分类表	ods_it_goods_cats	全量同步抽取	每周
it_orders	订单表	ods_it_orders	增量同步抽取	每天
it_order_goods	订单明细表	ods_it_order_goods	增量同步抽取	每天
it_users	用户信息表	ods_it_users	增量同步抽取	每天

• 全量同步抽取：将所有数据同步抽取到数据仓库
• 增量同步抽取：只抽取新增的数据到数据仓库

4.3.2    关于 ods 的意义

• ODS（英语：Operational 业务/ Data 数据/ Store 存储)是一种数据架构或数据库设计的概念，出现原因是来自于当需要集成来自多个系统的数据，结果又要给一或多个系统使用时。
• 数据仓库的ods表是将业务系统数据库表原样抽取进来，结构几乎是一样的，只不过加了一个抽取数据的日期字段。

4.3.3    每周数据抽取作业开发

4.3.3.1 开发行政区域数据抽取

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 数据库配置就参考输入

其他转换配置参考上面

其他看板参考上面的看板

4.4.4.2   开发商品分类数据抽取

 

 

 4.3.3.3开发每周数据抽取作业

新建-作业-从通用里面拖出组件-配置-连线

 

 

 其他作业参考这个

4.3.4.1    开发商品表数据抽取

 

 

 

4.3.4.2   开发订单数据抽取

 

 

 

 

 

 

4.3.4.3    开发用户信息表抽取

 

 

 

4.4.2   创建用于保存数据分析结果的表

--创建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;

-- 分析某天的订单总笔数、总金额
INSERT INTO `it_shop_bi`.app_order_total
SELECT NULL,                                  -- 对应无意义的唯一标识列
       dt,                                    -- 某一天
       SUM(t1.realTotalMoney) AS total_money, -- 订单总金额
       COUNT(1)               AS total_cnt    -- 订单总笔数
FROM `ods_it_shop`.ods_it_orders t1
WHERE t1.dt = '${dt}';


-- 统计每日下订单用户数
INSERT INTO it_shop_bi.app_order_user
SELECT NULL,
       dt,
       SUM(DISTINCT t1.userId)
FROM `ods_it_shop`.ods_it_orders t1
WHERE t1.dt = '${dt}';


-- 统计不同支付方式的订单总金额、总笔数
INSERT INTO `it_shop_bi`.app_order_paytype
SELECT NULL,
       dt,
       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 dt = '${dt}'
GROUP BY t1.payType;


-- 统计不同一级商品分类订单总额/总笔数分析
DROP TABLE IF EXISTS `it_shop_bi`.tmp_goods_cat;
CREATE TEMPORARY 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;


-- 在商品分类临时表上添加索引
CREATE UNIQUE INDEX idx_goods_cat3 ON `it_shop_bi`.tmp_goods_cat (cat_id_l3);

-- 查看临时表中的数据
SELECT *
FROM `it_shop_bi`.tmp_goods_cat;

INSERT INTO `it_shop_bi`.app_order_goods_cat
SELECT NULL,
       t3.dt,
       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 t3.dt = '${dt}'
GROUP BY t1.cat_name_l1;



-- 统计订单笔数TOP5用户分析
INSERT INTO `it_shop_bi`.app_order_user_top5
SELECT
    NULL,
    SUBSTRING(CURDATE(), 1, 7) AS today,
    userId,
    userName,
    COUNT(orderId) AS total_cnt
FROM `ods_it_shop`.ods_it_orders t2
WHERE SUBSTRING(createTime, 1, 7) IS NOT NULL
GROUP BY userId,userName
ORDER BY total_cnt DESC
LIMIT 5

 

4.9   开发Kettle作业

 

 

 

 

 

 

 

5.使用Apache Superset进行数据可视化

 

 

 

cd  cd /root/anaconda3/ superset run -h 192.168.88.100 -p 8080 --with-threads --reload --debugger

• 登录superset
• http://192.168.88.100:8080/superset/welcome
• 用户名： admin
• 密码：123456

1.1.1    添加MySQL数据库

mysql://root:123456@192.168.88.100/it_shop_bi?charset=utf8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.1     订单支付方式分析开发

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5.7 订单商品分类分析

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5.8 Superset Dashboard（看板）开发