# 语法-函数

# 聚合函数

聚合函数又被称为组函数，它是对一组值进行操作，返回一个单一的值。常见的聚合函数有

count()：统计函数，统计满足条件的记录的条数
sum()：求和函数，求和
avg()：平均值函数，求平均值
max()：最大值函数，求最大值
min()：最小值函数，求最小值
var_pop()：方差函数，求方差
var_samp()：样本方差函数，求样本方差
stddev_pop()：标准差函数，求标准差
stddev_samp()：样本标准差函数，求样本标准差
covar_pop()：协方差函数，求协方差

示例：

-- 查询学生表中男生和女生的数量
SELECT gender, COUNT(*) FROM student GROUP BY gender;

-- 查询学生表中男生的平均年龄
SELECT AVG(age) FROM student WHERE gender = '男';

-- 查询学生表中女生的平均年龄
SELECT AVG(age) FROM student WHERE gender = '女';

# 窗口函数

# 什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行进行复杂分析。窗口函数的语法

<窗口函数> OVER (PARTITION BY <列名> ORDER BY <列名> [ASC|DESC])

窗口函数的本质是over()，其作用是把原表格复制出来，建立独立运行的表格副本，然后对表格副本进行重新排序或聚合，over()前需要搭配rank()，max()等分析函数来实现副本的排序与聚合，而SQL母语句中的操作则按照over()形成的副本做参考，可以理解成Excel中vlookup和SQL中的左右表连接所实现的功能。

partitionby

partition by

窗口函数中重要有partition by 和order by，partition by用于分组，order by用于排序，partition by和order by可以单独使用，也可以同时使用。其中PARTITION BY类似于SQL中的GROUP BY但不同的是GROUP BY是分组后进行聚合，而PARTITION BY是在窗口副本中起到的作用是分组不聚合。

# 常用窗口函数

row_number()：函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。例如 12345，同名次也分先后
rank()：排名函数，返回当前行的排名，例如 11145，三个第一名，第四名就是第四名。
dense_rank()：密集排名函数，返回当前行的密集排名，例如 11123，三个第一名第四名返回2。
ntile()：分桶函数，将数据分成指定数量的桶。例如 11122233 同名次不分先后。
lead()：向前取值函数，返回当前行之后指定行数的值
lag()：向后取值函数，返回当前行之前指定行数的值
first_value()：返回当前行所在窗口的第一行的值
last_value()：返回当前行所在窗口的最后一行的值
cume_dist()：累计分布函数，返回当前行所在窗口的累计分布
percent_rank()：百分比排名函数，返回当前行所在窗口的百分比排名

row_number()，rank()，dense_rank()区别我们使用班级成绩来举例：

当学校进行同年级的10个班进行排名，其中一个班有五个人，成绩分别是：b=[100,90,90,90,80]。

-- 按照[100,90,90,90,80] 得到的排名就是 [1,2,3,4,5]
select row_number() over(partition by 年级 order by 分数) as 'row_number' from 成绩表;

-- 按照[100,90,90,90,80] 得到的排名就是 [1,2,2,2,3]
select dense_rank() over(partition by 年级 order by 分数) as 'dense_rank' from 成绩表;

-- 按照[100,90,90,90,80] 得到的排名就是 [1,2,2,2,5]
select rank() over(partition by 年级 order by 分数) as 'rank' from 成绩表;

ntile() 函数常用在下面的需求中：如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?这时比较好的选择，就是使用ntile函数：ntile是要把查询得到的结果平均分为几组，如果不平均则分给第一组。上一组个数和下一组个数的差值不能超过2，目的是尽量平分。该函数返回一个数字，该数字表示该行属于的分组号。在使用场景，例如在市场分析中，可以利用ntile()函数将客户细分成具有相似消费行为的组别。

-- 从employees表中查询，按照salary排序，将结果分成4个部分，并命名为quartile，同时查询name和salary
SELECT ntile(4) OVER (ORDER BY salary) AS quartile, name, salary
FROM employees;

Lag和Lead分析函数可以在一次查询中取出当前后N行和前N行的数据. 语法如下：

Lag/Lead(col,n,DEFAULT) 
-- col:列名
-- n:取前n行或后n行，可选，默认为1
-- DEFAULT:当取不到值的时候，默认给定的值

例子：

-- 计算当前用户的第一次访问时间以及当前访问时间的上一次访问时间和下一次访问时间
select
	userid,url,ctime,
	first_value(ctime) over(partition by userid order by ctime) as first_ctime, -- 第一次访问时间
  lag(ctime,1) over(partition by userid order by ctime) as lag_ctime, -- 上一次访问时间
  lead(ctime,1) over(partition by userid order by ctime) as lead_ctime -- 下一次访问时间
from ods_user_log;

cume_dist()函数用于计算当前行在窗口分区的累积分布。它返回一个介于0到1之间的值，表示当前行在窗口分区中的相对位置。例如，如果窗口分区中有10行数据，并且当前行是第5行，那么cume_dist()函数将返回0.5。

使用例子：

-- 统计小于等于当前工资的人数占总人数的比例。
SELECT salary, cume_dist() OVER (ORDER BY salary) AS cume_dist
FROM employees;

# 常见及使用

窗口函数常用在下面的业务场景中

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励
复购分析：App内要分析复购用户有多少
累计问题：医院要经常统计累计患者数

窗口函数示例

-- 查询学生表中男生的年龄排名
SELECT name, age, RANK() OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄密集排名
SELECT name, age, DENSE_RANK() OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄分桶
SELECT name, age, NTILE(3) OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄向前取值
SELECT name, age, LEAD(age, 1) OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄向后取值
SELECT name, age, LAG(age, 1) OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄第一行
SELECT name, age, FIRST_VALUE(age) OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄最后一行
SELECT name, age, LAST_VALUE(age) OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄累计分布
SELECT name, age, CUME_DIST() OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

-- 查询学生表中男生的年龄百分比排名
SELECT name, age, PERCENT_RANK() OVER (ORDER BY age DESC) FROM student WHERE gender = '男';

# 日期函数

# 日期处理函数

now()：返回当前日期和时间
getdate()：返回当前日期和时间
curdate()：返回当前日期
curtime()：返回当前时间
unix_timestamp()：返回当前时间的Unix时间戳
from_unixtime()：将Unix时间戳转换为日期和时间
year()：返回日期中的年份
week()：返回日期中的周数
dayofyear()：返回日期中的天数
dayofmonth()：返回日期中的月份中的天数
dayofweek()：返回日期中的星期几
hour()：返回日期中的小时
minute()：返回日期中的分钟
second()：返回日期中的秒数
date()：返回日期中的日期部分
time()：返回日期中的时间部分
timestamp()：返回日期中的时间戳
date_format()：将日期转换为指定格式的字符串
str_to_date()：将字符串转换为日期
datediff()：返回两个日期之间的天数
date_add()：在日期上添加指定的时间间隔
date_sub()：在日期上减去指定的时间间隔
date_part()：返回日期中的指定部分
date_trunc()：将日期截断到指定的时间间隔

# 季度处理函数

quarter()：返回日期中的季度

示例

-- 获取每季度的数据
SELECT DATE_FORMAT(date, '%Y-%m-01') AS first_day, COUNT(*) FROM table_name GROUP BY first_day;

-- 获取每年第三季度的数据
SELECT DATE_FORMAT(date, '%Y-07-01') AS third_quarter, COUNT(*) FROM table_name GROUP BY third_quarter;

-- 获取上一季度的数据
SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 3 MONTH), '%Y-01-01') AS last_quarter, COUNT(*) FROM table_name GROUP BY last_quarter;

-- 获取下季度的数据
SELECT DATE_FORMAT(DATE_ADD(date, INTERVAL 3 MONTH), '%Y-01-01') AS next_quarter, COUNT(*) FROM table_name GROUP BY next_quarter;

-- 本季度的第一天
SELECT COUNT(*) FROM 表名 WHERE 日期字段 = DATE_FORMAT(NOW(),'%Y-%m-01')

-- 每季度的第一天数据
SELECT COUNT(*) FROM 表名 WHERE DATE_FORMAT(日期字段,'%d') = '01'

-- 获取同比季度数据
SELECT DATE_FORMAT(date, '%Y-%m-01') AS first_day, COUNT(*) FROM table_name WHERE DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 YEAR), '%Y-%m') GROUP BY first_day;

# 月处理

month()：返回日期中的月份
monthname()：返回日期中的月份名称

示例：

-- 获取每月第一天的数据
SELECT DATE_FORMAT(date, '%Y-%m-01') AS first_day, COUNT(*) FROM table_name GROUP BY first_day;

-- 获取每月最后一天的数据
SELECT DATE_FORMAT(LAST_DAY(date), '%Y-%m-%d') AS last_day, COUNT(*) FROM table_name GROUP BY last_day;

-- 获取每年第一月的数据
SELECT DATE_FORMAT(date, '%Y-01-01') AS first_month, COUNT(*) FROM table_name GROUP BY first_month;

-- 获取每年最后一个月的数据
SELECT DATE_FORMAT(LAST_DAY(date), '%Y-%m-01') AS last_month, COUNT(*) FROM table_name GROUP BY last_month;

-- 获取上一月的数据
SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 1 MONTH), '%Y-%m-01') AS last_month, COUNT(*) FROM table_name GROUP BY last_month;

-- 获取下月的数据
SELECT DATE_FORMAT(DATE_ADD(date, INTERVAL 1 MONTH), '%Y-%m-01') AS next_month, COUNT(*) FROM table_name GROUP BY next_month;

-- 本月的第一天
SELECT COUNT(*) FROM 表名 WHERE 日期字段 = DATE_FORMAT(NOW(),'%Y-%m-01')

-- 每月的第一天数据
SELECT COUNT(*) FROM 表名 WHERE DATE_FORMAT(日期字段,'%d') = '01'

# 星期处理

weekday()：返回日期中的星期几（0-6，0表示星期一）
dayname()：返回日期中的星期几名称

示例：

-- 获取每周一的数据
SELECT DATE_FORMAT(date, '%Y-%m-%d') AS monday, COUNT(*) FROM table_name WHERE DAYOFWEEK(date) = 2 GROUP BY monday;

-- 获取上周的数据
SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 1 WEEK), '%Y-%m-%d') AS last_monday, COUNT(*) FROM table_name GROUP BY last_monday;

-- 获取下周的数据
SELECT DATE_FORMAT(DATE_ADD(date, INTERVAL 1 WEEK), '%Y-%m-%d') AS next_monday, COUNT(*) FROM table_name GROUP BY next_monday;

-- 本周的第一天
SELECT COUNT(*) FROM 表名 WHERE 日期字段 = DATE_FORMAT(NOW(),'%Y-%m-%d')

-- 每周的第一天数据
SELECT COUNT(*) FROM 表名 WHERE DATE_FORMAT(日期字段,'%d') = '01'

-- 获取同比周数据
SELECT DATE_FORMAT(date, '%Y-%m-%d') AS monday, COUNT(*) FROM table_name WHERE DATE_FORMAT(date, '%Y-%m-%d') = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 YEAR), '%Y-%m-%d') GROUP BY monday;

-- 获取每月第二周的数据
SELECT DATE_FORMAT(date, '%Y-%m-%d') AS second_week, COUNT(*) FROM table_name WHERE WEEK(date, 1) = 2 GROUP BY second_week;

# 天处理函数

day()：返回日期中的日期

示例：

-- 获取每天的数据
SELECT DATE_FORMAT(date, '%Y-%m-%d') AS day, COUNT(*) FROM table_name GROUP BY day;

-- 获取昨天数据
SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 1 DAY), '%Y-%m-%d') AS yesterday, COUNT(*) FROM table_name GROUP BY yesterday;

-- 获取明天数据
SELECT DATE_FORMAT(DATE_ADD(date, INTERVAL 1 DAY), '%Y-%m-%d') AS tomorrow, COUNT(*) FROM table_name GROUP BY tomorrow;

-- 昨天
select * from table1 where datediff(dd,时间字段,getdate()) = 1
-- 最近七天
select * from table1 where datediff(dd,时间字段,getdate()) <= 7

# 运算函数

DATE_SUB语法

DATE_SUB(date, INTERVAL expr unit)

date：日期
expr：表达式，如1、2、3
unit：单位，如day、month、year

示例：

-- 获取昨天数据
SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 1 DAY), '%Y-%m-%d') AS yesterday, COUNT(*) FROM table_name GROUP BY yesterday;

DATE_ADD语法

DATE_ADD(date, INTERVAL expr unit)

date：日期
expr：表达式，如1、2、3
unit：单位，如day、month、year

示例：

-- 获取明天数据
SELECT DATE_FORMAT(DATE_ADD(date, INTERVAL 1 DAY), '%Y-%m-%d') AS tomorrow, COUNT(*) FROM table_name GROUP BY tomorrow;

# 字符串函数

length()：返回字符串的长度
concat()：连接字符串
substring()：截取字符串
lower()：将字符串转换为小写
upper()：将字符串转换为大写
replace()：替换字符串中的子串
trim()：去除字符串两端的空格
substring_index(str, delimiter, count)：从字符串中提取特定分隔符前后的部分。
- str：要处理的字符串
- delimiter：分隔符
- count：计数值，如果为正数：返回从左侧开始的 count 个分隔符之前的子串，如果为负数：返回从右侧开始的 count 个分隔符之后的子串

示例：

-- 获取字符串长度
SELECT LENGTH('Hello World');

-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');

-- 截取字符串
SELECT SUBSTRING('Hello World', 1, 5);

-- 将字符串转换为小写
SELECT LOWER('Hello World');

-- 将字符串转换为大写
SELECT UPPER('Hello World');

-- 替换字符串中的子串
SELECT REPLACE('Hello World', 'World', 'MySQL');

-- 去除字符串两端的空格
SELECT TRIM('   Hello World   ');

-- 返回 'www'
SELECT SUBSTRING_INDEX('www.example.com', '.', 1);

# substring_index

substring_index 是sql中常用的字符串处理函数，用于从字符串中提取特定分隔符前后的部分。

语法：

SUBSTRING_INDEX(str, delimiter, count)

str：要处理的字符串
delimiter：分隔符
count：计数值，如果为正数：返回从左侧开始的 count 个分隔符之前的子串，如果为负数：返回从右侧开始的 count 个分隔符之后的子串

示例：

-- 返回 'com'
SELECT SUBSTRING_INDEX('www.example.com', '.', -1);
-- 提取文件扩展名
SELECT SUBSTRING_INDEX('document.pdf', '.', -1); -- 返回 'pdf'

常见的一个场景如我们需要将字符串"a,b,c,d,e"转换为数组，我们可以使用substring_index来实现：

-- 返回 ['a', 'b', 'c', 'd', 'e']
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', numbers.n), ',', -1) as split_type_id
FROM (
  SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) numbers;

-- SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
-- 生成了一个包含1到5的表

# 数学函数

abs()：返回绝对值
ceil()：返回大于或等于指定数字的最小整数
floor()：返回小于或等于指定数字的最大整数
round(n,d)：返回四舍五入后的数字,n为数字,d为保留小数位数
power(n,m)：返回指定数字的指定次幂,n为数字,m为次幂
sqrt(n)：返回指定数字的平方根,n为数字
format(n, m,[d])：返回金额字符串含千位逗号分隔
- n：数字
- m：指定小数位数。如果省略，默认值为 0。
- d：可选参数，用于指定区域设置，影响千位分隔符和小数点符号等。如果省略，使用服务器的默认区域设置。

示例：



-- 获取绝对值
SELECT ABS(-10);

-- 返回大于或等于指定数字的最小整数
SELECT CEIL(3.14);

-- 返回小于或等于指定数字的最大整数
SELECT FLOOR(3.14);

-- 返回四舍五入后的数字
SELECT ROUND(3.14);

-- 保留两位小数
SELECT ROUND(3.14159, 2);

-- 返回指定数字的指定次幂
SELECT POWER(2, 3);

-- 返回指定数字的平方根
SELECT SQRT(16);

-- 千位逗号分隔
SELECT FORMAT(123456789, 0); -- 123,456,789

← 语法-GROUP BY 语法-子查询 →

语法-函数

聚合函数
窗口函数
什么是窗口函数
常用窗口函数
常见及使用
日期函数
日期处理函数
季度处理函数
月处理
星期处理
天处理函数
运算函数
字符串函数
substring_index
数学函数