Excel是數(shù)據(jù)分析中最常用的工具，本篇文章通過(guò)mysql與excel的功能對(duì)比介紹如何使用mysql完成excel中的數(shù)據(jù)生成，數(shù)據(jù)清洗，預(yù)處理，以及最常見(jiàn)的數(shù)據(jù)分類，數(shù)據(jù)篩選，分類匯總，以及數(shù)據(jù)透視等操作。本篇文章我們介紹第5,6,7部分內(nèi)容，數(shù)據(jù)提取，數(shù)據(jù)篩選以及數(shù)據(jù)匯總及透視。
5，數(shù)據(jù)提取
第五部分是數(shù)據(jù)提取，也是數(shù)據(jù)分析中最常見(jiàn)的一個(gè)工作。下面介紹每一種函數(shù)的使用方法。
按列提取數(shù)據(jù)
#按列提取 SELECT city FROM data1;
按行提取數(shù)據(jù)
#按行提取SELECT * FROM data1 WHERE city='beijing';
按位置提取數(shù)據(jù)
#按位置提取SELECT * FROM data1 LIMIT 2,5;
按條件提取數(shù)據(jù)
#按條件提取并計(jì)算SELECT AVG(price) FROM data1 WHERE city='beijing' AND age<25;
6，數(shù)據(jù)篩選
第六部分為數(shù)據(jù)篩選，使用與，或，非三個(gè)條件配合大于，小于和等于對(duì)數(shù)據(jù)進(jìn)行篩選，并進(jìn)行計(jì)數(shù)和求和。與excel中的篩選功能和countifs和sumifs功能相似。
按條件篩選（與，或，非）
Excel數(shù)據(jù)目錄下提供了“篩選”功能，用于對(duì)數(shù)據(jù)表按不同的條件進(jìn)行篩選。mysql中使用WHERE完成篩選操作，配合sum和count函數(shù)還能實(shí)現(xiàn)excel中sumif和countif函數(shù)的功能。
#數(shù)據(jù)篩選ANDSELECT * FROM data1 WHERE city='shanghai' AND age>30;
#數(shù)據(jù)篩選INSELECT * FROM data1 WHERE city IN ('shanghai','beijing'); 
#數(shù)據(jù)篩選ORSELECT * FROM data1 WHERE city='shanghai' OR age>30;
#數(shù)據(jù)篩選(不等于)SELECT * FROM data1 WHERE city !='beijing';
#數(shù)據(jù)篩選like(模糊篩選)SELECT * FROM data1 WHERE city LIKE 'bei%';
#篩選后計(jì)數(shù) countifSELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;
#篩選后求和 sumtifSELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age<30;
#篩選后求均值 averageifSELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';
7，數(shù)據(jù)分類匯總及透視
第七部分是對(duì)數(shù)據(jù)進(jìn)行分類匯總，Excel中使用分類匯總和數(shù)據(jù)透視可以按特定維度對(duì)數(shù)據(jù)進(jìn)行匯總，mysql中使用的主要函數(shù)是GROUP BY和CASE WHEN。下面分別介紹這兩個(gè)函數(shù)的使用方法。
分類匯總
Excel的數(shù)據(jù)目錄下提供了“分類匯總”功能，可以按指定的字段和匯總方式對(duì)數(shù)據(jù)表進(jìn)行匯總。mysql中通過(guò)GROUP BY完成相應(yīng)的操作，并可以支持多級(jí)分類匯總。
GROUP BY是進(jìn)行分類匯總的函數(shù)，使用方法很簡(jiǎn)單，制定要分組的列名稱就可以，也可以同時(shí)制定多個(gè)列名稱，GROUP BY按列名稱出現(xiàn)的順序進(jìn)行分組。同時(shí)要制定分組后的匯總方式，常見(jiàn)的是計(jì)數(shù)和求和兩種。
#單列分類匯總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;
數(shù)據(jù)透視
Excel中的插入目錄下提供“數(shù)據(jù)透視表”功能對(duì)數(shù)據(jù)表按特定維度進(jìn)行匯總。mysql中沒(méi)有直接提供數(shù)據(jù)透視表功能。但通過(guò)CASE WHEN函數(shù)實(shí)現(xiàn)同樣的效果。
數(shù)據(jù)透視表也是常用的一種數(shù)據(jù)分類匯總方式，并且功能上比GROUP BY要強(qiáng)大一些。下面的代碼中設(shè)定city為行字段，colour為列字段，price為值字段，計(jì)算price金額。
#查看原始數(shù)據(jù)表SELECT * FROM data1;
#使用CASE WHEN進(jìn)行數(shù)據(jù)透視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);
#查看結(jié)果SELECT * FROM data_Items;
#對(duì)字段進(jìn)行求和匯總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);
#查看結(jié)果SELECT * FROM data1_Extended_Pivot;
#對(duì)空值進(jìn)行處理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);
#查看數(shù)據(jù)透視結(jié)果SELECT * FROM data1_Extended_Pivot_Pretty;
新聞熱點(diǎn)
疑難解答
圖片精選
