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)

 案例1: 汇总 员工 每年都的销售总额

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)

案例2:  按员工分组,  其他字段排序后 取前几条  

select employeeId,xxx from (
    select w.*,RANK() OVER (PARTITION  BY employeeId order by effectiveDate desc,completedDate desc) as rk 
    from tb001 w where effectiveDate <='2024-08-01' and payGroup is not null and status ='Successfully'
) t
where t.rk<2 and t.employeeId='S00670';

案例2: 使用普通sql  (不完全 =上面的sql) 

select * from (



SELECT 
    w.*,
    @rank := IF(@current_employee = employeeId, @rank + 1, 1) AS rk,
    @current_employee := employeeId
FROM 
    tb001 w,
    (SELECT @rank := 0, @current_employee := NULL) r
where employeeId  ='S71366'
group by 
employeeId 
order by effectiveDate desc,completedDate desc



) t where rk=1
    

案例3: 查询 递归查询 每一条记录都 endDate, 

(比如有多个 任务, 每个任务只记录了开始时间,  结束时间是下一条记录都开始时间) 这 可以递归计算出每条记录都 结束时间.

select employeeId, startDate, endDate from (
              SELECT a.employeeId,a.effectiveDate startDate,
              LEAD(a.effectiveDate, 1,  DATE ? )  OVER (PARTITION BY a.employeeId,a.isIAPosition ORDER BY a.effectiveDate,a.completedDate) AS endDate 
                  FROM tb001 a where a.status ='Successfully' and a.effectiveDate <= ? 
              ) t where endDate between ? and ? 

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