数据分析项目-某加油站订单分析（SQL需求篇）

需求1：对数据进行清洗，删除订单号为null和支付金额小于15的订单。 需求2：统计各种状态的数量和占比
需求3：对支付成功的订单进行描述分析性分析

• 3.1：优惠类型数量及占比
• 3.2：不同油品的加油量及占比
• 3.3：不同油枪月加油车辆数
• 3.4： 开票行为分析
• 3.5：开票数量排前10的公司或者个人名及开票数量，消费金额，加油量
• 3.6：按实付金额排名取前5名，查看用户id和总加油量，加油次数，
• 3.7：按支付次数排名取前10名，查看用户id和总加油量，加油次数，平均每次消费金额 需求4：查看11月每日的订单数，订单总额 需求5：按周统计总订单数、金额、平均客单价（eg：11.04-11.10） 需求6：求周权重指数 并排序。
  （周权重指数=（周日到周六）日权重指数之和 日权重指数=日订单数/最低日订单数） 需求7：不同油品每日加油量统计

思维部分

代码实现部分

需求1：对数据进行清洗，删除订单号为null和支付金额小于15的订单。

ALTER TABLE `某油站订单流水`   RENAME TO satation_datas;

需求2：统计各种状态的数量和占比

修改表名
ALTER TABLE `某油站订单流水`   RENAME TO satation_datas;

删除（删除之前需要做好备份）
DELETE FROM  satation_datas
WHERE `订单号` is null or ` 实付金额`<15;

需求3：对支付成功的订单进行描述分析性分析

• 3.1：优惠类型数量及占比

 SELECT
	`优惠类型`,
	count(`优惠类型` ) AS num,
	count( `优惠类型`) / ( SELECT count( DISTINCT `订单号` ) FROM satation_datas WHERE  `状态`='成功') AS rate 
FROM 
	satation_datas 
WHERE  `状态`='成功'
GROUP BY
	`优惠类型`
ORDER BY num desc;

• 3.2:不同支付入口的数量及占比

 SELECT
	`支付入口`,
	count(`支付入口` ) AS num,
	count( `支付入口`) / ( SELECT count( DISTINCT `订单号` ) FROM satation_datas WHERE  `状态`='成功') AS rate 
FROM 
	satation_datas 
WHERE  `状态`='成功'
GROUP BY
	`支付入口`
ORDER BY num desc;

• 3.3：不同油品的加油量及占总加油比

 SELECT
	`油品`,
	sum(`油量`) AS Fuel,
	 ROUND(sum(`油量`)/(SELECT sum(`油量`) FROM satation_datas WHERE  `状态`='成功'),2) AS rate 
FROM 
	satation_datas 
WHERE  `状态`='成功'
GROUP BY
`油品`
ORDER BY rate desc;

• 3.4：不同油枪月加油车辆数

 SELECT
 `油枪`,
 count(`订单号`) as car_num
 FROM 
	satation_datas 
WHERE  `状态`='成功'
GROUP BY
 `油枪`
ORDER BY car_num desc;

• 3.5： 开票行为分析

SELECT
CASE
		`发票名称` 
		WHEN `发票名称` IS NULL THEN
		0 ELSE 1 
	END '是否开票',
	count( `订单号` ) AS num,
	count( `订单号` ) / ( SELECT count( DISTINCT `订单号` ) FROM satation_datas WHERE `状态` = '成功' ) AS rate 
FROM
	satation_datas 
WHERE
	`状态` = '成功' 
GROUP BY
CASE
		`发票名称` 
		WHEN `发票名称` IS NULL THEN
		0 ELSE 1 
	END;

• 3.6：开票数量排前10的公司或者个人名及开票数量，消费金额，加油量

 SELECT
 `发票名称`,
 count(`订单号`) as '开票数量',
 sum(` 实付金额`) as '消费金额',
 sum(`油量`) as '加油量'
 from satation_datas
 WHERE 
	`状态` = '成功' and `发票名称`  IS not NULL
GROUP BY
`发票名称`
ORDER BY count(`订单号`) desc
LIMIT 10;

• 3.7：按实付金额排名取前5名，查看用户id和总加油量，加油次数，

  SELECT
 `主用户标识Openid`,
 sum(` 实付金额`) as '消费金额',
 sum(`油量`) as '总加油量',
 count(`订单号`) as '加油次数',
 ROUND(sum(`油量`)/count(`订单号`),2) as '均加油量'
 from satation_datas
 WHERE 
	`状态` = '成功'
GROUP BY
1
ORDER BY sum(` 实付金额`) desc
LIMIT 5;

• 3.8：按支付次数排名取前10名，查看用户id和总加油量，加油次数，平均每次消费金额

   SELECT
 `主用户标识Openid`,
 count(` 实付金额`) as '支付次数',
 sum(` 实付金额`) as '消费金额',
 sum(`油量`) as '总加油量',
 ROUND(sum(` 实付金额`)/count(`订单号`),2) as '均消费金额'
 from satation_datas
 WHERE 
	`状态` = '成功'
GROUP BY
1
ORDER BY count(` 实付金额`) desc
LIMIT 10;

需求4：查看11月每日的订单数，订单总额

 思路：5位excel格式日期转为日期 ，参考：https://bbs.csdn.net/topics/392559907 然后按日期分组
SELECT
	DATE_SUB( DATE_FORMAT( '1899-12-30', '%Y-%m-%d' ), INTERVAL - `打印完成` DAY ) AS date,
	count( `订单号` ) AS '订单数',
	sum( ` 实付金额` ) AS '订单总额',
	ROUND(sum( `油量` ),2) AS '总加油量',
	ROUND( sum( ` 实付金额` ) / count( `订单号` ), 2 ) AS '均消费金额' 
FROM
	satation_datas 
WHERE
	`状态` = '成功' 
GROUP BY
	1;

需求5：按周统计总订单数、金额、平均客单价（eg：11.04-11.10）

在这里插入代码片

 mysql 取当前日期对应的周一或周日
 
select subdate(curdate(),date_format(curdate(),'%w')-1)//获取当前日期在本周的周一 

select subdate(curdate(),date_format(curdate(),'%w')-2)//获取当前日期在本周的周二 

select subdate(curdate(),date_format(curdate(),'%w')-7)//获取当前日期在本周的周日 

%W     星期名字(Sunday……Saturday)    
%w     一个星期中的天数(0=Sunday   ……6=Saturday)
date_sub ()减去间隔
date_add()加上间隔
获取日期所在周的周一
SELECT  DATE_SUB('2015/1/29',INTERVAL WEEKDAY('2015/1/29') DAY);
获取日期所在周的周日
SELECT  DATE_ADD('2019/11/03',INTERVAL 6-WEEKDAY('2019/11/03') DAY);
获取日期所在周前一周的周日
SELECT DATE_SUB('2015/2/5', INTERVAL WEEKDAY('2015/2/5')+1 DAY);

SELECT  DATE_SUB('2019/11/01',INTERVAL WEEKDAY('2019/11/01') DAY);

创建中间视图，方便查询所在周
drop  view  date_v;
CREATE VIEW date_v as 
SELECT
	d.data_date,
	DATE_SUB( d.data_date, INTERVAL WEEKDAY( d.data_date ) DAY ) AS week_start,
	DATE_add( d.data_date, INTERVAL 6-WEEKDAY( d.data_date ) DAY ) AS week_end,
	CONCAT(
		DATE_SUB( d.data_date, INTERVAL WEEKDAY( d.data_date ) DAY ),
		"~",
		DATE_ADD( d.data_date, INTERVAL 6-WEEKDAY( d.data_date ) DAY ) 
	) AS week_info 
FROM
	( SELECT DISTINCT DATE_SUB( DATE_FORMAT( '1899-12-30', '%Y-%m-%d' ), INTERVAL - `打印完成` DAY ) AS data_date FROM satation_datas WHERE `状态` = '成功' ) d
#验证一下
SELECT * from date_v;
	
	连接查询
SELECT  v.week_info,
	count( `订单号` ) AS '订单数',
	sum( ` 实付金额` ) AS '订单总额',
	ROUND(sum( `油量` ),2) AS '总加油量',
	ROUND( sum( ` 实付金额` ) / count( `订单号` ), 2 ) AS '均消费金额' 
from
(SELECT `订单号`,
	DATE_SUB( DATE_FORMAT( '1899-12-30', '%Y-%m-%d' ), INTERVAL - `打印完成` DAY ) AS data_date,
` 实付金额` ,
`油量` 
from satation_datas WHERE `状态`= '成功' GROUP BY 1)a 
left join date_v v on a.data_date=v.data_date
GROUP BY 1;

需求6：求周权重指数 并排序。

周权重指数=（周日到周六）日权重指数之和

日权重指数=日订单数/最低日订单数

来自《数据化管理》
周权重指数概念相关
周权重是以某段销售周期内的历史日销售额数据为基础，以周为单位，进行权重分析处理的一种管理工具。
周权重指数是一个相对概念，每个企业都不尽相同，一般介于7．0一14．0之间。
值越大表示该企业或者店铺的日销售额波动幅度越大。周权重指数是零售店铺用来犀化处理各种销售状况、销售事件的管理工具，非常强大。
周权重指数=周一到周日每天的日权重指数相加
假如某个零售店铺的周权重指数为10．0，其中周一到周日依次为1.0，1.2，1.3，1.2，1.6，1.9，1.80可以这样简单地理解周权重指数和日权重指数，如果这个店铺每周销售額为10元，那一般来说周一可以销售1.0元，周二可以销售1.2元…权重指数是一个相对值。
为了标准化管理，每个零售企业应该是统一的周权重指数。
可应用行业或业态
总的来说以普通消费者为销售或服务对象的业态都会具有这种规律，只不过零售行业表现得相对更明显一些。
大体上来说权重指数概念适用于如下这些行业或业态：

1. 传统零售业，包括百货商场、ShoppingMall,超市、便利店等，同时这些零售业态中销售的品牌也同样具有这种特性。
2. 各种专卖店，包括如服装专卖店、电器专卖店、手机专卖店、建材专卖店、药店等。
3. 以普通消费者为对象的电子商务模式，包括B2C、C2C等。
4. 售卖服务的业态，比如火车站、汽车站、手机营运商的营业万、电影院、饭店、旅游景点等 等，甚至你们家附近的理发店、彩票店都适用。

以下为个人总结：
这道需求主要用于简单了解周权重数，数据量方面还是有点小，主要关注以下实现步骤吧。

SELECT weekday,
avg_amount,
(SELECT min(avg_amount) from week_v) as min,
#--取出最小值
ROUND(avg_amount/(SELECT min(avg_amount) from week_v),2) as num
#--用每日值除以最小值 得到每日权重
from week_v
ORDER BY num desc
;

需求7：不同油品每日加油量统计

--求出每日加油量
SELECT
`油品`,
DATE_SUB( DATE_FORMAT( '1899-12-30', '%Y-%m-%d' ), INTERVAL - `打印完成` DAY ) AS data_date,
sum( ` 实付金额` ) AS amount
FROM
	satation_datas 
WHERE
	`状态` = '成功' 
GROUP BY
	1,2;

实现行转换列
SELECT data_date,
SUM(IF (`油品`='0# 柴油',amount,NULL)) '0# 柴油',
SUM(IF (`油品`='92#汽油',amount,NULL)) '92#汽油',
SUM(IF (`油品`='95#汽油',amount,NULL)) '95#汽油',
SUM(IF (`油品`='98#汽油',amount,NULL)) '98#汽油',
amount as total
FROM 
(SELECT
`油品`,
DATE_SUB( DATE_FORMAT( '1899-12-30', '%Y-%m-%d' ), INTERVAL - `打印完成` DAY ) AS data_date,
sum( ` 实付金额` ) AS amount
FROM
	satation_datas 
WHERE
	`状态` = '成功' 
GROUP BY
	1,2
)a
GROUP BY data_date;