Excel是數據分析中最常用的工具，本篇文章通過mysql與excel的功能對比介紹如何使用mysql完成excel中的數據生成，數據清洗，預處理，以及最常見的數據分類，數據篩選，分類匯總，以及數據透視等操作。本篇文章我們介紹第5,6,7部分內容，數據提取，數據篩選以及數據匯總及透視。本文主要介紹了如何使用mysql完成excel中的數據生成的相關資料,需要的朋友可以參考下。

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;

大家在開發過程中可能會經常遇到，現在就跟著本文嘗試操作一下吧。

相關推薦：

PHP導出EXCEL快速開發指南

Python操作excel文件的案例

php使用原生的方法導出excel實例分享

以上就是如何使用mysql完成excel中的數據生成的詳細內容，更多請關注php中文網其它相關文章！