餐饮业日销售情况分析仪（MySQL+Excel综合应用案例）

KPI指标介绍：

表结构信息：

导入数据：

create database CateringCase;

use CateringCase;

--  Bill table
create table Bill(
	billdate date not null,
    billnumber varchar(20) not null default '-',
    shopname varchar(20) not null default '-',    
    billdiscount float not null default 0,
    paytime time not null,
    tablenumber int not null default 0,
    peoplecount int not null default 0
);

#导入数据
load data local infile 'F:/ProgramData/MySQL/MySQL Server 8.0/Uploads/-bill.csv' 
into table Bill
fields terminated by ',';


--  OrderDetail table
create table OrderDetail(
    billnumber varchar(20) not null default '-',
    detail varchar(20) not null default '-',
    pay int not null default 0
);

#导入数据
load data local infile 'F:/ProgramData/MySQL/MySQL Server 8.0/Uploads/-order.csv' 
into table OrderDetail
fields terminated by ',';


--  ShopDetail table
create table ShopDetail(
    ShopName varchar(20) not null default '-',
    twotable int not null default 0,
    threetable int not null default 0,
    fourtable int not null default 0,
    alltable int not null default 0
);

#导入数据
load data local infile 'F:/ProgramData/MySQL/MySQL Server 8.0/Uploads/-shop.csv' 
into table ShopDetail
fields terminated by ',';

数据加工：

用orderdetail表创建单汇总金额表（OrderGroup）

以orderdetail表的billnumber字段为汇总依据，求出每条billnumber下pay的加总值。

新表字段：billnumber（单号）、pay（金额）

用Bill表与OrderGroup表创建新单号详细表（NewBill）

以billnumber为关键字段关联两表，将OrderGroup表中的pay字段合并到Bill表中，并使用pay与billdiscount字段计算出折扣金额。

新表字段：所有Bill表中的字段、pay（金额）、rebate（折扣金额）

计算逻辑：Rebate = pay * billdiscount

create table OrderGroup( select billnumber,sum(pay) pay
from orderdetail
group by billnumber);

Create table NewBill(
select *,billdiscount*pay rebate 
from bill left join ordergroup using(billnumber));

用OrderDetail表与Bill表创建新点菜明细表（NewOrderDetail）

以billnumber为关键字段关联两表，并用Bill表中的shopname与OrderDetail表中的所有字段组成新表

新表字段：shopname（店名）、OrderDetail表中的所有字段

create table neworderdetail(
select b.shopname,o.* 
from orderdetail o left join bill b using(billnumber)
);

用Shopdetail表创建新店面情况表（NewShopDetail）

在原有shopdetail表字段基础上计算并添加allseats字段

新表字段：所有ShopDetail表中的字段、allseats（总座位数）

计算逻辑：allseats = twotable * 2 + three * 3 + fourtable * 6

用NewBill表与NewShopDetail表创建店汇总信息表（ShopTotal）

以shopname字段为关键字段关联两表，并以shopname字段为汇总条件，创建以下字段

新表字段：

 店名： b.shopname

单数： b.billnumber的计数

 人数： b.peoplecount的加总  

折扣总金额： b.rebate的加总

店汇总金额： b.pay的加总

单均消费： b.pay的合计值/b.billnumber的计数值

人均消费： b.pay的合计值/b.peoplecount的合计值

总台数： s.alltable

总座位数： s.allseats

翻台率： b.billnumber的计数值/s.alltable

上座率： b.peoplecount的合计值/s.allseats

折扣率： b.rebate的合计值/b.pay的合计值    

create table NewShopDetail(
select*,twotable*2+threetable*3+fourtable*6 allseats
from shopdetail);

create table ShopTotal(
select b.shopname 店名,count(billnumber) 单数,sum(peoplecount) 人数,
       sum(rebate) 折扣总金额,sum(pay) 店汇总金额,sum(rebate)/sum(pay) 折扣率,
       sum(pay)/count(billnumber) 单均消费,sum(pay)/sum(peoplecount) 人均消费,
       alltable 总台数,count(billnumber)/alltable 翻台率,allseats 总座位数,sum(peoplecount)/allseats 上座率
from newbill b left join newshopdetail using(shopname) 
group by b.shopname
);

导入Excel中：

在Power Query中处理：

更改数据类型：NewBill表中的billdiscount更改数据类型为百分比，rebate更改数据类型为整数；Shop Total表中的折扣总金额、单均消费、人均消费、翻台率、上座率更改数据类型为整数，折扣率更改数据类型为百分比。

在Power Pivot中处理：

更改数据类型：导入的时候可能会发生变化，所以要确认、核实以及调整。

创建报表连接：

创建店面销售情况的KPI：

每家店的平均金额与所有店的总平均金额对比，店的平均金额小于所有店的总平均金额时，说明该店拉低了所有店的总平均金额。

定义目标值：1.5。如果一家店的平均值超过了所有店的总平均值的1.5倍，那么该店就是很好的销售情况。

创建数据透视表：

制作分析仪：

将制作好的数据透视表一一复制，以链接的图片形式粘贴，并将所有链接的图片进行组合；然后插入切片器，选择shoptotal表中的店名，将切片器剪切到分析仪中：

创建数据透视图：展现一天中各小时的销售额和销量

制作饼图：菜品的销售金额和菜品的销量

制作数据条：

最后将切片器关联到所有制作的内容上。

最后效果：