乘风原创程序

  • 通过mysql实现excel中的数据生成
  • 2020/5/13 14:16:57
  • 下面一起来了解下通过mysql实现excel中的数据生成,相信大家看完肯定会受益匪浅,文字在精不在多,希望通过mysql实现excel中的数据生成这篇短内容是你想要的。                                                      

    5,数据提取

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

    按列提取数据

    #按列提取 SELECT city FROM data1;

    通过mysql实现excel中的数据生成

    按行提取数据

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

    通过mysql实现excel中的数据生成

    按位置提取数据

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

    通过mysql实现excel中的数据生成

    按条件提取数据

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

    通过mysql实现excel中的数据生成

    6,数据筛选

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

    按条件筛选(与,或,非)

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

    通过mysql实现excel中的数据生成

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

    通过mysql实现excel中的数据生成

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

    通过mysql实现excel中的数据生成

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

    通过mysql实现excel中的数据生成

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

    通过mysql实现excel中的数据生成


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

    通过mysql实现excel中的数据生成

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

    通过mysql实现excel中的数据生成

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

    通过mysql实现excel中的数据生成

    #筛选后求均值 averageif
    SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';

    通过mysql实现excel中的数据生成

    7,数据分类汇总及透视

    第七部分是对数据进行分类汇总,Excel中使用分类汇总和数据透视可以按特定维度对数据进行汇总,mysql中使用的主要函数是GROUP BY和CASE WHEN。下面分别介绍这两个函数的使用方法。

    分类汇总

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

    通过mysql实现excel中的数据生成

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

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

    通过mysql实现excel中的数据生成

    #多列分类汇总
    SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;

    通过mysql实现excel中的数据生成

    数据透视

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

    通过mysql实现excel中的数据生成

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


    #查看原始数据表
    SELECT * FROM data1;

    通过mysql实现excel中的数据生成


    #使用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;

    通过mysql实现excel中的数据生成


    #对字段进行求和汇总
    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;

    通过mysql实现excel中的数据生成


    #对空值进行处理
    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;

    通过mysql实现excel中的数据生成

    看完通过mysql实现excel中的数据生成这篇文章后,很多读者朋友肯定会想要了解更多的相关内容,如需获取更多的行业信息,可以关注我们的行业资讯栏目。