MySQL常用窗口函数总和

在 MySQL 中，窗口函数是一类用于在查询结果集中计算值的函数，允许用户根据数据行进行聚合或排序操作，同时保留行的详细信息。窗口函数在分析数据时非常有用，因为它们允许您在不缩小结果集的情况下对数据进行复杂的计算。

常见的窗口函数包括：

1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()
4. NTILE(n)
5. LAG()
6. LEAD()
7. FIRST_VALUE()
8. LAST_VALUE()
9. 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降序排序。

总结：

窗口函数提供了强大的分析能力，允许在不改变结果集的情况下执行复杂的计算和聚合操作。通过适当使用窗口函数，可以轻松实现排名、运行总和、前后比较等常见的分析需求。