1、创建数据库以及表结构

查询语句；

在表创建完成之后记得检查表，排除在导入数据时因为数据表的构建的原因

create database cy;
use cy;-- 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'
) charset="utf8";-- orderdetail table
create table orderdetail(
billnumber varchar(20) not null,
dateil varchar(20) not null,
pay int not null default '0'
) charset="utf8";-- shopdetail table
create table shopdetail(
shopname varchar(20) not null,
twotable int not null default '0',
threetable int not null default '0',
fourtable int not null default '0',
alltable int not null default '0'
) charset="utf8";-- 文件路径非中文
load data local infile "文件路径"
into table bill -- 表名
fields terminated by ","-- 创建单汇总金额表
create table ordergroup(
select billnumber,sum(pay) as pay
from orderdetail
group by billnumber);-- 创建新单号信息表
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);-- 创建新店面情况表
create table newshopdetail(
select shopdetail.*,(twotable * 2 + threetable * 3 + fourtable * 4) allseats
from shopdetail);-- 创建创建新点菜明细表
create table neworderdetail(
select shopname,orderdetail.*
from orderdetail 
left join bill on orderdetail.billnumber=bill.billnumber);-- 创建店汇总信息表
create table shoptotal(
select newbill.shopname as 店名,
count(newbill.billnumber) as 单数,
sum(newbill.peoplecount) as 人数,
sum(newbill.rebate) as 折扣总金额,
sum(newbill.pay) 店汇总金额,
sum(newbill.pay)/count(newbill.billnumber) 单均消费,
sum(newbill.pay)/sum(newbill.peoplecount) 人均消费,
newshopdetail.alltable 总台数,
newshopdetail.allseats 总座位数,
count(newbill.billnumber)/newshopdetail.alltable 翻台率,
sum(newbill.peoplecount)/newshopdetail.allseats 上座率,
sum(newbill.rebate)/sum(newbill.pay) 折扣率
from newbill
left join newshopdetail on newbill.shopname=newshopdetail.shopname
group by newbill.shopname);

2、向数据库导入数据

执行此代码需要做一些准备工作：

①安装pandas包；pandas在进行数据的导入时具有更快的速度和效率

②安装pymysql；连接MYSQL数据库的第三方包

代码注意事项：

①//后面跟的是用户名，：后面是用户名所对应的密码@后面的是IP地址以及端口号，再后面就是数据库名称

②读入文件的地址；此处使用的是相对地址，因为所需要的文件在当前目录的data下面，如果不在可以使用绝对路径

数据导入完成后记得检查数据导入是否正确，排除在之后的过程中因为数据库的数据的原因

import pandas as pd
from sqlalchemy import create_engine
# pip install pymysqlengine = create_engine('mysql+pymysql://root:123456@localhost:3306/cy?charset=utf8')bill = pd.read_csv('./data/bill.csv')
orderdetail = pd.read_csv('./data/order.csv')
shopdetail = pd.read_csv('./data/shop.csv')bill.to_sql('bill', engine, if_exists='replace', index=False)
orderdetail.to_sql('orderdetail', engine, if_exists='replace', index=False)
shopdetail.to_sql('shopdetail', engine, if_exists='replace', index=False)

3、从数据库中提取数据

在将数据库的数据导入到excel中可能会遇到两个问题

①从来自数据库的选项中有连接MYSQL的选项，但是连接不上；可能是需要安装插件

参考此网址：Excel Power Query连接MySQL数据库组件下载安装 - 知乎一、插件下载下载地址 https://dev.mysql.com/downloads/connector/net/1.1直接点击Download 1.2选择No thanks just start my download直接下载 1.3下载到桌面二、安装 2.1双击安装 2.2 next 下一步 2.3选择Typi…https://zhuanlan.zhihu.com/p/435906559

②从来自数据库的选项中没有连接MYSQL的选项，此时只能通过ODBC去连接MYSQL，此时需要去安装一个驱动；

参考网址：使用Excel 通过 ODBC 连接到 MySQL 数据库使用Excel 通过 ODBC 连接到 MySQL 数据库http://www.360doc.com/content/21/0712/19/62685306_986266766.shtml

4、数据清洗

上一步的转换数据之后进入到的是PowerQuery；

PowerQuery擅长处理的就是数据清洗，例如改变字段的格式，去除空行，错误

数据清洗完之后就上载到表中，然后再加载到擅长数据分析、数据建模的PowerPivot中

5、PowerPivot

将数据加载到PowerPviot中之后除了要检查数据之外，最先要做的事情就是建立表与表之间的关系，也就是1对多的关系中的谁是多谁是1；

在PowerPviot的右下角有两个按钮，一个是进入到关系视图，一个是在表视图

         事情的关键在于如何去建立关系，以及确定谁是1谁是多，因为在进行建模的时候会涉及到聚合运算；

聚合运算分为以下几种情况：

        1、以一表的字段分组，对多表的字段进行聚合计算（可行）

                2、以多表的字段为分组，对一表的字段进行聚合计算：（不可行）

                3、以一表的字段为分组，对一表的字段进行聚合计算，两者为同一张表：（可行）

                4、以多表的字段为分组，对多表的字段进行聚合计算，两者为同一张表：（可行）