MySql 窗口函数 PARTITION BY

从version 8.0开始，MySQL支持在查询中使用窗口函数。这篇文章是对一篇英文资料的不完全翻译，加上自己的一些理解。

文中的示例用到的建表语句和插值语句如下：

CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);
 
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
    sales;

+-------------+----------------+--------+-------------+
| fiscal_year | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      450.00 |
|        2016 | Bob            | 100.00 |      450.00 |
|        2016 | John           | 200.00 |      450.00 |
|        2017 | Alice          | 100.00 |      400.00 |
|        2017 | Bob            | 150.00 |      400.00 |
|        2017 | John           | 150.00 |      400.00 |
|        2018 | Alice          | 200.00 |      650.00 |
|        2018 | Bob            | 200.00 |      650.00 |
|        2018 | John           | 250.00 |      650.00 |
+-------------+----------------+--------+-------------+
9 rows in set (0.00 sec)

 

SELECT 
   fiscal_year, 
   sales_employee,
   sale,
   SUM(sale) OVER (PARTITION BY sales_employee 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_sales
FROM
   sales;

 

+-------------+----------------+--------+-------------+
| fiscal_year | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      150.00 |
|        2017 | Alice          | 100.00 |      250.00 |
|        2018 | Alice          | 200.00 |      450.00 |
|        2016 | Bob            | 100.00 |      100.00 |
|        2017 | Bob            | 150.00 |      250.00 |
|        2018 | Bob            | 200.00 |      450.00 |
|        2016 | John           | 200.00 |      200.00 |
|        2017 | John           | 150.00 |      350.00 |
|        2018 | John           | 250.00 |      600.00 |
+-------------+----------------+--------+-------------+
9 rows in set (0.00 sec)

原文: https://blog.csdn.net/m0_38063172/article/details/