ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]Oracle/PLSQL: Lag LEAD Function

[转]Oracle/PLSQL: Lag LEAD Function

原创 Linux操作系统 作者:kawontony 时间:2013-08-30 16:26:18 0 删除 编辑

转自:http://www.techonthenet.com/oracle/functions/lead.php

In Oracle/PLSQL, the LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table. To return a value from a previous row, try using the LAG function.

Syntax

The syntax for the LEAD function is:

LEAD ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

expression is an expression that can contain other built-in functions, but can not contain any analytic functions.

offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

Applies To

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

The LEAD function can be used in Oracle/PLSQL.

Let's take a look at an example. If we had an orders table that contained the following data:

ORDER_DATEPRODUCT_IDQTY
25/09/2007100020
26/09/2007200015
27/09/200710008
28/09/2007200012
29/09/200720002
30/09/200710004

And we ran the following SQL statement:

select product_id, order_date,
LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
from orders;

It would return the following result:

PRODUCT_IDORDER_DATENEXT_ORDER_DATE
100025/09/200726/09/2007
200026/09/200727/09/2007
100027/09/200728/09/2007
200028/09/200729/09/2007
200029/09/200730/09/2007
100030/09/2007

Since we used an offset of 1, the query returns the next order_date.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders later. If we had used an offset of 3, it would have returned the order_date from 3 orders later....and so on.

If we wanted only the orders for a given product_id, we could run the following SQL statement:

select product_id, order_date,
LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
from orders
where product_id = 2000;

It would return the following result:

PRODUCT_IDORDER_DATENEXT_ORDER_DATE
200026/09/200728/09/2007
200028/09/200729/09/2007
200029/09/2007

In this example, it returned the next order_date for product_id = 2000 and ignored all other orders.

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

下一篇: [转]Cache Pattern
请登录后发表评论 登录
全部评论

注册时间:2010-11-17

  • 博文量
    164
  • 访问量
    362850