转PLSQL分窗函数

原

oracle开窗函数over(partition by)函数整理

2017年04月20日 16:58:51 naomi_qing 阅读数 465

一、开窗函数与聚合函数的不同之处在于：

聚合函数每个组返回一行，而开窗函数返回多行。所有有些时候使用开窗函数时需要去重。

例如： select t.deptno,sum(t.sal) sal from linshi_ljq t group by t.deptno;

结果是：

结果中没有重复数据，不需要去重

如果用开窗函数： select  t1.deptno, sum(t1.sal) over(partition by t1.deptno)
    from linshi_ljq t1;

结果是：

即原表中有多少条数据就会查询出多少条。这些数据是重复的，必要的时候需要去除重复数据。

二、OVER后的几种写法

1）over(order by )

例如：select rank() over(order by t.sal) as rank, t.* from linshi_ljq t;

2) over(partition by )

例如：select sum(t.sal) over(partition by t.deptno) as sum, t.* from linshi_ljq t;

3）over( order by  range)

例如： select first_value(t.sal) over(order by t.sal range between 100 preceding and 100 following), --窗口范围为当前行数据幅度加100减100后的范围
       t.sal,
       t.*
  from linshi_ljq t;

sum(lirun) over(partition by zsd_soto order by to_date(call_month,'yyyymm') range between interval '11' month preceding and CURRENT ROW)--利润值往前推11个月到当月的合计值

max( maoshouru) over(partition by zsd_soto order by to_date(yuefen,'yyyymm') rows  between  unbounded preceding  and   current  row)--历史最大毛收入

三、与OVER()函数结合的函数

1）排序函数：row_number()over()、rank()over()、dense_rank()over 

2)聚合函数：sum()over()、min()over()、max()over()、avg()over()、count()over()

3)返回特定行函数：first_value()over()、last_value()over()

4)返回前N行数据和后N行数据：lag()over()、lead()over()