MySQL窗口函数详解

MySQL窗口函数详解

MySQL从8.0版本开始引入了窗口函数，这是一个强大的特性，可以大大简化复杂的数据分析使命。本文将详细介绍MySQL窗口函数的概念、语法和常见用法，并联合实际应用场景举行阐明。
什么是窗口函数?

窗口函数是一种可以或许对结果集中的一组行举行操作的函数。它们类似于聚合函数，但不会将结果集缩减为单个行 - 相反，它们为每一行返回一个结果。
窗口函数的语法

基本语法如下:

1. function_name() OVER (
2.     [PARTITION BY column_list]
3.     [ORDER BY column_list]
4.     [frame_clause]
5. )

复制代码

• function_name: 窗口函数的名称
  
• PARTITION BY: 可选，界说行分组的方式
  
• ORDER BY: 可选，界说分区老手的排序方式
  
• frame_clause: 可选，界说当前分区内的行子集(窗口帧)
  

常用的窗口函数及其应用场景

1. ROW_NUMBER()

ROW_NUMBER() 为每一行分配一个唯一的整数，用于在每个分区内对行举行排序并编号。
可以帮助我们对数据举行分区后排序，获取排名信息。
具体来说，ROW_NUMBER() 是一种分析函数，它可以根据 ORDER BY 子句中指定的列对行举行排序，并为每个分区内的行根据排序结果来分配唯一的连续编号。 PARTITION BY 子句类似于 GROUP BY 用于分组，该子句指定希望分区的列或表达式。行号将在每个分区内分配，然后重新开始为下一个分区分配。
基本用法

1. SELECT
2.     name,
3.     score,
4.     ROW_NUMBER() OVER (ORDER BY score DESC) as rank
5. FROM students;

复制代码

实际应用场景：查找每个部门的前N名员工

假设我们要找出每个部门薪资最高的3名员工：

1. CREATE TABLE employees (
2.     id INT,
3.     name VARCHAR(50),
4.     department VARCHAR(50),
5.     salary DECIMAL(10, 2)
6. );
8. INSERT INTO employees (id, name, department, salary) VALUES
9. (1, 'Alice', 'Sales', 60000),
10. (2, 'Bob', 'Sales', 50000),
11. (3, 'Charlie', 'Sales', 55000),
12. (4, 'David', 'Marketing', 65000),
13. (5, 'Eve', 'Marketing', 60000),
14. (6, 'Frank', 'Marketing', 70000),
15. (7, 'Grace', 'IT', 80000),
16. (8, 'Henry', 'IT', 75000),
17. (9, 'Ivy', 'IT', 78000);
19. SELECT *
20. FROM (
21.     SELECT
22.         name,
23.         department,
24.         salary,
25.         ROW_NUMBER() OVER (
26.             PARTITION BY department
27.             ORDER BY salary DESC
28.         ) as salary_rank
29.     FROM employees
30. ) ranked
31. WHERE salary_rank <= 3
32. ORDER BY department, salary_rank;

复制代码

这个查询首先为每个部门的员工按薪资举行排名，然后筛选出排名前三的员工。

2. RANK() 和 DENSE_RANK()

RANK() 为每一行分配排名，雷同值的行得到雷同排名，但会产生隔断。
DENSE_RANK() 类似于RANK()，但不会产生隔断。
基本用法

1. SELECT
2.     name,
3.     score,
4.     RANK() OVER (ORDER BY score DESC) as rank,
5.     DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
6. FROM students;

复制代码

实际应用场景：学生成绩排名

假设我们要为学生的考试成绩举行排名，同时展示 RANK() 和 DENSE_RANK() 的区别：

1. CREATE TABLE student_scores (
2.     id INT,
3.     name VARCHAR(50),
4.     score INT
5. );
7. INSERT INTO student_scores (id, name, score) VALUES
8. (1, 'Alice', 95),
9. (2, 'Bob', 95),
10. (3, 'Charlie', 90),
11. (4, 'David', 88),
12. (5, 'Eve', 88),
13. (6, 'Frank', 85);
15. SELECT
16.     name,
17.     score,
18.     RANK() OVER (ORDER BY score DESC) as rank_number,
19.     DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_number
20. FROM student_scores;

复制代码

这个查询展示了学生成绩的排名，同时表现了 RANK() 和 DENSE_RANK() 的区别。RANK() 会在雷同分数后产生隔断，而 DENSE_RANK() 不会。

3. LAG() 和 LEAD()

LAG() 和 LEAD() 允许我们访问当前行之前或之后的行。
基本用法

1. SELECT
2.     date,
3.     sales,
4.     LAG(sales) OVER (ORDER BY date) as previous_day_sales,
5.     LEAD(sales) OVER (ORDER BY date) as next_day_sales
6. FROM daily_sales;

复制代码

实际应用场景：计算同比增长率

假设我们要计算每月贩卖额的同比增长率：

1. CREATE TABLE monthly_sales (
2.     year INT,
3.     month INT,
4.     sales DECIMAL(10, 2)
5. );
7. INSERT INTO monthly_sales (year, month, sales) VALUES
8. (2022, 1, 10000), (2022, 2, 12000), (2022, 3, 15000),
9. (2023, 1, 11000), (2023, 2, 13000), (2023, 3, 16000);
11. SELECT
12.     year,
13.     month,
14.     sales,
15.     LAG(sales) OVER (PARTITION BY month ORDER BY year) as prev_year_sales,
16.     (sales - LAG(sales) OVER (PARTITION BY month ORDER BY year)) /
17.     LAG(sales) OVER (PARTITION BY month ORDER BY year) * 100 as growth_rate
18. FROM monthly_sales
19. ORDER BY month, year;

复制代码

这个查询计算了每个月的贩卖额相比客岁同期的增长率。

4. 聚合窗口函数 (如 SUM(), AVG())

聚合函数如 SUM() 和 AVG() 也可以作为窗口函数使用，可以计算累计总和或移动匀称值。
基本用法

1. SELECT
2.     date,
3.     sales,
4.     SUM(sales) OVER (ORDER BY date) as cumulative_sales,
5.     AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg
6. FROM daily_sales;

复制代码

实际应用场景1：计算累计总和

假设我们要计算每个部门的累计贩卖额：

1. CREATE TABLE sales (
2.     id INT,
3.     department VARCHAR(50),
4.     sale_date DATE,
5.     amount DECIMAL(10, 2)
6. );
8. INSERT INTO sales (id, department, sale_date, amount) VALUES
9. (1, 'Electronics', '2023-01-01', 1000),
10. (2, 'Clothing', '2023-01-01', 500),
11. (3, 'Electronics', '2023-01-02', 1500),
12. (4, 'Clothing', '2023-01-02', 750),
13. (5, 'Electronics', '2023-01-03', 1200),
14. (6, 'Clothing', '2023-01-03', 600);
16. SELECT
17.     department,
18.     sale_date,
19.     amount,
20.     SUM(amount) OVER (
21.         PARTITION BY department
22.         ORDER BY sale_date
23.     ) as cumulative_sales
24. FROM sales
25. ORDER BY department, sale_date;

复制代码

这个查询计算了每个部门的累计贩卖额，按日期排序。

实际应用场景2：计算移动匀称值

假设我们有一个股票价格表，我们想计算7天移动匀称价格：

1. CREATE TABLE stock_prices (
2.     date DATE,
3.     price DECIMAL(10, 2)
4. );
6. INSERT INTO stock_prices (date, price) VALUES
7. ('2023-01-01', 100.00),
8. ('2023-01-02', 101.00),
9. ('2023-01-03', 102.00),
10. ('2023-01-04', 101.50),
11. ('2023-01-05', 103.00),
12. ('2023-01-06', 104.00),
13. ('2023-01-07', 103.50),
14. ('2023-01-08', 105.00),
15. ('2023-01-09', 106.00),
16. ('2023-01-10', 107.00);
18. SELECT
19.     date,
20.     price,
21.     AVG(price) OVER (
22.         ORDER BY date
23.         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
24.     ) AS moving_avg
25. FROM stock_prices
26. ORDER BY date;

复制代码

这个查询将计算包括当前日期在内的前7天的移动匀称价格。

结论

窗口函数是MySQL 8.0中的一个强大新特性，可以大大简化复杂的数据分析使命。通过上述实际应用场景的例子，我们可以看到窗口函数在处置处罚排名、时间序列数据、累计计算等方面的强大本领。这些函数使得我们可以或许更高效地处置处罚诸如员工排名、同比增长、累计总和、移动匀称等常见的数据分析问题。
随着对窗口函数的深入明确和熟练应用，你将可以或许编写更简洁、更高效的SQL查询，大大提高数据分析的效率。窗口函数不但可以简化查询，还可以提高查询性能，由于它们通常比使用子查询或自毗连的等效查询更有效率。
继承探索和实践这些窗口函数，你会发现它们在一样平常数据分析工作中的无穷潜力。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。