一、關于本次案例的概述

? ?將某電商脫敏后數據導入數據庫進行處理加工，并對相關數據進行查詢。本次案列中是很經典的關于電商數據的整理，一共包含了九張數據表，還是比較多，內在邏輯也是比較繞，所以先對這九張表進行大體上的分類。

1、買方數據表（3張）

（1）UserInfo.csv——用戶主表，買方信息

?

?（2）RegionInfo.csv——區域表 ?國家行政區域劃分表

（3）UserAddress.csv——用戶地址表 ??用戶收貨地址信息

2、賣方數據表（4張）

（1）GoodsInfo.csv:商品主表

（2）GoodsBrand.csv:商品品牌表

?

?（3）GoodsColor.csv:商品顏色表

（4）GoodsSize.csv:商品尺碼表

3、購買行為數據表（2張）

（1）?OrderInfo.csv:訂單主表 ?????主鍵 訂單ID+商品ID，用來連接賣方

?

?（2）OrderDetail.csv:訂單詳情表? ? ? 主鍵 訂單ID? 用來連接買方

?

?

4、表關系詳解

其實，sql語句并不難，難的是這些表之間的邏輯關系，如上圖，總共給出了12個關系，要清楚的知道每個表里有哪些內容，每個表之間又怎么建立起關系，也就是哪個是關鍵字段，同時，還要明白，

所用的關鍵字段到底是?一對一?一對多?還是?多對多的關系，圖上我已經標出，都是一對多。

?個人認為重要思想就是：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1、我要什么內容？

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2、這些內容存放在哪張表里？

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 3、我該怎么取出來？好，用多表查詢，那么我的關鍵字段應該選那個？

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 4、關鍵字段的對應關系是什么？對我所查找的結果有什么影響？

? ? ? ? ? ? ? ? ? ??

二、按照所給出的表結構信息建表并且導入數據

? ? ? ? ?本次案列使用的是work bench 6.3+mysql5.7

1、建立數據庫??

-- 建立數據庫 luo create database luo;-- 使用數據庫 use ds2;

2、表 UserInfo?的建立及數據導入

那么，在建表前先看看數據

?

大部分字段其實還是挺好定義的，就是 regtime 和?lastlogin，里面是時間戳，所以先用文本來儲存，后面再轉化成時間格式

?

-- 建立數據庫luo create database ds2;-- 使用數據庫 use ds2;-- 創建表 userinfo create table userinfo(userid varchar(6) not null default '-',username varchar(20) not null default '-',userpassword varchar(100) not null default '-', sex int not null default 0,usermoney int not null default 0,frozenmoney int not null default 0,addressid varchar(20) not null default '-',regtime varchar(20) not null default '-',lastlogin varchar(20) not null default '-',lasttime date not null );#導入數據 load data local infile 'D:rawdata/UserInfo.csv' into table userinfofields terminated by ','ignore 1 lines;-- 檢查數據信息 select * from userinfo; -- 檢查總行數是否是1000 select count(*) from userinfo; -- 檢查數據結構 desc userinfo; -- 創建新的日期時間字段并賦值-- 解除行上安全限定.1175 set sql_safe_updates = 0; alter table userinfo add regtime_ datetime not null; update userinfo set regtime_ = from_unixtime(regtime);-- 創建新的日期時間字段并賦值 alter table userinfo add lastlogin_ datetime not null; update userinfo set lastlogin_ = from_unixtime(lastlogin);-- 刪除原有錯誤日期格式字段 alter table userinfo drop column regtime; alter table userinfo drop column lastlogin;desc userinfo;

不出問題，表信息應如下：

?

3、表?regioninfo 的建立及數據導入

create table regioninfo(regionid varchar(4) not null default '-',parentid varchar(4) not null default '-',regionname varchar(20) not null default '-', regiontype int not null default 0,agencyid int not null default 0,pt varchar(9) not null default '-' );#導入數據 load data local infile 'D:rawdata/regioninfo.csv' into table regioninfofields terminated by ','ignore 1 lines;select * from regioninfo;-- 取最后文本型字段的前八位字符 update regioninfo set pt = substring(pt,1,8);-- 創建新的日期時間字段并賦值 alter table regioninfo add pt_ datetime not null; update regioninfo set pt_ = date_format(pt,'%y-%m-%d');-- 刪除原有錯誤日期格式字段 alter table regioninfo drop column pt;desc regioninfo;

結果應該如下：

?

4、表 useraddress? 的建立及數據導入

create table useraddress(addressid varchar(5) not null default '-',userid varchar(6) not null default '-', consignee varchar(50) not null default '-',country varchar(1) not null default '-',province varchar(2) not null default '-',city varchar(4) not null default '-',district varchar(4) not null default '-', address varchar(200) not null default '-',pt varchar(10) not null default '-' );#導入數據 load data local infile 'D:rawdata/UserAddress.csv' into table useraddressfields terminated by ','ignore 1 lines;update useraddress set pt = substring(pt,1,8);alter table useraddress add pt_ datetime not null; update useraddress set pt_ = date_format(pt,'%y-%m-%d');alter table useraddress drop column pt;select count(*) from useraddress;select * from useraddress;

結果如下：

?

5、表?goodsinfo ?的建立及數據導入

create table goodsinfo(goodsid varchar(6) not null default '-',typeid varchar(3) not null default '-',markid varchar(4) not null default '-',goodstag varchar(100) not null default '-',brandtag varchar(100) not null default '-',customtag varchar(100) not null default '-',goodsname varchar(100) not null default '-',clickcount int not null default 0,clickcr int not null default 0,goodsnumber int not null default 0,goodsweight int not null default 0,marketprice double not null default 0,shopprice double not null default 0,addtime varchar(20) not null default 0,isonsale int not null default 0,sales int not null default 0,realsales int not null default 0,extraprice double not null default 0,goodsno varchar(9) not null default 0,pt varchar(9) not null default 0 );#導入數據 load data local infile 'D:rawdata/GoodsInfo.csv' into table goodsinfofields terminated by ','ignore 1 lines;select * from goodsinfo;alter table goodsinfo add addtime_ datetime not null; update goodsinfo set addtime_ = from_unixtime(addtime);alter table goodsinfo drop column addtime;update goodsinfo set pt = substring(pt,1,8);alter table goodsinfo add pt_ datetime not null; update goodsinfo set pt_ = date_format(pt,'%y-%m-%d');alter table goodsinfo drop column pt;

結果如下：

?

6、表?goodsbrand ?的建立及數據導入

create table goodsbrand(SupplierID varchar(4) not null default '-',BrandType varchar(100) not null default '-',pt varchar(9) not null default '-' );#導入數據 load data local infile 'D:rawdata/GoodsBrand.csv' into table goodsbrandfields terminated by ','ignore 1 lines;select * from goodsbrand;update goodsbrand set pt = substring(pt,1,8);alter table goodsbrand add pt_ datetime not null; update goodsbrand set pt_ = date_format(pt,'%y-%m-%d');alter table goodsbrand drop column pt;

結果如下：

?

7、表?goodscolor ?的建立及數據導入

create table goodscolor(ColorID varchar(4) not null default '-',ColorNote varchar(20) not null default '-',ColorSort int not null default 0, pt varchar(9) not null default '-' );#導入數據 load data local infile 'D:rawdata/goodscolor.csv' into table goodscolorfields terminated by ','ignore 1 lines;select * from goodscolor;update goodscolor set pt = substring(pt,1,8);alter table goodscolor add pt_ datetime not null; update goodscolor set pt_ = date_format(pt,'%y-%m-%d');alter table goodscolor drop column pt;

結果如下：

?

8、表?goodssize?的建立及數據導入

create table goodssize(SizeID varchar(4) not null default '-',SizeNote varchar(100) not null default '-',SizeSort int not null default 0, pt varchar(9) not null default '-' );#導入數據 load data local infile 'D:rawdata/goodssize.csv' into table goodssizefields terminated by ','ignore 1 lines;select * from goodssize;update goodssize set pt = substring(pt,1,8);alter table goodssize add pt_ datetime not null; update goodssize set pt_ = date_format(pt,'%y-%m-%d');alter table goodssize drop column pt;

結果如下：

?

9、表?OrderInfo?的建立及數據導入

create table OrderInfo(OrderID varchar(6) not null default '-',UserID varchar(10) not null default '-',OrderState int not null default 0,PayState int not null default 0,AllotStatus int not null default 0,Consignee varchar(100) not null default '-',Country int not null default 0,Province int not null default 0,City int not null default 0,District int not null default 0,Address varchar(100) not null default 0,GoodsAmount double not null default 0,OrderAmount double not null default 0,ShippingFee int not null default 0,RealShippingFee int not null default 0,PayTool int not null default 0,IsBalancePay int not null default 0,BalancePay double not null default 0,OtherPay double not null default 0,PayTime varchar(20) not null default 0,AddTime varchar(20) not null default 0 );#導入數據 load data local infile 'D:rawdata/OrderInfo.csv' into table OrderInfofields terminated by ','ignore 1 lines;select * from OrderInfo;alter table OrderInfo add PayTime_ datetime not null; update OrderInfo set PayTime_ = from_unixtime(PayTime);alter table OrderInfo add AddTime_ datetime not null; update OrderInfo set AddTime_ = from_unixtime(AddTime);alter table OrderInfo drop column PayTime; alter table OrderInfo drop column AddTime;

結果如下：

?

10、表?OrderInfo?的建立及數據導入

create table OrderDetail(RecID varchar(7) not null default '-',OrderID varchar(6) not null default '-',UserID varchar(6) not null default '-',SpecialID varchar(6) not null default '-',GoodsID varchar(6) not null default '-',GoodsPrice double not null default 0,ColorID varchar(4) not null default '-',SizeID varchar(4) not null default '-',Amount int not null default 0 );#導入數據 load data local infile 'D:rawdata/OrderDetail.csv' into table OrderDetailfields terminated by ','ignore 1 lines;select * from OrderDetail;select count(*) from OrderDetail;

結果如下：

?

11、對建立的九張表再進行一次檢驗

-- 查詢導入表的行數 select count(*) from userinfo; -- 1000 select count(*) from RegionInfo; -- 3415 select count(*) from useraddress; -- 10000 select count(*) from goodsinfo; -- 10000 select count(*) from goodsbrand; -- 64 select count(*) from goodscolor; -- 2641 select count(*) from goodssize; -- 289 select count(*) from orderinfo; -- 3711 select count(*) from orderdetail; -- 10000

?

若檢驗沒有問題，可繼續往下，如果任何不對，重新再來吧，否則肯定是錯的！

三、統計與查詢

1、求出購買產品金額最多的前十名顧客

按照我的思考：

1、我需要什么？顧客

2、什么樣的顧客？金額最多的前十

3、?也就是說我還需要產品總金額，然后對其進行排序，遞減，保留前十即可

4、顧客和金額從哪里來？?根據表的信息，來自于同一張表 orderinfo，這就簡單了，不需要多表查詢

select userid as 顧客, sum(orderamount) as 金額 from orderinfo group by 顧客 order by 金額 desc limit 10; 求出購買產品金額最多的前十名顧客

2、求出購買產品金額最多的前十名顧客的最后登錄時間

感覺很熟悉啊，和1相比，無非就是多求一個最后登錄時間??lastlogin_，最后登錄時間在表?userinfo?中，所以，這就要用到多表查詢了，

這里顯然是在1的基礎上增加而已，自然應該選用表?orderinfo作為主表，關鍵字段，在表關系里已經給出?orderinfo.userid =userinfo.userid。

select oi.userid as 顧客, sum(orderamount) as 金額, lastlogin_ as 最后登錄時間 from orderinfo as oi left join userinfo as ui on oi.userid = ui.userid group by oi.userid order by 金額 desc limit 10; 求出購買產品金額最多的前十名顧客的最后登錄時間

3、 求出購買產品金額最多的前十名顧客的所在城市

沒毛病 ，和2其實是一回事，只是改成求 城市 去了，那么城市在哪里，根據表關系，所有的 country? ?province? city district

都在表?RegionInfo種，對應于regionname，關鍵字段也給出了?city = regionid? 注意主表應該選誰

select userid as 顧客, sum(orderamount) as 金額, regionname as 城市 from orderinfo left join regioninfo on city = regionid group by userid order by 金額 desc limit 10; 求出購買產品金額最多的前十名顧客的所在城市

?

4、求出購買力最強的前十個城市

這個問題是這樣的，可以先對城市進行分類，把各個城市的?OrderAmount?求和，降序，保留前十即可

select regionname as 城市, sum(orderamount) as 金額 from orderinfo left join regioninfo on city = regionid group by city order by 金額 desc limit 10; 求出購買力最強的前十個城市

?

5、求出購買力最強的前十個城市以及他們所在的省份

這個問題就比較有意思了，首先購買力最強的前十個城市，我們是可以求出來的，沒毛病，根據表的關系? 省份 也需要連接表?RegionInfo，

也需要?regionname?來取值，但是，?regionname已經用來取?城市?了

所以不能直接來求。但是在取 城市 的同時，可以在?表?OrderInfo?中，取到該城市的?province?編號，

于是我就可以先得到??購買力最強的前十個城市以及他們所在的省份的編號?這樣一個表

再和表?RegionInfo ，以 省份編號 =?RegionInfo.regionid,再以?regionname來取省份就好了

create table a (select regionname as 城市, sum(orderamount) as 金額, province as 省份id from orderinfo left join regioninfo on city = regionid group by city order by 金額 desc limit 10);select * from a;select regionname as 省份, 城市, 金額 from a left join regioninfo on 省份id = regionid order by 金額 desc; 求出購買力最強的前十個城市以及他們所在的省份

?

6、求出最暢銷的十個品牌

這道題，也比較有意思了，需要的是品牌其實是在表 goodsbrand?中的?，其條件是最暢銷，自然需要商品的銷量，而商品的銷量 Amount?在表? OrderDetail 中，

最遺憾的是這兩個表并沒有直接的關系，不能直接聯合查詢，只能借表?GoodsInfo?來過度

select brandtype as 品牌, sum(amount) as 銷量 from orderdetail as od left join goodsinfo as gi on od.goodsid = gi.goodsid left join goodsbrand on typeid = supplierid group by typeid order by 銷量 desc limit 10; 求出最暢銷的十個品牌

?

7、求出最暢銷的十種顏色、以及最不暢銷的十種顏色

需要顏色，顏色在表?GoodsColor?里，銷量自然在表?orderdetail里，根據表關系，可以通過 ColorID?查詢，查兩次，一個升序，一個降序，用 union?連接即可

(select colornote as 顏色, sum(amount) as 銷量 from orderdetail as od left join goodscolor as gc on od.colorid = gc.colorid group by od.colorid order by 銷量 desc limit 10) union (select colornote as 顏色, sum(amount) as 銷量 from orderdetail as od left join goodscolor as gc on od.colorid = gc.colorid group by od.colorid order by 銷量 limit 10); 求出最暢銷的十種顏色、以及最不暢銷的十種顏色

?

8、求出最暢銷的十個商品所屬品牌中所有商品的銷售額

-- a. 求出最暢銷的十個商品所屬品牌 create table x (select typeid as 品牌id from orderdetail as od left join goodsinfo as gi on od.goodsid = gi.goodsid group by od.goodsid order by sum(amount) desc limit 10);-- b. 求出品牌下所有的商品 create table y (select goodsid as 商品id from x left join goodsinfo on 品牌id = typeid group by 商品id);-- c. 求出商品的銷售額 select 商品id, sum(goodsprice * amount) as 銷售額 from y left join orderdetail on 商品id = goodsid group by 商品id order by 銷售額 desc; 求出最暢銷的十個商品所屬品牌中所有商品的銷售額

?

?

所有數據：

鏈接：https://pan.baidu.com/s/1y_XOrrriionxuSpuFgPTkg
提取碼：g3zi

?

轉載于:https://www.cnblogs.com/LUOyaXIONG/p/10442340.html

總結

以上是生活随笔為你收集整理的电商数据处理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。