SQL中的高级函数
学会了SQL的语法以及一些基础的常用函数已经可以完成大多数的业务操作，而学习高级函数也是在一个进阶过程。如分析函数，数值函数，加密函数
分析函数
MySQL 中的分析函数是指用于在查询结果中进行分析和计算的函数。常见的 MySQL 分析函数包括：
- ROW_NUMBER(): 返回结果集中行的排名。
- RANK(): 计算结果集中行的排名，如果有相同的数值，则会跳过相同的排名。
- DENSE_RANK(): 计算结果集中行的排名，如果有相同的数值，则会按照顺序进行排名，不会跳过。
- NTILE(n): 将结果集均匀地分成 n 份，并返回每行所在的组别。
- LEAD(expr, offset, default): 返回结果集中当前行之后第 offset 行的 expr 列的值，如果超出结果集范围，则返回 default。
- LAG(expr, offset, default): 返回结果集中当前行之前第 offset 行的 expr 列的值，如果超出结果集范围，则返回 default。
mysql从8.0引入了窗口函数，通过 OVER 子句来指定窗口的范围和排序规则，并可以在查询结果中进行分组、排名和聚合等操作。
ROW_NUMBER函数
为结果集中每一行分配一个行号。
语法如下
SELECT column1, column2, ..., ROW_NUMBER() OVER (ORDER BY column ASC|DESC) AS row_number
FROM table_name;
示例如下
SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_number
FROM students;
解释如下
这个查询会返回 students 表中每个学生的姓名和年龄，并为每个学生分配一个按年龄降序排列的行号。
name | age | score
----------------------
Alice | 18 | 90
Bob | 19 | 85
Charlie | 20 | 90
Dave | 18 | 80
Eva | 21 | 70
Frank | 22 | 60
Gina | 19 | 50
Henry | 20 | 40
Ivy | 19 | 30
name | age | row_number
--------------------------
Frank | 22 | 1
Eva | 21 | 2
Charlie | 20 | 3
Henry | 20 | 4
Bob | 19 | 5
Gina | 19 | 6
Alice | 18 | 7
Dave | 18 | 8
Ivy | 17 | 9
RANK函数
为结果集中的每一行分配一个排名。
语法如下
SELECT column1, column2, ..., RANK() OVER (ORDER BY column ASC|DESC) AS rank
FROM table_name;
示例如下
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM students;
解释如下
这个查询会返回 students 表中每个学生的姓名和分数，并为每个学生分配一个按照分数降序排列的排名。如果有两个学生的分数相同，它们将被分配相同的排名，并且下一个排名将会被跳过。这意味着可能会存在不连续的排名。
name | score
----------------
Alice | 90
Bob | 85
Charlie | 90
Dave | 80
Eva | 70
Frank | 60
Gina | 50
Henry | 40
Ivy | 30
name | score | rank
-----------------------
Alice | 90 | 1
Charlie | 90 | 1
Bob | 85 | 3
Dave | 80 | 4
Eva | 70 | 5
Frank | 60 | 6
Gina | 50 | 7
Henry | 40 | 8
Ivy | 30 | 9
DENSE_RANK函数
类似于 RANK() 函数，用于为结果集中的每一行分配一个排名，不同之处在于 DENSE_RANK() 不会出现重复的排名。
语法如下
SELECT column1, column2, ..., DENSE_RANK() OVER (ORDER BY column ASC|DESC) AS dense_rank
FROM table_name;
示例如下
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;
解释如下
这个查询会返回 students 表中每个学生的姓名和分数，并为每个学生分配一个按照分数降序排列的稠密排名。会为相同的排序值分配相同的排名，并在跳过相同排名后继续分配连续的排名。这意味着排名是稠密的且连续的，不会出现空缺。
name | score
----------------
Alice | 90
Bob | 85
Charlie | 90
Dave | 80
Eva | 70
Frank | 60
Gina | 50
Henry | 40
Ivy | 30
name | score | dense_rank
----------------------------
Alice | 90 | 1
Charlie | 90 | 1
Bob | 85 | 2
Dave | 80 | 3
Eva | 70 | 4
Frank | 60 | 5
Gina | 50 | 6
Henry | 40 | 7
Ivy | 30 | 8
NTILE函数
用于将结果集划分为指定数量的桶，并为每个桶分配一个标识符。
语法如下
SELECT column1, column2, ..., NTILE(n) OVER (ORDER BY column ASC|DESC) AS bucket
FROM table_name;
示例如下
SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS bucket
FROM students;
解释如下
这个查询会返回
students表中每个学生的姓名和分数，并将结果集划分为4个桶，按照分数降序排列，同时为每个桶分配一个标识符。
name | score
----------------
Alice | 90
Bob | 85
Charlie | 90
Dave | 80
Eva | 70
Frank | 60
Gina | 50
Henry | 40
Ivy | 30
name | score | bucket
--------------------------
Alice | 90 | 1
Charlie | 90 | 1
Bob | 85 | 2
Dave | 80 | 2
Eva | 70 | 3
Frank | 60 | 3
Gina | 50 | 4
Henry | 40 | 4
Ivy | 30 | 4
LEAD函数
用于访问结果集中当前行之后的行的数据。具体来说，LEAD() 函数可以访问指定偏移量后的行，并返回该行指定列的值。
语法如下
LEAD(column, offset, default) OVER (ORDER BY column ASC|DESC)
示例如下
SELECT name, score, LEAD(score, 1, 0) OVER (ORDER BY score DESC) AS next_score
FROM students;
解释如下
这个查询会返回
students表中每个学生的姓名和分数，以及每个学生后面一个学生的分数。在这个例子中，LEAD(score, 1, 0)表示获取当前行之后一行的分数值，如果没有下一行，则返回默认值0。同时，我们按照分数降序排列结果集。
name | score
----------------
Alice | 90
Bob | 85
Charlie | 90
Dave | 80
name | score | next_score
----------------------------
Alice | 90 | 85
Charlie | 90 | 85
Bob | 85 | 80
Dave | 80 | 0
LAG函数
用于访问前一行的数据的窗口函数。它可以帮助你在结果集中获取前一行的数值，从而进行比较或计算增量等操作。
语法如下
LAG (expression, offset, default) OVER ( [partition_by_clause] order_by_clause )
示例如下
SELECT name, age,
LAG(age, 1, 0) OVER (ORDER BY name) AS prev_age,
age - LAG(age, 1, 0) OVER (ORDER BY name) AS age_difference
FROM students;
解释如下
通过使用 LAG 函数，我们得到了每个学生与前一个学生的年龄差值。
name | age
------------
Alice | 18
Bob | 19
Charlie | 20
Dave | 21
Eva | 22
name | age | prev_age | age_difference
----------------------------------------
Alice | 18 | 0 | 18
Bob | 19 | 18 | 1
Charlie | 20 | 19 | 1
Dave | 21 | 20 | 1
Eva | 22 | 21 | 1
窗口帧
用于定义窗口函数计算范围的一种语法。这也意味着它是跟随窗口函数一块出现的。
常用语法
[ROWS/RANGE] BETWEEN <start> AND <end>
参数
ROWS或RANGE用于指定窗口帧的类型，ROWS表示基于行的帧，RANGE表示基于值的帧。<start>和<end>用于指定窗口帧的起始和结束位置。
常用的窗口帧包括
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从查询结果集的第一行到当前行。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到查询结果集的最后一行。ROWS BETWEEN x PRECEDING AND y FOLLOWING：从当前行向前 x 行，并从当前行向后 y 行。
举例说明
SELECT
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_total
FROM orders;
解释
这个查询将计算每个订单及其前面所有订单的累计金额，并将结果作为一个新的列
cumulative_total返回。
order_id | order_date | total_amount
-----------------------------------
1 | 2023-01-01 | 100
2 | 2023-02-01 | 200
3 | 2023-03-01 | 300
4 | 2023-04-01 | 400
5 | 2023-05-01 | 500
order_id | order_date | total_amount | cumulative_total
------------------------------------------------------
1 | 2023-01-01 | 100 | 100
2 | 2023-02-01 | 200 | 300
3 | 2023-03-01 | 300 | 600
4 | 2023-04-01 | 400 | 1000
5 | 2023-05-01 | 500 | 1500
2484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
