在 MySQL 中,窗口函数是一类用于在查询结果集中计算值的函数,允许用户根据数据行进行聚合或排序操作,同时保留行的详细信息。窗口函数在分析数据时非常有用,因为它们允许您在不缩小结果集的情况下对数据进行复杂的计算。
常见的窗口函数包括:
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- NTILE(n)
- LAG()
- LEAD()
- FIRST_VALUE()
- LAST_VALUE()
- SUM(), AVG(), COUNT(), MIN(), MAX()(这些聚合函数在窗口函数上下文中也可以使用)
窗口函数的基本语法:
<窗口函数> OVER (
[PARTITION BY <分组列>]
[ORDER BY <排序列>]
[ROWS or RANGE <窗口框架>]
)
- PARTITION BY:用于将数据划分成不同的组(分区),窗口函数将在每个分区内进行计算。
- ORDER BY:用于指定窗口函数计算的顺序。
- ROWS or RANGE:定义了窗口框架,指定计算窗口的大小。
下面是进行演示的表数据
具体的窗口函数及示例:
1. ROW_NUMBER()
-
- 用法:返回分区中每行的唯一行号。
- 示例:
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM
employee;
这将根据薪水的降序为每个员工分配一个唯一的行号。(可用于排名的场景,排序数重复时不重复排名)
2. RANK()
-
- 用法:返回当前行在其分区中的排名,排名相同的行将具有相同的排名号,但排名之后的行的排名号会跳过。也就是不紧凑。
- 示例:
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM
employee;
如果两名员工的薪水相同,他们将获得相同的排名,但下一个员工的排名将跳过一个数字。
3. DENSE_RANK()
-
- 用法:类似于 RANK(),但没有跳过排名编号。
- 示例:
SELECT
name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM
employee;
这会给排名相同的行相同的编号,并且不会跳过接下来的排名。
4. NTILE(n)
-
- 用法:将结果集划分为指定数量的桶,返回当前行属于哪个桶。NTILE(int n)
- 示例:
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM
employee;
这会将员工划分为四个组,并根据薪水的降序将每个员工分配到一个组。排名靠前的优先分大组。
5. LAG(n)
-
- 用法:获取当前行的前n行的值。LAG(column,n);
- 示例:
SELECT
name,
salary,
LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM
employee;
这会返回当前行的前一行的薪水。
6. LEAD(n)
-
- 用法:获取当前行的后n行的值。
- 示例:
SELECT
name,
salary,
LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM
employee;
这会返回当前行的后一行的薪水。
7. FIRST_VALUE()
-
- 用法:返回窗口中第一个值。
- 示例:
SELECT
name,
salary,
FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS first_salary
FROM
employee;
这会返回窗口中按薪水降序排列的第一个薪水值。
8. LAST_VALUE()
-
- 用法:返回窗口中最后一个值。
- 示例:
SELECT
name,
salary,
LAST_VALUE(salary) OVER (ORDER BY salary DESC) AS last_salary
FROM
employee;
这会返回窗口中按薪水降序排列的最后一个薪水值。
9. SUM(), AVG(), COUNT(), MIN(), MAX()(作为窗口函数)
-
- 用法:可以在窗口函数上下文中使用这些聚合函数。
- 示例:
SELECT
name,
salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running_total
FROM
employee;
这将计算每个部门的薪水累积和。每个部门的员工按照salary降序排序。
总结:
窗口函数提供了强大的分析能力,允许在不改变结果集的情况下执行复杂的计算和聚合操作。通过适当使用窗口函数,可以轻松实现排名、运行总和、前后比较等常见的分析需求。
标签:salary,窗口,函数,示例,OVER,MySQL,ORDER,总和 From: https://blog.csdn.net/m0_65013257/article/details/141903714