MySQL 中 Over 函数的使用及示例说明（mysql 中over）

MySQL 中 Over 函数的使用及示例说明

MySQL 中的 over 函数是一种强大的分析函数，它可以让我们在一个查询中执行多个计算，而不是在不同的查询中进行。通过 over 函数，我们可以轻松地实现计算平均值、总和、最大值、最小值以及其他统计量等操作。本文将介绍 over 函数的使用及示例说明，帮助读者更好的掌握这一技术。

一、over 函数语法

over 函数的语法如下：

[ aggregate_function ] OVER (
    [ PARTITION BY partition_expression1, partition_expression2, ... ]
    [ ORDER BY sort_expression1 [ ASC | DESC ], sort_expression2, ... ]
    [  ]
)

其中，aggregate_function 指的是聚合函数，例如 COUNT、SUM、MAX、MIN 等，partition_expression 指的是分区表达式，sort_expression 指的是排序表达式。Window_frame_clause 可选，指定窗口大小的表达式，例如 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING。

二、over 函数示例

下面的示例将演示如何在 MySQL 中使用 over 函数。

1. 计算销售额排名

假设我们有一个 sales 表，记录每个地区的销售额，我们想要查询出每个地区的销售额排名。我们可以使用 RANK 函数和 over 函数来实现：

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

执行该语句后，我们可以得到一个包含排名信息的结果集：

+--------+--------+------+
| region | amount | rank |
+--------+--------+------+
| east   | 50000  | 1    |
| west   | 40000  | 2    |
| south  | 30000  | 3    |
| north  | 20000  | 4    |
+--------+--------+------+

2. 计算分区内的求和

假设我们有一个 orders 表，记录了每个用户每天的订单金额。我们想要计算每个用户的每天订单总金额，以及每个用户的所有订单总金额。我们可以使用 SUM 函数和 over 函数进行计算：

SELECT user, order_date, amount, 
       SUM(amount) OVER (PARTITION BY user, order_date) AS dly_total, 
       SUM(amount) OVER (PARTITION BY user) AS total
FROM orders;

执行该语句后，我们可以得到每个用户的每天订单总金额和所有订单总金额：

+------+------------+--------+-------------+-------+
| user | order_date | amount | dly_total | total |
+------+------------+--------+-------------+-------+
| tom  | 2019-01-01 | 100    | 150         | 250   |
| tom  | 2019-01-01 | 50     | 150         | 250   |
| tom  | 2019-01-02 | 200    | 300         | 250   |
| jim  | 2019-01-01 | 80     | 100         | 240   |
| jim  | 2019-01-02 | 120    | 220         | 240   |
+------+------------+--------+-------------+-------+

在上述语句中，我们使用 PARTITION BY 子句将用户和订单日期作为分区条件，计算每个用户的每天订单总金额；然后使用 SUM 函数和 over 函数计算每个用户的所有订单总金额。

3. 计算移动平均值

假设我们有一个 stocks 表，记录了每个股票每天的收盘价。我们想要计算每个股票的移动平均值，以便分析股票的趋势。我们可以使用 AVG 函数和 over 函数进行计算：

SELECT stock_id, date, price, 
       AVG(price) OVER (PARTITION BY stock_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM stocks;

执行该语句后，我们可以得到每个股票的移动平均值：

+---------+------------+-------+------------+
| stock_id | date       | price | moving_avg |
+---------+------------+-------+------------+
| 1       | 2019-01-01 | 10    | 10         |
| 1       | 2019-01-02 | 15    | 12.5       |
| 1       | 2019-01-03 | 20    | 15         |
| 1       | 2019-01-04 | 25    | 20         |
| 1       | 2019-01-05 | 30    | 25         |
| 2       | 2019-01-01 | 8     | 8          |
| 2       | 2019-01-02 | 12    | 10         |
| 2       | 2019-01-03 | 16    | 12         |
| 2       | 2019-01-04 | 20    | 16         |
| 2       | 2019-01-05 | 24    | 20         |
+---------+------------+-------+------------+

在上述语句中，我们使用 ORDER BY 子句按日期排序，然后使用 ROWS BETWEEN 子句指定计算窗口大小为两行，计算每个股票的移动平均值。

三、总结

本文介绍了 MySQL 中 over 函数的使用及示例说明。通过 over 函数，我们可以轻松地实现计算平均值、总和、最大值、最小值以及其他统计量等操作。需要注意的是，在使用 over 函数时，要注意分区条件和排序条件的设置，以便得到正确的结果。