扫码加入数据分析学习群

create database ds;

use ds;

-- 建表导数---------------------------------------
--  UserInfo table
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 infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/userinfo.csv"
	into table userinfo
    fields terminated by ','
	ignore 1 lines;

select * from userinfo limit 10; -- 检查数据信息
select count(*) from userinfo; -- 检查总行数1000
desc userinfo; -- 检查数据结构

-- 添加一个日期时间型的型字段
alter table userinfo add lastlogin_new datetime;
-- 赋值
update userinfo set lastlogin_new = FROM_UNIXTIME(lastlogin);







-- drop table userinfo;

-- --------------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(11) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/RegionInfo.csv"
	into table regioninfo
    fields terminated by ','
	ignore 1 lines;

select * from regioninfo limit 10;








-- drop table regioninfo;

-- --------------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(11) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/UserAddress.csv"
	into table useraddress
    fields terminated by ','
	ignore 1 lines;
    

select * from useraddress limit 10;










-- drop table useraddress;

-- ----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(10) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/goodsinfo.csv"
	into table goodsinfo
    fields terminated by ','
	ignore 1 lines;

select * from goodsinfo limit 10;











-- drop table goodsinfo;

-- ----GoodsBrand----
create table goodsbrand(
	SupplierID varchar(4) not null default '-',
	BrandType varchar(100) not null default '-',
	pt varchar(11) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/GoodsBrand.csv"
	into table goodsbrand
    fields terminated by ','
	ignore 1 lines;

select * from goodsbrand limit 10;










-- drop table goodsbrand;

-- ----GoodsColor----
create table goodscolor(
	ColorID varchar(4) not null default '-',
	ColorNote varchar(20) not null default '-',
	ColorSort int not null default 0,    
	pt varchar(11) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/GoodsColor.csv"
	into table goodscolor
    fields terminated by ','
	ignore 1 lines;

select * from goodscolor limit 10;










-- drop table goodscolor;

-- ----GoodsSize----
create table goodssize(
	SizeID varchar(4) not null default '-',
	SizeNote varchar(100) not null default '-',
	SizeSort int not null default 0,    
	pt varchar(11) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/GoodsSize.csv"
	into table goodssize
    fields terminated by ','
	ignore 1 lines;

select * from goodssize limit 10;










-- drop table goodssize;

-- ----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 '-',
    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),
    AddTime varchar(20) not null default '-'
);

#导入数据
load data infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/orderinfo.csv"
	into table OrderInfo
    fields terminated by ','
	ignore 1 lines;

select * from OrderInfo limit 10;

-- 转换日期格式
alter table orderinfo add payt datetime;
update orderinfo set payt = FROM_UNIXTIME(paytime);

alter table orderinfo add addt datetime;
update orderinfo set addt = FROM_UNIXTIME(addtime);





-- drop table OrderInfo;

-- ----OrderDetail----
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 infile "D:/MySQL/mysql-8.0.19-winx64/Uploads/ds/OrderDetail.csv"
	into table OrderDetail
    fields terminated by ','
	ignore 1 lines;
    
select * from OrderDetail limit 10;

select count(*) from OrderDetail;

-- drop table orderdetail;


-- 查询导入表的行数
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



-- 不同时段的登陆用户数
select hour(lastlogin_new),count(userid) from userinfo
group by hour(lastlogin_new)
order by hour(lastlogin_new);



-- 不同时段的累计销售额
select hour(addt)时段,
	sum(OrderAmount)订单总金额,
	sum(sum(OrderAmount)) over (order by hour(addt))累计销售额
from orderinfo 
group by hour(addt);


-- 各省市消费金额
select ri2.regionname 省份,ri1.regionname 城市,sum(OrderAmount) 消费金额 from orderinfo oi
left join regioninfo ri1
on oi.City = ri1.regionid
left join regioninfo ri2
on oi.Province = ri2.regionid
group by ri2.regionid,ri1.regionid;


-- 不同支付方式的订单量
select paytool,count(orderid) 订单量
from orderinfo
group by paytool;


-- 哪种支付方式可能导致用户支付不成功而取消订单
select paytool,avg(orderstate = 3 and paystate = 0) 未支付而取消的订单占比
from orderinfo
group by paytool;


-- 不同品牌的总销量
select typeid,BrandType,sum(amount) from orderdetail od
left join goodsinfo gi
on od.goodsid = gi.goodsid
left join goodsbrand gb
on gi.typeid = gb.supplierid
group by typeid;


-- 不同品牌的复购用户数
select typeid,brandtype,count(复购用户) 复购用户数
from 
(select typeid,brandtype,userid 复购用户
from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on typeid=SupplierID
group by typeid,userid
having count(distinct orderid)>=2) t
group by typeid;