PL/SQL识趣--巧用Over函数

• 作者: 三十而立
• 时间：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)

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

下面是步骤，这里需要前后记录做比对，从而算出异动值，同时有可能只有一条记录的，时间后-时间前，所以用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  

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

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

其实这里两天记录我们只用第一条就可以了。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
 

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

李四 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特性，啊，做这样的题，自己也受益不浅哟，快哉，快哉。

• 如果没有那句“三十而立”，三十岁的男人正可以轻轻松松
• 专业论坛 http://www.inthirties.com
• 技术博客 inthirties的博客_CSDN博客-跟Inthirties学Oracle,Oracle 每日一帖,Oracle数据库管理专栏领域博主
• 个人站点 http://blog.inthirties.com
• Oracle Mysql技术论坛| 打造实用的Oracle Mysql技术交流园地