如何使用mysql完成excel中的数据生成

excel是数据分析中最常用的工具，本篇文章通过mysql与excel的功能对比介绍如何使用mysql完成excel中的数据生成，数据清洗，预处理，以及最常见的数据分类，数据筛选，分类汇总，以及数据透视等操作。本篇文章我们介绍第5,6,7部分内容，数据提取，数据筛选以及数据汇总及透视。

5，数据提取

第五部分是数据提取，也是数据分析中最常见的一个工作。下面介绍每一种函数的使用方法。

按列提取数据

#按列提取 select city from data1;

按行提取数据

#按行提取
select * from data1 where city='beijing';

按位置提取数据

#按位置提取
select * from data1 limit 2,5;

按条件提取数据

#按条件提取并计算
select avg(price) from data1 where city='beijing' and age<25;

6，数据筛选

第六部分为数据筛选，使用与，或，非三个条件配合大于，小于和等于对数据进行筛选，并进行计数和求和。与excel中的筛选功能和countifs和sumifs功能相似。

按条件筛选（与，或，非）

excel数据目录下提供了“筛选”功能，用于对数据表按不同的条件进行筛选。mysql中使用where完成筛选操作，配合sum和count函数还能实现excel中sumif和countif函数的功能。

#数据筛选and
select * from data1 where city='shanghai' and age>30;

#数据筛选in
select * from data1 where city in ('shanghai','beijing');

#数据筛选or
select * from data1 where city='shanghai' or age>30;

#数据筛选(不等于)
select * from data1 where city !='beijing';

#数据筛选like(模糊筛选)
select * from data1 where city like 'bei%';

#筛选后计数 countif
select count(id) as id_count from data1 where city='shanghai'and age>30;

#筛选后求和 sumtif
select sum(price) as price from data1 where city='beijing' and age<30;

#筛选后求均值 averageif
select avg(price) as avg_price from data1 where city !='beijing';

7，数据分类汇总及透视

第七部分是对数据进行分类汇总，excel中使用分类汇总和数据透视可以按特定维度对数据进行汇总，mysql中使用的主要函数是group by和case when。下面分别介绍这两个函数的使用方法。

分类汇总

excel的数据目录下提供了“分类汇总”功能，可以按指定的字段和汇总方式对数据表进行汇总。mysql中通过group by完成相应的操作，并可以支持多级分类汇总。

group by是进行分类汇总的函数，使用方法很简单，制定要分组的列名称就可以，也可以同时制定多个列名称，group by按列名称出现的顺序进行分组。同时要制定分组后的汇总方式，常见的是计数和求和两种。

#单列分类汇总
select city,count(id) as id_count from data1 group by city order by id_count;

#多列分类汇总
select city,colour,round(sum(price),2) as id_count from data1 group by city,colour;

数据透视

excel中的插入目录下提供“数据透视表”功能对数据表按特定维度进行汇总。mysql中没有直接提供数据透视表功能。但通过case when函数实现同样的效果。

数据透视表也是常用的一种数据分类汇总方式，并且功能上比group by要强大一些。下面的代码中设定city为行字段，colour为列字段，price为值字段，计算price金额。

#查看原始数据表
select * from data1;

#使用case when进行数据透视
create view data_items as (
 select
 data1.city,
 case when colour = "a" then price end as a,
 case when colour = "b" then price end as b,
 case when colour = "c" then price end as c,
 case when colour = "f" then price end as f
 from data1
);

#查看结果
select * from data_items;

#对字段进行求和汇总
create view data1_extended_pivot as (
 select
 city,
 sum(a) as a,
 sum(b) as b,
 sum(c) as c,
 sum(f) as f
 from data_items
 group by city
);

#查看结果
select * from data1_extended_pivot;

#对空值进行处理
create view data1_extended_pivot_pretty as (
 select 
 city, 
 coalesce(a, 0) as a, 
 coalesce(b, 0) as b, 
 coalesce(c, 0) as c,
 coalesce(f, 0) as f
 from data1_extended_pivot
);

#查看数据透视结果
select * from data1_extended_pivot_pretty;