mysql-kettle-superset電商視覺化資料分析

小菜菜愛大餅餅發表於2020-05-16
MySql視覺化

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、自動化構建抽取實現


1、地區表以及商品分類表的自動抽取




 


 


2、商品表、訂單表、訂單詳情表、使用者表




3、設定定時自動執行




 


 


 


4、資料分析


需求1


需求：統計 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)




需求2


需求：統計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);




需求3


需求；每天不同支付方式訂單總額/訂單筆數分析


指標：訂單總額、訂單總筆數


維度：時間維度【天】、支付方式維度




-- 建立結果表
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;




需求4


需求；統計2019年9月下訂單最多的使用者TOP5，也就是前5名


方式一：上面考慮的是簡單的情況，只獲取訂單個數最多的前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;




需求5


需求：統計不同分類的訂單總金額以及訂單總筆數【類似於統計不同支付型別的訂單總金額和總筆數】




-- 建立結果表
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;






5、構建自動化Kettle作業實現自動化分析


建立一個作業




配置SQL指令碼




定義作業的變數




 


6、視覺化構建


訂單銷售總額


訂單總筆數


訂單總使用者數


不同支付方式的總訂單金額比例


不同支付方式的訂單個數


不同商品分類的訂單總金額


不同商品分類的訂單總個數


詞雲圖


7、構建看板




 




 




 






相關文章