MySQL8.0新特性-窗口函數(shù)

MySQL8.0新特性：窗口函數(shù)

1. 使用窗口函數(shù)前后對比

假設我現(xiàn)在有這樣一個數(shù)據(jù)表，它顯示了某購物網(wǎng)站在每個城市每個區(qū)的銷售額：

CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);
INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝陽',20.00),
('上海','黃埔',30.00),
('上海','長寧',10.00);

查詢：

mysql> SELECT * FROM sales;
+----+------+--------+-------------+
| id | city | county | sales_value |
+----+------+--------+-------------+
| 1 | 北京 | 海淀 | 10 |
| 2 | 北京 | 朝陽 | 20 |
| 3 | 上海 | 黃埔 | 30 |
| 4 | 上海 | 長寧 | 10 |
+----+------+--------+-------------+
4 rows in set (0.00 sec)

**需求：**現(xiàn)在計算這個網(wǎng)站在每個城市的銷售總額立润、在全國的銷售總額宇植、每個區(qū)的銷售額占所在城市銷售額中的比率卷胯，以及占總銷售額中的比率。如果用分組和聚合函數(shù)验辞，就需要分好幾步來計算甲喝。

第一步虏两，計算總銷售金額愧旦，并存入臨時表 a：

CREATE TEMPORARY TABLE a -- 創(chuàng)建臨時表
SELECT SUM(sales_value) AS sales_value -- 計算總計金額
FROM sales;

查看一下臨時表 a ：

mysql> SELECT * FROM a;
+-------------+
| sales_value |
+-------------+
| 70 |
+-------------+
1 row in set (0.00 sec)

第二步，計算每個城市的銷售總額并存入臨時表 b：

CREATE TEMPORARY TABLE b -- 創(chuàng)建臨時表
SELECT city,SUM(sales_value) AS sales_value -- 計算城市銷售合計
FROM sales
GROUP BY city;

查看臨時表 b ：

mysql> SELECT * FROM b;
+------+-------------+
| city | sales_value |
+------+-------------+
| 北京 | 30 |
| 上海 | 40 |
+------+-------------+
2 rows in set (0.00 sec)

第三步定罢，計算各區(qū)的銷售占所在城市的總計金額的比例忘瓦，和占全部銷售總計金額的比例。我們可以通過下面的連接查詢獲得需要的結果：

mysql> SELECT s.city AS 城市,s.county AS 區(qū),s.sales_value AS 區(qū)銷售額,
 b.sales_value AS 市銷售額,s.sales_value/b.sales_value AS 市比率,
 a.sales_value AS 總銷售額,s.sales_value/a.sales_value AS 總比率
 FROM sales s
 JOIN b ON (s.city=b.city) -- 連接市統(tǒng)計結果臨時表
 JOIN a -- 連接總計金額臨時表
 ORDER BY s.city,s.county;
+------+------+----------+----------+--------+----------+--------+
| 城市 | 區(qū) | 區(qū)銷售額 | 市銷售額 | 市比率 | 總銷售額 | 總比率 |
+------+------+----------+----------+--------+----------+--------+
| 上海 | 長寧 | 10 | 40 | 0.2500 | 70 | 0.1429 |
| 上海 | 黃埔 | 30 | 40 | 0.7500 | 70 | 0.4286 |
| 北京 | 朝陽 | 20 | 30 | 0.6667 | 70 | 0.2857 |
| 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |
+------+------+----------+----------+--------+----------+--------+
4 rows in set (0.00 sec)

結果顯示：市銷售金額引颈、市銷售占比、總銷售金額境蜕、總銷售占比都計算出來了蝙场。

同樣的查詢，如果用窗口函數(shù)粱年，就簡單多了售滤。我們可以用下面的代碼來實現(xiàn)：

mysql> SELECT city AS 城市,county AS 區(qū),sales_value AS 區(qū)銷售額,
 SUM(sales_value) OVER(PARTITION BY city) AS 市銷售額, -- 計算市銷售額
 sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
 SUM(sales_value) OVER() AS 總銷售額, -- 計算總銷售額
 sales_value/SUM(sales_value) OVER() AS 總比率
 FROM sales
 ORDER BY city,county;
+------+------+----------+----------+--------+----------+--------+
| 城市 | 區(qū) | 區(qū)銷售額 | 市銷售額 | 市比率 | 總銷售額 | 總比率 |
+------+------+----------+----------+--------+----------+--------+
| 上海 | 長寧 | 10 | 40 | 0.2500 | 70 | 0.1429 |
| 上海 | 黃埔 | 30 | 40 | 0.7500 | 70 | 0.4286 |
| 北京 | 朝陽 | 20 | 30 | 0.6667 | 70 | 0.2857 |
| 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |
+------+------+----------+-----------+--------+----------+--------+
4 rows in set (0.00 sec)

結果顯示，我們得到了與上面那種查詢同樣的結果台诗。

使用窗口函數(shù)完箩，只用了一步就完成了查詢。而且拉队，由于沒有用到臨時表弊知，執(zhí)行的效率也更高了。很顯然粱快，在這種需要用到分組統(tǒng)計的結果對每一條記錄進行計算的場景下秩彤，使用窗口函數(shù)更好叔扼。

2. 窗口函數(shù)的分類

MySQL從8.0版本開始支持窗口函數(shù)。窗口函數(shù)的作用類似于在查詢中對數(shù)據(jù)進行分組漫雷，不同的是瓜富，分組操作會把分組的結果聚合成一條記錄，而窗口函數(shù)是將結果置于每一條數(shù)據(jù)記錄中降盹。窗口函數(shù)可以分為 靜態(tài)窗口函數(shù) 和 動態(tài)窗口函數(shù) 与柑。

靜態(tài)窗口函數(shù)的窗口大小是固定的，不會因為記錄的不同而不同蓄坏；
動態(tài)窗口函數(shù)的窗口大小會隨著記錄的不同而變化价捧。

MySQL官方網(wǎng)站窗口函數(shù)的網(wǎng)址為 **https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number**。

窗口函數(shù)總體上可以分為序號函數(shù)剑辫、分布函數(shù)干旧、前后函數(shù)、首尾函數(shù)和其他函數(shù)妹蔽，如下表：

[圖片上傳失敗...(image-3fcda6-1713621400415)]

3. 語法結構

窗口函數(shù)的語法結構是：

函數(shù) OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

或者是：

函數(shù) OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

OVER 關鍵字指定函數(shù)窗口的范圍椎眯。
- 如果省略后面括號中的內容，則窗口會包含滿足WHERE條件的所有記錄胳岂，窗口函數(shù)會基于所有滿足WHERE條件的記錄進行計算编整。
- 如果OVER關鍵字后面的括號不為空，則可以使用如下語法設置窗口乳丰。
窗口名：為窗口設置一個別名掌测，用來標識窗口。
PARTITION BY子句：指定窗口函數(shù)按照哪些字段進行分組产园。分組后汞斧，窗口函數(shù)可以在每個分組中分別執(zhí)行。
ORDER BY子句：指定窗口函數(shù)按照哪些字段進行排序什燕。執(zhí)行排序操作使窗口函數(shù)按照排序后的數(shù)據(jù)記錄的順序進行編號粘勒。
FRAME子句：為分區(qū)中的某個子集定義規(guī)則，可以用來作為滑動窗口使用屎即。

4. 分類講解

創(chuàng)建表：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
);

添加數(shù)據(jù)：

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女裝/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '連衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '衛(wèi)衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '牛仔褲', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '呢絨外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '戶外運動', '自行車', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '戶外運動', '山地自行車', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '戶外運動', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '戶外運動', '騎行裝備', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '戶外運動', '運動外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '戶外運動', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

下面針對goods表中的數(shù)據(jù)來驗證每個窗口函數(shù)的功能庙睡。

1. 序號函數(shù)

1．ROW_NUMBER()函數(shù)

ROW_NUMBER()函數(shù)能夠對數(shù)據(jù)中的序號進行順序顯示。

舉例：查詢 goods 數(shù)據(jù)表中每個商品分類下價格降序排列的各個商品信息技俐。

mysql> SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num, id, category_id, category, NAME, price, stock
FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 戶外運動 | 山地自行車 | 1399.90 | 2500 |
| 2 | 11 | 2 | 戶外運動 | 運動外套 | 799.90 | 500 |
| 3 | 12 | 2 | 戶外運動 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 戶外運動 | 自行車 | 399.90 | 1000 |
| 5 | 10 | 2 | 戶外運動 | 騎行裝備 | 399.90 | 3500 |
| 6 | 9 | 2 | 戶外運動 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)

舉例：查詢 goods 數(shù)據(jù)表中每個商品分類下價格最高的3種商品信息乘陪。

mysql> SELECT *
 FROM (
 SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,
 id, category_id, category, NAME, price, stock
 FROM goods) t
 WHERE row_num <= 3;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 1 | 8 | 2 | 戶外運動 | 山地自行車 | 1399.90 | 2500 |
| 2 | 11 | 2 | 戶外運動 | 運動外套 | 799.90 | 500 |
| 3 | 12 | 2 | 戶外運動 | 滑板 | 499.90 | 1200 |
+---------+----+-------------+---------------+------------+----------+-------+
6 rows in set (0.00 sec)

在名稱為“女裝/女士精品”的商品類別中，有兩款商品的價格為89.90元雕擂，分別是衛(wèi)衣和牛仔褲啡邑。兩款商品的序號都應該為2，而不是一個為2井赌，另一個為3谣拣。此時募寨，可以使用RANK()函數(shù)和DENSE_RANK()函數(shù)解決。

2．RANK()函數(shù)

使用RANK()函數(shù)能夠對序號進行并列排序森缠，并且會跳過重復的序號拔鹰，比如序號為1、1贵涵、3列肢。

舉例：使用RANK()函數(shù)獲取 goods 數(shù)據(jù)表中各類別的價格從高到低排序的各商品信息。

mysql> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
 id, category_id, category, NAME, price, stock
 FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 戶外運動 | 山地自行車 | 1399.90 | 2500 |
| 2 | 11 | 2 | 戶外運動 | 運動外套 | 799.90 | 500 |
| 3 | 12 | 2 | 戶外運動 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 戶外運動 | 自行車 | 399.90 | 1000 |
| 4 | 10 | 2 | 戶外運動 | 騎行裝備 | 399.90 | 3500 |
| 6 | 9 | 2 | 戶外運動 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)

舉例：使用RANK()函數(shù)獲取 goods 數(shù)據(jù)表中類別為“女裝/女士精品”的價格最高的4款商品信息宾茂。

mysql> SELECT *
 FROM(
 SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
 id, category_id, category, NAME, price, stock
 FROM goods) t
 WHERE category_id = 1 AND row_num <= 4;
+---------+----+-------------+---------------+----------+--------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+----------+--------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
+---------+----+-------------+---------------+----------+--------+-------+
4 rows in set (0.00 sec)

可以看到瓷马，使用RANK()函數(shù)得出的序號為1、2跨晴、2欧聘、4，相同價格的商品序號相同端盆，后面的商品序號是不連續(xù)的怀骤，跳過了重復的序號。

3．DENSE_RANK()函數(shù)

DENSE_RANK()函數(shù)對序號進行并列排序焕妙，并且不會跳過重復的序號蒋伦，比如序號為1、1焚鹊、2痕届。

舉例：使用DENSE_RANK()函數(shù)獲取 goods 數(shù)據(jù)表中各類別的價格從高到低排序的各商品信息。

mysql> SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,
 id, category_id, category, NAME, price, stock
 FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 3 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 4 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 5 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 戶外運動 | 山地自行車 | 1399.90 | 2500 |
| 2 | 11 | 2 | 戶外運動 | 運動外套 | 799.90 | 500 |
| 3 | 12 | 2 | 戶外運動 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 戶外運動 | 自行車 | 399.90 | 1000 |
| 4 | 10 | 2 | 戶外運動 | 騎行裝備 | 399.90 | 3500 |
| 5 | 9 | 2 | 戶外運動 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)

舉例：使用DENSE_RANK()函數(shù)獲取 goods 數(shù)據(jù)表中類別為“女裝/女士精品”的價格最高的4款商品信息末患。

mysql> SELECT *
 FROM(
 SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,
 id, category_id, category, NAME, price, stock
 FROM goods) t
 WHERE category_id = 1 AND row_num <= 3;
+---------+----+-------------+---------------+----------+--------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+----------+--------+-------+
| 1 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 3 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
+---------+----+-------------+---------------+----------+--------+-------+
4 rows in set (0.00 sec)

可以看到研叫，使用DENSE_RANK()函數(shù)得出的行號為1、2璧针、2嚷炉、3，相同價格的商品序號相同陈莽，后面的商品序號是連續(xù)的，并且沒有跳過重復的序號虽抄。

2. 分布函數(shù)

1．PERCENT_RANK()函數(shù)

PERCENT_RANK()函數(shù)是等級值百分比函數(shù)走搁。按照如下方式進行計算。

(rank - 1) / (rows - 1)

其中迈窟，rank的值為使用RANK()函數(shù)產(chǎn)生的序號私植，rows的值為當前窗口的總記錄數(shù)。

舉例：計算 goods 數(shù)據(jù)表中名稱為“女裝/女士精品”的類別下的商品的PERCENT_RANK值车酣。

#寫法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;
#寫法二：
mysql> SELECT RANK() OVER w AS r,
 PERCENT_RANK() OVER w AS pr,
 id, category_id, category, NAME, price, stock
 FROM goods
 WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price
DESC);
+---+-----+----+-------------+---------------+----------+--------+-------+
| r | pr | id | category_id | category | NAME | price | stock |
+---+-----+----+-------------+---------------+----------+--------+-------+
| 1 | 0 | 6 | 1 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 |
| 2 | 0.2 | 3 | 1 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 |
| 2 | 0.2 | 4 | 1 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 |
| 4 | 0.6 | 2 | 1 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 |
| 5 | 0.8 | 1 | 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 1 | 5 | 1 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 |
+---+-----+----+-------------+---------------+----------+--------+-------+
6 rows in set (0.00 sec)

2. CUME_DIST()函數(shù)

CUME_DIST()函數(shù)主要用于查詢小于或等于某個值的比例曲稼。

舉例：查詢goods數(shù)據(jù)表中小于或等于當前價格的比例索绪。

mysql> SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
 id, category, NAME, price
 FROM goods;
+---------------------+----+---------------+------------+---------+
| cd | id | category | NAME | price |
+---------------------+----+---------------+------------+---------+
| 0.16666666666666666 | 5 | 女裝/女士精品 | 百褶裙 | 29.90 |
| 0.3333333333333333 | 1 | 女裝/女士精品 | T恤 | 39.90 |
| 0.5 | 2 | 女裝/女士精品 | 連衣裙 | 79.90 |
| 0.8333333333333334 | 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 |
| 0.8333333333333334 | 4 | 女裝/女士精品 | 牛仔褲 | 89.90 |
| 1 | 6 | 女裝/女士精品 | 呢絨外套 | 399.90 |
| 0.16666666666666666 | 9 | 戶外運動 | 登山杖 | 59.90 |
| 0.5 | 7 | 戶外運動 | 自行車 | 399.90 |
| 0.5 | 10 | 戶外運動 | 騎行裝備 | 399.90 |
| 0.6666666666666666 | 12 | 戶外運動 | 滑板 | 499.90 |
| 0.8333333333333334 | 11 | 戶外運動 | 運動外套 | 799.90 |
| 1 | 8 | 戶外運動 | 山地自行車 | 1399.90 |
+---------------------+----+---------------+------------+---------+
12 rows in set (0.00 sec)

3. 前后函數(shù)

1．LAG(expr,n)函數(shù)

LAG(expr,n)函數(shù)返回當前行的前n行的expr的值。

舉例：查詢goods數(shù)據(jù)表中前一個商品價格與當前商品價格的差值贫悄。

mysql> SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
 FROM (
 SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
 FROM goods
 WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
+----+---------------+------------+---------+-----------+------------+
| id | category | NAME | price | pre_price | diff_price |
+----+---------------+------------+---------+-----------+------------+
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 29.90 | 10.00 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 39.90 | 40.00 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 79.90 | 10.00 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 89.90 | 0.00 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 89.90 | 310.00 |
| 9 | 戶外運動 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 戶外運動 | 自行車 | 399.90 | 59.90 | 340.00 |
| 10 | 戶外運動 | 騎行裝備 | 399.90 | 399.90 | 0.00 |
| 12 | 戶外運動 | 滑板 | 499.90 | 399.90 | 100.00 |
| 11 | 戶外運動 | 運動外套 | 799.90 | 499.90 | 300.00 |
| 8 | 戶外運動 | 山地自行車 | 1399.90 | 799.90 | 600.00 |
+----+---------------+------------+---------+-----------+------------+
12 rows in set (0.00 sec)

2. LEAD(expr,n)函數(shù)

LEAD(expr,n)函數(shù)返回當前行的后n行的expr的值瑞驱。

舉例：查詢goods數(shù)據(jù)表中后一個商品價格與當前商品價格的差值。

mysql> SELECT id, category, NAME, behind_price, price,behind_price - price AS
diff_price
 FROM(
 SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price
 FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
+----+---------------+------------+--------------+---------+------------+
| id | category | NAME | behind_price | price | diff_price |
+----+---------------+------------+--------------+---------+------------+
| 5 | 女裝/女士精品 | 百褶裙 | 39.90 | 29.90 | 10.00 |
| 1 | 女裝/女士精品 | T恤 | 79.90 | 39.90 | 40.00 |
| 2 | 女裝/女士精品 | 連衣裙 | 89.90 | 79.90 | 10.00 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 89.90 | 0.00 |
| 4 | 女裝/女士精品 | 牛仔褲 | 399.90 | 89.90 | 310.00 |
| 6 | 女裝/女士精品 | 呢絨外套 | NULL | 399.90 | NULL |
| 9 | 戶外運動 | 登山杖 | 399.90 | 59.90 | 340.00 |
| 7 | 戶外運動 | 自行車 | 399.90 | 399.90 | 0.00 |
| 10 | 戶外運動 | 騎行裝備 | 499.90 | 399.90 | 100.00 |
| 12 | 戶外運動 | 滑板 | 799.90 | 499.90 | 300.00 |
| 11 | 戶外運動 | 運動外套 | 1399.90 | 799.90 | 600.00 |
| 8 | 戶外運動 | 山地自行車 | NULL | 1399.90 | NULL |
+----+---------------+------------+--------------+---------+------------+
12 rows in set (0.00 sec)

4. 首尾函數(shù)

1. FIRST_VALUE(expr)函數(shù)

FIRST_VALUE(expr)函數(shù)返回第一個expr的值窄坦。

舉例：按照價格排序唤反，查詢第1個商品的價格信息。

mysql> SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS
first_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+-------+-------------+
| id | category | NAME | price | stock | first_price |
+----+---------------+------------+---------+-------+-------------+
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 | 29.90 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 | 29.90 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 | 29.90 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 | 29.90 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 | 29.90 |
| 9 | 戶外運動 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 戶外運動 | 自行車 | 399.90 | 1000 | 59.90 |
| 10 | 戶外運動 | 騎行裝備 | 399.90 | 3500 | 59.90 |
| 12 | 戶外運動 | 滑板 | 499.90 | 1200 | 59.90 |
| 11 | 戶外運動 | 運動外套 | 799.90 | 500 | 59.90 |
| 8 | 戶外運動 | 山地自行車 | 1399.90 | 2500 | 59.90 |
+----+---------------+------------+---------+-------+-------------+
12 rows in set (0.00 sec)

2．LAST_VALUE(expr)函數(shù)

LAST_VALUE(expr)函數(shù)返回最后一個expr的值鸭津。

舉例：按照價格排序彤侍，查詢最后一個商品的價格信息。

mysql> SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+-------+------------+
| id | category | NAME | price | stock | last_price |
+----+---------------+------------+---------+-------+------------+
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 1000 | 39.90 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 2500 | 79.90 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 1500 | 89.90 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 3500 | 89.90 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 1200 | 399.90 |
| 9 | 戶外運動 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 戶外運動 | 自行車 | 399.90 | 1000 | 399.90 |
| 10 | 戶外運動 | 騎行裝備 | 399.90 | 3500 | 399.90 |
| 12 | 戶外運動 | 滑板 | 499.90 | 1200 | 499.90 |
| 11 | 戶外運動 | 運動外套 | 799.90 | 500 | 799.90 |
| 8 | 戶外運動 | 山地自行車 | 1399.90 | 2500 | 1399.90 |
+----+---------------+------------+---------+-------+------------+
12 rows in set (0.00 sec)

5. 其他函數(shù)

1．NTH_VALUE(expr,n)函數(shù)

NTH_VALUE(expr,n)函數(shù)返回第n個expr的值逆趋。

舉例：查詢goods數(shù)據(jù)表中排名第2和第3的價格信息盏阶。

mysql> SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
 NTH_VALUE(price,3) OVER w AS third_price
 FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+--------------+-------------+
| id | category | NAME | price | second_price | third_price |
+----+---------------+------------+---------+--------------+-------------+
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 39.90 | NULL |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 39.90 | 79.90 |
| 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 | 39.90 | 79.90 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 39.90 | 79.90 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 39.90 | 79.90 |
| 9 | 戶外運動 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 戶外運動 | 自行車 | 399.90 | 399.90 | 399.90 |
| 10 | 戶外運動 | 騎行裝備 | 399.90 | 399.90 | 399.90 |
| 12 | 戶外運動 | 滑板 | 499.90 | 399.90 | 399.90 |
| 11 | 戶外運動 | 運動外套 | 799.90 | 399.90 | 399.90 |
| 8 | 戶外運動 | 山地自行車 | 1399.90 | 399.90 | 399.90 |
+----+---------------+------------+---------+--------------+-------------+
12 rows in set (0.00 sec)

2．NTILE(n)函數(shù)

NTILE(n)函數(shù)將分區(qū)中的有序數(shù)據(jù)分為n個桶，記錄桶編號闻书。

舉例：將goods表中的商品按照價格分為3組名斟。

mysql> SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
 FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+----+---------------+------------+---------+
| nt | id | category | NAME | price |
+----+----+---------------+------------+---------+
| 1 | 5 | 女裝/女士精品 | 百褶裙 | 29.90 |
| 1 | 1 | 女裝/女士精品 | T恤 | 39.90 |
| 2 | 2 | 女裝/女士精品 | 連衣裙 | 79.90 |
| 2 | 3 | 女裝/女士精品 | 衛(wèi)衣 | 89.90 |
| 3 | 4 | 女裝/女士精品 | 牛仔褲 | 89.90 |
| 3 | 6 | 女裝/女士精品 | 呢絨外套 | 399.90 |
| 1 | 9 | 戶外運動 | 登山杖 | 59.90 |
| 1 | 7 | 戶外運動 | 自行車 | 399.90 |
| 2 | 10 | 戶外運動 | 騎行裝備 | 399.90 |
| 2 | 12 | 戶外運動 | 滑板 | 499.90 |
| 3 | 11 | 戶外運動 | 運動外套 | 799.90 |
| 3 | 8 | 戶外運動 | 山地自行車 | 1399.90 |
+----+----+---------------+------------+---------+
12 rows in set (0.00 sec)

2.5 小結

窗口函數(shù)的特點是可以分組，而且可以在分組內排序惠窄。另外蒸眠，窗口函數(shù)不會因為分組而減少原表中的行數(shù)，這對我們在原表數(shù)據(jù)的基礎上進行統(tǒng)計和排序非常有用杆融。

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者

人面猴
序言：七十年代末楞卡，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子脾歇，更是在濱河造成了極大的恐慌蒋腮，老刑警劉巖，帶你破解...
沈念sama閱讀 218,640評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件藕各，死亡現(xiàn)場離奇詭異池摧，居然都是意外死亡，警方通過查閱死者的電腦和手機激况，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,254評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門作彤，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人乌逐，你說我怎么就攤上這事竭讳。” “怎么了浙踢？”我有些...
開封第一講書人閱讀 165,011評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵绢慢，是天一觀的道長。經(jīng)常有香客問我洛波，道長胰舆，這世上最難降的妖魔是什么骚露？我笑而不...
開封第一講書人閱讀 58,755評論 1贊 294
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮缚窿，結果婚禮上棘幸，老公的妹妹穿的比我還像新娘。我一直安慰自己滨攻，他們只是感情好够话，可當我...
茶點故事閱讀 67,774評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著光绕，像睡著了一般女嘲。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上诞帐，一...
開封第一講書人閱讀 51,610評論 1贊 305
城市分裂傳說
那天欣尼，我揣著相機與錄音，去河邊找鬼停蕉。笑死愕鼓，一個胖子當著我的面吹牛，可吹牛的內容都是我干的慧起。我是一名探鬼主播菇晃，決...
沈念sama閱讀 40,352評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼蚓挤！你這毒婦竟也來了磺送？” 一聲冷哼從身側響起，我...
開封第一講書人閱讀 39,257評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤灿意，失蹤者是張志新（化名）和其女友劉穎估灿，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體缤剧，經(jīng)...
沈念sama閱讀 45,717評論 1贊 315
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡馅袁，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內容為張勛視角年9月15日...
茶點故事閱讀 37,894評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了荒辕。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片汗销。...
茶點故事閱讀 40,021評論 1贊 350
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖抵窒，靈堂內的尸體忽然破棺而出弛针，到底是詐尸還是另有隱情，我是刑警寧澤估脆，帶...
沈念sama閱讀 35,735評論 5贊 346
?日本核電站爆炸內幕
正文年R本政府宣布钦奋，位于F島的核電站座云，受9級特大地震影響疙赠，放射性物質發(fā)生泄漏付材。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 41,354評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一圃阳、第九天我趴在偏房一處隱蔽的房頂上張望厌衔。院中可真熱鬧如筛，春花似錦薯鳍、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,936評論 0贊 22
一樁弒父案锣夹，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽页徐。三九已至，卻和暖如春银萍，著一層夾襖步出監(jiān)牢的瞬間变勇，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,054評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工贴唇，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留搀绣，地道東北人。一個月前我還...
沈念sama閱讀 48,224評論 3贊 371
代替公主和親
正文我出身青樓戳气，卻偏偏與公主長得像链患，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子瓶您，可洞房花燭夜當晚...
茶點故事閱讀 44,974評論 2贊 355

MySQL8.0新特性-窗口函數(shù)