MySQL 8.0的新特性包括：

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

1. 問題

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

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

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

建一個(gè)簡(jiǎn)單的電影信息表，字段有：

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

插入測(cè)試數(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）查詢需求

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

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

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

我們可以使用子查詢來(lái)計(jì)算各年的平均分，然后使用 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

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

2. window functions 的解決方案

什么是 window functions

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

語(yǔ)法示例：

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

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

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

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

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ù)庫(kù)把結(jié)果數(shù)據(jù)集合分割成更小的部分，把 release_year 相同的放在一起，函數(shù) AVG(rating) 會(huì)對(duì)每個(gè)窗口數(shù)據(jù)進(jìn)行計(jì)算，然后把結(jié)果放到每一行中

查詢示例1

計(jì)算每部電影在所屬年份中的評(píng)分排行位置

查詢語(yǔ)句

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 來(lái)根據(jù) release_year 劃分窗口，使用 ORDER BY 在窗口內(nèi)排序

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

查詢結(jié)果

查詢示例2

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

查詢語(yǔ)句

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

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

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

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

查詢結(jié)果

3. 小結(jié)

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

最新評(píng)論