MySQL窗口函數(shù)框架設(shè)置(Frame子句)

(轉(zhuǎn)載自己在另一博客的文章)

窗口函數(shù)：普通聚合函數(shù)會(huì)將多條記錄聚合為一條，但有時(shí)候我們希望聚合結(jié)果在每一行當(dāng)中顯示，從而實(shí)現(xiàn)對聚合結(jié)果的運(yùn)算，為達(dá)到該效果，我們通常需要嵌套子查詢，而MySQL8.0開始支持窗口函數(shù)，可以更便捷地實(shí)現(xiàn)這一功能。

窗口函數(shù)語法：

window_function_name(expr) 
    OVER([window_name] [partition_clause][order_clause] [frame_clause]);

本文章著重展示窗口函數(shù)中的框架設(shè)置（frame_clause）

示例—某商店的商品訂單

建立數(shù)據(jù)表

CREATE TABLE `sql_store`.`product_order`  (
  `order_id` int NOT NULL,
  `product` varchar(50) NOT NULL,
  `quantity` int NOT NULL,
  PRIMARY KEY (`order_id`)
);
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('1', 'apple', '10');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('2', 'cereal', '5');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('3', 'apple', '5');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('4', 'cereal', '3');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('5', 'apple', '10');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('6', 'bread', '10');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('7', 'bread', '12');
INSERT INTO `sql_store`.`product_order` (`order_id`, `product`, `quantity`) VALUES ('8', 'bread', '6');

建立store表如下

+----------+---------+----------+
| order_id | product | quantity |
+----------+---------+----------+
|        1 | apple   |       10 |
|        2 | cereal  |        5 |
|        3 | apple   |        5 |
|        4 | cereal  |        3 |
|        5 | apple   |       10 |
|        6 | bread   |       10 |
|        7 | bread   |       12 |
|        8 | bread   |        6 |
+----------+---------+----------+

默認(rèn)框架

窗口函數(shù)的框架即窗口的范圍，可使用[frame_clause]進(jìn)行設(shè)置。由于[frame_clause]是可選參數(shù)，如果沒有寫出，則會(huì)設(shè)置為默認(rèn)框架范圍，默認(rèn)框架范圍取決于是否進(jìn)行了排序或分類。

  • 未進(jìn)行排序或分類(如sum1)，則默認(rèn)框架范圍為所有行
SELECT 
    *,
    SUM(quantity) OVER () AS sum1
FROM sql_store.product_order;

+----------+---------+----------+------+
| order_id | product | quantity | sum1 |
+----------+---------+----------+------+
|        1 | apple   |       10 |   61 |
|        2 | cereal  |        5 |   61 |
|        3 | apple   |        5 |   61 |
|        4 | cereal  |        3 |   61 |
|        5 | apple   |       10 |   61 |
|        6 | bread   |       10 |   61 |
|        7 | bread   |       12 |   61 |
|        8 | bread   |        6 |   61 |
+----------+---------+----------+------+
  • 使用 ORDER BY 進(jìn)行排序,且未進(jìn)行分類(如sum2)，則默認(rèn)框架范圍為第一個(gè)分區(qū)到所在行的分區(qū)
  • 使用 PARTITION BY 進(jìn)行分類(sum3)，則默認(rèn)框架范圍為每個(gè)分區(qū)的所有行
SELECT 
    *,
    SUM(quantity) OVER (ORDER BY product) AS sum2
    SUM(quantity) OVER (PARTITION BY product) AS sum3
FROM sql_store.product_order;

+----------+---------+----------+------+------+
| order_id | product | quantity | sum2  | sum3 |
+----------+---------+----------+------+------+
|        1 | apple   |       10 |   25 |   25 |
|        3 | apple   |        5 |   25 |   25 |
|        5 | apple   |       10 |   25 |   25 |
|        6 | bread   |       10 |   53 |   28 |
|        7 | bread   |       12 |   53 |   28 |
|        8 | bread   |        6 |   53 |   28 |
|        2 | cereal  |        5 |   61 |    8 |
|        4 | cereal  |        3 |   61 |    8 |
+----------+---------+----------+------+------+

框架基本單位(frame_units)

框架的計(jì)數(shù)單位分為兩種：{ROWS | RANGE}，默認(rèn)值為RANGE

  • ROWS：以一行為一個(gè)單位
  • RANGE(有排序)：以連續(xù)相同的值為一個(gè)單位
  • RANGE(無排序)：以分區(qū)為單位
SELECT 
    *,
    SUM(quantity) OVER (PARTITION BY product ORDER BY quantity ROWS CURRENT ROW) AS sum1,
    SUM(quantity) OVER (PARTITION BY product ORDER BY quantity RANGE CURRENT ROW) AS sum2,
    SUM(quantity) OVER (PARTITION BY product RANGE CURRENT ROW) AS sum3
FROM sql_store.product_order

+----------+---------+----------+------+------+------+
| order_id | product | quantity | sum1 | sum2 | sum3 |
+----------+---------+----------+------+------+------+
|        3 | apple   |        5 |    5 |    5 |   25 |
|        1 | apple   |       10 |   10 |   20 |   25 |
|        5 | apple   |       10 |   10 |   20 |   25 |
|        8 | bread   |        6 |    6 |    6 |   28 |
|        6 | bread   |       10 |   10 |   10 |   28 |
|        7 | bread   |       12 |   12 |   12 |   28 |
|        4 | cereal  |        3 |    3 |    3 |    8 |
|        2 | cereal  |        5 |    5 |    5 |    8 |
+----------+---------+----------+------+------+------+
  • sum1中，使用ROWS作為基本單位，所以對每一行進(jìn)行單獨(dú)計(jì)算
  • sum2中，使用RANGE作為基本單位，apple 的第二、第三個(gè)訂單數(shù)量都為10，所以第二、第三行視為一個(gè)單位
  • sum3中，使用RANGE作為基本單位，并且沒有ORDER BY，所以每個(gè)分區(qū)為一個(gè)單位

框架范圍(frame_extent)

框架語句分為以下幾種：

  • CURRENT ROW --- 當(dāng)前行
  • UNBOUNDED PRECEDING ---當(dāng)前行上側(cè)所有行
  • UNBOUNDED FOLLOWING ---當(dāng)前行下側(cè)所有行
  • expr PRECEDING---當(dāng)前行上側(cè)expr行(expr可以是數(shù)字，也可以是表達(dá)式)
  • expr FOLLOWING---當(dāng)前行下側(cè)expr行(expr可以是數(shù)字，也可以是表達(dá)式)
窗口范圍

框架范圍可由兩種形式來定義：{frame_start | frame_between}，范圍的定義基于基本單位。

  • frame_start：僅指定開始行(或區(qū)域)，則結(jié)束范圍為默認(rèn)值，即當(dāng)前行(或區(qū)域) 
SELECT 
    *,
    SUM(quantity) OVER (PARTITION BY product ORDER BY quantity ROWS 1 PRECEDING) AS sum
FROM sql_store.product_order

+----------+---------+----------+------+
| order_id | product | quantity | sum  |
+----------+---------+----------+------+
|        3 | apple   |        5 |    5 |
|        1 | apple   |       10 |   15 |
|        5 | apple   |       10 |   20 |
|        8 | bread   |        6 |    6 |
|        6 | bread   |       10 |   16 |
|        7 | bread   |       12 |   22 |
|        4 | cereal  |        3 |    3 |
|        2 | cereal  |        5 |    8 |
+----------+---------+----------+------+

如上圖所示，該語句僅設(shè)置了frame_start部分(ROWS 1 PRECEDING)，即當(dāng)前行的前一行，而結(jié)束行則默認(rèn)為當(dāng)前行，所以sum的結(jié)果是相鄰兩行求和。

  • frame_between：指定開始行(或區(qū)域)與結(jié)束行(或區(qū)域)
    • frame_between語句：BETWEEN frame_start AND frame_end
SELECT 
    *,
    SUM(quantity) OVER (PARTITION BY product ORDER BY quantity ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum
FROM sql_store.product_order

+----------+---------+----------+------+
| order_id | product | quantity | sum  |
+----------+---------+----------+------+
|        3 | apple   |        5 |    5 |
|        1 | apple   |       10 |   15 |
|        5 | apple   |       10 |   25 |
|        8 | bread   |        6 |    6 |
|        6 | bread   |       10 |   16 |
|        7 | bread   |       12 |   28 |
|        4 | cereal  |        3 |    3 |
|        2 | cereal  |        5 |    8 |
+----------+---------+----------+------+

如上圖所示，該框架利用BETWEEN ... AND...語句設(shè)置了frame_start(UNBOUNDED PRECEDING)和frame_end(CUREENT ROW)，即框架范圍為第一行至當(dāng)前行，實(shí)現(xiàn)了累計(jì)求和的效果。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容