物流公司dashboard--SQL+Excel

2020-11-12 本文已影响0人 jinghenggl

目录：
● 项目背景
● 分析目标
● 分析过程

一、项目背景

XX快运是XX旗下的集运平台，主营10kg以上产品快运。通过现有数据监控车辆承运与货主签收情况，并建立dashboard可视化展示，生成周报、月报。

二、分析目标

通过对常用指标分析，建立dashboard看板。

三、分析过程

1、SQL数据提取

先从数据库中提取指标相关数据，本文对下列6个指标的计算进行演示与练习。

（1）计算每月每周下单情况
（2）计算每周订单运单送达情况
（3）每月每周取件情况
（4）每周车辆使用情况
（5）各车型数量
（6）每月每周新增车辆情况

（1）计算每月每周下单情况

select  
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week ,
count(1) as order_num
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1

image.png

（2）计算每周订单运单送达情况

select  
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week ,
count(1) as order_num,
sum(case when is_service='1' then 1 else 0 end) as service,
format(sum(case when is_service='1' then 1 else 0 end)/count(1),2) as service_prop,
sum(case when is_service='0' then 1 else 0 end) as no_service,
format(sum(case when is_service='0' then 1 else 0 end)/count(1),2)as no_service_prop
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 ;

image.png

（3）每月每周取件情况

select  
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week ,
count(1) as order_num,
sum(case when is_pick_up='1' then 1 else 0 end) as pick,
format(sum(case when is_pick_up='1' then 1 else 0 end)/count(1),2) as pick_prop,
sum(case when is_pick_up='0' then 1 else 0 end) as no_pick,
format(sum(case when is_pick_up='0' then 1 else 0 end)/count(1),2)as no_pick_prop
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 ;

image.png

（4）每周车辆使用情况

select 
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week,
count(distinct car_info) car_num,
concat(format((count(distinct car_info) /(select count(1) from ds_car_info))*100,2),'%') as car_num_prop
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1

image.png

（5）各车型数量

select model,count(order_no) as use_car
from ds_order_info t1
left join ds_car_info t2
on t1.car_info= t2.license_plate
group by model

image.png

（6）每月每周新增车辆情况

select  t1.month,t1.month_week,t1.car_num,t2.month_week,t2.car_num,(t1.car_num-t2.car_num) as 新增 from
(select 
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week,
count(distinct car_info) as car_num
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 
) t1
left join 
(select 
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week,
count(distinct car_info) as car_num
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 
) t2
on t1.month=t2.month and t1.month_week -1 =t2.month_week