这里介绍SQL中的聚合函数Aggregate Function

概述

聚合函数是多对一函数。它们使用多个记录的值作为输入、计算、转换返回为单个值。聚合函数通常与GROUP BY子句一起使用，以对数据进行分组并对每个组下的若干条记录应用聚合函数。这样可以在一个查询中得到每个组汇总的统计结果。这里建立一张表，以供后续测试

CREATE TABLE `stu_info` (
    `id` int NOT NULL COMMENT '主键',
    `name` varchar(255) DEFAULT NULL COMMENT '姓名',
    `age` int DEFAULT NULL COMMENT '年龄',
    `sex` varchar (255) DEFAULT NULL COMMENT '性别',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='学生信息表';

INSERT INTO stu_info (id, name, age, sex) VALUES(1, 'Tony', 18, '男');
INSERT INTO stu_info (id, name, age, sex) VALUES(2, 'Bob', 11, '男');
INSERT INTO stu_info (id, name, age, sex) VALUES(3, 'Amy', 22, '女');
INSERT INTO stu_info (id, name, age, sex) VALUES(4, 'Tom', 3, '男');
INSERT INTO stu_info (id, name, age, sex) VALUES(5, 'Aaron', 14,'女');
INSERT INTO stu_info (id, name, age, sex) VALUES(6, 'David',9,'男');

常用聚合函数

sum函数

用于计算指定列的数值之和

1	SELECT SUM(age) as my_total_age_1 FROM stu_info;

GROUP BY 子句

对于用于分组的列，例如下面的sex列。由于在GROUP BY子句中作为分组依据列。故可以在SELECT子句中直接引用
对于不是用于分组的列，不可以SELECT子句中直接引用。例如下面的age列。需要使用聚合函数对相应分组下所有记录的该列数据进行汇总后，才可以在SELECT子句中使用

1	SELECT sex, SUM(age) as my_total_age_2 FROM stu_info GROUP BY sex;

count函数

用于计算行或指定列的记录总数

1
2
3

SELECT COUNT(*) as my_count_1 FROM stu_info;
SELECT COUNT(name) as my_count_2 FROM stu_info;
SELECT sex, COUNT(*) as my_count_3 FROM stu_info GROUP BY sex;

avg函数

用于计算指定列的平均值

1 2	SELECT AVG(age) as my_avg_age_1 FROM stu_info; SELECT sex, AVG(age) as my_avg_age_2 FROM stu_info GROUP BY sex;

max函数

用于计算指定列的最大值

1 2	SELECT MAX(age) as my_max_age_1 FROM stu_info; SELECT sex, MAX(age) as my_max_age_2 FROM stu_info GROUP BY sex;

min函数

用于计算指定列的最小值

1 2	SELECT MIN(age) as my_min_age_1 FROM stu_info; SELECT sex, MIN(age) as my_min_age_2 FROM stu_info GROUP BY sex;

HAVING 子句

HAVING子句需要在有GROUP BY子句的条件下オ可以使用。在HAVING子句中可使用聚合函数对分组后的各组数据进行过滤。其WHERE子句的不同点在于，WHERE子句是对原始的数据进行过滤

-- 先按sex性别进行分组，再统计各分组对平均年龄
SELECT sex, AVG(age) as avg_age_1 FROM stu_info
    GROUP BY sex;

-- 先按sex性别进行分组，然后筛选出记录总数超过3条的分组，最后计算筛选后的各分组平均年龄
SELECT sex, AVG(age) as avg_age_2 FROM stu_info
    GROUP BY sex HAVING COUNT(name)>3;

-- 先按sex性别进行分组，然后筛选出年龄范围在[12，18]内的分组，最后计算筛选后各分组的最大id值
SELECT sex, MAX(id) as max_id FROM stu_info
    GROUP BY sex
    HAVING MIN(age)>=2 and MAX(age)<=18;

xyZGHio

SQL之聚合函数Aggregate Function

概述