sql中over函数介绍

摘自：http://blog.csdn.net/inthirties/archive/2009/10/15/4673331.aspx

 

作者: 三十而立
时间：2009年10月15日 19:21:13
本文出自 “inthirties（三十而立） ”博客，转载请务必注明作者和保留出处http://blog.csdn.net/inthirties/archive/2009/10/15/4673331.aspx
学习是枯燥的，所以作为一个学习者，要学会在学习中找到快乐，这样才能激发兴趣，兴趣是最好的老师，这样，学习就慢慢的变成了一件快乐的事情叻。原本一直做Oracle的管理和维护，对PLSQL不是很感兴趣，在CSDN里经常有些朋友问到开发的问题，开始是通过google找到答案，后来在找寻的过程中，学习到了知识点，也就慢慢的开始帮助别人进行sql的开发帮助，这种帮助也给我了学习的动力，所以PLSQL的开发的基本东西也就掌握了不少了，而且由于是快乐的学习，所以也比较轻松，这里就是用个一个网友的问题的完整解题过程来看看我当时对Over的学习。

这是一个比较有趣的SQL题，

有一个表，结构如下：
编号，姓名，时间，基本工资，奖金，扣款
1 张三 8-20 1000 1000 5
2 李四 8-20 1100 900 10
3 张三 9-20 900 1000 10
4 李四 9-20 1100 900 13
5 王五 9-20 900 100 0

如果我对8-20 和 9-20 的基本工资做异动数据查询 我要求得到这样的一个临时数据视图

编号，姓名，基本工资 备注
1 张三 -100
2 王五 900 新增

原帖在http://topic.csdn.net/u/20090830/20/4f135d40-a52d-4346-ac15-599b2951af39.html?seed=709316630&r=59447116#r_59447116 

遇到有趣的问题，一下就激发我的兴趣，看这样需要上下记录对照的，就想到lag和lead函数了

lag和lead的功能是Oracle数据库独有的功能，是分组函数中的一个很有价值的方法。

这里也借这个题，好好的解释一下lag和lead的用法

Lag 取前面n条的记录

lag(field, n, defaultv)

这里field是我们需要取的字段， n是取多少个，defaultV是取不到的默认值。

 

Lead取后面那条的记录
lead(field, n, defaultv)

和lag一样的，field是取值的字段， n是前后几条， defualtv是默认值，类似nvl(lag(field, n), defaultv)

lag和lead必须和开窗函数over一起来使用，

那么知道Oracle里有这样实用的功能，那么我们如何具体来使用lag/lead和over，实战胜于一切

先做数据
现在习惯用with as来做数据了

with temp as (select '张三' name, '8-20' date1, 1000 a1, 1000 a2, 5 a3 from dual union all select '李四' name, '8-20' date1, 1100 a1, 900 a2, 10 a3 from dual union all select '张三' name, '9-20' date1, 900 a1, 1000 a2, 10 a3 from dual union all select '李四' name, '9-20' date1, 1100 a1, 900 a2, 13 a3 from dual union all select '王五' name, '9-20' date1, 900 a1, 100 a2, 0 a3 from dual)  
with temp as (select '张三' name, '8-20' date1, 1000 a1, 1000 a2, 5 a3 from dual union all select '李四' name, '8-20' date1, 1100 a1, 900 a2, 10 a3 from dual union all select '张三' name, '9-20' date1, 900 a1, 1000 a2, 10 a3 from dual union all select '李四' name, '9-20' date1, 1100 a1, 900 a2, 13 a3 from dual union all select '王五' name, '9-20' date1, 900 a1, 100 a2, 0 a3 from dual) 

数据如下，建立临时的视图。

下面是步骤，这里需要前后记录做比对，从而算出异动值，同时有可能只有一条记录的，时间后-时间前，所以用date1排序desc,

刚才说到了，lag, lead都是分组的方法，必须和开窗Over函数一起用，

Over函数是Oracle里的开窗函数，功能相当的强大，这个类似一个时间之窗，以指定的字段进行group by，也就是这里所说到的类似一个值为维度的时间之窗。 这里指定维度字段用partition by关键字，这样就是以这个partition by后面的字段作为分组的依据，形成这个时间之窗。 我们还可以在维度里，指定另一个维度的排序。

结合着，我们这里想要的结果，我们可以看到需要用name做这个维度。

写法如下。

以user为单位，所以需要按name来partition

如下
over(partition by name order by date1 desc)

select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c from temp; NAME A1 DATE C ---- ---------- ---- ---------- -- 李四 1100 9-20 0 李四 1100 8-20 1100 王五 900 9-20 900 张三 900 9-20 -100 张三 1000 8-20 1000  
select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c from temp; NAME A1 DATE C ---- ---------- ---- ---------- -- 李四 1100 9-20 0 李四 1100 8-20 1100 王五 900 9-20 900 张三 900 9-20 -100 张三 1000 8-20 1000   

 

这里执行的结果如下，现在异动数据出来了，

但是，多余的记录要去掉，怎么去掉多余的记录了。

其实这里两天记录我们只用第一条就可以了。row_number就可以搞定哟。

继续

select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn from temp NAME A1 DATE C RN  
select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn from temp NAME A1 DATE C RN 
 

---- ---------- ---- ---------- ---------- --

李四 1100 9-20 0 1

李四 1100 8-20 1100 2

王五 900 9-20 900 1

张三 900 9-20 -100 1

张三 1000 8-20 1000 2

用rn和c=0过滤就可以了，变化为

select name "姓名", c "基本工资" from (

select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn from temp) where rn = 1 and c <>0

姓名 基本工资

---- ----------

王五 900

张三 -100

下面该处理备注了，备注的应该是只有一条记录的，用sum或者count就可以了

sum(1) over(partition by name) 这里要特别注意 开窗函数的range的问题。不能用order by在over里，否则默认的开窗的range不是full range，而是current position。

sum(1) over(partition by name order by date1) 

差别

NAME A1 DATE C RN CNT

---- ---------- ---- ---------- ---------- ----------

李四 1100 9-20 0 1 1

李四 1100 8-20 1100 2 2

王五 900 9-20 900 1 1

张三 900 9-20 -100 1 1

张三 1000 8-20 1000 2 2

 

sum(1) over(partition by name)

NAME A1 DATE C RN CNT

---- ---------- ---- ---------- ---------- ----------

李四 1100 9-20 0 1 2

李四 1100 8-20 1100 2 2

王五 900 9-20 900 1 1

张三 900 9-20 -100 1 2

张三 1000 8-20 1000 2 2

主要是cnt的差别，这是over开窗的一个特性，很多不熟悉的朋友经常这里不知何为了。

去掉order by使用full range

最后的效果如下

select name "姓名", c "基本工资", decode(cnt, 1, '新增', '') "备注" from (select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn, sum(1) over(partition by name) cnt from temp) where rn = 1 and c<>0;

姓名 基本工资 备注

---- ---------- ----

王五 900 新增

张三 -100

这里的题很有趣，也带出了lag lead over这样功能强大的函数。

这里还合理的利用了over的range特性，啊，做这样的题，自己也受益不浅哟，快哉，快哉。

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/inthirties/archive/2009/10/15/4673331.aspx