LAG and LEAD Analytic Functions

The LAG and LEAD analytic functions were introduced in 8.1.6 to give access to multiple rows within a table, without the need for a self-join. The following examples use the EMP table from the SCOTT sample schema, which is created as follows.

CONN sys/password AS SYSDBA-- Create SCOTT schema if you don't currently have it.@$ORACLE_HOME/rdbms/admin/utlsampl.sqlALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

Both functions have the same usage, as shown below.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)

• value_expression - Can be a column or a built-in function, except for other analytic functions.
• offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
• default - The value returned if the offset is outside the scope of the window. The default value is NULL.

Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno,       ename,       job,       salFROM   empORDER BY sal;     EMPNO ENAME      JOB              SAL---------- ---------- --------- ----------      7369 SMITH      CLERK            800      7900 JAMES      CLERK            950      7876 ADAMS      CLERK           1100      7521 WARD       SALESMAN        1250      7654 MARTIN     SALESMAN        1250      7934 MILLER     CLERK           1300      7844 TURNER     SALESMAN        1500      7499 ALLEN      SALESMAN        1600      7782 CLARK      MANAGER         2450      7698 BLAKE      MANAGER         2850      7566 JONES      MANAGER         2975      7788 SCOTT      ANALYST         3000      7902 FORD       ANALYST         3000      7839 KING       PRESIDENT       500014 rows selected.SQL>

Next, we use the LAG function to return the salary from the previous row, and to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno,       ename,       job,       sal,       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diffFROM   emp;     EMPNO ENAME      JOB              SAL   SAL_PREV   SAL_DIFF---------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK            800          0        800      7900 JAMES      CLERK            950        800        150      7876 ADAMS      CLERK           1100        950        150      7521 WARD       SALESMAN        1250       1100        150      7654 MARTIN     SALESMAN        1250       1250          0      7934 MILLER     CLERK           1300       1250         50      7844 TURNER     SALESMAN        1500       1300        200      7499 ALLEN      SALESMAN        1600       1500        100      7782 CLARK      MANAGER         2450       1600        850      7698 BLAKE      MANAGER         2850       2450        400      7566 JONES      MANAGER         2975       2850        125      7788 SCOTT      ANALYST         3000       2975         25      7902 FORD       ANALYST         3000       3000          0      7839 KING       PRESIDENT       5000       3000       200014 rows selected.SQL>

The following example uses the LEAD function to return the salary from the next row, and to calulate the difference between the salary of the current row and the following row.

SELECT empno,       ename,       job,       sal,       LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,       LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diffFROM   emp;     EMPNO ENAME      JOB              SAL   SAL_NEXT   SAL_DIFF---------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK            800        950        150      7900 JAMES      CLERK            950       1100        150      7876 ADAMS      CLERK           1100       1250        150      7521 WARD       SALESMAN        1250       1250          0      7654 MARTIN     SALESMAN        1250       1300         50      7934 MILLER     CLERK           1300       1500        200      7844 TURNER     SALESMAN        1500       1600        100      7499 ALLEN      SALESMAN        1600       2450        850      7782 CLARK      MANAGER         2450       2850        400      7698 BLAKE      MANAGER         2850       2975        125      7566 JONES      MANAGER         2975       3000         25      7788 SCOTT      ANALYST         3000       3000          0      7902 FORD       ANALYST         3000       5000       2000      7839 KING       PRESIDENT       5000          0      -500014 rows selected.-----------------------------------------------------------------------------------------

Example 1:

CREATE TABLE sensor_data
(sensor_id VARCHAR2(6),
measurement_id NUMBER(9),
measurement_value NUMBER(6,3),
measurement_datetime DATE
);

INSERT INTO sensor_data VALUES ('UNIT1', 1, 1.234, SYSDATE);
INSERT INTO sensor_data VALUES ('UNIT1', 2, 1.240, SYSDATE);
INSERT INTO sensor_data VALUES ('UNIT1', 4, 1.237, SYSDATE);
INSERT INTO sensor_data VALUES ('UNIT1', 5, 1.240, SYSDATE);
INSERT INTO sensor_data VALUES ('UNIT1', 7, 1.235, SYSDATE);
COMMIT;

WITH aquery AS
(SELECT measurement_id after_gap,
         LAG(measurement_id, 1, 0) OVER(ORDER BY measurement_id) before_gap
    FROM sensor_data)
SELECT before_gap, after_gap
  FROM aquery
WHERE before_gap != 0 AND after_gap - before_gap > 1
ORDER BY before_gap;

drop table sensor_data;