oracle之统计函数lead，lag

最新推荐文章于 2024-08-30 09:55:57 发布

雅冰石

最新推荐文章于 2024-08-30 09:55:57 发布

阅读量1.4k

点赞数

分类专栏： # ORACLE DEVELOPER

ORACLE DEVELOPER 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

一,Lead 语法及例子
Lead函数是十分的好用的一个函数.它的语法如下图:

简单地说，lead是个奇特函数，在允许不使用自连接的情况下，一次返回多行。
参数说明：
       value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。
        offset 偏移，应该是很熟悉的数学概念了，或者是相对偏移，表格来开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。如果不提供这个参数，就是默认为1.
        default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null
        over 可以简单地翻译为在什么。。。的基础之上
        query_partition_clause 分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。
        Order_by_clause 排序语句必须需要 ,形如order by xxx desc/asc

举例一：
有TABLE TJGMXLS(LSH INT UNIQUE,KHH VARCHAR2(20),RQ NUMBER(8),CJSL NUMBER(12));
现在要查询连续三天CJSL都大于1000的记录,则可以按照以下方式查询.
已经假定了每天都有数据.
select * from (
select cjrq,khh,
           lead(cjrq,1) over (order by cjrq) as next_day,
           cjsl,lead(cjsl,1) over (order by cjrq) as next_cjsl,
           lead(cjrq,2) over (order by cjrq) as next2_day,
           lead(cjsl,2) over (order by cjrq) as next2_cjsl
                from
                   (select khh,cjrq,sum(cjsl) cjsl from
                tjgmxls group by khh,cjrq)
                 where khh='000100000012' ) a
   where a.cjsl>1000 and a.next_cjsl>1000 and a.next2_cjsl>1000
结果如下:
     CJRQ      KHH               CJSL    NEXT_DAY NEXT_CJSL NEXT2_DAY NEXT2_CJSL
--------- ------------ ---------- ---------- ---------- ---------- ----------------------------------------------------
20070801 000100000012      11049   20070802     211185   20070803     282227
20070807 000100000012      75419   20070808     454943   20070809     109248
20070808 000100000012     454943   20070809     109248   20070810     240963
20070809 000100000012     109248   20070810     240963   20070813      24302
20070810 000100000012     240963   20070813      24302   20070814      38201
20070813 000100000012      24302   20070814      38201   20070816       7322
20070818 000100000012       4383   20070820       4434   20070821       5702
20070820 000100000012       4434   20070821       5702   20070822      69022
20070821 000100000012       5702   20070822      69022   20070823      52327
--------------------------------------------------------------------------------------------------------------------
举例二：
通过这个例子应该能够很清楚的了解lead是如何工作的了。
SQL> select * from test_value;
              MONS JJR               CJL       CJJE
        ---------- ---------- ---------- ----------
            200801 LZF               250       1999
            200802 LZF               200       2000
            200803 LZF               300       1000
            200804 LZF                23        189
            200805 LZF               356        456
            200806 LZF               100        200
            200807 LZF               600        700
            200808 LZF                23        123
            200809 LZF               400        500

        9 rows selected

        SQL> select rownum 序号,Mons,cjl cjl_01,
        2 lead(cjl,1) over (order by mons desc) cjl_02,
        3 lead(cjl,2) over (order by mons desc) cjl_03,
        4 lead(cjl,3) over (order by mons desc) cjl_04,
        5 lead(cjl,4) over (order by mons desc) cjl_05,
        6 lead(cjl,5) over (order by mons desc) cjl_06,
        7 lead(cjl,6) over (order by mons desc) cjl_07,
        8 lead(cjl,7) over (order by mons desc) cjl_08,
        9 lead(cjl,8) over (order by mons desc) cjl_09
        10   from test_value
        11 /

              序号       MONS     CJL_01     CJL_02     CJL_03     CJL_04     CJL_05     CJL_06     CJL_07     CJL_08     CJL_09
        ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                 9     200809        400         23        600        100        356         23        300        200        250
                 8     200808         23        600        100        356         23        300        200        250
                 7     200807        600        100        356         23        300        200        250
                 6     200806        100        356         23        300        200        250
                 5     200805        356         23        300        200        250
                 4     200804         23        300        200        250
                 3     200803        300        200        250
                 2     200802        200        250
                 1     200801        250

        9 rows selected

二）和LAG函数的区别以及转换
LAG函数的格式和LEAD一样，而且是容易和LEAD混淆的。不过看看它们翻译过来的意思，应该就能大概了解：
LEAD ：前导，向前； LAG:落后。
它们就是对反义词。

先看看个查询吧,并把lead的查询结果放在后面比较。

SQL> select rownum 序号,Mons,cjl cjl_01,
2   LAG(cjl,1) over (order by mons desc) cjl_02,
3 LAG(cjl,2) over (order by mons desc) cjl_03,
4 LAG(cjl,3) over (order by mons desc) cjl_04,
5 LAG(cjl,4) over (order by mons desc) cjl_05,
6 LAG(cjl,5) over (order by mons desc) cjl_06,
7 LAG(cjl,6) over (order by mons desc) cjl_07,
8 LAG(cjl,7) over (order by mons desc) cjl_08,
9 LAG(cjl,8) over (order by mons desc) cjl_09
10 from test_value;

      序号                                    MONS     CJL_01     CJL_02     CJL_03     CJL_04     CJL_05     CJL_06     CJL_07     CJL_08     CJL_09
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         9          200809        400
         8          200808         23        400
         7          200807        600         23        400
         6          200806        100        600         23        400
         5          200805        356        100        600         23        400
         4          200804         23        356        100        600         23        400
         3          200803        300         23        356        100        600         23        400
         2          200802        200        300         23        356        100        600         23        400
         1          200801        250        200        300         23        356        100        600         23        400
-------------------------------------lead的数据在下面
         9    200809        400         23        600        100        356         23        300        200        250
         8          200808         23        600        100        356         23        300        200        250
         7          200807        600        100        356         23        300        200        250
         6          200806        100        356         23        300        200        250
         5          200805        356         23        300        200        250
         4          200804         23        300        200        250
         3          200803        300        200        250
         2          200802        200        250
         1          200801        250

它们的区别最重要的在于：
1)LEAD 访问的是结果集合位于当前记录之后的数据。
2)LAG 范围的是结果集合位于当前记录之前的数据。
有点拗口！还是以上面的例子来说明下。
先定义两个字：
前记录：指的是后于当前记录输出的记录，在屏幕上就是指在当前记录下方列出的数据。
后记录：指的是限于当前记录输出的记录，在屏幕上就是位于当前记录之上的数据。
例如LEAD(CJL,1) OVER (ORDER BY MONS DESC) ,它是这么访问数据的：
1)首先，从数据搜索满足条件（满足where..)的数据，然后把这些数据按照MONS 倒序排序,这个时候的结果集合就称为"初步结果"
2)如果当前记录的Mons＝200809，那么系统就找到前面一条记录，也就是mons=200808的记录，并把cjl(23)放在当前行。
3)其它行，依此类推。
而LAG，和这个最主要的区别在于上面的第2步骤，这个时候系统往后找，对于MONS=200809而言，后面已经没有数据了，所以返回NULL。

最后，如果适当修改下查询，它们是可以达到同样效果的，例如修改lad语句如下：

select rownum 序号,Mons,cjl cjl_01,
LAG(cjl,1) over (order by mons ASC) cjl_02,
LAG(cjl,2) over (order by mons ASC) cjl_03,
LAG(cjl,3) over (order by mons asc) cjl_04,
LAG(cjl,4) over (order by mons asc) cjl_05,
LAG(cjl,5) over (order by mons asc) cjl_06,
LAG(cjl,6) over (order by mons asc) cjl_07,
LAG(cjl,7) over (order by mons asc) cjl_08,
LAG(cjl,8) over (order by mons asc) cjl_09
from test_value;
这里仅仅是改变了下排序的顺序，从降序变为升序。
结果如下：
       序号                                    MONS     CJL_01     CJL_02     CJL_03     CJL_04     CJL_05     CJL_06     CJL_07     CJL_08     CJL_09
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          200801        250
         2          200802        200        250
         3          200803        300        200        250
         4          200804         23        300        200        250
         5          200805        356         23        300        200        250
         6          200806        100        356         23        300        200        250
         7          200807        600        100        356         23        300        200        250
         8          200808         23        600        100        356         23        300        200        250
         9          200809        400         23        600        100        356         23        300        200        250

如果说，还有什么区别，那么就是月份大的结果在最后输出而已.