一、函數(shù)寫法
函數(shù)名(參數(shù)) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)
由三部分組成： 函數(shù)名：如sum、max、min、count、avg等聚合函數(shù)以及l(fā)ead、lag行比較函數(shù)等； over：
關(guān)鍵字，表示前面的函數(shù)是分析函數(shù)，不是普通的集合函數(shù)； 分組子句：over關(guān)鍵字后面掛號內(nèi)的內(nèi)容；
分析子句又由下面三部分組成： PARTITION BY ：分組子句，表示分析函數(shù)的計算范圍，不同的組互不相干； ORDER BY：
排序子句，表示分組后，組內(nèi)的排序方式； ROWS/RANGE：窗口子句，是在分組(PARTITION
BY)后，組內(nèi)的子分組（也稱窗口），此時分析函數(shù)的計算范圍窗口，而不是PARTITON。窗口有兩種，ROWS和RANGE；
二、開窗的窗口范圍ROWS與RANGE
1.范圍限定用法 CURRENT ROW: 當(dāng)前行
UNBOUNDED：不受控制的，無限的
UNBOUNDED PRECEDING: 區(qū)間的第一行
UNBOUNDED FOLLOWING：區(qū)間的最后一行
UNBOUNDED PRECEDING AND UNBOUNED FOLLOWING：針對當(dāng)前所有記錄的前一條、后一條記錄，分組中的所有記錄
PRECEDING：在…之前， N PRECEDING:當(dāng)前行之前的N行，可以是數(shù)字用于RANGE數(shù)據(jù)范圍限定，也可以是一個能計算出數(shù)字的表達(dá)式
FOLLOWING：在…之后，N FOLLOWING：當(dāng)前行之后的N行，可以是數(shù)字用于RANGE數(shù)據(jù)范圍限定，也可以是一個能計算出數(shù)字的表達(dá)式 ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW ：指第一行至當(dāng)前行的數(shù)據(jù)
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ：指當(dāng)前行到最后一行的匯總
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ：指當(dāng)前行的上一行(ROWNUM-1)到當(dāng)前行的數(shù)據(jù)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ：指當(dāng)前行的上一行(ROWNUM-1)到當(dāng)前行的下一行(ROWNUM+1)的數(shù)據(jù)
RANGE BETWEEN CURRENT ROW AND 350 FOLLOWING：指當(dāng)前行到當(dāng)前行數(shù)據(jù)+350的范圍內(nèi)的數(shù)據(jù)
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING：指當(dāng)前行數(shù)據(jù)幅度減5加5后的范圍內(nèi)的數(shù)據(jù)
三、練習(xí)：
```bash
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`video_id` int(0) NOT NULL COMMENT '視頻ID',
`dt` date NULL DEFAULT NULL,
`if_follow` tinyint(0) NULL DEFAULT NULL COMMENT '是否關(guān)注'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (2001, '2021-09-24', 1);
INSERT INTO `test` VALUES (2001, '2021-10-03', 9);
INSERT INTO `test` VALUES (2001, '2021-10-02', 2);
INSERT INTO `test` VALUES (2001, '2021-10-01', 6);
INSERT INTO `test` VALUES (2002, '2021-09-25', 1);
INSERT INTO `test` VALUES (2002, '2021-09-25', 1);
INSERT INTO `test` VALUES (2002, '2021-09-26', 6);
INSERT INTO `test` VALUES (2002, '2021-09-27', 1);
INSERT INTO `test` VALUES (2002, '2021-09-28', 1);
INSERT INTO `test` VALUES (2002, '2021-09-29', 8);
INSERT INTO `test` VALUES (2002, '2021-09-30', 7);
INSERT INTO `test` VALUES (2002, '2021-10-01', 1);
INSERT INTO `test` VALUES (2002, '2021-10-02', 9);
INSERT INTO `test` VALUES (2002, '2021-10-03', 1);
range是對order by 的值進(jìn)行判斷范圍計算的，根據(jù)實(shí)際的值和當(dāng)前的值進(jìn)行判斷取定數(shù)據(jù)范圍的。
#preceding 在…之前
#following 在…之后
select dt,video_id,if_follow, sum(if_follow) over(partition by video_id order by if_follow range between 1 preceding and 1 following) as num from test ;
#current row 當(dāng)前行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN CURRENT ROW and 3 following) as num from test ;
#unbounded preceding 區(qū)間的第一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN unbounded preceding and 3 following) as num from test ;
#unbounded following 區(qū)間的最后一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN current row and unbounded following) as num from test ;
#unbounded preceding and unbounded following 最后一行的值和第一行的值
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN unbounded preceding and unbounded following) as num from test ;
row是對order by 的值進(jìn)行判斷范圍計算的，根據(jù)實(shí)際的值和當(dāng)前的值進(jìn)行判斷取定數(shù)據(jù)范圍的
#preceding 在…之前
#following 在…之后
select dt,video_id,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows between 1 preceding and 1 following) as num from test ;
#current row 當(dāng)前行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN CURRENT ROW and 3 following) as num from test ;
#unbounded preceding 區(qū)間的第一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN unbounded preceding and 3 following) as num from test ;
#unbounded following 區(qū)間的最后一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN current row and unbounded following) as num from test ;
#unbounded preceding and unbounded following 最后一行的值和第一行的值文章來源：http://www.zghlxwxcb.cn/news/detail-439742.html
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN unbounded preceding and unbounded following) as num from test ;
文章來源地址http://www.zghlxwxcb.cn/news/detail-439742.html
到了這里，關(guān)于開窗函數(shù)的使用詳解(窗口范圍ROWS與RANGE圖文詳解)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
