Oracle分析函数Lead()，Lag()

其实这2个函数的作用非常好理解，Lead()就是取当前顺序的下一条记录，相对Lag()就是取当前顺序的上一行记录。

经常会有判断在一定条件下的两条记录之间的时间差这样的需求。

Lead()函数的用法和Lag()是一样的，所以只说明一个就可以了。

 

一、语法结构：

lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)

二、参数说明：

value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。

offset 偏移，应该是很熟悉的数学概念了，或者是相对偏移，表格来开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。 如果不提供这个参数，就是默认为1.

default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null

over  理解成在一个结果集范围内，如果后面的partition by为空，那么就是当前的结果集范围内。

query_partition_clause  分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。

Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc

 

用一个很简单的例子来介绍一下用法。

这里介绍的是Lag()函数，就是去上N行的字段的数据。

Sql代码  

点击(此处)折叠或打开

1. with tmp as(
   
2. select '1' id ,'aa' name from dual union all
   
3. select '2' id ,'bb' name from dual
   
4. )
   
5. select a.*,
   
6.        lag(name,1) over (order by id desc) lag
   
7. from tmp a ;

其中，lead()的用法根据需求而调整：
lead(name) over(partion by [] order by []) 表示获取分组排序后的当前分组中每一行的下一行的name值（下面举例中有使用）。
lead(name，1) over(partion by []  order by [] ) 表示获取分组排序后的当前分组中每一行的下一行的name 值（上面举例中有使用），参数‘1’是对上一种写法 的补全，‘1’也可以修改为‘2’，‘3’等 。
lead(null，1,'Y') over(partion by []  order by [] ) 表示获取分组排序后的如果当前分组该行的下一行为空，则返回'Y' （下面举例中有使用） 。

三、使用举例：

我有一张表保存了“电影大片奖”中不同类别的电影的得票数：

点击(此处)折叠或打开

1. create table plch_movies (
   
2.    category varchar2(10) not null
   
3. , name varchar2(20) not null
   
4. , votes integer not null
   
5. , constraint plch_movies_pk primary key (category, name)
   
6. )
   
7. /
   
8. 
   
9. insert into plch_movies values ('Sci-Fi' , 'Star Wars' , 700)
   
10. /
    
11. insert into plch_movies values ('Sci-Fi' , 'The Matrix', 500)
    
12. /
    
13. insert into plch_movies values ('Sci-Fi' , 'Aliens' , 500)
    
14. /
    
15. insert into plch_movies values ('Western', 'Unforgiven', 600)
    
16. /
    
17. insert into plch_movies values ('Western', 'High Noon' , 400)
    
18. /
    
19. insert into plch_movies values ('Western', 'Rio Bravo' , 300)
    
20. /
    
21. commit
    
22. /

我想要以类别的字母顺序，然后按得票的降序（从多到少）来显示电影，如果同一类别有两个电影的得票数相同，则以电影名称的字母顺序来输出。

但是显示这个结果的客户软件还需要知道每个类别的最后一行是什么，以便于在每个类别之间放置一个图形的分割符。

所以我需要在输出中包含一个名为LAST_ROW的列，如果是同类别的最后一行则值为'Y'，其他行则为NULL。我这有个不完整的查询：

select category
     , name
     , votes
##REPLACE##
  from plch_movies
order by category
        , votes desc
        , name
/

哪些选项所包含的表达式可以用来取得上述查询中的##REPLACE##，使得它返回这个输出：

CATEGORY   NAME                      VOTES L
---------- -------------------- ---------- -
Sci-Fi     Star Wars                   700
Sci-Fi     Aliens                      500
Sci-Fi     The Matrix                  500 Y
Western    Unforgiven                  600
Western    High Noon                   400
Western    Rio Bravo                   300 Y

(A)
     , lead(null, 1, 'Y') over (
          partition by category
          order by votes desc, name
       ) last_row

(B)
     , lag(null, 1, 'Y') over (
          partition by category
          order by votes, name desc
       ) last_row

(C)
     , case
          when lead(name) over (
                  partition by category
                  order by votes desc, name
               ) is null then
             'Y'
       end last_row

(D)
     , nvl2(
          lead(name) over (
             partition by category
             order by votes desc, name
          )
        , null
        , 'Y'
       ) last_row

(E)
     , case
          when row_number() over (
                  partition by category
                  order by votes, name desc
               ) = 1 then
             'Y'
       end last_row
       
(F)
     , case
          when row_number() over (
                  partition by category
                  order by votes desc, name
               ) = count(*) over (
                  partition by category
               ) then
             'Y'
       end last_row       

(G)
     , case
          when last_value(name) over (
                  partition by category
                  order by votes desc, name
                  rows between current row and unbounded following
               ) = name then
             'Y'
       end last_row

上面中的A,B,C,D,E,F,G的结果都是相同的。也就是题目需要的结果。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30260000/viewspace-1852169/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30260000/viewspace-1852169/