MySQL强大的窗口函数，拒绝group by和left join

一、排序窗口函数

• 根据特定的分组、特定的排序，来新增一列序列号。
• 格式和用法的基础可以先看看这篇：MySQL使用排序窗口函数来为表新增一列序列号
• 示例：为每个部门的员工按照工资降序分配一个部门内的排名。

   SELECT
       department_id,
       employee_id,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
   FROM
       employees;

 二、聚合窗口函数

• 就是聚合函数（SUM、AVG、MIN、MAX、COUNT等）在窗口函数中的应用。它们允许在不合并行的情况下，为每行计算聚合值。
• 示例：计算每个部门员工的最高工资。（注意：不进行group by，总行数不变。新增了一列，同一个部门下显示的都是一个值）

   SELECT
       department_id,
       employee_id,
       salary,
       MAX(salary) OVER (PARTITION BY department_id) AS department_max_salary,
   FROM
       employees;

三、其他强大窗口函数

1. LAG(expr [, offset [, default_value ]]) OVER ( [PARTITION BY partition_expression,... ] [ORDER BY order_expression [ASC | DESC],... ] )：访问当前行之前的行的值。可以指定偏移量和默认值。

• 示例：获取每个员工上一个月的工资，如果没有上一个月的数据，则使用默认值 0。

   SELECT
       employee_id,
       month,
       salary,
       LAG(salary, 1, 0) OVER (PARTITION BY employee_id ORDER BY month) AS previous_month_salary
   FROM
       employee_salaries;

2. LEAD(expr [, offset [, default_value ]]) OVER ( [PARTITION BY partition_expression,... ] [ORDER BY order_expression [ASC | DESC],... ] )：访问当前行之后的行的值。可以指定偏移量和默认值。

• 示例：获取每个员工下一个月的工资，如果没有下一个月的数据，则使用默认值 0。

   SELECT
       employee_id,
       month,
       salary,
       LEAD(salary, 1, 0) OVER (PARTITION BY employee_id ORDER BY month) AS next_month_salary
   FROM
       employee_salaries;

3. FIRST_VALUE(expr) OVER ( [PARTITION BY partition_expression,... ] [ORDER BY order_expression [ASC | DESC],... ] [frame_clause] )：返回窗口中第一行的值。

• 示例：获取每个部门中工资最高的员工的工资。（效果同MAX）

   SELECT
       department_id,
       employee_id,
       salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary_in_department
   FROM
       employees;

4. LAST_VALUE(expr) OVER ( [PARTITION BY partition_expression,... ] [ORDER BY order_expression [ASC | DESC],... ] [frame_clause] )：返回窗口中最后一行的值。

• 示例：获取每个部门中工资最低的员工的工资。

   SELECT
       department_id,
       employee_id,
       salary,
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary_in_department
   FROM
       employees;

最后，窗口函数强大的点在于：

1. 不需要复杂的自连接或子查询：例如，要计算每个员工在部门内的工资排名，不需要通过自连接来比较每个员工与其他员工的工资。

2. 不改变原始数据的分组和数据行数：例如，要加一列显示员工所在部门的最高薪资，不需要先分组排序取最大值，再右连接到原表。

PS：要是能早一点知道这些函数，当年有个项目做薪资那块的时候就不会那么憋屈了🙉🙉🙉