说说mysql窗口函数

MySQL 的窗口函数（Window Functions）是一种非常强大的功能，它允许你在查询中执行复杂的计算，如累积总和、排名、行数等，而不必使用复杂的子查询或连接操作。窗口函数通常用于数据分析领域，可以大大简化 SQL 查询。

MySQL 支持的窗口函数

MySQL 支持以下几种主要的窗口函数：

1. 排名函数:

   • ROW_NUMBER(): 返回行号。
   • RANK(): 返回行的排名，如果有相同的值，则排名会跳过。
   • DENSE_RANK(): 返回行的排名，如果有相同的值，则排名不会跳过。

2. 偏移函数:

   • LAG(): 返回当前行前一行的值。
   • LEAD(): 返回当前行后一行的值。

3. 聚合函数:

   • SUM(): 计算累计和。
   • AVG(): 计算平均值。
   • COUNT(): 计算行数。
   • MIN(): 计算最小值。
   • MAX(): 计算最大值。

窗口函数的语法

窗口函数的语法通常如下：

function_name(window_specification)

其中 function_name 是窗口函数的名称，window_specification 是窗口规格，可以是以下形式：

• 无窗口规格:

  • 如果省略 OVER 子句，则默认为在整个结果集上计算。

• 指定窗口规格:

  • OVER ( [partition_by_clause] [order_by_clause] [frame_clause] )

窗口规格

• PARTITION BY: 将数据分成不同的分区，并在每个分区内独立计算窗口函数。
• ORDER BY: 确定每个分区内的行排序。
• ROWS BETWEEN 或 RANGE BETWEEN: 定义窗口帧，即计算窗口函数时使用的行范围。

示例

假设我们有一个销售表 sales，包含以下字段：sale_id, product_id, sale_date, amount。

示例 1: 计算累计销售额

SELECT sale_id, product_id, sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

这里 SUM(amount) OVER (ORDER BY sale_date) 计算了每个销售日期之前的累计销售额。

示例 2: 计算产品排名

SELECT sale_id, product_id, sale_date, amount,
       RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS rank
FROM sales;

这里 RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) 为每个产品的销售额按降序排名。

示例 3: 计算每个产品的销售额百分比

SELECT sale_id, product_id, sale_date, amount,
       amount / SUM(amount) OVER (PARTITION BY product_id) AS percentage_of_total
FROM sales;

这里 SUM(amount) OVER (PARTITION BY product_id) 计算了每个产品的总销售额，然后通过除以当前行的销售额来得到百分比。

示例 4: 使用 LAG 和 LEAD 函数

SELECT sale_id, product_id, sale_date, amount,
       LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_amount,
       LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

这里 LAG(amount, 1) OVER (ORDER BY sale_date) 和 LEAD(amount, 1) OVER (ORDER BY sale_date) 分别获取当前行前一行和后一行的销售额。

示例 5: 使用 ROW_NUMBER()

SELECT sale_id, product_id, sale_date, amount,
       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

这里 ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) 为每个产品的每笔销售按销售日期排序分配行号。

总结

窗口函数是 MySQL 中一个非常强大的特性，它可以帮助你执行复杂的计算，而无需使用复杂的子查询或连接操作。通过使用窗口函数，你可以轻松地执行各种数据分析任务，如计算累计和、排名、百分比等。掌握窗口函数的使用可以大大提高你的 SQL 查询能力。