1. Toy模板网首页
  2. > Toy博客

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

8月前 作者：Ahuuua 分类：Toy博客 阅读(40) 违法举报

这篇具有很好参考价值的文章主要介绍了窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

目录

一、函数写法

二、开窗的窗口范围ROWS与RANGE

1.范围限定用法

2.ROWS和RANGE的区别

(1) ROWS按行数限定

(2) RANGE按数据范围限定

        order by 数字

                例1    汇总数据范围为：[当前行值,当前行值+3]

                例2    汇总数据范围为：[当前行值-3,当前行值]

        order by 时间

                例1    [当前行日期,当前行日期+2]

                例2    [当前行日期-2,当前行日期]

三、函数介绍

1.排序函数

2.聚合函数

3.比较函数

        例1    lag 偏移为负数offset=-1

        例2    lag取出向前第0行，即偏移为0

        例3    lag取出向前第2行，即偏移为2

        例4    换个字段，lag取出向前第2行，即向前偏移为2

        例5    lead取出向后第2行，即向后偏移2

        例6    lead取出向后第2行，即向后偏移2，不加默认值

一、函数写法

函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)

由三部分组成：
函数名：如sum、max、min、count、avg等聚合函数以及lead、lag行比较函数等；
over： 关键字，表示前面的函数是分析函数，不是普通的集合函数；
分组子句：over关键字后面挂号内的内容；

分析子句又由下面三部分组成：
PARTITION BY ：分组子句，表示分析函数的计算范围，不同的组互不相干；
ORDER BY： 排序子句，表示分组后，组内的排序方式；
ROWS/RANGE：窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；

二、开窗的窗口范围ROWS与RANGE

1.范围限定用法

  • CURRENT ROW: 当前行
  • UNBOUNDED：不受控制的，无限的
  • UNBOUNDED PRECEDING: 区间的第一行
  • UNBOUNDED FOLLOWING：区间的最后一行
  • UNBOUNDED PRECEDING AND UNBOUNED FOLLOWING：针对当前所有记录的前一条、后一条记录，分组中的所有记录
  • PRECEDING：在...之前，N PRECEDING: 当前行之前的N行，可以是数字用于RANGE数据范围限定，也可以是一个能计算出数字的表达式
  • FOLLOWING：在...之后，N FOLLOWING：当前行之后的N行，可以是数字用于RANGE数据范围限定，也可以是一个能计算出数字的表达式
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  ：指第一行至当前行的数据
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ：指当前行到最后一行的汇总
  • ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ：指当前行的上一行(ROWNUM-1)到当前行的数据
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ：指当前行的上一行(ROWNUM-1)到当前行的下一行(ROWNUM+1)的数据
  • RANGE BETWEEN CURRENT ROW AND 350 FOLLOWING：指当前行到当前行数据+350的范围内的数据
  • RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING：指当前行数据幅度减5加5后的范围内的数据

2.ROWS和RANGE的区别

ROWS按行数限定

RANGE按数据范围限定

(1) ROWS按行数限定

表结构及测试数据：

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 '是否关注'
) 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', 1);
INSERT INTO `test` VALUES (2001, '2021-10-02', 1);
INSERT INTO `test` VALUES (2001, '2021-10-01', 1);
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', 1);
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', 1);
INSERT INTO `test` VALUES (2002, '2021-09-30', 1);
INSERT INTO `test` VALUES (2002, '2021-10-01', 1);
INSERT INTO `test` VALUES (2002, '2021-10-02', 1);
INSERT INTO `test` VALUES (2002, '2021-10-03', 1);

语句：

select video_id,dt, sum(if_follow) over(partition by video_id order by dt rows BETWEEN CURRENT ROW and 1 following ) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

(2) RANGE按数据范围限定

表结构及测试数据：

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 '是否关注'
) 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和PRECEDING/FOLLOWING一起使用时，order by的表达式必须为数字或者时间差

select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN 3 preceding and CURRENT ROW ) from test ;

 报错内容如下：
select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN 3 preceding and CURRENT ROW ) from test
> 3587 - Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type

order by 数字

例1    汇总数据范围为：[当前行值,当前行值+3]

select video_id,dt, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN  CURRENT ROW and  3 following) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

例2    汇总数据范围为：[当前行值-3,当前行值]

select video_id,dt, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN 3 PRECEDING and CURRENT ROW ) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

order by 时间

order by表达式的类型为时间（date、datetime）时，必须使用Interval

例1    [当前行日期,当前行日期+2]

select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN  CURRENT ROW and  interval 2 day following) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

例2    [当前行日期-2,当前行日期]

select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN interval 2 day PRECEDING and CURRENT ROW ) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

三、函数介绍

下面是mysql中能使用的

1.排序函数

rank()函数，如果有并列情况，会占用下一个名次的位置，比如，成绩为100的学生有三个并列第一，那么99分的学生是第二名，通过rank()函数，名次是：1,1,1,4；
dense()函数，如果有并列的情况，不会占用下一个名词，同用上个例子，名次是：1,1,1,2；
row_number()函数，会忽略并列的情况，同用上述例子，名次是：1,2,3,4;

2.聚合函数

count() over(partition by ... order by ...)：求分组后的总数；
max() over(partition by ... order by ...)：求分组后的最大值；
min() over(partition by ... order by ...)：求分组后的最小值；
avg() over(partition by ... order by ...)：求分组后的平均值；

3.比较函数

lag() over(partition by ... order by ...)：取出向前第n行数据。　　
lead() over(partition by ... order by ...)：取出向后第n行数据。

lag(arg1,arg2,arg3)、lead(arg1,arg2,arg3)
第一个参数是列名，
第二个参数是偏移的offset，不能为负数，
第三个参数是超出记录窗口时的默认值。

表结构及测试数据：

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 '是否关注'
) 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);

例1    lag 偏移为负数offset=-1

语法错误，偏移offset，不能为负数

select video_id,dt, lag(dt,-1,'偏移超出了') over(order by dt ) from test ;

 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,'偏移超出了') over(order by dt ) from test' at line 1 

例2    lag取出向前第0行，即偏移为0

select video_id,dt, lag(dt,0,'偏移超出了') over(order by dt ) from test ;

 窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

例3    lag取出向前第2行，即偏移为2

select video_id,dt, lag(dt,2,'偏移超出了') over(order by dt ) from test ;

 窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

例4    换个字段，lag取出向前第2行，即向前偏移为2

select video_id,dt, lag(video_id,2,'偏移超出了') over(order by dt ) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

例5    lead取出向后第2行，即向后偏移2

select video_id,dt, lead(video_id,2,'偏移超出了') over(order by dt ) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

例6    lead取出向后第2行，即向后偏移2，不加默认值

select video_id,dt, lead(video_id,2) over(order by dt ) from test ;

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

下面可能是Oracle函数，mysql没能使用出来：

first_value() over()和last_value() over()，分别是求分组中第一个和最后一个

ratio_to_report() over(partition by ... order by ...)：ratio_to_report() 括号中就是分子，over() 括号中就是分母

percent_rank() over(partition by ... order by ...)文章来源地址https://www.toymoban.com/news/detail-403167.html

到了这里，关于窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • SQL - 开窗(窗口)函数

    SQL - 开窗(窗口)函数

    开窗函数对一组值进行操作，它不像普通聚合函数那样需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列 开窗函数的语法形式为：函数 + over(partition by 分组用列 order by 排序用列)，表示对数据集按照分组用列进行分区，并且并且对每个分区按

    2024年02月12日
    浏览(31)

  • 【SQL】MySQL中的窗口函数（开窗函数）

    窗口函数是 MYSQL8.0 新增的 聚合函数： 多行变一行，常见的sum，count，max，min 窗口函数： 行数不变，常见的row_number,rank 语法格式： 窗口函数(表达式) over (partition by … order by … frame_clause) partition by是分区，类似于group by，如去掉相当于对所有数据进行计算 order by排序 frame_c

    2024年02月07日
    浏览(36)
  • MySQL---控制流函数、窗口函数（序号函数、开窗聚合函数、分布函数、前后函数、头尾函数、其他函数）

    MySQL---控制流函数、窗口函数（序号函数、开窗聚合函数、分布函数、前后函数、头尾函数、其他函数）

    格式 解释 案例 IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1 ；否则，返回结果 v2 。 SELECT IF(1 0,\\\' 正确 \\\',\\\' 错误 \\\')    - 正确 IFNULL(v1,v2） 如果 v1 的值不为 NULL ，则返回 v1 ，否则返回 v2 。 SELECT IFNULL(null,\\\'Hello Word\\\') -Hello Word ISNULL(expression) 判断表达式是否为 NULL SELECT ISNULL(NUL

    2024年02月04日
    浏览(33)
  • 【大数据之Hive】十六、Hive-HQL函数之窗口函数（开窗函数）

    【大数据之Hive】十六、Hive-HQL函数之窗口函数（开窗函数）

      先定义了窗口的大小（按行来算），然后对窗口内的行的数据进行计算，再将计算结果返回给改行。   窗口函数包括窗口和函数两部分，窗口用于定义计算范围，函数用于定义计算逻辑，窗口函数只会在原来的表上增加一列结果列，不改变原来的数据。 函数：   绝

    2024年02月11日
    浏览(32)
  • 开窗函数的使用详解(窗口范围ROWS与RANGE图文详解)

    开窗函数的使用详解(窗口范围ROWS与RANGE图文详解)

    函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句) 由三部分组成： 函数名：如sum、max、min、count、avg等聚合函数以及lead、lag行比较函数等； over： ，表示前面的函数是分析函数，不是普通的集合函数； 分组子句：over后面挂号内的内容； 分析子句又由下

    2024年02月04日
    浏览(67)
  • MYSQL窗口函数（Rows & Range）——滑动窗口函数用法

    MYSQL窗口函数（Rows & Range）——滑动窗口函数用法

    窗口函数语法： 窗口函数 over (partition by 用于分组的列名 order by 用于排序的列名 rows/range子句用于定义窗口大小 ) 窗口函数可以放以下两种函数： 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg, count, max, min等 1）专用窗口

    2024年02月10日
    浏览(30)
  • 【sql高级】postgresql之窗口函数用法

    【sql高级】postgresql之窗口函数用法

    窗口函数在在SQL中是非常有用的工具，特别是在需要对查询结果进行分析、排名、聚合或者对结果进行一些特定的计算时。以下是一些常见的场景： 排名和分组：窗口函数可以轻松地对结果集进行排名、分组和分区。例如，你可以使用 ROW_NUMBER()、RANK()、DENSE_RANK() 等函数来为

    2024年03月23日
    浏览(37)

  • 【sqlsever】具体案例理解PARTITION BY

    当使用 PARTITION BY 时，它通常是与窗口函数一同使用的。下面将提供一个简单的例子，使用一个包含以下列的表： 现在，我们想要计算每个 column1 的每一行的 column3 列的累积总和。我们可以使用 PARTITION BY 来实现这个目标： 这将生成以下结果： 在这个例子中， PARTITION BY col

    2024年01月25日
    浏览(26)

  • SQL中Partition的相关用法

    使用Partition可以根据指定的列或表达式将数据分成多个分区。每个分区都是逻辑上独立的，可以单独进行查询、插入、更新和删除操作。Partition可以提高查询性能，因为它可以限制在特定分区上执行查询，而不是在整个表上执行。 在SQL中，常用的Partition函数包括： PARTITION

    2024年02月16日
    浏览(32)

  • MySQL 基本概念 基础用法 增删改查（特殊查询）语法 详细篇

    今天来分享一期MySQL的基本用法（增删改查 对库 对表 对值） 目录 MySQL的基础概念  SQL 语言的主要分类 DDL(数据定义语言） DML(数据操控语言） DQL(数据库查询语言) DCL(数据库控制语言) 基础语法 增 库的操作 表操作 列操作 值操作 删 删除库 删除表 删除值 改 修改表名 更新数

    2024年03月16日
    浏览(36)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包