Add language support for null treatment clause (RESPECT NULLS | IGNORE NULLS) with offset window functions (LAG, LEAD, FIRST_VALUE
There�s a common need with offset window functions (LAG, LEAD, FIRSTVALUE, LASTVALUE) to request to ignore NULLs. A classic example is to return the last non-NULL in an ordered stream of values. Consider the following sample data:
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1
(
id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
col1 INT NULL
);
INSERT INTO dbo.T1(id, col1) VALUES
( 2, NULL),
( 3, 10),
( 5, -1),
( 7, NULL),
(11, NULL),
(13, -12),
(17, NULL),
(19, NULL),
(23, 1759);
The id column represents the order of the events, and whenever the col1 value is non-NULL, this represents a value change, and from that point, the new value is considered the applicable one. You want to return the last non-NULL col1 value based on id ordering. For this sample data, the desired result is the following:
id col1 lastnonnull
2 NULL NULL
3 10 10
5 -1 -1
7 NULL -1
11 NULL -1
13 -12 -12
17 NULL -12
19 NULL -12
23 1759 1759
The SQL standard supports a <null treatment> clause with offset window functions, allowing you to specify RESPECT NULLS (default) | IGNORE NULLS. With this clause, the above task would be achieved like so:
SELECT id, col1,
LAG(col1) IGNORE NULLS OVER(ORDER BY id) AS lastnonnull
FROM dbo.T1;
Instead, currently we need to resort to more complex solutions such as this one:
WITH C AS
(
SELECT id, col1,
MAX(CASE WHEN col1 IS NOT NULL THEN id END)
OVER(ORDER BY id
ROWS UNBOUNDED PRECEDING) AS grp
FROM dbo.T1
)
SELECT id, col1,
MAX(col1) OVER(PARTITION BY grp
ORDER BY id
ROWS UNBOUNDED PRECEDING) AS lastnonnull
FROM C;
Thanks much for your consideration!
Cheers,
Itzik
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 60
<=-=Mar 29 2017 11:32PM=-=>Just wanted to add that both Oracle and DB2 seem to support this feature:
http://docs.oracle.com/database/122/SQLRF/LAG.htm#SQLRF00652
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm
Cheers,
Itzik
That last IBM link was for Informix!
The DB2 link is https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html
<=-=Jul 4 2017 3:30AM=-=>Thanks for reporting this request.
It is in our backlog, and we have planned to do something like this, but we cannot confirm when it would be completed.
Jovan
<=-=Jul 10 2017 11:54AM=-=>Thanks, Jovan; good to hear.
2 comments
-
Alexander Sharovarov
commented
It very useful feature. It would allow the use of LAST_VALUE function for filling gaps in the data. A very common problem in data analysis and data science. If it is difficult to implement for all functions it should at least implemented for the LAST_VALUE function.
-
Koen Verbeeck
commented
This is a must have. It would make writing window functions a lot easier when NULLs are involved.