电商数据处理

一、关于本次案例的概述

   将某电商脱敏后数据导入数据库进行处理加工，并对相关数据进行查询。本次案列中是很经典的关于电商数据的整理，一共包含了九张数据表，还是比较多，内在逻辑也是比较绕，所以先对这九张表进行大体上的分类。

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 userinfo
    fields 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 regioninfo
    fields 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 useraddress
    fields 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 goodsinfo
    fields 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 goodsbrand
    fields 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 goodscolor
    fields 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 goodssize
    fields 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 OrderInfo
    fields 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 OrderDetail
    fields 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;

 

-- 9. 买不同商品种类最多的前十名用户所使用的收货城市都有哪些
-- （非重复计数count(distinct 字段名)）

-- 买不同商品种类最多的前十名用户所使用的收货城市都有哪些
-- （非重复计数count(distinct 字段名)）
select od.userid as 用户, count(distinct goodsid) as 商品种类, group_concat(distinct regionname) as 城市名
from orderdetail as od left join orderinfo as oi on od.orderid = oi.orderid
left join regioninfo on city = regionid
group by od.userid
order by 商品种类 desc
limit 10;

 

 

所有数据：

链接：https://pan.baidu.com/s/1y_XOrrriionxuSpuFgPTkg
提取码：g3zi