MySQL 开窗函数

MySQL开窗函数是一种高级的SQL函数，它提供了一种计算聚合值、将结果分组并对组内数据进行排序等功能的方式。我们可以使用它来执行复杂的分析和计算操作，例如：排名、分组百分比、累积和和均值、获取上/下行记录等。

下面是使用MySQL开窗函数的完整操作步骤：

1. 创建测试数据

在开始使用MySQL开窗函数之前，首先需要创建一些测试数据，这样我们才能更好地理解其用法。我们可以使用以下命令创建一个名为employees的表，并插入一些示例数据。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  department_id INT,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
  (1, 1, 'John Doe', 5000),
  (2, 2, 'Jane Doe', 6000),
  (3, 2, 'Bob Smith', 5500),
  (4, 3, 'Mary Johnson', 7000),
  (5, 1, 'Joe Black', 4000),
  (6, 3, 'Tom White', 6500),
  (7, 1, 'Bill Green', 4500),
  (8, 2, 'Sarah Lee', 7500),
  (9, 1, 'Dave Brown', 3500);

2. 了解开窗函数语法

MySQL开窗函数的语法与常规函数类似，使用“OVER”子句来指定分析操作。基本语法格式如下：

WINDOW_FUNCTION_NAME ([arguments]) OVER (
  [PARTITION BY partition_expression, ... ] 
  [ORDER BY order_expression [ASC|DESC], ... ] 
  [FRAME_TYPE frame_units
    { UNBOUNDED PRECEDING 
      | value PRECEDING 
      | CURRENT ROW 
      | value FOLLOWING 
      | UNBOUNDED FOLLOWING }
  ]
)

其中，WINDOW_FUNCTION_NAME 指明所使用的开窗函数名称；arguments 则是对函数进行计算所需的一个或多个参数；PARTITION BY（可选）定义了分组标准；ORDER BY（可选）用于指定排序顺序；FRAME_TYPE（可选）则是用于定义窗口大小和位置的窗口规范。

3. 求出每个部门薪水排名

下面我们将使用“ROW_NUMBER()”函数来表示每个员工在其部门中的排名。首先，我们需要用“PARTITION BY”子句将数据分组为多个部门，然后对每个部门内的员工按照薪水进行排序，最终使用“ROW_NUMBER()”来分配排名。

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

结果如下：

+----+---------------+--------------+--------+------+
| id | department_id | name         | salary | rank |
+----+---------------+--------------+--------+------+
|  4 |             3 | Mary Johnson |   7000 |    1 |
|  6 |             3 | Tom White    |   6500 |    2 |
|  8 |             2 | Sarah Lee    |   7500 |    1 |
|  2 |             2 | Jane Doe     |   6000 |    2 |
|  3 |             2 | Bob Smith    |   5500 |    3 |
|  1 |             1 | John Doe     |   5000 |    1 |
|  7 |             1 | Bill Green   |   4500 |    2 |
|  5 |             1 | Joe Black    |   4000 |    3 |
|  9 |             1 | Dave Brown   |   3500 |    4 |
+----+---------------+--------------+--------+------+

4. 求出每个部门薪水排名占比

下面我们将使用“RANK()”函数来计算每个员工在其部门中的排名，并通过窗口函数计算每个部门中每个员工工资排名占比。我们需要使用以下语句：

SELECT
  id, department_id, name, salary,
  RANK() OVER (
    PARTITION BY department_id ORDER BY salary DESC
  ) AS rank,
  ROUND(
    (100 * RANK() OVER (
              PARTITION BY department_id ORDER BY salary DESC
            ) / COUNT(*) OVER (PARTITION BY department_id)
    )
  ) AS rank_percent
FROM
  employees;

该查询使用了“RANK()”函数来计算每个员工在其部门中的排名。“COUNT(*) OVER (PARTITION BY department_id)”计算了每个部门中的员工总数。最后，我们使用窗口函数来计算每个员工在部门中的排名占比。

结果如下：

+----+---------------+--------------+--------+------+--------------+
| id | department_id | name         | salary | rank | rank_percent |
+----+---------------+--------------+--------+------+--------------+
|  4 |             3 | Mary Johnson |   7000 |    1 |           50 |
|  6 |             3 | Tom White    |   6500 |    2 |           50 |
|  8 |             2 | Sarah Lee    |   7500 |    1 |           33 |
|  2 |             2 | Jane Doe     |   6000 |    2 |           22 |
|  3 |             2 | Bob Smith    |   5500 |    3 |           44 |
|  1 |             1 | John Doe     |   5000 |    1 |           50 |
|  7 |             1 | Bill Green   |   4500 |    2 |           25 |
|  5 |             1 | Joe Black    |   4000 |    3 |           25 |
|  9 |             1 | Dave Brown   |   3500 |    4 |           25 |
+----+---------------+--------------+--------+------+--------------+

至此，我们已经学会了使用MySQL开窗函数计算排名和占比等操作。通过使用窗口规范功能，我们可以在SQL中执行高级数据分析，从而更好地理解和研究数据。