Oracle analytic function-LEAD

有LAG函数获取上个offset记录的函数,就会有获取下一个OFFSET的记录的函数,这个函数就是LEAD函数,这个函数基本上是LAG函数的逆向。

语法(11gR2)
LEAD
{ ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)

本文的原始数据与Oracle analytic function-LAG中使用的数据相同。

原始数据

SQL> select employee_id,hire_date,salary from employees where department_id <= 40 order by hire_date;

EMPLOYEE_ID HIRE_DATE               SALARY
----------- ------------------- ----------
        203 2002-06-07 00:00:00       6500
        114 2002-12-07 00:00:00      11000
        115 2003-05-18 00:00:00       3100
        200 2003-09-17 00:00:00       4400
        201 2004-02-17 00:00:00      13000
        117 2005-07-24 00:00:00       2800
        202 2005-08-17 00:00:00       6000
        116 2005-12-24 00:00:00       2900
        118 2006-11-15 00:00:00       2600
        119 2007-08-10 00:00:00       2500

采用子查询的方式实现lead函数的功能

SQL> select department_id,
  2         employee_id,
  3         salary,
  4         (select salary
  5            from (select department_id, salary, rownum rn
  6                    from (select employee_id, salary, department_id
  7                            from employees
  8                           order by department_id, salary)) a
  9           where a.rn = emp.rn + 1
 10             and a.department_id = emp.department_id) salary_next
 11    from (select employee_id, salary, department_id, rownum rn
 12            from (select *
 13                    from employees
 14                   order by department_id, salary)) emp
 15                   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_ID     SALARY SALARY_NEXT
------------- ----------- ---------- -----------
           10         200       4400
           20         202       6000       13000
           20         201      13000
           30         119       2500        2600
           30         115       2600        2800
           30         118       2800        2900
           30         116       2900        3100
           30         117       3100       11000
           30         114      11000
           40         203       6500

已选择10行。

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |        |       |     3 (100)|       |       |          |
|*  1 |  VIEW                           |                   |     10 |   390 |     3  (34)|       |       |          |
|   2 |   COUNT                         |                   |        |       |            |       |       |          |
|   3 |    VIEW                         |                   |     10 |   260 |     3  (34)|       |       |          |
|   4 |     SORT ORDER BY               |                   |     10 |   110 |     3  (34)|  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |   110 |     2   (0)|       |       |          |
|*  6 |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|       |       |          |
|   7 |  VIEW                           |                   |     10 |   520 |     3  (34)|       |       |          |
|   8 |   COUNT                         |                   |        |       |            |       |       |          |
|   9 |    VIEW                         |                   |     10 |   390 |     3  (34)|       |       |          |
|  10 |     SORT ORDER BY               |                   |     10 |   690 |     3  (34)|  2048 |  2048 | 2048  (0)|
|  11 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |   690 |     2   (0)|       |       |          |
|* 12 |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|       |       |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("A"."DEPARTMENT_ID"=:B1 AND "A"."RN"=:B2+1))
   6 - access("DEPARTMENT_ID"<=40)
  12 - access("DEPARTMENT_ID"<=40)

使用lead函数,offset为1,默认值用0填充。

SQL> select employee_id,
  2         hire_date,
  3         salary,
  4         lead(salary,1,0) over(order by hire_date) as salary_next
  5    from employees
  6   where department_id <= 40
  7   order by hire_date;

EMPLOYEE_ID HIRE_DATE               SALARY SALARY_NEXT
----------- ------------------- ---------- -----------
        203 2002-06-07 00:00:00       6500       11000
        114 2002-12-07 00:00:00      11000        3100
        115 2003-05-18 00:00:00       3100        4400
        200 2003-09-17 00:00:00       4400       13000
        201 2004-02-17 00:00:00      13000        2800
        117 2005-07-24 00:00:00       2800        6000
        202 2005-08-17 00:00:00       6000        2900
        116 2005-12-24 00:00:00       2900        2600
        118 2006-11-15 00:00:00       2600        2500
        119 2007-08-10 00:00:00       2500           0  --0填充

已选择10行。

over()部分增加Group by分组。

SQL> select employee_id,
  2         hire_date,
  3         job_id,
  4         salary,
  5         lead(salary, 1, null) over(partition by job_id order by hire_date) as salary_next
  6    from employees
  7   where department_id <= 40
  8   order by hire_date;

EMPLOYEE_ID HIRE_DATE           JOB_ID                   SALARY SALARY_NEXT
----------- ------------------- -------------------- ---------- -----------
        203 2002-06-07 00:00:00 HR_REP                     6500
        114 2002-12-07 00:00:00 PU_MAN                    11000
        115 2003-05-18 00:00:00 PU_CLERK                   3100        2800
        200 2003-09-17 00:00:00 AD_ASST                    4400
        201 2004-02-17 00:00:00 MK_MAN                    13000
        117 2005-07-24 00:00:00 PU_CLERK                   2800        2900
        202 2005-08-17 00:00:00 MK_REP                     6000
        116 2005-12-24 00:00:00 PU_CLERK                   2900        2600
        118 2006-11-15 00:00:00 PU_CLERK                   2600        2500
        119 2007-08-10 00:00:00 PU_CLERK                   2500

IGNORE NULL获取到的结果

SQL> select employee_id,hire_date,salary,lead(salary,1,null) ignore nulls over (order by employee_id) as salary_next
  2  from t_lag
  3  order by employee_id;

EMPLOYEE_ID HIRE_DATE               SALARY SALARY_NEXT
----------- ------------------- ---------- -----------
        114 2002-12-07 00:00:00      11000        3100
        115 2003-05-18 00:00:00       3100        2900
        116 2005-12-24 00:00:00       2900        2800
        117 2005-07-24 00:00:00       2800        2500
        118 2006-11-15 00:00:00                   2500
        119 2007-08-10 00:00:00       2500        4400
        200 2003-09-17 00:00:00       4400       13000
        201 2004-02-17 00:00:00      13000        6000
        202 2005-08-17 00:00:00       6000        6500
        203 2002-06-07 00:00:00       6500
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s