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<p></p><p><img alt="" src="https://img.php.cn/upload/article/000/054/025/81ed3b1991d8577a6eb51a76ad1f6c3b-4.jpg"></p><p>6，數據篩選</p><p>第六部分為數據篩選，使用與，或，非三個條件配合大于，小于和等于對數據進行篩選，并進行計數和求和。與excel中的篩選功能和countifs和sumifs功能相似。</p><p>按條件篩選（與，或，非）</p><p>Excel數據目錄下提供了“篩選”功能，用于對數據表按不同的條件進行篩選。mysql中使用WHERE完成篩選操作，配合sum和count函數還能實現excel中sumif和countif函數的功能。</p><p><img alt="" src="https://img.php.cn/upload/article/000/054/025/81ed3b1991d8577a6eb51a76ad1f6c3b-5.png"></p><p class="jb51code"></p><pre class="brush:sql;">#數據篩選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<p></p><p><img alt="" src="https://img.php.cn/upload/article/000/054/025/e859a0ba031d0006454764f78e44a485-12.jpg"></p><p class="jb51code"></p><pre class="brush:sql;">#篩選后求均值?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;
大家在開發過程中可能會經常遇到，現在就跟著本文嘗試操作一下吧。
相關推薦：
