Oracle lag and lead analysis functions

The lag and lead analysis functions can retrieve the data of the first n rows of the same field (Lag) and the data of the last n rows (LEAD) as independent columns in the same query.

This operation can replace table self-join, and lag and lead have higher efficiency.

Syntax:

/* Syntax */lag (exp_str, offset, defval) over () lead (exp_str, offset, defval) over () -- exp_str: column to be retrieved -- the number of rows of data after offset -- defval: no default value that meets the condition

1.

/* Basic Data */select T. * From test1 T;

2.

/* Use lag and lead to analyze future data */select T. ID, lag (T. name, 1, 0) over (order by id desc) max_v, T. name, lead (T. name, 1, 0) over (order by id desc) min_vfrom test1 T;

3.

/* Data that meets the query results */select I. * From (select T. ID, lag (T. name, 1, 0) over (order by id desc) max_v, T. name, lead (T. name, 1, 0) over (order by id desc) min_v from test1 t) I where I. name = '3a ';