Mysql 8 新特性 window functions 的作用

MySQL 8.0的新特性包括：

• 對Unicode 9.0的開箱即用的完整支持
• 支持窗口函數(shù)和遞歸SQL語法，這在以往是不可能或者很難才能編寫這樣的查詢語句
• 對原生JSON數(shù)據(jù)和文檔存儲功能的增強(qiáng)支持
• MySQL 8.0的發(fā)布，跳過了多個版本號（從5.5開始），由于6.0修改和7.0是用來保留做MySQL的集群版本，因此采用了8.0的版本號。

1. 問題

Mysql 8.0.2 中新增加了一個主要功能 - 窗口函數(shù) window function

這個功能具體是解決什么問題？下面先看一個SQL查詢的場景，看一下平時我們是怎么做的，然后再看一下如何使用窗口函數(shù)來更方便的解決

（1）準(zhǔn)備測試表和數(shù)據(jù)

建一個簡單的電影信息表，字段有：

ID
release_year（發(fā)行年份）
category_id（所屬分類ID）
rating（評分）
CREATE TABLE films (
 id int(11),
 release_year int(11),
 category_id int(11),
 rating decimal(3,2)
)

插入測試數(shù)據(jù)

insert into films2 values
(1,2015,1,8.00),
(2,2015,2,8.50),
(3,2015,3,9.00),
(4,2016,2,8.20),
(5,2016,1,8.40),
(6,2017,2,7.00);

整體形式如下

（2）查詢需求

查詢每一年中的平均評分，要求每條記錄后面都顯示當(dāng)年的平均評分

例如 2015 年，有3條記錄，8.00, 8.50, 9.00 的平均分是 8.5，2016年有2條記錄，平均分是 8.3，2017年有1條記錄，平均分為 7.00

最終結(jié)果的形式如下：

我們可以使用子查詢來計算各年的平均分，然后使用 join 把結(jié)果連接回去

SELECT
 f.id, f.release_year, 
 f.rating, years.year_avg
FROM films f
LEFT JOIN (
 SELECT f.release_year, 
  AVG(rating) AS year_avg
 FROM films f
 GROUP BY f.release_year
) years 
ON f.release_year = years.release_year

是不是有點復(fù)雜，下面看下窗口函數(shù)的處理方式

2. window functions 的解決方案

什么是 window functions

window functions 是對一組數(shù)據(jù)進(jìn)行計算，與使用 group by 時不同，不會進(jìn)行單行的結(jié)果輸出，而是與每條記錄相關(guān)聯(lián)

語法示例：

SELECT
function_name OVER ( window_definition )
FROM (...)

window_definition 是定義要計算的記錄集合，就像是一個小窗口，在整體數(shù)據(jù)集合上顯示出一部分

function_name 指定了對于窗口中的數(shù)據(jù)集合執(zhí)行什么計算

回頭看下上面的那個查詢，需要計算每部電影所在年份的所有電影平均評分，下面使用窗口函數(shù)來處理

SELECT
 f.id, f.release_year, 
 f.category_id, f.rating,
 AVG(rating) OVER 
 (PARTITION BY release_year) AS year_avg
FROM films f

window_definition 部分使用了 PARTITION BY 從句，它告訴數(shù)據(jù)庫把結(jié)果數(shù)據(jù)集合分割成更小的部分，把 release_year 相同的放在一起，函數(shù) AVG(rating) 會對每個窗口數(shù)據(jù)進(jìn)行計算，然后把結(jié)果放到每一行中

查詢示例1

計算每部電影在所屬年份中的評分排行位置

查詢語句

SELECT
 f.id, f.release_year, 
 f.category_id, f.rating,
 RANK() OVER (PARTITION BY release_year 
  ORDER BY rating DESC) AS year_rank
FROM films f

window_definition 部分使用 PARTITION BY 來根據(jù) release_year 劃分窗口，使用 ORDER BY 在窗口內(nèi)排序

RANK() 函數(shù)可以返回一行數(shù)據(jù)在本窗口中的位置

查詢結(jié)果

查詢示例2

查看每部電影在總排行榜中的位置

查詢語句

SELECT
 f.id, f.release_year, 
 f.category_id, f.rating,
 RANK() OVER (ORDER BY rating DESC) 
  AS general_rank
FROM films f order by id

主語句中的 order by 保證了整個數(shù)據(jù)集的排序

window_definition 中沒有使用 PARTITION BY，那么就是把整個結(jié)果集合當(dāng)做一個窗口，ORDER BY 對窗口中的數(shù)據(jù)根據(jù) rating 做降序排序，把得分最高的放前面

RANK() 函數(shù)取得每條記錄在窗口中的位置

查詢結(jié)果

3. 小結(jié)

窗口函數(shù)是 Mysql 8.0.2 中的高級特性，可以方便的執(zhí)行聚合計算，而不用對結(jié)果集進(jìn)行實際的聚合，大大增加了靈活性、可讀性，更便于維護(hù)