mysql窗口函数（over()、排序函数、with语句）

窗口函数：

seleect 聚合函数 over(partition by_______ order by ________)from table;

基本语法:

<window_function>over(…)

优点：

• 简单

  窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。

• 快速

  这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。

• 多功能性

  最重要的是，窗口函数具有多种功能，比如，添加移动平均线，添加行号和滞后数据，等等。

over子句基础用法：

over():

SELECT
  first_name,
  last_name,
  salary,  
  AVG(salary) OVER()
FROM employee;

需要注意：窗口函数在WHERE 子句后执行！

小结：

• 可以使用<window_function> OVER()，对全部查询结果进行聚合计算
• 在WHERE条件执行之后，才会执行窗口函数
• 窗口函数在执行聚合计算的同时还可以保留每行的其它原始信息
• 不能在WHERE子句中使用窗口函数

over（partition by）的使用：

基本语法：

<window_function> OVER (PARTITION BY column1, column2 … column_n)

partition by与order 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透视表）

SELECT
  id,
  model,
  first_class_places,
  SUM(first_class_places) OVER (PARTITION BY model)
FROM train;

排序函数：

基本语法：

RANK() OVER (ORDER BY …)

与oredr by的区别：

• RANK()会返回每一行的等级（序号）
• ORDER BY对行进行排序将数据按升序或降序排列
• RANK（）OVER（ORDER BY ...）是一个函数，与ORDER BY 配合返回序号

rank():

SELECT
  name,
  genre,
  updated,
  RANK() OVER(ORDER BY updated) as date_rank
FROM game;

DENSE_RANK():

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

ROW_NUMBER():

SELECT
  name,
  platform,
  editor_rating,
  ROW_NUMBER() OVER(ORDER BY editor_rating) `row_number`
FROM game;

NTILE(x):

NTILE(X)函数将数据分成X组，并给每组分配一个数字（1，2，3…)，例如：

SELECT
  name,
  genre,
  editor_rating,
  NTILE(3) OVER (ORDER BY editor_rating DESC)
FROM game;

with语句：

WITH ranking AS
  (SELECT
    RANK() OVER (ORDER BY col2) AS RANK,
    col1
  FROM table_name)

SELECT col1
FROM ranking
WHERE RANK = place1;