New sql features

Window functions

Syntax:

LAG | LEAD (scalar_expression [,OFFSET] [,DEFAULT])
    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

SELECT  code, 
LAG(code) OVER(ORDER BY code) prev_code, 
LEAD(code) OVER(ORDER BY code) next_code
FROM printer;

select  code, LAG(code) OVER(ORDER BY code) prev_code, LEAD(code) OVER(ORDER BY code) next_code from printer;

    Scheme

No scheme

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

SELECT  code, 
LAG(code,1,-999) OVER(ORDER BY code) prev_code, 
LEAD(code,1,999) OVER(ORDER BY code)  next_code
FROM printer;

select  code, LAG(code,1,-999) OVER(ORDER BY code) prev_code, LEAD(code,1,999) OVER(ORDER BY code)  next_code from printer;

    Scheme

No scheme

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

SELECT  code, 
LAG(code,2,-999) OVER(ORDER BY code) prev_code, 
LEAD(code,2,999) OVER(ORDER BY code) next_code
FROM printer;

select  code, LAG(code,2,-999) OVER(ORDER BY code) prev_code, LEAD(code,2,999) OVER(ORDER BY code) next_code from printer;

    Scheme

No scheme

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

SELECT  code, 
LAG(code) OVER(ORDER BY code) prev_code, 
LEAD(code) OVER(ORDER BY code) next_code
FROM printer 
ORDER BY code DESC;

select  code, LAG(code) OVER(ORDER BY code) prev_code, LEAD(code) OVER(ORDER BY code) next_code from printer order by code desc;

    Scheme

No scheme

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

Self join

Console

Execute

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

select p1.code,p3.code,p2.code from printer p1 left join Printer p2 on p1.code=p2.code-1 left join Printer p3 on p1.code=p3.code+1;

    Scheme

Correlated subqueries

Console

Execute

SELECT p1.code,
(SELECT MAX(p3.code) FROM Printer p3 WHERE p3.code < p1.code) prev_code,
(SELECT MIN(p2.code) FROM Printer p2 WHERE p2.code > p1.code) next_code 
FROM printer p1;

select p1.code, (select max(p3.code) from Printer p3 where p3.code < p1.code) prev_code, (select min(p2.code) from Printer p2 where p2.code > p1.code) next_code from printer p1;

    Scheme

Suggested exercises: 126, 130, 145