MySQL窗口函数的具体使用

MySQL窗口函数（Window Functions）是一种功能强大的MySQL特性，当您需要在查询中进行聚合分析等复杂操作时，它可以帮助您更加高效地完成查询。

窗口函数的语法

在MySQL中，我们可以使用以下标准SQL语法来使用窗口函数：

<窗口函数> OVER ([PARTITION BY <partition列>]
                 [ORDER BY <order列> [ASC|DESC]]
                 [ROWS <N> {PRECEDING|FOLLOWING}|RANGE...])

在此语法中，窗口函数用于对分组的行进行计算，而PARTITION BY用于指定窗口函数的分组依据，即对哪些列进行分组计算；ORDER BY用于对窗口内的行进行排序，以保证窗口函数的正确性；ROWS用于指定相对于当前行的窗口范围。

窗口函数的示例

以下是几个示例，帮助您更好地理解窗口函数的用法：

示例一：使用窗口函数进行分组汇总

假设我们有如下的一张销售订单表：

CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE,
  customer_name VARCHAR(50),
  total_sales DECIMAL(10, 2)
);

INSERT INTO sales (order_date, customer_name, total_sales)
VALUES ('2021-05-01', 'John', 20),
       ('2021-05-01', 'Mary', 30),
       ('2021-05-02', 'John', 40),
       ('2021-05-02', 'Mary', 50),
       ('2021-05-03', 'John', 10),
       ('2021-05-03', 'Mary', 5);

如果我们需要按照每个客户的销售额排名，并计算出每个客户的销售额在所有客户销售额中的占比百分比，可以使用如下的SQL语句：

SELECT 
  customer_name, 
  total_sales, 
  ROUND(total_sales / SUM(total_sales) OVER(), 2) AS sales_pct 
FROM sales 
ORDER BY total_sales DESC;

在这个SQL语句中，我们使用了SUM窗口函数和ROUND函数进行求和计算，并使用了OVER子句来分组计算每个客户的总销售额，最终得到了一个按照销售额排名的结果集：

customer_name	total_sales	sales_pct
Mary	50	0.44
John	70	0.56

示例二：使用窗口函数进行滑动平均计算

假设我们有如下的一张气温数据表：

CREATE TABLE temperature (
  id INT AUTO_INCREMENT PRIMARY KEY,
  measure_time TIMESTAMP,
  temperature FLOAT
);

INSERT INTO temperature (measure_time, temperature)
VALUES 
  ('2021-05-01 00:00:00', 30),
  ('2021-05-01 01:00:00', 32),
  ('2021-05-01 02:00:00', 34),
  ('2021-05-01 03:00:00', 33),
  ('2021-05-01 04:00:00', 30),
  ('2021-05-01 05:00:00', 29),
  ('2021-05-01 06:00:00', 28);

如果我们需要计算出每个时间点的3小时滑动平均温度，可以使用如下的SQL语句：

SELECT 
  measure_time, temperature, 
  AVG(temperature) OVER(ORDER BY measure_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sliding_avg 
FROM temperature;

在这个SQL语句中，我们使用了AVG窗口函数和ROWS子句来计算3小时滑动平均温度，最终得到了一个包含每个时间点的温度和滑动平均温度的结果集：

measure_time	temperature	sliding_avg
2021-05-01 00:00:00	30.0	30.0
2021-05-01 01:00:00	32.0	31.0
2021-05-01 02:00:00	34.0	32.0
2021-05-01 03:00:00	33.0	33.0
2021-05-01 04:00:00	30.0	32.3
2021-05-01 05:00:00	29.0	30.7
2021-05-01 06:00:00	28.0	29.0