（二）SQL 函数篇

SQL 函数

函数是指一段可以直接被另外一段程序调用的程序或代码。

• 字符串函数
• 数值函数
• 数据类型转换函数
• 日期函数
• 流程函数
• 窗口函数

1.字符串函数

常用函数：

示例：

-- 拼接
SELECT CONCAT('Hello',' ','World');-- 返回'Hello World'
SELECT CONCAT('Hello',null,'World');-- 返回null
-- concat函数返回连接参数s1、s2等产生的字符串
-- 任一参数为null时，则返回null

-- 查询首都和名称，其中首都需是国家名称的扩展，例如：答案中应该包括墨西哥城(Mexico City)，因为它比墨西哥(Mexico)更长，而不应该将卢森堡(Luxembourg)包括在内，因为首都名与国家名相同
select capital, name
from world
where capital like cancat('%',name,'%') and capital != name;

-- 左右返回
select left('abcdefg',3)-- 返回'abc'
select right('abcdefg',3)-- 返回'efg'
select substring('abcdefg',2,3)-- 返回'bcd'
select substring('abcdefg',-2,3)-- 返回'bfg'
select substring('abcdefg',2)-- 返回'bcdefg'
-- substring函数返回字符串s从第n个字符起取长度为len的子字符串
-- n也可以作为负值，则从倒数第n个字符起取长度为len的子字符
-- 没有len值则取从第n个字符起到最后一位

-- 替换
select replace('MYSQLMYSQL','SQL','sql')-- 返回'MYsqlMYsql'
-- 小写
SELECT LOWER('Hello');-- 返回'hello'
-- 大写
SELECT UPPER('Hello');-- 返回'HELLO'
-- 左填充
SELECT LPAD('01', 5, '-');-- 返回'---01'
-- 右填充
SELECT RPAD('01', 5, '-');-- 返回'01---'
-- 去除前后空格（不去除中间空格）
SELECT TRIM(' Hello World ');-- 返回'Hello World'
-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);-- 返回'Hello'

2.数值函数

常见函数：

示例：

round(3.15,1)
-- 返回3.2
round(14.15,-1)
-- 返回10
-- round函数对x值进行四舍五入，精确到小数点后y位
-- y为负值时，保留小数点左边相应的位数为0，不进行四舍五入

3.数据类型转换函数

常用 datatype：

示例：

SELECT CAST('123' AS INT);
-- 结果：123（字符串 '123' 被转换为整数 123）

SELECT CAST('2023-10-15' AS DATE);
-- 结果：2023-10-15（字符串 '2023-10-15' 被转换为日期类型）

SELECT CAST(NULL AS VARCHAR);
-- 结果：NULL（NULL 可以被转换为任何类型，结果仍然是 NULL）

SELECT CAST('{"name": "Alice", "age": 30}' AS JSON);
-- 结果：JSON 对象 {"name": "Alice", "age": 30}

SELECT CAST(1 AS BOOLEAN);
-- 结果：TRUE（1 被转换为 TRUE，0 被转换为 FALSE）

注意事项：

• ①数据类型兼容性：转换时需确保源数据与目标数据类型兼容，否则会报错。例如，CAST('ABC' AS INT) 会失败，因为 'ABC' 无法转换为整数；
• ②数据库差异：不同数据库对数据类型的支持可能略有不同。例如：
  • MySQL 使用 VARCHAR，而 PostgreSQL 使用 TEXT 或 VARCHAR。
  • SQL Server 使用 NVARCHAR 或 VARCHAR。
• ③性能影响：频繁使用 CAST 可能会影响查询性能，尤其是在大数据集上。

4.日期函数

常用函数：

示例：

-- 现在是 2024-06-19 02:00:19
select curdate()-- 返回'2024-06-19'
select curtime()-- 返回'02:00:19'
select now()-- 返回'2024-06-19 02:00:19'

-- 年月日
select year('2021-08-03')-- 返回'2021'
select month('2021-08-03')-- 返回'8'
select day('2021-08-03')-- 返回'3'

-- date_add
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);
select date_add('2024-06-19 02:00:19',interval 1 second)-- 返回'2024-06-19 02:00:20'

-- date_sub
select date_sub('2024-06-19 02:00:19',interval 2 month)-- 返回'2024-04-19 02:00:19'

-- datediff
select datediff('2021-06-08','2021-06-01')-- 返回'7'
select datediff('2021-06-08 23:59:59','2021-06-01 21:00:00')-- 返回'7'
select datediff('2021-06-01','2021-06-08')-- 返回'-7'
-- datediff函数由date1-date2计算出间隔的时间，只有date的日期部分参与计算，时间不参与

日期格式字符串：

常用的日期格式字符串：

示例：

select date_format('2024-06-19 02:00:19','%a, %d %b %Y %T')
-- 返回'Wed, 19 Jun 2024 02:00:19'
select date_format('2024-06-19 02:00:19','%W %D %M %Y %H:%i')
-- 返回'Wednesday 19th June 2024 02:00'

5.流程函数

常用函数：

示例：

-- 查询emp表的员工姓名和工作地址(北京|上海 → 一线城市，其他 → 二线城市)
select
	name,
	(case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'
from employee;

6.窗口函数

窗口函数功能：

• ①同时具有分组和排序的功能；
• ②不减少原表的行数。

基本语法：
<窗口函数> over ( [partition by <用于分组的列名>] [order by <用于排序的列名>] )
语法中 <窗口函数> 的位置，可以放以下两种函数：

• ①聚合函数，如sum, avg, count, max, min等。
• ②专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数；

函数：

• ①排名函数：根据 SQL 标准能够支持 4 种窗口函数用于排名计算。
  • ROW_NUMBER：根据指定的顺序，从1开始计算连续的行号；
  • NTILE：函数对一个数据分区中的有序结果集进行划分，将其分成数量大致相等的块，根据输入的块数和指定的窗口进行排序；
  • RANK 与 DENSE_RANK：计算一组数值中的排序值。
• ②分布函数：分布函数主要作用是为静态统计服务提供数据的分布情况。
  • 排名分布函数：PERCENT_RANK (百分位排名)和 CUME_DIST (累积分布)；
  • 逆分布函数：PERCENT_CONT (百分位连续)和 PERCENTILE_DISC (百分位离散)。
• ③偏移量函数：LAG 和 LEAD
  • LAG(col,n,DEFAULT)：用于统计窗口内往上第 n 行值；
  • LEAD(col,n,DEFAULT)：与 LAG 相反，用于统计窗口内往下第 n 行值。
• ④取值函数
  • FIRST_VALUE()：取分组内排序后，截止到当前行，第一个值；
  • LAST_VALUE()：取分组内排序后，截止到当前行，最后一个值。

万能模板：
select * from(select* rows number () over(partition by 分组 order by 排序 desc)as ranking from 表名) as a where ranking <=N;

注意事项：

• ① partition by 不会改变原表的行数；group by 会改变原表的行数；
• ②专用窗口函数里的 () 不需要任何参数，保持空着就行,反之聚合函数 () 不能为空；
• ③因为窗口函数是对 where 或者 group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在 select 子句中；
• ④ partition 子句可以省略，省略就是不指定分组，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响；
• ⑤ order by 子句也可以省略；
• ⑥传统的聚合、排序等函数都是基于全局整表的，窗口函数可以基于表中的每个细分部分。

示例：

-- 查询每一年S14000021选区中所有候选人所在的团体(party)和得票数(votes)，并对每一年中的所有候选人根据选票数的高低赋予名次，选票数最高则为1，第二名则为2，后续以此类推，最后根据团体(party)和年份(yr)排序
select yr, party, votes,
rank()over(partition by yr order by votes desc) as posn
from ge
where constituency = 'S14000021'
order by party, yr
-- ①where constituency='S14000021'从原表筛选出的数据
-- ②partition by yr依据yr对数据分区
-- ③order by votes desc“在依据yr的分区内，对votes降序排序”，通过rank()指定排序方法
-- ④order by party,yr依次依据party和yr对全表进行排序select选取显示字段
-- 1.窗口函数只能写在select字句中
-- 2.窗口函数中的partition by子句可以指定数据的分区，和group by要去重分组不同的是，partitionby只分区不去重
-- 3.窗口函数中没有partition by子句时，即不对数据分区，直接整个表为一个区
-- 4.排序窗口函数中order by子句是必选顶，窗口函数中order by子句在分区内，依据指定字段和排序方法对数据行排序
-- 5.rank()、dense_rank()、row_number()指定排序赋值方法，对比三个排序窗口函数的异同

select name, date_format(whn,'%Y-%m-%d') date,
confirmed as 当天截至时间累计确诊人数, lag(confirmed,1)over(partition by name order by whn) as 昨天截至时间累计确诊人数,
(confirmed -lag(confirmed,1)over(partition by name order by whn))每天新增确诊人数
from covid
where name in ('France','Germany') and month(whn) = 1
order by whn
-- ①partition by name对筛选后的数据依据name分区
-- ②order by whn在分区内依据whn排序
-- ③lag(confirmed,1)指定偏移字段confirmed，偏移量为1，该列数据就会在confirmed列每行向上取1行，confirmed -lag(confirmed,1)原字段减去偏移后的字段得到新增人数
-- ④order by whn对全表依据whn升序排序
-- ⑤在排序后提取要显示的字段
-- 1.偏移分析窗口函数中order by子句是必选顶
-- 2.lag()和lead()指定偏移的方向，lag是向上偏移，行向上取数据;lead是向下偏移，行向下取数据
-- 3.lag(字段名,偏移量[,默认值])over()还有一个参数为默认值，是可选项，在分区中没有前一行的情况下填充默认值，不填的情况下默认是null

参考资料

[1] 黑马程序员 MySQL数据库入门到精通
[2] 数据库窗口函数总结_数据库 每个数据的窗口计算-CSDN博客