1、專案概述
需求
對電商業務中的使用者、商品、訂單的資料進行分析，觀察運營的情況
架構
業務資料庫：Mysql：儲存最原始的資料
ETL：Kettle
資料倉儲：Mysql：儲存需要進行分析處理的資料
分析處理：SQL/Kettle
視覺化：Superset
2、準備工作
系統
linux系統
軟體
VMware虛擬機器——安裝linux作業系統
1 Windows版下載地址: 2 https://www.vmware.com/
finalshell——遠端作業系統
1 Windows版下載地址: 2 http://www.hostbuf.com/downloads/finalshell_install.exe 3 Mac版,Linux版安裝及教程: 4 http://www.hostbuf.com/t/1059.html
mysql——資料庫（安裝版和壓縮包版）
1 Windows版下載地址: 2 https://www.mysql.com//downloads/
datagrip——資料庫管理工具
連結：https://pan.baidu.com/s/1K1pPIX9uZiAKOAiFgHMlnw
提取碼：lhr4
Navicat——資料庫管理工具
連結：https://pan.baidu.com/s/1eaW3CMhen_7X5sjVgs7enw
提取碼：fqov
kettle——如有安裝問題請自行度娘
1、Kettle的下載與安裝（本文使用kettle版本為pdi-ce-7.1.0.0-12）點選下載地址官方網站
視覺化工具
superset——有問題請度娘
linux環境安裝依賴
yum upgrade python-setuptools
yum install -y gcc gcc-c++ libffi-devel python-devel python-pip python-wheel openssl-devel libsasl2-devel openldap-devel
安裝superset
supersetcd /root/anaconda3/
pip install email_validator -i https://pypi.douban.com/simple
pip install superset==0.30.0 -i https://pypi.douban.com/simple
3、資料環境
1、匯入業務資料
將這段sql程式碼下載執行，生成資料庫，表格
連結：https://pan.baidu.com/s/1uVYISah6hYkBqiyhIk407w
提取碼：sfdm
2、構建資料倉儲
通過kettle將業務資料抽取到資料分析的資料庫中
連結：https://pan.baidu.com/s/1shH0zexh3WraQnMt17n-SA
提取碼：ao7n
生成表格——kettle操作略
mysql> use itcast_shop_bi; Database changed
mysql> show tables; +--------------------------+ | Tables_in_itcast_shop_bi | +--------------------------+ | ods_itcast_good_cats || ods_itcast_goods || ods_itcast_order_goods || ods_itcast_orders |訂單表
| ods_itcast_users |使用者表
| ods_itcast_area |+--------------------------+
3、
2、商品表、訂單表、訂單詳情表、使用者表
3、設定定時自動執行
4、資料分析
需求：統計 2019-09-05 訂單支付的總金額、訂單的總筆數
演變：統計每天的訂單支付的總金額和訂單的總筆數
指標：總金額、訂單總筆數
維度：天
-- 建立結果表
use itcast_shop_bi; create table app_order_total( id int primary key auto_increment, dt date, total_money double, total_cnt int );
-- 將分析的結果儲存到結果表
insert into app_order_total select null, substring(createTime,1,10) as dt,-- 2019-09-05這一天的日期 round(sum(realTotalMoney),2) as total_money, -- 分組後這一天的所有訂單總金額 count(orderId) as total_cnt -- 分組後這一天的訂單總個數 from ods_itcast_orders where substring(createTime,1,10) = '2019-09-05' group by substring(createTime,1,10);
-- 表結構及內容
mysql> desc app_order_user; +----------------+------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | dt | date | YES | | NULL | | | total_user_cnt | int | YES | | NULL | | +----------------+------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from app_order_user; +----+------------+----------------+ | id | dt | total_user_cnt | +----+------------+----------------+ | 1 | 2019-09-05 | 11 | | 2 | 2019-09-05 | 11 | +----+------------+----------------+ 2 rows in set (0.01 sec)
需求：統計2019-09-05當天所有下單的使用者總數
演變：統計訂單表中2019-09-05這一天的所有訂單的使用者id的個數
-- 建立結果表 use itcast_shop_bi; create table app_order_user( id int primary key auto_increment, dt date, total_user_cnt int );
-- 插入結果資料 insert into app_order_user select null, substring(createTime,1,10) as dt,-- 2019-09-05這一天的日期 count(distinct userId) as total_user_cnt from ods_itcast_orders where substring(createTime,1,10) = '2019-09-05' group by substring(createTime,1,10);
每天不同支付方式訂單總額/訂單筆數分析
指標：訂單總額、訂單總筆數
-- 建立結果表
create table app_order_paytype( id int primary key auto_increment, dt date, pay_type varchar(20), total_money double, total_cnt int );
-- 插入結果資料 insert into app_order_paytype select null, substring(createTime,1,10) as dt,-- 獲取每一天的日期 case payType when 1 then '支付寶' when 2 then '微信' when 3 then '現金' else '其他' end as pay_type, round(sum(realTotalMoney),2) as total_money, -- 分組後這一天的所有訂單總金額 count(orderId) as total_cnt -- 分組後這一天的訂單總個數 from ods_itcast_orders group by substring(createTime,1,10),payType;
方式一：上面考慮的是簡單的情況，只獲取訂單個數最多的前5個人
select date_format(dt,'%Y-%m') as dt, userId, userName, count(orderId) as total_cnt from ods_itcast_orders where date_format(dt,'%Y-%m') = '2019-09' group by date_format(dt,'%Y-%m'),userId,userName order by total_cnt desc limit 5;
方式二：我們希望得到訂單個數最多的排名的前5名，如果個數相同排名相同
select * from ( select *, dense_rank() over (partition by dt order by total_cnt desc) as rn from ( select date_format(dt, '%Y-%m') as dt, userId, userName, count(orderId) as total_cnt from ods_itcast_orders where date_format(dt, '%Y-%m') = '2019-09' group by date_format(dt, '%Y-%m'), userId, userName ) tmp1 ) tmp2 where rn < 6;
-- 建立結果表 use itcast_shop_bi; drop table if exists app_order_goods_cat; create table app_order_goods_cat( id int primary key auto_increment, dt date, cat_name varchar(20), total_money double, total_num int ); -- step2：先構建三級分類與一級分類之間的關係 -- 使用join實現 drop table if exists tmp_goods_cats; create temporary table tmp_goods_cats as select t3.catId as t3Id,-- 三級分類id t3.catName as t3Name, -- 三級分類名稱 t2.catId as t2Id, t2.catName as t2Name, t1.catId as t1Id, t1.catName as t1Name from ods_itcast_good_cats t3 join ods_itcast_good_cats t2 on t3.parentId = t2.catId join ods_itcast_good_cats t1 on t2.parentId = t1.catId; CREATE UNIQUE INDEX idx_goods_cat3 ON tmp_goods_cats(t3Id); CREATE UNIQUE INDEX idx_itheima_goods ON ods_itcast_goods(goodsId); CREATE INDEX idx_itheima__order_goods ON ods_itcast_order_goods(goodsId);
-- 插入結果資料
insert into app_order_goods_cat select null, substring(c.createtime,1,10) as dt, a.t1Name, sum(c.payPrice) as total_money, count(distinct orderId) as total_num from tmp_goods_cats a left join ods_itcast_goods b on a.t3Id = b.goodsCatId left join ods_itcast_order_goods c on b.goodsId = c.goodsId where substring(c.createtime,1,10) = '2019-09-05' group by substring(c.createtime,1,10),a.t1Name;
定義作業的變數
不同支付方式的總訂單金額比例
不同支付方式的訂單個數
不同商品分類的訂單總金額
不同商品分類的訂單總個數
詞雲圖
7、
