oracle lag function（标红部分是容易忘的）

Oracle/PLSQL: Lag Function

---

In Oracle/PLSQL, the lag 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

在oracle/PLSQL 里，Lag 函数是一个分析函数，可以在不用进行自连接的情况下获得该表的多行记录。返回值为当前行之前的指定行。

a previous row in the table. To return a value from the next row, try using thelead function.

要返回当前行之后的指定行可以用Lead函数。

The syntax for the lag function is:

lag 函数的语法：

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

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

表达式可以是oracle内置函数，但不能是分析函数

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

偏移量是可选的，用于指明返回行相对当前行的位置，缺省时，默认值为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.

默认值是可以参数，用于设置当偏移量超过表的界限时，lag函数返回的值。缺省时，默认为null

Applies To:

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

oracle 8i起，开始使用