Oracle analysis functions Lead (), Lag ()

The Oracle analysis functions Lead () and Lag () are actually very well understood. Lead () is to take the next record of the current order, relative to Lag () is to take the last row of records in the current order. It is often necessary to determine the time difference between two records under certain conditions. The use of the Lead () function is the same as that of Lag (), so you only need to describe one function. Syntax structure: lead (value_expr [, offset] [, default]) over ([query_partition_clause] order by Order_by_clause) parameter description: value_expr value expression, usually a field or expression. Value_expr itself does not support analysis functions, that is, lead does not support multi-layer calls. Offset, which should be a familiar mathematical concept or relative offset. The table is used to open the offset row of the current row, if offset is an integer, it indicates the nth row before the order, and if it is a negative number, it indicates the nth row after the order. If this parameter is not provided, the default value is 1. default. If no value is found, what value should be returned, which is similar to nvl (col, value ). If it is not set and cannot be found, the returned Nullover is considered to be within the range of a result set. If the following partition by is null, it is within the range of the current result set. The query_partition_clause partition statement is optional for the result Set Partition statement. If not, it is all partitions. The Order_by_clause sorting statement is required, for example, order by xxx desc/asc. Here we will introduce the Lag () function, which is the data of the fields in N rows. SQL code with tmp as (select '1' id, 'A' name from dual union all select '2' id, 'bb' name from dual) select. *, lag (name, 1) over (order by id desc) lag from tmp