loading..
Ðóññêèé    English
23:39

LAG and LEAD functions

Syntax:

  1. LAG | LEAD (scalar_expression [,OFFSET] [,DEFAULT])
  2.     OVER ( [ partition_by_clause ] order_by_clause )

Window functions LAG and LEAD have appeared in  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server in version 2012.

These functions return value of expression calculated for previous (LAG) or next (LEAD) row of the result set respectively. Let's consider the example of simple query that deduces codes of printers along with codes of previous and next rows:

Console
Execute
  1. SELECT  code,
  2. LAG(code) OVER(ORDER BY code) prev_code,
  3. LEAD(code) OVER(ORDER BY code) next_code
  4. FROM printer;

code    prev_code    next_code
1    NULL    2
2    1    3
3    2    4
4    3    5
5    4    6
6    5    NULL

Notice that if the next or previous row (in the ascending order of code values) does not exist, NULL value is used. However this behavior can be changed with aid of optional argument (the third one) of each function. Value of this argument will be used in the case when the corresponding row does not exist. In the following example the value of -999 is used when the previous row does note. In the following example, the one row is missed.

Console
Execute
  1. SELECT  code,
  2. LAG(code,1,-999) OVER(ORDER BY code) prev_code,
  3. LEAD(code,1,999) OVER(ORDER BY code)  next_code
  4. FROM printer;

code    prev_code    next_code
1    -999    2
2    1    3
3    2    4
4    3    5
5    4    6
6    5    999

To apply the third argument, we have been forced to use the second optional argument with value of 1 which is a default for this argument. This argument defines which row among previous rows (or the next ones) should be used with respect to the current row. In the following example, the second row with respect to current row is used.

Console
Execute
  1. SELECT  code,
  2. LAG(code,2,-999) OVER(ORDER BY code) prev_code,
  3. LEAD(code,2,999) OVER(ORDER BY code) next_code
  4. FROM printer;

code    prev_code    next_code
1    -999    3
2    -999    4
3    1    5
4    2    6
5    3    999
6    4    999

Finally note that the order in which the previous or next rows are being picked is given by the ORDER BY clause in OVER clause, but not by the order used for the sorting the query result set. The following query illustrates what was being said.

Console
Execute
  1. SELECT  code,
  2. LAG(code) OVER(ORDER BY code) prev_code,
  3. LEAD(code) OVER(ORDER BY code) next_code
  4. FROM printer
  5. ORDER BY code DESC;

code    prev_code    next_code
6    5    NULL
5    4    6
4    3    5
3    2    4
2    1    3
1    NULL    2

Let's consider the "classic" solutions for this task to compare these ones with new approach in SQL.

Self join

Console
Execute
  1. SELECT p1.code,p3.code,p2.code
  2. FROM printer p1 LEFT JOIN Printer p2 ON p1.code=p2.code-1
  3. LEFT JOIN Printer p3 ON p1.code=p3.code+1;

Correlated subqueries

Console
Execute
  1. SELECT p1.code,
  2. (SELECT MAX(p3.code) FROM Printer p3 WHERE p3.code < p1.code) prev_code,
  3. (SELECT MIN(p2.code) FROM Printer p2 WHERE p2.code > p1.code) next_code
  4. FROM printer p1;

Suggested exercises: 126, 130145

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
https://exchangesumo.com/obmen/to/GPBRUB/ . Êàê ðàññ÷èòàòü êåðàìè÷åñêóþ ïëèòêó íà ïîë è ñòåíû?
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.