over() 是一个常用的函数,不管在oralce 还是大数据hive-sql 都支持。最近在看SQL的时候,才发现,自己以前的理解与over()实际使用有一定的偏差。
使用
over(order by xxx) 按照xxx排序进行累计,order by是个默认的开窗函数
over(partition by xxx)按照xxx分区
over(partition by xxx order by xx)按照xxx分区,并以xx排序
一般大家想到的是这几种,其实还有一种
over()
下面将以sum()与 over()结合,举几个例子方便理解。
首先创建一张简单的表(CLIENT):
情况一
over(order by xxx)
按照ID排序
SELECT NAME, SUM("ID") OVER(ORDER BY "ID" ) FROM CLIENT;
得到结果:
实现逻辑:按照ID升序排序,第N行数据为:第N行相等排序值唯一时,累加第一行至第N-1行值,并加上第N行的数据作为第N行的最终值;第N行相等排序值不唯一时,累加第一行至第N-1行值,并加上第N行的数据*n(n为与第N行相同值的个数)数据作为第N行的最终值;
情况二
over(partition by xxx)
按照xxx分区
SELECT NAME, SUM("ID") OVER(PARTITION BY NAME ) FROM CLIENT;
得到结果:
实现逻辑:某个分区的值:按照 NAME 分区,将与NAME相同分区下的值求和;
情况三
over(partition by xxx order by xx)
按照xxx分区,并以xx排序
SELECT NAME, SUM("ID") OVER(PARTITION BY NAME ORDER BY "ID" ) FROM CLIENT;
得到结果:
实现逻辑:如果理解了前两种实现逻辑,那么这种情况其实很容易理解,就是前两种的结合体。先按照情况二按照NAME分区,然后按照情况一处理分区内的数据即可;
情况四
over()
over中为空
SELECT name, SUM("ID") over() FROM CLIENT;
得到结果:
实现逻辑:此时,其实就是对所有ID进行求和而已, 你可认为over()函数不存在一样处理得到值,不同的是,此时会有多条数据,而如果没有over的sum 仅有一条数据,这也正是over函数的一大特点;