开窗函数

3月 15, 2021

排序函数

开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by 排序字段 order by 分区字段 rows between 开窗规则)。

开窗函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，

示例数据：

第一大类：聚合开窗函数====》聚合函数(列) OVER (选项)

：sum(),count()等都可以拼接

第二大类：排序开窗函数====》排序函数(列) OVER(选项)

：rank(),row_number()等需要和over一起使用排序函数

不限于此，很多函数都可以配合over（），在这里不做过多介绍

语法：

--select 函数名()over(partition by 需要分区的列名 order by 想排序的列名 rows/range 
 --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在...之前
            --following：在...之后
          --  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from 表名
--such as 
select *,sum(sage)over(order by sage rows between UNBOUNDED preceding and UNBOUNDED following)
from "student"

分区（partition by ）：

功能与group by几乎相同，但group by对于聚合的分组只显示一行数据，并且select后的字段如果其中一个被group by，那么其他字段要么是聚合函数要么也被group by分组聚合。

栗子：分别求男女同学年龄总和

而聚合函数搭配over（）可以一组返回多行数据，而且其他字段不需要为被分组或被聚合。

排序（order by）：

栗子：男女分别根据年龄降序排列

没什么需要多说的，这里使用了rank()函数排序，partition by相当于group by，order by还是原意。

注：关于rank()等排序函数请移步其他文章，本文不做说明

栗子ii：男女按照年龄降序分别求出年龄累计

这里发生了变化，当使用sum()或count()配合over使用，并且使用partition by和order by，就会根据排序进行逐渐的累加或者求和，可以用于计算各个月份随时间累计数等情况

窗口（rows/range）注：rows可以使用，但是range没研究明白报错：

在Over子句中，使用Rows 或Range 进一步限制分区的数据行，在使用时，必须注意：

必需条件：如果使用Rows 或 Range必须跟在Order by 子句之后，对排序的结果进行限制；
Rows：使用固定的行数来限制分区中的数据行数量；The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row.
Range：使用Value的范围来限制分区中的数据行数量，排序列的重复值，被认为是一个值；The RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row.
在分区中，如果排序行不存在重复值，Rows和Range返回的结果是相同的；如果排序行存储在重复值，Rows和Range返回的结果可能不同；
Range子句只能从分区的开始或结尾到当前行开始计算，不能使用 PRECEDING 和 FOLLOWING；
使用在Rows 和 Range子句中的特殊关键字：

UNBOUNDED PRECEDING：指定分区的第一行
UNBOUNDED FOLLOWING：指定分区的最后一行
CURRENT ROW：指定分区的当前数据行
PRECEDING：在分区中，指定当前行之前的数据行数量，UINT_Number是>=0的整数
FOLLOWING：在分区中，指定当前行之后的数据行数量，UINT_Number是>=0的整数

--每行数据都会被开窗影响，每行数据都会根据开窗的行数来向上或向下进行函数的操作。
--当上面没有行时上面的行不做影响，下面也没有行时同上。
select *,sum(sage)over(order by sage rows between 前多少行 preceding and 后多少行 following)
from "student"

栗子：对分区中的连续两行计算加和，将每行和下一行进行相加

第一行=第一行sage+第二行sage，第二行=第二行sage+第三行sage………..以此类推

图中第五行数据和第十行数据都是分区中的最后一行数据，没有下面的行进行相加所以是原数

–栗子ii：对分区中的当前行向下计算加和，将每行到当前分区最底下的进行相加

Could not execute ‘select *,sum(sage)over(order by sage range between unbounded preceding and unbounded following) …’
SAP DBTech JDBC: [7]: feature not supported: Window frame specification of RANGE not allowed for this window function: line 1 col 10 (at pos 9)

202005241421367251716257711.500x0.jpg.webp

to be continued（多半是鸽了😜

sql study