餐饮汇总表mysql_基于某餐饮数据的mysql+powerBI综合案列

最新推荐文章于 2022-06-25 17:21:27 发布

沂樾

最新推荐文章于 2022-06-25 17:21:27 发布

阅读量521

点赞数

文章标签：餐饮汇总表mysql

本文链接：https://blog.csdn.net/weixin_29324877/article/details/113160823

版权

一、关于本次案列的概述

相关资料与结果：

本次案列的数据是来自某餐饮数据的日销售情况，基于已经提供的数据，需要在excel中做一个各个店面的分析仪，其KPI 指标相关如下：

该问题的难点还是在于表之间的逻辑关系，以及相关业务知识。总之大致的效果要做成如下：

数据表一共有三张，包括：bill orderdetail shopdetail

他们的表结构如下：

而我们的目的是获得不同店面的数据情况，上面的三张表是不能满足这样情况的，所以我选择先在MySQL中进行数据加工，再用Excel中利用power pivot生成数据透视表和数据透视图，

当然还有其他一些操作，整体动态图的展现等。

二、数据预处理 —基于MySQL5.7 +workbench 6.3

(1)建表与数据导入

在MySQL中先建立库 MySQL_powerBI

create databasemysql_powerbi;usemysql_powerbi;--Bill table

create tableBill(

billdate datenot null,

billnumbervarchar(20) not null default '-',

shopnamevarchar(20) not null default '-',

billdiscountfloat not null default 0,

paytime timenot null,

tablenumberint not null default 0,

peoplecountint not null default 0);

#导入数据load data local infile 'D:/mysql_powerBI/data/-bill.csv'

into tableBill

fields terminatedby ',';select * from Bill;

表 Bill 的情况如下图，一共有 682 行

--OrderDetail table

create tableOrderDetail(

billnumbervarchar(20) not null default '-',

detailvarchar(20) not null default '-',

payint not null default 0);

#导入数据load data local infile 'D:/mysql_powerBI/data/-order.csv'

into tableOrderDetail

fields terminatedby ',';select * from OrderDetail;

表 OrderDetail 共有 3410 行，如下：

--ShopDetail table

create tableShopDetail(

ShopNamevarchar(20) not null default '-',

twotableint not null default 0,

threetableint not null default 0,

fourtableint not null default 0,

alltableint not null default 0);

#导入数据load data local infile 'D:/mysql_powerBI/data/-shop.csv'

into tableShopDetail

fields terminatedby ',';select * from ShopDetail;

表 ShopDetail 共有 5 行，如下：

在确保上面的步骤无误后，接下来进入数据加工阶段，得到我们想要的数据

(2)数据加工

在进行数据加工之前必须要非常清楚的理解各表结构信息，不然根本没有办法做！一般来说，我都会画一个表之间的连接关系来帮助自己进行数据处理，看看每个表是怎么来的

(a) 用orderdetail表创建单汇总金额表(OrderGroup)

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

新表字段：billnumber(单号)、pay(金额)

--数据加工--创建单汇总金额表

Create tableOrderGroup(select billnumber, sum(pay) as pay fromOrderDetailgroup bybillnumber

);select * from OrderGroup;

结果共682行，如下：

(b)用Bill表与OrderGroup表创建新单号详细表(NewBill)

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

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

计算逻辑：Rebate = pay * billdiscount

--创建新单号详细表

Create tableNewBill(select b.*,o.pay,b.billdiscount * o.pay as rebate from bill as b left join ordergroup asoon b.billnumber =o.billnumber);select * from NewBill;

结果共682行，如下：

(c)用Shopdetail表创建新店面情况表(NewShopDetail)

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

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

计算逻辑：allseats = twotable * 2 + three * 3 + fourtable * 6 (这里四人桌(含)都乘以6，你可以理解为过道也可以坐人....)

--创建新店面情况表

create tableNewShopDetail(select *, (twotable * 2 + threetable * 3 + fourtable * 6) asallseatsfrom shopdetail ass);select * from NewShopDetail;

结果共有5行，如下：

(d)用OrderDetail表与Bill表创建新点菜明细表(NewOrderDetail)

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

新表字段：shopname(店名)、OrderDetail表中的所有字段

--创建新点菜明细表

create tableneworderdetail(select b.shopname,o.* from orderdetail as o left join bill asbon 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 tableShopTotal(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.alltableas总台数,

s.allseatsas总座位数,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 asson b.shopname =s.shopnamegroup byb.shopname);select * from shoptotal;

结果共 5 行，完整如下：