MySQL8.0窗口函数概述

• MYSQL 8.0 之后，加入了窗口函数功能，简化了数据分析工作中查询语句的书写

• 在没有窗口函数之前，我们需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数会更加简洁高效

• 优点：简单/快速/多功能性

1、聚合函数

• over()

• 注意事项：

  1. 可以使用<window_function> OVER()，对全部查询结果进行

  2. 聚合计算在WHERE条件执行之后，才会执行窗口函数

  3. 窗口函数在执行聚合计算的同时还保留每行的其它原始信息

  4. 不能在WHERE子句中使用窗口函数

select 
    name,
    salary,
    department_id,
    avg(salary) over()
from employee
where department_id  in (1,2,3);

• over(partition by )

• PARTITION BY与GROUP BY区别

  ① group by是分组函数，partition by是分析函数

  ② 在执行顺序上：from > where > group by > having > order by，而partition by应用在以上关键字之后，可以简单理解为就是在执行完select之后，在所得结果集之上进行partition by分组

  ③ partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），而group by则只保留参与分组的字段和聚合函数的结果（类似excel中的pivot透视表）

• OVER（PARTITION BY x）的工作方式与GROUP BY类似，将x列中，所有值相同的行分到一组中 PARTITON BY 后面可以传入一列数据，也可以是多列（需要用逗号隔开列名）

• 需求：查询每天，每条线路速的最快车速 查询结果包括如下字段：线路ID，日期，车型，相同线路每天的最快车速

SELECT
  journey.id,
  journey.date,
  train.model,
  train.max_speed,
  MAX(max_speed) OVER(PARTITION BY route_id, date)
FROM journey
JOIN train
  ON journey.train_id = train.id;

• 统计每一个员工的姓名，所在部门，薪水，该部门的最低薪水，该部门的最高薪水

2、排序函数

格式：函数（）over（order by XXX）

SELECT
  name,
  platform,
  editor_rating,
  RANK() OVER(ORDER BY editor_rating) as rank_
FROM game;
?

• rank() 重复不连续

• dense_rank()重复连续

• row_number()不重复连续

• ntile(x) 分为几组

3、with函数

• --类似子函数--

• with 临时表名 as ( sql语句 )

  select * from 临时表名 where

  with ranking as(
    select 
      name,
      dense_rank() over(ORDER BY editor_rating desc) as rank_
    from game
  )
  select name,
   rank_
  from ranking
  where rank_=3;

•  

MySQL8.0----窗口函数