走在风中今天阳光 突然好温柔 天的温柔地的温柔 像你抱着我 …… 1. 什么是聚合函数? 在数据分析过程中,我们经常需要对一组数据进行计算,求其最大值、最小值、平均值、总和和数目等,这种行为我们称之为聚合分析;而用来实现聚合分析的函数,我们称之为聚合函数。 在SQL中,常用的聚合函数有SUM(总和)、MAX(最大值),MIN(最小值),AVG(平均值)以及COUNT(计数)。 我们之前也分享过几个函数,例如IIF,INSTR等。那么,聚合函数和这些函数的不同之处是什么呢? 简单说,聚合函数是对一组数据(一列或多列)进行处理,返回单个结果;而普通函数则通常是对一组数据中的每条记录进行处理,返回的是一组结果…… 2. 举个小栗子。 上图所示,是某校学生期末考试成绩表。 语文最高分: SELECT MAX(语文) AS 语文最高分 FROM [成绩表$] 数学最低分: SELECT MIN(数学) AS 数学最低分 FROM [成绩表$] 四舍五入后的英语平均分: SELECT ROUND(AVG(英语),2) AS 英语平均分 FROM [成绩表$] 英语、语文、数学各科成绩的分别总值: SELECT SUM(语文) AS 语文总分,SUM(数学) AS 数学总分,SUM(英语) AS 英语总分 FROM [成绩表$] 总分最高分: SELECT MAX(语文 数学 英语) AS 总分最高分 FROM [成绩表$] 3. 重点说下聚合函数COUNT(计数),这位比较有意思。 COUNT(*)和COUNT(字段) COUNT(*)可以返回指定表的记录总行数(默认情况下不包含标题行),需要说明的重点是,对于Excel而言,它不会忽略空白行;对于数据库而言,它不会忽略整行NULL值的记录。 例如以下语句,计算结果为8,非7,计算结果包含了第5行的空白行。 SELECT COUNT(*) AS 学生人数 FROM [成绩表$] 但当COUNT用于计算指定字段时,它会忽略Excel的空白值以及数据库的NULL。 例如以下语句,计算结果为7,忽略了第5行的空白行。 SELECT COUNT(姓名) AS 学生人数 FROM [成绩表$] 小贴士: 在SQL in Excel中,尽量不要使用COUNT(*)语句,除非已进行了条件筛选,例如使用WHERE子句等。 这是因为在Excel中,SQL默认读取的是整张表格的UsedRange区域(可以简单理解成表格内已使用的单元格所构成的最大矩形区域)。例如上例中的 SELECT * FROM [成绩表$] 读取的是已使用的单元格区域A1:E9,而并非整张表格——但当F14单元格有被使用过时,哪怕只是填充了背景色,SQL读取的区域也会演变成A1:F14…………这常常就会产生糟糕的结果…… 条件计数 聚合函数搭配WHERE子句等可以实现多种条件统计。 例如以下语句可以计算语文成绩及格的人数: SELECT COUNT(*) AS 语文及格人数 FROM [成绩表$] WHERE 语文>=60 更多关于WHERE子句的应用(单条件、多条件、模糊条件等)参考前文: Excel VBA ADO SQL入门教程007:条件查询(上) Excel VBA ADO SQL入门教程008:条件查询(下) …… 这里需要说明的是逻辑值(TRUE和FALSE)在SQL中的一种应用。 例如,以下语句可以计算三科成绩均及格的人数: SELECT COUNT(*) AS 三科均及格人数 FROM [成绩表$] WHERE 语文>=60 AND 数学>=60 AND 英语>=60 但上述SQL语句也可以写成: SELECT COUNT(*) AS 三科均及格人数 FROM [成绩表$] WHERE (语文>=60) (数学>=60) (英语>=60)=-3 负3是什么鬼? 我们知道在EXCEL中,当进行数学运算时,逻辑值TRUE等于1,FALSE等于0,但在SQL In Excel 中并非如此。 在SQL中,逻辑值FALSE依然等于0,但TRUE则等于-1。当三个条件均为真时,也就是-1 -1 -1,其结果必然等于-3,以此即可判断三条件是否均成立。 知道这个知识点有什么用处? 例如下面这个问题: 计算三科成绩中至少有两科及格的人数。 使用逻辑值运算技巧,如下即可: SELECT COUNT(*) AS 至少两科及格人数 FROM [成绩表$] WHERE (语文>=60) (数学>=60) (英语>=60)<=-2 计算三科成绩中至少有两科及格的学生名单。 SELECT * FROM [成绩表$] WHERE (语文>=60) (数学>=60) (英语>=60)<=-2 …… …… 图文作者:看见星光 |
|