mysql窗口函数

MySQL 窗口函数是一种高级的 SQL 函数，它可以进行一些比较复杂的数据分析和处理。与传统的聚合函数不同，窗口函数不会合并行，而是根据特定的条件为每行分配一个值。

MySQL 窗口函数可以用来计算每个行的聚合值，而不需要合并行，因此可以保留原始数据的所有细节。常见的 MySQL 窗口函数包括：

• ROW_NUMBER：给每行分配一个唯一的数字，可以用来计算排名。
• RANK：计算每行的排名，如果有相同值，则排名相同，下一个排名会跳过重复的行。
• DENSE_RANK：计算每行的排名，如果有相同值，则排名相同，下一个排名不会跳过重复的行。
• LEAD：获取当前行之后的指定偏移量的值。
• LAG：获取当前行之前的指定偏移量的值。
• FIRST_VALUE：获取分组中第一行的值。
• LAST_VALUE：获取分组中最后一行的值。
• SUM、AVG、COUNT、MAX、MIN 等聚合函数。

使用 MySQL 窗口函数需要用到 OVER 子句，它用来定义窗口的边界，包括窗口大小、排序规则、分区等。以下是一个例子：

SELECT department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employee;

这个查询使用 ROW_NUMBER 窗口函数计算每个部门员工的工资排名，PARTITION BY 子句指定了分区列，ORDER BY 子句指定了排序规则。

以下是一些 MySQL 窗口函数以及它们在处理复杂场景数据时的示例：

1. ROW_NUMBER

ROW_NUMBER() 函数用于为每一行分配一个唯一的数字，通常用于排序或排名。例如，假设我们有一个表格 employees，其中包含每个部门的员工，我们可以使用 ROW_NUMBER() 函数为每个部门的员工计算排名：

SELECT department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

这将为每个部门内的员工按工资降序排名，并在每个部门内为每个员工分配一个排名。

2. RANK

RANK() 函数用于计算每行的排名，如果有相同的值，则排名相同，下一个排名会跳过重复的行。例如，假设我们有一个表格 sales，其中包含每个销售员的销售额，我们可以使用 RANK() 函数为每个销售员计算排名：

SELECT salesman, sales, RANK() OVER (ORDER BY sales DESC) AS rank FROM sales;

这将按销售额降序排列销售员，并为每个销售员分配一个排名。如果两个销售员的销售额相同，则它们将获得相同的排名，而下一个排名将跳过这两个销售员。

3. DENSE_RANK

DENSE_RANK() 函数与 RANK() 函数类似，但是它不会跳过重复的排名。例如，假设我们有一个表格 sales，其中包含每个销售员的销售额，我们可以使用 DENSE_RANK() 函数为每个销售员计算排名：

SELECT salesman, sales, DENSE_RANK() OVER (ORDER BY sales DESC) AS rank FROM sales;

这将按销售额降序排列销售员，并为每个销售员分配一个排名。如果两个销售员的销售额相同，则它们将获得相同的排名，下一个排名将不会跳过这两个销售员。

4. LEAD

LEAD() 函数用于获取当前行之后的指定偏移量的值。例如，假设我们有一个表格 orders，其中包含每个客户的订单和订单日期，我们可以使用 LEAD() 函数获取每个客户的下一个订单日期：

SELECT customer, order_date, LEAD(order_date, 1) OVER (PARTITION BY customer ORDER BY order_date) AS next_order_date FROM orders;

这将按客户和订单日期对订单进行分区，并为每个订单获取下一个订单日期。

5. LAG

LAG() 函数用于获取窗口中指定列的前一行的值。例如，假设我们有一个表格 sales，其中包含每个销售员的销售额和日期，我们可以使用 LAG() 函数计算每个销售员的销售额与前一天相比的增长率：

SELECT salesman, sale_date, sales, LAG(sales) OVER (PARTITION BY salesman ORDER BY sale_date) AS previous_sales, (sales - LAG(sales) OVER (PARTITION BY salesman ORDER BY sale_date)) / LAG(sales) OVER (PARTITION BY salesman ORDER BY sale_date) AS growth_rate FROM sales;

这将按销售员和日期对销售进行分区，并使用 LAG() 函数获取前一天的销售额，然后计算增长率。

6. FIRST_VALUE

FIRST_VALUE() 函数用于获取窗口中指定列的第一行的值。例如，假设我们有一个表格 employees，其中包含每个部门的员工和薪资，我们可以使用 FIRST_VALUE() 函数计算每个部门的最高薪资员工：

SELECT department, name, salary, FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary_employee FROM employees;

这将按部门对员工进行分区，并使用 FIRST_VALUE() 函数获取薪资最高的员工的名称。

7. LAST_VALUE

LAST_VALUE() 函数用于获取窗口中指定列的最后一行的值。例如，假设我们有一个表格 logs，其中包含每个用户的登录时间和日期，我们可以使用 LAST_VALUE() 函数计算每个用户最后一次登录的时间：

SELECT user, login_time, LAST_VALUE(login_time) OVER (PARTITION BY user ORDER BY login_time) AS last_login_time FROM logs;

这将按用户和登录时间对日志进行分区，并使用 LAST_VALUE() 函数获取每个用户最后一次登录的时间。

8. SUM

SUM() 函数用于计算窗口中指定列的总和。例如，假设我们有一个表格 sales，其中包含每个销售员的销售额和日期，我们可以使用 SUM() 函数计算每个销售员的累计销售额：

SELECT salesman, sale_date, sales, SUM(sales) OVER (PARTITION BY salesman ORDER BY sale_date) AS cumulative_sales FROM sales;

这将按销售员和日期对销售进行分区，并为每个销售员计算累计销售额。

9. AVG

AVG() 函数用于计算窗口中指定列的平均值。例如，假设我们有一个表格 employees，其中包含每个部门的员工和薪资，我们可以使用 AVG() 函数计算每个部门的平均工资：

SELECT department, name, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees;

这将按部门对员工进行分区，并为每个部门计算平均工资。

10. MAX

MAX() 函数用于计算窗口中指定列的最大值。例如，假设我们有一个表格 products，其中包含每个产品的价格和制造商，我们可以使用 MAX() 函数计算每个制造商的最高价格产品：

SELECT manufacturer, product, price, MAX(price) OVER (PARTITION BY manufacturer) AS max_price FROM products;

这将按制造商对产品进行分区，并为每个制造商计算最高价格。

11. MIN

MIN() 函数用于计算窗口中指定列的最小值。例如，假设我们有一个表格 orders，其中包含每个客户的订单和订单日期，我们可以使用 MIN() 函数计算每个客户的最早订单日期：

SELECT customer, order_date, MIN(order_date) OVER (PARTITION BY customer) AS first_order_date FROM orders;

这将按客户对订单进行分区，并为每个客户计算最早订单日期。

12. COUNT

COUNT() 函数用于计算窗口中指定列的行数。例如，假设我们有一个表格 logs，其中包含每个用户的登录时间和日期，我们可以使用 COUNT() 函数计算每个用户在过去一小时内的登录次数：

SELECT user, login_time, COUNT(*) OVER (PARTITION BY user ORDER BY login_time RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW) AS login_count_last_hour FROM logs;

这将按用户和登录时间对日志进行分区，并为每个用户计算过去一小时内的登录次数。注意，在此示例中，我们使用 RANGE 子句指定窗口的范围。这将计算从当前行开始向前一个小时内的所有行的计数。

综上所述，MySQL 窗口函数可以进行一些比较复杂的数据分析和处理，常见的窗口函数包括 ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG、FIRST_VALUE、LAST_VALUE 和聚合函数等。使用 MySQL 窗口函数需要用到 OVER 子句来定义窗口的边界，包括窗口大小、排序规则、分区等。