基于天猫订单的数据分析

• 前言
• 一、项目介绍
• 二、实验环境
• 三、数据预处理
• • 1. 将csv文件导入MySQL数据库中
  • 2. 缺失值处理
  • 3. 异常值处理
  • 4. 重复值处理
• 四、数据分析
• • 1. 描述性统计分析
  • 2. 总体销售情况
  • 3. 周、日销售情况
  • 4. 产品价格
  • 5. 地区分析
• 总结

该项目来自和鲸社区天猫订单数据分析
该项目本来使用的是Python语言，但因自身的SQL+Excel实战经验较少，使用SQL+Excel进行分析。
分享给大家，如有错误，烦请指出！

对2020年2月份的真实天猫订单成交数据（共28010条记录）进行数据分析，阐述销售现状、挖掘潜在规律、发现存在问题、提出可行性建议。

数据集来自和鲸社区 天猫订单数据分析

MySQL、VScode、Excel、Python

参考：将CSV文件导入MySQL表

从和鲸社区下载order_report.csv 数据集，保存至mysql.ini文件规定的目录下

SQL语句创建表tmall

CREATE TABLE tmall(  
    order_id int NOT NULL PRIMARY KEY COMMENT 'Primary Key',
    total_amount float(9,1) NOT NULL COMMENT 'total amount',
    actual_amount float(9,1) NOT NULL COMMENT 'actual amount',
    address VARCHAr(255) NOT NULL COMMENT 'content',
    order_time DATETIME NOT NULL,
    pay_time DATETIME,
    refund_amount float(9,1)
) DEFAULT CHARSET UTF8 COMMENT 'newTable';

将数据从csv文件导入tmall表中

LOAD DATA INFILE 'C:/web/xxxx/uploads/order_report.csv' 
INTO TABLE tmall 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS;

查看tmall表结构

desc tmall

忽视异常值的存在是十分危险的，如果不加剔除地把异常值包括进数据的计算分析过程中，那么将对结果会带来不良影响。
参考知乎博客：异常值处理

该处使用的url网络请求的数据。

计算2月份每天的成交金额GMV和订单数。
GMV：成交金额，属于电商平台的成交类指标，主要指拍下订单的总金额，包含付款和未付款两部分。

select DATE_FORMAT(order_time,"%Y-%m-%d")  as day, sum(total_amount) as gmv,
count(*) as order_num
from tmall
group by day
order by day
into outfile 'C:/web/mysql-8.0.19-winx64/uploads/day_order.csv' fields terminated by ","  escaped by '' optionally enclosed  by ''   lines terminated by 'n' ;

信息获取
1、2.17日之前，销售额都很少
2、2.10-2.16销售额都仅在1000内
3、2.17日的销售额持续增长，且在2.25日达到了本月最高销售额33.9万

原因分析

分析一周内的销售趋势
需要思考如何把时间类型转换为周，参考date_format函数
这里还需注意，每个月多少个周一、周二是不固定的，需要求平均

select week, 
case week when 6 then order_num/5
else order_num/4 end as order_num,
case when week = 6 then total/5
else total/4 end as total
from (
select DATE_FORMAT(order_time,"%w") as week, count(*) as order_num,sum(actual_amount) as total
from tmall
group by week
order by week) t

观察到的信息：
1、每周周五销售额最高，其次是周二和周四
2、周末和周一销售额最低

分析
1、周五下班开启周末生活，大多数人可能会选择在周五购物来放松
2、周末大多数人会选择外出休闲，不会花费时间在电商购物
3、促销活动可以安排在周五

分析一天中每小时的销售趋势

# 日销售情况
select DATE_FORMAT(order_time,"%H") as hour, count(*) as order_num,sum(actual_amount) as total
from tmall
where actual_amount != 0 
group by hour
order by hour

观察到的信息
1、从早上六点，销量开始提升，中午11点左右、16点左右、22点左右出现销售高峰
2、销量最高是晚上22点，销量最低是凌晨5点

分析
1、在三个销量高峰时期，可以多推送促销信息，增加销量

计算订单在每个区间的数量，和占比
使用interval()函数 和 elt()函数 对生成价格区间
使用窗口函数计算每个区间的订单量的占比

SELECt *,SUM(num)OVER(PARTITION BY price)/SUM(num)OVER() as rate
from (
SELECt ELT(interval(actual_amount,0,100,200,300,400,500),'0-100','100-200','200-300','300-400','400-500','>500') as price, count(*) as num
from  tmall
group by price) t

观察到的信息
1、用户每次提交订单的额度大多在0-200内
2、用户更喜欢在20-40内下单，订单量最多
3、20元以下和400以上的订单就很少了
4、但是用户的实际付款金额却在0-20以内，说明用户虽然在提交了20以上的订单金额，却付款了部分金额

select address, sum(actual_amount) as total, count(*) as order_num
from tmall
group by address
order by total desc

获得信息
销售额最高的省市是上海，其次是北京、江苏、广东、浙江；
销售额高的省市主要集中在东部和南部沿海，以及四川省；
销售额最少的省市为西藏、青海、湖北、新疆、宁夏， 主要为西部地区。

提示：这里对文章进行总结：

例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。