• 售前

  • 售后

热门帖子
入门百科

【一周入门MySQL—5】

[复制链接]
明功 显示全部楼层 发表于 2022-1-16 20:22:55 |阅读模式 打印 上一主题 下一主题
数据库实例应用


【电商数据处置惩罚案例】
目标需求:将某电商脱敏后数据导入数据库举行加工处置惩罚,使用加工好的数据分析业务标题数据获取


  • 客户干系:UserInfo.csv:用户主表、RegionInfo.csv:地区表、UserAddress.csv:用户地点表
  • 商品干系:GoodsInfo.csv:商品主表、GoodsBrand.csv:商品品牌表、GoodsColor.csv:商品颜色表、GoodsSize.csv:商品尺码
  • 订单干系文件:OrderInfo.csv:订单主表、OrderDetail.csv:订单详情表

   SQL数据处置惩罚:
  数据洗濯→数据筛选→数据透视→数据排序
  
表布局一览:









表之间的关联关系:



数据准备:
新建数据库
  1. create database ds;
  2. use ds;
复制代码

建表并导入数据
  1. --  UserInfo table
  2. create table userinfo(
  3.        userid varchar(6) not null default '-',
  4.     username varchar(20) not null default '-',
  5.     userpassword varchar(100) not null default '-',   
  6.     sex int not null default 0,
  7.     usermoney int not null default 0,
  8.     frozenmoney int not null default 0,
  9.     addressid varchar(20) not null default '-',
  10.     regtime varchar(20) not null default '-',
  11.     lastlogin varchar(20) not null default '-',
  12.     lasttime date not null
  13. );
  14. #导入数据
  15. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/UserInfo.csv"
  16.        into table userinfo
  17.     fields terminated by ','
  18.        ignore 1 lines;
  19. -- regioninfo
  20. create table regioninfo(
  21.        regionid varchar(4) not null default '-',
  22.     parentid varchar(4) not null default '-',
  23.     regionname varchar(20) not null default '-',   
  24.     regiontype int not null default 0,
  25.     agencyid int not null default 0,
  26.     pt varchar(11) not null default '-'
  27. );
  28. #导入数据
  29. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/RegionInfo.csv"
  30.        into table regioninfo
  31.     fields terminated by ','
  32.        ignore 1 lines;
  33. -- UserAddress
  34. create table useraddress(
  35.        addressid varchar(5) not null default '-',
  36.     userid varchar(6) not null default '-',  
  37.     consignee varchar(50) not null default '-',
  38.     country varchar(1) not null default '-',
  39.     province varchar(2) not null default '-',
  40.     city varchar(4) not null default '-',
  41.     district varchar(4) not null default '-', 
  42.     address varchar(200) not null default '-',
  43.     pt varchar(11) not null default '-'
  44. );
  45. #导入数据
  46. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/UserAddress.csv"
  47.        into table useraddress
  48.     fields terminated by ','
  49.        ignore 1 lines;
  50. -- GoodsInfo
  51. create table goodsinfo(
  52.        goodsid varchar(6) not null default '-',
  53.        typeid varchar(3) not null default '-',
  54.        markid varchar(4) not null default '-',
  55.        goodstag varchar(100) not null default '-',
  56.        brandtag varchar(100) not null default '-',
  57.        customtag varchar(100) not null default '-',
  58.        goodsname varchar(100) not null default '-',
  59.        clickcount int not null default 0,
  60.        clickcr int not null default 0,
  61.        goodsnumber int not null default 0,
  62.        goodsweight int not null default 0,
  63.        marketprice double not null default 0,
  64.        shopprice double not null default 0,
  65.        addtime varchar(20) not null default 0,
  66.        isonsale int not null default 0,
  67.        sales int not null default 0,
  68.        realsales int not null default 0,
  69.        extraprice double not null default 0,
  70.        goodsno varchar(10) not null default '-'
  71. );
  72. #导入数据
  73. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/goodsinfo.csv"
  74.        into table goodsinfo
  75.     fields terminated by ','
  76.        ignore 1 lines;
  77. -- GoodsBrand
  78. create table goodsbrand(
  79.        SupplierID varchar(4) not null default '-',
  80.        BrandType varchar(100) not null default '-',
  81.        pt varchar(11) not null default '-'
  82. );
  83. #导入数据
  84. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/GoodsBrand.csv"
  85.        into table goodsbrand
  86.     fields terminated by ','
  87.        ignore 1 lines;
  88. -- GoodsColor
  89. create table goodscolor(
  90.        ColorID varchar(4) not null default '-',
  91.        ColorNote varchar(20) not null default '-',
  92.        ColorSort int not null default 0,   
  93.        pt varchar(11) not null default '-'
  94. );
  95. #导入数据
  96. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/GoodsColor.csv"
  97.        into table goodscolor
  98.     fields terminated by ','
  99.        ignore 1 lines;
  100. -- GoodsSize
  101. create table goodssize(
  102.        SizeID varchar(4) not null default '-',
  103.        SizeNote varchar(100) not null default '-',
  104.        SizeSort int not null default 0,   
  105.        pt varchar(11) not null default '-'
  106. );
  107. #导入数据
  108. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/GoodsSize.csv"
  109.        into table goodssize
  110.     fields terminated by ','
  111.        ignore 1 lines;
  112. -- OrderInfo
  113. create table OrderInfo(
  114.        OrderID varchar(6) not null default '-',
  115.        UserID varchar(10) not null default '-',
  116.        OrderState int not null default 0,
  117.        PayState int not null default 0,
  118.     AllotStatus int not null default 0,
  119.        Consignee varchar(100) not null default '-',
  120.     Country int not null default 0,
  121.     Province int not null default 0,
  122.     City int not null default 0,
  123.     District int not null default 0,
  124.     Address varchar(100) not null default '-',
  125.     GoodsAmount double not null default 0,
  126.     OrderAmount double not null default 0,
  127.     ShippingFee int not null default 0,
  128.     RealShippingFee int not null default 0,
  129.     PayTool int not null default 0,
  130.     IsBalancePay int not null default 0,
  131.     BalancePay double not null default 0,
  132.     OtherPay double not null default 0,
  133.     PayTime varchar(20),
  134.     AddTime varchar(20) not null default '-'
  135. );
  136. #导入数据
  137. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orderinfo.csv"
  138.        into table OrderInfo
  139.     fields terminated by ','
  140.        ignore 1 lines;
  141. -- OrderDetail
  142. create table OrderDetail(
  143.        RecID varchar(7) not null default '-',
  144.        OrderID varchar(6) not null default '-',
  145.        UserID varchar(6) not null default '-',
  146.        SpecialID varchar(6) not null default '-',
  147.        GoodsID varchar(6) not null default '-',
  148.     GoodsPrice double not null default 0,
  149.     ColorID varchar(4) not null default '-',
  150.     SizeID varchar(4) not null default '-',
  151.     Amount int not null default 0
  152. );
  153. #导入数据
  154. load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/OrderDetail.csv"
  155.        into table OrderDetail
  156.     fields terminated by ','
  157.        ignore 1 lines;
  158. -- 查询导入表的行数
  159. select count(*) from userinfo; -- 1000
  160. select count(*) from RegionInfo; -- 3415
  161. select count(*) from useraddress; -- 10000
  162. select count(*) from goodsinfo; -- 10000
  163. select count(*) from goodsbrand; -- 64
  164. select count(*) from goodscolor; -- 2641
  165. select count(*) from goodssize; -- 289
  166. select count(*) from orderinfo; -- 3711
  167. select count(*) from orderdetail; -- 10000
复制代码

实践题

表数据调解
用户信息表

  1. select * from userinfo;
  2. -- 时间戳转换为标准的日期时间型格式
  3. set sql_safe_updates=0; -- 设置数据库的安全权限
  4. update userinfo set regtime = from_unixtime(regtime);
  5. -- 执行报错 :Error Code: 1406. Data too long for column 'regtime'
  6. -- 因为日期时间型不能直接放到文本格式内
  7. alter table userinfo modify regtime datetime; -- 表中列有数据的情况下不能直接修改数据类型
  8. -- 在表中添加一个新字段“regtime_new”,类型为日期时间型
  9. alter table userinfo add regtime_new datetime;
  10. -- 这个时候可以去更新新字段“regtime_new”的值,将时间戳转换为标准日期时间格式
  11. update userinfo set regtime_new = from_unixtime(regtime);
  12. -- 同样的方法,设置lastlogin(最后登录时间)
  13. alter table userinfo add lastlogin_new datetime;
  14. update userinfo set lastlogin_new = from_unixtime(lastlogin);
复制代码


地区信息表

  1. select * from RegionInfo;
  2. -- 需要将“pt”字段由文本型转换为日期型
  3. -- 首先需要提取文本中的日期信息(中间8个字符)
  4. -- 然后将提取的信息转换为日期型格式(同样使用添加一列的方式)
  5. alter table RegionInfo add pt_new date;
  6. update RegionInfo set pt_new=mid(pt,2,8);
复制代码


其他表的调解
  1. select * from useraddress;
  2. alter table useraddress add pt_new date;
  3. update useraddress set pt_new=mid(pt,2,8);
  4. select * from goodsinfo;
  5. alter table goodsinfo add addtime_new datetime;
  6. update goodsinfo set addtime_new = from_unixtime(addtime);
  7. select * from goodsbrand;
  8. alter table goodsbrand add pt_new date;
  9. update goodsbrand set pt_new=mid(pt,2,8);
  10. select * from goodscolor;
  11. alter table goodscolor add pt_new date;
  12. update goodscolor set pt_new=mid(pt,2,8);
  13. select * from goodssize;
  14. alter table goodssize add pt_new date;
  15. update goodssize set pt_new=mid(pt,2,8);
  16. select * from orderinfo;
  17. alter table orderinfo add addtime_new datetime;
  18. update orderinfo set addtime_new = from_unixtime(addtime);
  19. -- paytime字段是支付时间,0的数据不能直接通过from_unixtime()转换(否则会变成1970-01-01 00:00:00),需要先转换成null
  20. alter table orderinfo add paytime_new datetime;
  21. update orderinfo set paytime_new = from_unixtime(paytime) where paytime <> '0';;
  22. select * from orderdetail;
  23. -- orderdetail表不需要调整列数据字段格式
复制代码

【实际查询案例】

   -- 1、差别时段的登岸用户数
  1. -- 按照时间分组,统计每个小时有多少用户登录
  2. -- 针对字段“lastlogin_new”提取小时
  3. select hour(lastlogin_new) 时段,count(userid) 登录用户数
  4. from userinfo
  5. group by hour(lastlogin_new)
  6. order by 时段;
  7. -- 在实际情况中最后登录时间会是不同的日期,我们就可以用来统计最近7天或者30天登录的用户数,用来统计潜在流失用户
复制代码


   -- 2、差别时段的下单数目
  1. -- 在订单信息表orderinfo中,orderid是唯一的
  2. select hour(addtime_new) 时段,count(orderid) 下单数量
  3. from orderinfo
  4. group by hour(addtime_new)
  5. order by 时段;
  6. -- 不同时段的累计下单数量
  7. select hour(addtime_new) 时段,count(orderid) 下单数量,
  8.        sum(count(orderid)) over( order by hour(addtime_new) )累计下单数量
  9. from orderinfo
  10. group by hour(addtime_new);
  11. -- 上面的例子指定了分区内的排序, 默认就是统计滑动窗口第一行到当前行的订单数量
复制代码


   -- 3、当日GMV(未付款订单金额0+待发货订单金额1+已发货订单金额2+已取消订单金额3)
  1. -- GMV(Gross Merchandise Volume)即商品交易总额,是一段时间内的成交总额
  2. -- 按照订单状态进行orderstate分组
  3. select orderstate,sum(orderamount) 订单金额
  4. from orderinfo
  5. group by orderstate
  6. with rollup; -- with rollup 对分组之后的聚合值进行求和
复制代码


   -- 4、各省市斲丧金额(orderinfo-orderamount)
  1. -- orderinfo表中的省份字段province 城市字段city
  2. -- RegionInfo表中都存放在regionid字段
  3. -- select * from RegionInfo;
  4. select r1.regionname 省份,r2.regionname 城市,round(sum(orderamount),2)   消费金额
  5. from orderinfo
  6. join RegionInfo as r1 on province = r1.regionid
  7. join RegionInfo as r2 on city = r2.regionid
  8. group by province,city
  9. order by province,city;
复制代码


   -- 5、差别付出方式的订单量
  1. -- 支付工具 paytool 用户提交订单后会进入支付界面,选择支付方式,不管支付成功与否,都会产生支付方式
  2. select paytool 支付方式,count(orderid) 订单量
  3. from orderinfo
  4. group by paytool
  5. order by paytool;
复制代码


   -- 6、哪种付出方式大概导致用户付出不乐成而取消订单
  1. -- 查询因为支付不成功而取消的订单数量
  2. -- 订单状态 orderstate = 3 支付失败 paystate = 0
  3. select t1.paytool, 未支付取消的订单量,每个支付工具订单总量,
  4. ifnull(未支付取消的订单量/每个支付工具订单总量,0) 占比
  5. from
  6. (select paytool,count(orderid) 每个支付工具订单总量
  7. from orderinfo
  8. group by paytool) t1
  9. join
  10. (select paytool,count(orderid) 未支付取消的订单量
  11. from orderinfo
  12. where orderstate = '3' and paystate = '0'
  13. group by paytool) t2
  14. on t1.paytool = t2.paytool;
复制代码


   -- 7、当日差别品牌的总销量
  1. -- 销量在表orderdetail   品牌在表goodsbrand
  2. -- 通过中间表商品表goodsinfo进行三表连接
  3. -- 会存在有的商品品牌没有在品牌表中存在的情况
  4. select goodsinfo.typeid 品牌ID,brandtype 品牌名称,sum(amount) 销量
  5. from orderdetail
  6. left join goodsinfo on orderdetail.goodsid = goodsinfo.goodsid
  7. left join goodsbrand on goodsinfo.typeid = goodsbrand.supplierid
  8. group by goodsinfo.typeid;
复制代码


   -- 8、当日差别品牌的复购用户数
  1. select  t.品牌ID,brandtype,count(t.userid) 复购用户数
  2. from
  3. (select goodsinfo.typeid 品牌ID,brandtype,userid,count(distinct orderid) 购买次数
  4. from orderdetail
  5. left join goodsinfo on orderdetail.goodsid = goodsinfo.goodsid
  6. left join goodsbrand on goodsinfo.typeid = goodsbrand.supplierid
  7. group by goodsinfo.typeid,userid
  8. having count(distinct orderid)  > 1) t
  9. group by t.品牌ID;
复制代码


   -- 9、查询效果生存为表,用于后续重复使用
  1. create table pro_amount as
  2. select r1.regionname 省份,r2.regionname 城市,round(sum(orderamount),2)   消费金额
  3. from orderinfo
  4. join RegionInfo as r1 on province = r1.regionid
  5. join RegionInfo as r2 on city = r2.regionid
  6. group by province,city
  7. order by province,city;
  8. select * from pro_amount;
复制代码


   -- 10、将查询效果导出到指定的路径中
  1. -- 主要需要使用以下路径 "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
  2. select r1.regionname 省份,r2.regionname 城市,round(sum(orderamount),2)   消费金额
  3. from orderinfo
  4. join RegionInfo as r1 on province = r1.regionid
  5. join RegionInfo as r2 on city = r2.regionid
  6. group by province,city
  7. order by province,city
  8. into outfile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/pro_amount.csv";
复制代码




 
 

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作