一、关于本次案列的概述
本次案列的数据是来自某餐饮数据的日销售情况，基于已经提供的数据，需要在excel中做一个各个店面的分析仪，其KPI 指标相关如下：
该问题的难点还是在于表之间的逻辑关系，以及相关业务知识。总之大致的效果要做成如下：
数据表一共有三张，包括：bill orderdetail shopdetail
他们的表结构如下：
而我们的目的是获得不同店面的数据情况，上面的三张表是不能满足这样情况的，所以我选择先在MySQL中进行数据加工，再用Excel中利用power pivot生成数据透视表和数据透视图，
当然还有其他一些操作，整体动态图的展现等。
二 、数据预处理 —基于MySQL5.7 +workbench 6.3
（1）建表与数据导入
在MySQL中先建立 库 MySQL_powerBI
create database mysql_powerbi;
use mysql_powerbi;
-- 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 'D:/mysql_powerBI/data/-bill.csv'
into table Bill
fields terminated by ',';
select * from Bill;
表 Bill 的情况如下图，一共有 682 行
-- 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 'D:/mysql_powerBI/data/-order.csv'
into table OrderDetail
fields terminated by ',';
select * from OrderDetail;
表 OrderDetail 共有 3410 行，如下：
-- 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 'D:/mysql_powerBI/data/-shop.csv'
into table ShopDetail
fields terminated by ',';
select * from ShopDetail;
表 ShopDetail 共有 5 行，如下：
在确保上面的步骤无误后，接下来进入数据加工阶段，得到我们想要的数据
（2）数据加工
在进行数据加工之前必须要非常清楚的理解各表结构信息，不然根本没有办法做！一般来说，我都会画一个表之间的连接关系来帮助自己进行数据处理，看看每个表是怎么来的
(a) 用orderdetail表创建单汇总金额表（OrderGroup）
以orderdetail表的billnumber字段为汇总依据，求出每条billnumber下pay的加总值。
新表字段：billnumber（单号）、pay（金额）
-- 数据加工
-- 创建单汇总金额表
Create table OrderGroup(
select billnumber, sum(pay) as pay from OrderDetail
group by billnumber
);
select * from OrderGroup;
结果共682行，如下：
（b）用Bill表与OrderGroup表创建新单号详细表（NewBill）
以billnumber为关键字段关联两表，将OrderGroup表中的pay字段合并到Bill表中，并使用pay与billdiscount字段计算出折扣金额。
新表字段：所有Bill表中的字段、pay（金额）、rebate（折扣金额）
计算逻辑：Rebate = pay * billdiscount
-- 创建新单号详细表
Create table NewBill(
select b.*,o.pay,b.billdiscount * o.pay as rebate from bill as b left join ordergroup as o
on b.billnumber = o.billnumber);
select * from NewBill;
结果共682行 ，如下：
（c）用Shopdetail表创建新店面情况表（NewShopDetail）
在原有shopdetail表字段基础上计算并添加allseats字段
新表字段：所有ShopDetail表中的字段、allseats（总座位数）
计算逻辑：allseats = twotable * 2 + three * 3 + fourtable * 6 (这里四人桌（含）都乘以6，你可以理解为过道也可以坐人....)
-- 创建新店面情况表
create table NewShopDetail(
select *, (twotable * 2 + threetable * 3 + fourtable * 6) as allseats
from shopdetail as s);
select * from NewShopDetail;
结果共有5行，如下：
（d）用OrderDetail表与Bill表创建新点菜明细表（NewOrderDetail）
以billnumber为关键字段关联两表，并用Bill表中的shopname与OrderDetail表中的所有字段组成新表
新表字段：shopname（店名）、OrderDetail表中的所有字段
-- 创建新点菜明细表
create table neworderdetail(
select b.shopname,o.* from orderdetail as o left join bill as b
on o.billnumber = b.billnumber
);
select * from neworderdetail;
结果共有3410行，如下：
（e） 用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 ShopTotal(
select b.shopname as 店名, count(b.billnumber) as 单数,
sum(b.peoplecount) as 人数,sum(b.rebate) as 折扣总金额,sum(b.pay) as 店汇总金额,
sum(b.pay)/count(b.billnumber) as 单均消费,
sum(b.pay)/sum(b.peoplecount) as 人均消费,
s.alltable as 总台数,
s.allseats as 总座位数,
count(b.billnumber)/s.alltable as 翻台率,
sum(b.peoplecount)/s.allseats as 上座率,
sum(b.rebate)/sum(b.pay) as 折扣率
from newbill as b left join newshopdetail as s
on b.shopname = s.shopname
group by b.shopname);
select * from shoptotal;
结果共 5 行，完整如下：
基于mysql 的数据加工到这里就完成了，在保证上面没有问题的情况下，接下来利用Excel 连接MySQL,将数据表导入power pivot进行数据透视表和数据透视图的制作。
三、用powerBI进行数据透视
新建空白表格——数据——新建连接——从数据库——从MySQL数据库，在弹出的窗口中这样填
确定，弹出以下窗口，选择 newbill neworderdetail shoptotal 三张表
在 加载 出下拉，选着 加载到
加载 完成后，进入excel界面，点击 power pivot——管理数据模型——关系图视图
a: newbill 的 shopname 连接到 shoptotal 的 店名
b: neworderdetail de shopname 连接到 shoptotal 的 店名
返回到 数据视图，在表 shoptotail 里，计算如下两个式子，目的是为了建立 门店各自销售金额与总的平均销售金额的KPI
选中 门店各自销售金额 ——创建kpi——度量值（门店总的平均销售金额）———修改数值——确定
接下来创建数据透视表，数据透视表下拉，选中数据透视表，创建如下数据透视表
在利用 复制，粘贴为图片连接到另一张工作表，如下：
再来创建一个数据透视表，这里以detail 分组，值为 pay 改名 销售额 和 billnumber 改名 销量
创建数据透视图：在newbill 表里，以paytime小时分组，值是pay和billnumber，选择组合图，billnumber 用折线图，添加第二坐标，我是比较喜欢黑色，整理如下：
再把上面的第二个数据透视表，做成饼状的数据透视图
最后插入 切片器，选着 shoptotal[店名]，连接所有的数据透视表 数据透视图
最后检验一下，在切片器里点击 北新桥店， 结果如下：
在点击 亚运村店， 结果如下：
