SQL知识梳理（五）常用函数（聚合、条件、窗口）

一、聚合函数

1、 用处

聚合函数常和分组计算group by语句结合使用，对数据分组后执行计算并为每组返回唯一值。

2、 常用的聚合函数

COUNT()，SUM()，AVG()，MIN()，MAX()。
COUNT()：计数，返回每组的行数,也会返回有NULL值的列，可用于数字和字符列。
SUM()：返回每组数值的总和，忽略NULL值，仅用于数字列。
AVG()：返回每组数值的平均值，忽略NULL值，仅用于数字列。
MIN()：返回每组数据的最小值，忽略NULL值，可用于数字、字符和日期时间列。
MAX()：返回每组数据的最大值，忽略NULL值，可用于数字、字符和日期时间列。

3、注意

聚合函数不能用在where语句中，需要用在having语句中进行过滤。
原因是SQL语句执行顺序为from 、on 、join 、where 、group by(开始使用select中的别名，后面的语句中都可以使用)、 聚合函数… 、having 、select 、distinct 、order by、limit，
聚合函数是在分组之后进行计算，而分组是where语句过滤完数据后才进行分组，因此如果在where语句中使用聚合函数，还未分组无法进行统计计算，程序会报错。

二、 条件判断-case when

1、用处

利用现有字段，根据条件语句，生成新字段。

2、语法

①
case id when 0 then '学生'
       when 1 then '学生'
when2 then '老师'
	else '其他'
end as identification

②
Case when id in (0,1) then '学生'
When id = 2 then '老师'
Else '其他'
end as identification

注意：end关键词不可省

3、使用场景

① case when可用于select后 生成新字段:

select id, quantity
case when quantity >30 then 1
when quantity = 30 then 2
else 3
end as QuantityText
from orders;

② case when可以写在分组group by后按新字段分组，注意case when用在group by后不可以使用字段别名，即到end关键字结束，无as new_colname。
③ case when可以用在聚合函数中。比如统计每个学生考试通过的学科数，可写为：

select
id，
name,
count(distinct case when score>= 60 then subject end) as total_pass_subject
from scores
	where date>=’2022-01-01’
	group by id,name;

④ case when可以写在order by后

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

说明：将用户按城市排序，如果城市为空按国家排序。

三.条件判断-if函数

1、用处

根据条件判断，更改原始列数据或者新增列。if比case when执行用时更短一点。

2、语法

If（expression，v1,v2） 表达式为真，返回v1, 否则返回v2
例：
① id不是偶数且姓名不以M开头的职员bonus等于其薪水，否则为0：

select employee_id,
if(employee_id % 2 != 0 and left(name,1) != 'M',salary,0) as bonus
from Employees
order by employee_id；

② 将性别列的所有m和f对调：

set sex = if(sex='m','f','m');

四、窗口函数

1、用处

窗口函数与聚合函数类似，它也会对数据分组之后进行计算，但它不是为每组返回一个值，而是会为分组中的每条记录返回特定值。
窗口函数只能用在select 语句中。它不会用在group by,也不会用在聚合函数中。
窗口函数可以减少表连接。

2、语法：

函数名（）over (partition by col1,col2 order by col3 desc/asc, col4 desc/asc) as new_col

例，统计2022年每个用户最近一次登录的记录：

select id,
	name,
	login_time
from (select id,
		name,
		login_time
	row_number() over (partition by id,name order by login_time desc) as rank
	from user_login
	where login_time >= ‘2022-01-01’
）t
where rank = 1;

partition by 表示对所有记录按照用户的id和name进行分组，具有相同id和name的每组用户的记录按登录时间login_time降序排列，最新的记录会排在最前面，并通过窗口函数rou_number()over为同组所有记录返回排序值，最新的记录返回1，次新的记录返回2…最后在外层查询中使用where rank = 1，即得到所有用户最近一次登录的记录。

3、 常见窗口函数

4、MySQL排序窗口函数的区别

ROW_NUMBER()：按照顺序进行排序（1、2、3…）
RANK()：并列排序，会跳过重复的序号（1、1、3…）
DENSE_RANK()：并列排序，不会跳过重复的序号（1、1、2…）