如何使用mysql完成excel中的数据生成-木庄网络博客

本文摘自PHP中文网，作者小云云，侵删。

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

5，数据提取

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

按列提取数据

#按列提取 SELECT city FROM data1;

按行提取数据

1 2	`#按行提取` `SELECT` `*` `FROM` `data1` `WHERE` `city='beijing';`

按位置提取数据

1 2	`#按位置提取` `SELECT` `*` `FROM` `data1 LIMIT 2,5;`

按条件提取数据

1 2	`#按条件提取并计算` `SELECT` `AVG(price)` `FROM` `data1` `WHERE` `city='beijing'` `AND` `age<25;`

6，数据筛选

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

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

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

1 2	`#数据筛选AND` `SELECT` `*` `FROM` `data1` `WHERE` `city='shanghai'` `AND` `age>30;`

1 2	`#数据筛选IN` `SELECT` `*` `FROM` `data1` `WHERE` `city` `IN` `('shanghai','beijing');`

1 2	`#数据筛选OR` `SELECT` `*` `FROM` `data1` `WHERE` `city='shanghai'` `OR` `age>30;`

1 2	`#数据筛选(不等于)` `SELECT` `*` `FROM` `data1` `WHERE` `city !='beijing';`

1 2	`#数据筛选like(模糊筛选)` `SELECT` `*` `FROM` `data1` `WHERE` `city` `LIKE` `'bei%';`

1 2	`#筛选后计数 countif` `SELECT` `COUNT(id)` `AS` `id_count` `FROM` `data1` `WHERE` `city='shanghai'AND` `age>30;`

1 2	`#筛选后求和 sumtif` `SELECT` `SUM(price)` `AS` `price` `FROM` `data1` `WHERE` `city='beijing'` `AND` `age<30;`

1 2	`#筛选后求均值 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按列名称出现的顺序进行分组。同时要制定分组后的汇总方式，常见的是计数和求和两种。

1 2	`#单列分类汇总` `SELECT` `city,COUNT(id)` `AS` `id_count` `FROM` `data1` `GROUP` `BY` `city` `ORDER` `BY` `id_count;`

1 2	`#多列分类汇总` `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金额。

1 2	`#查看原始数据表` `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
);

1 2	`#查看结果` `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
);

1 2	`#查看结果` `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
);