ETL电商项目总结

最新推荐文章于 2024-07-15 19:53:14 发布

大强哥666

最新推荐文章于 2024-07-15 19:53:14 发布

阅读量367

点赞数 14

分类专栏： ETL工程师 kettle sql 文章标签： etl 数据仓库

本文链接：https://blog.csdn.net/liuzhiqiang66/article/details/140436547

版权

ETL工程师同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

kettle

3 篇文章 0 订阅

订阅专栏

sql

1 篇文章 0 订阅

订阅专栏

ETL电商项目总结

ETL电商业务简介及各数据表关系

业务背景

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

下面以京东电商为例，京东首页浏览商品页面：这个页面主要涉及的是《产品分类表》，往往电商网站只会有一个产品分类表；这个《产品分类表》的查询方式采用自连接查询方式，通常类似于这种大类套小类的场景，如同省，市，县这样分层的场景，都是采用“自连接”查询方式。

在这里插入图片描述

电商的购物流程

以京东电商网上购物为例，在京东上购买商品的一般流程是这样的：每一个用户购买商品，都会浏览商品、提交订单。所以商品、订单是电商中非常重要的。本次的综合案例，将主要围绕商品、订单展开。整个电商购物流程如下图所示：

在这里插入图片描述

电商系统结构介绍

电商系统简单结构介绍：1、用户打开浏览器，访问电商页面。→2、用户按下回车后，浏览器发出请求，请求电商网站的Web服务器。→3、Web服务器从数据库取出数据。→4、返回前端页面展示数据。

在这里插入图片描述

电商相关业务术语

电商相关业务术语：**一、商品：**1、商品的标题；2、商品的所属分类；3、商品的价格；4、商品的颜色；5、商品的版本；6、商品的介绍。**二、商品分类：**绝大多数的电商都有商品分类。每个商品一定是会属于某个类别。例如：电冰箱属于（家用电器 > 大家电分类）。不同的商品可能对应的分类是不一样的。**三、订单（订单表）：**用户购买商品是通过提交订单来完成的。用户每一次购物都会有订单，订单中包含了订单号、收货人、订单状态、支付方式、商品评价等。**四、订单详情（订单详情表，订单表和订单详情表通常是一对多关系）：**用户可能在一次购买中，买了多个商品。订单详情指的是订单走过来包含的具体信息。例如：订单中包含的商品信息、商品金额、商品数量等。**五、用户：**想要购买商品，需要先在电商网站上注册用户。用户包含了很多信息，例如：用户名、密码、性别、生日、以及手机、密码等信息。**六、区域：**每个订单都有区域的概念，例如：我们可以选择配送到哪儿，是配置到北京市昌平区百善镇还是其他地方。

在这里插入图片描述

技术方案

本项目基于MySQL搭建数据仓库，使用Kettle进行数据处理，编写SQL脚本，对MySQL数据仓库中的数据进行数据分析（分组、聚合等），并将分析后的结果保存。最后使用帆软FineBI借助Mysql搭建的数据仓库实现数据可视化分析。项目使用MySQL作为数据分析的存储以及查询引擎、以Kettle作为数据处理脚本执行工具、以及帆软FineBI实现数据可视化展示。

在这里插入图片描述

电商项目下属业务数据表

业务数据库（itcast_shop）下属各表：

在这里插入图片描述

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

ityewu_areas行政区域表

字段名	说明
areaId	区域ID
parentId	父ID
areaName	地区名称
areaKey	地区首字母
areaType	级别标志，0：省，,1：市，2：县区
createTime	创建时间

ityewu_goods商品表

字段名	说明
goodsId	商品id：相当于主键
goodsSn	商品编号：如超市商场上商品条形码，是商品的唯一编码
goodsName	商品名称
goodsImg	商品图片
shopId	门店ID
marketPrice	市场价
shopPrice	门店价
isHot	是否热销产品 0:否 1:是
goodsCatId	goodsCatId 最后一级商品分类ID
saleNum	总销售量
createTime	创建时间

ityewu_goods_cats商品分类表

字段名	说明
catId	品类ID
parentId	父ID
catName	分类名称
dataFlag	删除标志 1:有效 -1：删除
createTime	创建时间
cat_level	分类级别，共3级

ityewu_orders订单表

字段名	说明
orderId	订单id：主键，和实际业务没啥关系，只是为了方便后期查询标注
orderNo	订单编号
shopId	门店id
userId	用户id
orderStatus	订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货
goodsMoney	商品金额
deliverMoney	运费
totalMoney	订单金额（包括运费）
realTotalMoney	实际订单金额（折扣后金额）
payType	支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他
isPay	是否支付 0:未支付 1:已支付
userName	收件人姓名
userAddress	收件人地址
userPhone	收件人电话
createTime	创建时间
payTime	支付时间
totalPayFee	总支付金额

ityewu_order_goods订单明细表

ityewu_orders（订单表）与ityewu_order_goods（订单明细表）存在一对多的关系。

字段名	说明
ogId	订单明细ID
orderId	订单ID
goodsId	商品ID
goodsNum	商品数量
goodsPrice	商品价格
payPrice	实际支付价格
goodsName	商品名称
goodsImg	商品相片
createtime	创建时间

ityewu_users用户表

字段名	说明
userId	用户ID
loginName	登录名
loginSecret	登录秘钥
loginPwd	登录密码
userSex	用户性别
userName	用户名
trueName
brithday
userPhoto
userQQ
userPhone
userScore
userTotalScore
userFrom
userMoney
lockMoney
createTime
payPwd
rechargeMoney

创建数仓数据库（itcast_shop_bi）

执行下面代码，创建出一个名称为（itcast_shop_bi）的数据库。

create database itcast_shop_bi default charset = utf8;

目前，（itcast_shop_bi）中是没有任何数据的，是一个空的数据库。而后续我们的所有数据分析都将在该数据库中进行。我们第一件事情就是要将（itcast_shop）数据库中的所有表抽取到（itcast_shop_bi）数据仓库中。要抽取并装载数据到（itcast_shop_bi）中，我们首先要在（itcast_shop_bi）中创建对应的表。

数据抽取业务分析

我们已经大概熟悉了上面的6张表，这6张表有时候并不是将所有数据一次性原封不动地同步到数据仓库中，而是有一些处理细节。考虑以下几个可能出现的业务场景：
1、每一天都需要进行订单的分析，例如：2020年4月18日一共有多少笔订单、订单的总额是多少。
2、每一天都需要进行用户的分析，例如：2020年4月18日一共注册有多少个用户。
3、商品分类、区域的变化率很少，因为分类、区域几乎都是常年不变的（可以设置半年或一年抽取更新一次）。
4、商品的数据和订单的数据相对变化频率较高，因为可能每天都会有商品信息的更新。

因此，结合上述的业务场景，我们可以确定数据的抽取周期：

各业务表抽取周期

kettle中，如果抽取的过程是：表输入→表输出，通常是全量抽取，而表输入→插入/更新，通常是增量抽取。

表名	说明	装载表	抽取方式	抽取周期
ityewu_areas	行政区域表（很久才更新）	ods_ityewu_areas	同步增量抽取（数据量小时，也可全量）	每月
ityewu_goods	商品表	ods_ityewu_goods	同步增量抽取	每天
ityewu_goods_cats	商品分类表（很久才更新）	ods_ityewu_goods_cats	同步增量抽取（数据量小时，也可全量）	每月
ityewu_orders	订单表	ods_ityewu_orders	同步增量抽取	每天
ityewu_order_goods	订单明细表	ods_ityewu_order_goods	同步增量抽取	每天
ityewu_users	用户信息表	ods_ityewu_users	同步增量抽取	每天

ODS的含义

ODS（英语：Operational 业务/ Data 数据/ Store 存储)是一种数据架构或数据库设计的概念，出现原因是来自于当需要集成来自多个系统的数据，结果又要给一或多个系统使用时，我们就可以把源数据表抽取到ODS层中。数据仓库的ods表是将业务系统数据库表原样抽取进来，结构几乎是一样的，只不过加了一个抽取数据的日期字段，因为他有个时间限制，而这个时间就取进行抽取时的日期时间值。有了这个时间字段才可以方便后期统计时效性，因为一般数仓的超过一定的时间，就失效了，需要重新加载更新。

kettle概念模型

在这里插入图片描述

kettle的执行分为两个层次：job（作业）和transformation（转换）。transformation由两个核心构成，一个是Step（步骤：可以给Hop发送数据，也可以读取hop里的数据），一个是Hop（跳，可以将数据发给Step）。

Job主要是做一些执行，得到文件，及校验，job可以执行本身，也可以执行转换（transformation），也可以校验数据表或文件是否存在，也可以执行Shell脚本或SQL脚本，也可以发送邮件。得到文件可以使用FTP，SFTP，HTTP。

kettle创建资源库

1、数据库资源库

数据库资源库是将作业和转换相关的信息存储在数据库中，执行的时候直接去数据库读取信息，很容易跨平台使用。

数据库资源库搭建步骤：
1、点击右上角connect→Repository Manager（存储库管理器），然后点击Other Resporitory（其他存储库）按钮。

在这里插入图片描述

	2、点击Database Repository（数据库存储库）按钮→Get Started（开始）按钮。

在这里插入图片描述

	3、设置连接详细信息，依次填写“显示名称”，“数据库连接名称”，勾选“启动时连接”（可勾选可不勾选）。

在这里插入图片描述

	4、点击上一步的“数据库连接名称”右侧的向右箭头，弹出的功能框中点击“Create New Connection(创建新连接)”按钮，然后设置对应的数据库连接，选择相应的数据库类型（如mysql），设置“主机名称”，“数据库名称”，“端口号”，“用户名”，“密码”，设置完毕后点击“测试”按钮，显示测试成功后，点击“确定”按钮，然后点击“确认”按钮完成数据库资源库的配置。

在这里插入图片描述

5、第4步配置成功后，点击“Back“按钮返回到第3步的界面中，然后点击”Finish”按钮，系统显示“Congratulations(成功）”，再然后点击“Connect Now（现在连接）按钮”连接资源库，默认账号密码均为admin（也可以直接关闭后从步骤1右上端的Connect连已经配置好的数据库资源库）。

在这里插入图片描述

	6、将本地存在的转换或者job导入资源库。选择“从XML文件导入”按钮，选中本地的转换或job后，在数据库资源库对应的存储位置点击确认即完成本地上传到资源库的操作。（连接好数据库资源库后，第1步右上端的connect上会显示对应的资源库名称），如果数据库资源库上有多个转换或者job的话，在“工具”→“资源库”→“探索资源库”中看到一大推的转换或者job。

在这里插入图片描述

2、文件资源库

	文件资源库是：将作业和转换相关的信息存储在指定的目录中，其实和XML的方式一样。创建方式跟创建数据库资源库步骤类似，只是不需要用户密码就可以访问，跨平台使用比较麻烦。

文件资源库搭建步骤：
1、点击右上角connect→Repository Manager（存储库管理器）→Add按钮→Other Resporitory（其他存储库）按钮→点击File Repository（数据库存储库）按钮→Get Started（开始）按钮。

在这里插入图片描述

	2、填写相关信息，点击Finish按钮进行保存。

在这里插入图片描述

3、建立数据库连接

新建数据库连接：1、输入数据库连接名称如（itcast_shop_db）。→2、选择所需要的数据库类型。→3、依次设置主机名称，数据库名称，用户名及密码等等。→4、最后测试通过后点击确认即可完成数据库连接配置。注意：如果今后有多处项目都需要利用该数据库连接，可以选中（DB连接下的itcast_shop_db数据库连接），右键，弹出的功能中点击（共享）按钮，所创建的数据库连接就会变成深色。

在这里插入图片描述

ODS层抽取区域表和商品分类表

每月数据抽取作业开发：根据之前的分析，行政区域表和商品分类表都是为增量同步抽取，所以我们只需要全部抽取到数据仓库中建表即可。但需要注意：我们需要清晰地标识出数据是哪天抽取过来的，所以需要额外添加一个当前日期的字段。

创建数据流图

因为行政区域表和商品分类表都是为增量同步抽取，且都是很久才更新一次，设定两者的抽取周期都是（每月）更新一次。

注意：其他的客户表，订单表，订单明细表，商品表的ODS抽取方式与行政区域表和商品分类表的抽取方式类似，只不过在配置定时调度时，需要修改定时调度的时间。

在这里插入图片描述

表输入配置

1、输入表输入的名称（如这里的区域表输入）。→2、选择对应的数据库连接。→3、输入相应的SQL语句（如下的SQL代码）。→4、点击“预览”按钮查看数据。→5、预览数据确认无误后点击“确定”按钮完成“表输入”配置。

SELECT *,current_date() as dt
FROM itcast_shop.ityewu_areas

SELECT *,current_date() as dt
FROM itcast_shop.ityewu_goods_cats

在这里插入图片描述

插入/更新配置

1、选择对应的数据库连接。→2、输入目标表名称。→3、点击（用来查询的关键字）栏目框右侧的（获取字段）按钮，保留areaId字段，删除多余的字段。→4、点击（更新字段）栏目框右侧的（获取和更新字段）按钮，注意需要让（表字段）和（流字段）一一对应。→5、因为之前所粗昂见的数据仓库（itcast_shop_bi）目前是一个空库，里面没有数据表，所以需要点击右下角的（SQL）按钮，在弹出的（简单SQL编辑器）中点击（执行）按钮来新建一个名称为（ods_ityewu_areas）的新表。→6、最后点击右下角的（确定）按钮完成（插入/更新）配置。

在这里插入图片描述

配置ODS层定时调度抽取区域表和商品分类表作业

1、将上一步所创建的转换代入作业中，点击（Transformation）栏目右侧的（浏览）按钮，找到对应的（转换），然后点击（确定）按钮即可。

在这里插入图片描述

2、双击（Start）按钮，弹出的（作业定时调度）设置页面中，勾选（重复）按钮，将（类型）切换为（月），选择（每天）凌晨2点定时执行一次，选择（每月）为1号。这样就配置了该作业在每月1号的凌晨2点执行一次的定时调度设置。

在这里插入图片描述

APP应用层加载数据

在数仓的ODS层有了业务系统中的数据，接下来就可以开始对数据进行分析了，如果是比较麻烦的系统，通常需要进行数仓分层为ODS层，DWD层，DWS层及APP应用层，本示例属于简单的数据分析，直接由ODS层和APP应用层构成。那针对数据仓库应该以什么样的方式来进行分析呢？没错，就是编写SQL语句，但是这个SQL语句的编写方式是涉及许多多表关联的关联查询。在使用SELECT语句可以进行分析时，其分析的结果是需要保存下来的，通常是保存为一个数据表信息，将来进行可视化分析时，直接使用该数据表即可。

分析到这里，要进行数据仓库数据分析，一共会分为以下几步：1.明确数据分析的目标；2.创建用于保存数据分析结果的表；3.编写SQL语句进行数据分析；4.将SQL语句进行数据分析的结果保存到分析结果的表中。

分析某天订单总金额和订单总数

明确数据分析目标

运营部门想要统计每天的订单金额、订单总笔数，用来分析公司的经营状况。例如需要统计（例如2019-09-05）订单支付的总金额、订单的总笔数。这里我们一共要分析两个指标（订单总金额、订单总数量），而且需要在APP应用层中保存下来。共涉及两种表，分别是ods_ityewu_orders（订单表）和ityewu_order_goods（订单明细表）

在数仓(itcast_shop_bi)中创建对应表

为了方便后期管理，APP应用层上的表，表名通常才有app_开头来命名。这里我们创建一个（app_order_total）的表来保存某天订单总金额和订单总数的数据。

# 1.创建用于保存分析结果的表
create table if not exists app_order_total(
    id int primary key auto_increment,
    dt date,
    total_money double,
    total_cnt int

SQL进行数据分析(表输入)

在kettle中采用表输入进行分析，因为刚刚所创建的（app_order_total）有个id主键字段，所以这里需要加上一个null占位，让主键字段自动生成。至于需求中的订单总金额只需要对数仓的（ods_ityewu_orders）表的（realTotalMoney）字段进行汇总，然后按日期进行分组即可；至于需求中的订单总数，只需要数仓的（ods_ityewu_orders）表的行数进行计数即可，一般采用count(*)，但是这种计数方式需要对表中的字段进行统计，耗时耗内存，影响查询效率，所以这里采用count(1)进行计数统计。

# 1)确定表
# 取出年月日： substring(字段, 起始位置，字符长度)：
#   substring(createTime, 1, 10) ：取出创建日期createTime前10个字符
# select distinct substring(createTime, 1, 10)
# from ods_itheima_orders;

# 统计某天的订单支付的总金额、订单的总笔数。
select
	null,   # 占位，主键字段自动生成
    substring(createTime, 1, 10) as dt,
    sum(realTotalMoney) as total_money,
    count(1) as total_cnt
from ods_ityewu_orders
group by substring(createTime, 1, 10);

加载数据到APP应用层表(表输出)

在数仓（itcast_shop_bi）中加载数据到APP应用层表（app_order_total），有两种方法：一种方法：直接在数据库中输入下列代码，将查询所得信息加载到APP应用层表（app_order_total）中，可以保障id主键自增。

# 4.把sql分析的结果保存到结果表里
# 表复制
insert into app_order_total
select
    null,   # 占位，主键字段自动生成
    substring(createTime, 1, 10) as dt,
    sum(realTotalMoney) as total_money,
    count(1) as total_cnt
from ods_ityewu_orders
group by substring(createTime, 1, 10);

一种是直接在kettle中利用（表输出）控件将（表输入）控件的信息加载到APP应用层表（app_order_total）中，但不能保障id主键自增。

在这里插入图片描述

分析下订单的不同用户总数

明确数据分析目标

运营部门想要统计某天下过订单的不同用户总数。例如需要统计（例如2019-09-05）当天下过订单的不同用户总数（注意：1个用户在同一天可以下多个订单，所以需要去重）。这里我们只需要分析一个指标（用户数），而且需要在APP应用层中保存下来。共涉及一张表ods_ityewu_orders（订单表）。

在数仓(itcast_shop_bi)中创建对应表

在数仓(itcast_shop_bi)中我们创建一个（app_order_user）的表来保存某天下订单的不同用户总数的数据。

# 1.创建用于保存分析结果的表
create table if not exists app_order_user(
    id int primary key auto_increment,
    dt date,
    total_user_cnt int
);

SQL进行数据分析(表输入)

在kettle中采用表输入进行分析，因为刚刚所创建的（app_order_user）有个id主键字段，所以这里需要加上一个null占位，让主键字段自动生成。至于需求中的下订单的不同用户总数只需要对数仓的（ods_ityewu_orders）表的（userId）字段进行去重计数即可，计数采用count()函数，至于去重采用distinct函数。

# 3.确定表
#  count(字段) 统计字段非空行数
#  count(distinct 字段) 统计去重后的非空字段行数
select
	null,
    substring(createTime, 1, 10) as dt,
    count(distinct userId) as total_user_cnt
from ods_ityewu_orders
group by substring(createTime, 1, 10);

加载数据到APP应用层表(表输出)

在数仓（itcast_shop_bi）中加载数据到APP应用层表（app_order_user），有两种方法：一种方法：直接在数据库中输入下列代码，将查询所得信息加载到APP应用层表（app_order_user）中，可以保障id主键自增。

# 4.把sql分析的结果保存到结果表里
# 表复制
insert into app_order_user
select
    null,
    substring(createTime, 1, 10) as dt,
    count(distinct userId) as total_user_cnt
from ods_ityewu_orders
group by substring(createTime, 1, 10);

一种是直接在kettle中利用（表输出）控件将（表输入）控件的信息加载到APP应用层表（app_order_user）中，但不能保障id主键自增。截图略。

分析不同支付方式订单总额/订单笔数

明确数据分析目标

运营部门想要统计某天不同支付方式订单总额/订单笔数。例如需要统计（例如2019-09-05）当天不同支付方式订单总额和订单笔数。这里我们只需要分析订单金额（realTotalMoney）字段和订单笔试（订单计数）两个指标，至于不同支付方式，则采用（payType）字段进行区分，而且需要在APP应用层中保存下来。共涉及一张表ods_ityewu_orders（订单表）。

在数仓(itcast_shop_bi)中创建对应表

在数仓(itcast_shop_bi)中我们创建一个（app_order_paytype）的表来保存某天不同支付方式订单总额和订单笔数的数据。

# 1.创建用于保存分析结果的表
create table if not exists app_order_paytype(
    id int primary key auto_increment,
    dt date,
    paytype varchar(20),
    total_money double,
    total_cnt int
);

SQL进行数据分析(表输入)

在kettle中采用表输入进行分析，因为刚刚所创建的（app_order_paytype）有个id主键字段，所以这里需要加上一个null占位，让主键字段自动生成。至于需求中的订单总金额只需要对数仓的（ods_ityewu_orders）表的（realTotalMoney）字段进行汇总，然后按日期进行分组即可；至于需求中的订单总数，只需要数仓的（ods_ityewu_orders）表的行数进行计数即可，一般采用count(*)，但是这种计数方式需要对表中的字段进行统计，耗时耗内存，影响查询效率，所以这里采用count(1)进行计数统计。另外一个就是支付方式，根据（payType）字段进行划分，再根据（payType）字段进行分组，所以这里需要两个分组字段，分别是日期字段和（payType）字段。

# 3.确定表
#  count(字段) 统计字段非空行数
#  count(distinct 字段) 统计去重后的非空字段行数
select
	null,
    substring(createTime, 1, 10) as dt,
    case    # 自定义分类
        when payType=1 then "支付宝"
        when payType=2 then "微信"
        when payType=3 then "信用卡"
        when payType=4 then "其他方式"
    end as paytype,
    sum(realTotalMoney) as total_money,
    count(1) as total_cnt
from ods_ityewu_orders
group by substring(createTime, 1, 10), payType;

加载数据到APP应用层表(表输出)

在数仓（itcast_shop_bi）中加载数据到APP应用层表（app_order_paytype），有两种方法：一种方法：直接在数据库中输入下列代码，将查询所得信息加载到APP应用层表（app_order_user）中，可以保障id主键自增。

# 4.把sql分析的结果保存到结果表里
# 表复制
insert into app_order_paytype
select
    null,
    substring(createTime, 1, 10) as dt,
    case    # 自定义分类
        when payType=1 then "支付宝"
        when payType=2 then "微信"
        when payType=3 then "信用卡"
        when payType=4 then "其他方式"
    end as paytype,
    sum(realTotalMoney) as total_money,
    count(1) as total_cnt
from ods_ityewu_orders
group by substring(createTime, 1, 10), payType;

一种是直接在kettle中利用（表输出）控件将（表输入）控件的信息加载到APP应用层表（app_order_paytype）中，但不能保障id主键自增。截图略。

分析订单笔数TOP5用户

明确数据分析目标

运营部门想要知道某天下单笔数最多的5个用户分别是谁？例如需要统计（例如2019-09-05）当天下单笔数最多的5个用户分别是谁？这里我们只需要分析订单笔试（orderId计数）这个指标，然后根据用户（userid,username)维度进行分组，而且需要在APP应用层中保存下来。共涉及一张表ods_ityewu_orders（订单表）。

在数仓(itcast_shop_bi)中创建对应表

在数仓(itcast_shop_bi)中我们创建一个（app_order_user_top5）的表来保存某天订单笔数TOP5用户的数据。

# 1.创建用于保存分析结果的表
CREATE TABLE IF NOT EXISTS app_order_user_top5(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dt DATE,
    userid VARCHAR(20),
    username VARCHAR(50),
    total_cnt INT
);

SQL进行数据分析(表输入)

在kettle中采用表输入进行分析，因为刚刚所创建的（app_order_user_top5）有个id主键字段，所以这里需要加上一个null占位，让主键字段自动生成。至于需求中的查找出下订单笔数TOP5的用户，则需要对数仓的（ods_ityewu_orders）表的（orderId）字段进行计数即可，计数采用count()函数，但是需要根据用户（userid,username)维度进行分组，然后根据count(orderId)进行排序，最后用limit()函数进行限制。

# 3.确定表
#  count(字段) 统计字段非空行数
SELECT
    NULL,
    SUBSTRING(createTime, 1, 10) AS dt,
    userId         userid,
    userName       username,
    COUNT(orderId) AS total_cnt
FROM ods_ityewu_orders
GROUP BY SUBSTRING(createTime, 1, 10), userid,username
ORDER BY total_cnt DESC LIMIT 5;

加载数据到APP应用层表(表输出)

在数仓（itcast_shop_bi）中加载数据到APP应用层表（app_order_user_top5），有两种方法：一种方法：直接在数据库中输入下列代码，将查询所得信息加载到APP应用层表（app_order_user_top5）中，可以保障id主键自增。

# 4.把sql分析的结果保存到结果表里
# 表复制
INSERT INTO app_order_user_top5
SELECT
    NULL,
    SUBSTRING(createTime, 1, 10) AS dt,
    userId         userid,
    userName       username,
    COUNT(orderId) AS total_cnt
FROM ods_ityewu_orders
GROUP BY SUBSTRING(createTime, 1, 10), userid,username
ORDER BY total_cnt DESC LIMIT 5;

一种是直接在kettle中利用（表输出）控件将（表输入）控件的信息加载到APP应用层表（app_order_user_top5）中，但不能保障id主键自增。截图略。

分析商品分类订单总额/订单笔数

明确数据分析目标

运营部门想要统计某天，不同商品分类（一级分类）订单的总金额、订单的总笔数。例如需要统计（例如2019-09-05）当天不同支付方式订单总额和订单笔数。这里我们只需要分析不同商品分类（一级分类）订单的总金额（realTotalMoney）字段、订单的总笔数（订单计数）两个指标，至于不同支付方式，则采用（payType）字段进行区分，而且需要在APP应用层中保存下来。共涉及ods_ityewu_orders（订单表），但是因为订单表是没有分类字段的，所以跟订单表，没有直接关系，因为只有ods_ityewu_goods_cats(分类表)，有分类字段（catId，catName），所以需要关联ods_ityewu_goods_cats(分类表)，其中分类表中的（catLevel）字段代表分类级别，当catLevel=1时，即找到了一级分类的数据行；当知道某个三级分类的分类ID时，要想获得二级甚至一级分类ID时，需要使用parentId一层一层的获取。另外还需要关联ods_ityewu_goods(商品表)。

在数仓(itcast_shop_bi)中创建对应表

在数仓(itcast_shop_bi)中我们创建一个（app_order_goods_cat）的表来保存某天，不同商品分类（一级分类）订单的总金额、订单的总笔数的数据。

# 1.创建用于保存分析结果的表
CREATE TABLE IF NOT EXISTS app_order_goods_cat(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dt DATE,
    cat_name     VARCHAR(50),
    total_money  DOUBLE,
    total_cnt    INT
);

SQL进行数据分析(表输入)

创建一个中间表来存储商品分类信息。因为这里的商品分类是类似省市区的分层结构（如：橙柚是三级类别，其二级类别是进口水果，其一级类别是时蔬水果），所以采用自连接查询方式（相当于人为的将同一个数据表拆分为多个表，然后用这多个数据表进行关联）来查询，至于这里的where过滤条件（t3.cat_level = 3），那是因为我们购买商品时，第三级分类是最接近商品的，如同省市区的区是最接近个人居住地址的。因此在查询3级类别时，会自动将2级类别和1级类别查询带出，而且还会减少查询数据量，提升SQL查询效率。

在这里插入图片描述

# 3.创建中间表，采用自连接查询获得1,2,3三级分类信息
CREATE TABLE tmp_goods_cat AS
SELECT t3.catId AS cat_id_l3,
t3.catName AS cat_name_l3,
-- 获取三级分类
t2.catId AS cat_id_l2,
t2.catName AS cat_namea_l2,
-- 获取二级分类
t1.catId AS cat_id_l1,
t1.catName AS cat_namea_l1
-- 获取一级分类
FROM ods_ityewu_goods_cats t3,ods_ityewu_goods_cats t2,ods_ityewu_goods_cats t1
WHERE t3.parentId = t2.catId AND t2.parentId = t1.catId AND t3.cat_level = 3;

在kettle中采用表输入进行分析。至于需求中的订单总金额只需要对数仓的（ods_ityewu_order_goods）表的（goodsNum和payPrice）字段进行汇总，然后按日期进行分组即可；至于需求中的订单总数，只需要数仓的（ods_ityewu_order_goods）表的（orderId）进行计数即可，一般采用count()函数。

# 4.确定表
#  count(字段) 统计字段非空行数
SELECT
    t1.cat_id_l1 AS id,
    SUBSTRING(t2.createTime, 1, 10) AS dt,
    t1.cat_namea_l1 AS cat_name,
    SUM(t3.goodsNum * t3.payPrice) AS total_money,
    COUNT(DISTINCT t3.orderId) AS total_cnt
FROM tmp_goods_cat t1
LEFT JOIN ods_ityewu_goods t2 ON t1.cat_id_l3 = t2.goodsCatId
LEFT JOIN ods_ityewu_order_goods t3 ON t2.goodsId = t3.goodsId
GROUP BY SUBSTRING(t2.createTime, 1, 10), t1.cat_id_l1,t1.cat_namea_l1;

加载数据到APP应用层表(表输出)

在数仓（itcast_shop_bi）中加载数据到APP应用层表（app_order_goods_cat），有两种方法：一种方法：直接在数据库中输入下列代码，将查询所得信息加载到APP应用层表（app_order_goods_cat）中，可以保障id主键自增。

# 4.把sql分析的结果保存到结果表里
# 表复制
INSERT INTO app_order_goods_cat
SELECT
    NULL,
    SUBSTRING(t2.createTime, 1, 10) AS dt,
    t1.cat_namea_l1 AS cat_name,
    SUM(t3.goodsNum * t3.payPrice) AS total_money,
    COUNT(DISTINCT t3.orderId) AS total_cnt
FROM tmp_goods_cat t1
LEFT JOIN ods_ityewu_goods t2 ON t1.cat_id_l3 = t2.goodsCatId
LEFT JOIN ods_ityewu_order_goods t3 ON t2.goodsId = t3.goodsId
GROUP BY SUBSTRING(t2.createTime, 1, 10), t1.cat_id_l1,t1.cat_namea_l1;

一种是直接在kettle中利用（表输出）控件将（表输入）控件的信息加载到APP应用层表（app_order_goods_cat）中，但不能保障id主键自增。截图略。

大强哥666

关注

14
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
ETL电商项目总结

如电商公司，每天都有一些的用户会在线上采购商品，该电商公司想通过数据分析，查看每一天的电商经营情况。例如：电商公司的运营部门想要清楚的看到每天的订单笔数、订单的下单总额、不同支付类型对应的订单笔数和总额等等。
复制链接

扫一扫