Windowing Clause … Why it matters

Windowing clause in Analytical ( Oracle ) / Windows functions ( SQL Server ) plays an important role of how the results are rendered. This can be better explained with the following use case. Here is an use case.

A employee may have multiple email addresses in a table. The requirement was to display the first non null email address.

Listing 1

Create table tab_t 
( id int , 
 lname varchar2(20) ,
 fname varchar2(20) ,
 start_dt date , 
 end_dt date , 
 sal number(10,2) , 
 email varchar2(20) 
 ) ;

insert into tab_t values ( 1 , 'LA1' , 'FI1' , to_date('10-01-1992' , 'MM-DD-YYYY') , null , 1000.00 , 'fn1@email.com');
insert into tab_t values ( 1 , 'LA1' , 'FI1' , to_date('12-24-2000' , 'MM-DD-YYYY') , to_date('02-15-2002' , 'MM-DD-YYYY')  , 7.25 , null ); 
insert into tab_t values ( 1 , 'LA1' , 'FI1' , to_date('05-16-1999' , 'MM-DD-YYYY') ,  to_date('11-15-2000' , 'MM-DD-YYYY') , 5.25 , null );
insert into tab_t values ( 2 , 'LA2' , 'FI2' , to_date('01-01-1993' , 'MM-DD-YYYY') , to_date('01-01-1995' , 'MM-DD-YYYY'), 75000.00 , 'fn2@email.com');
insert into tab_t values ( 2 , 'LA2' , 'FI2' , to_date('12-24-2000' , 'MM-DD-YYYY') , to_date('02-15-2002' , 'MM-DD-YYYY')  , 7.25 , 'fn2@newemail.com' ); 
insert into tab_t values ( 3 , 'LA3' , 'FI3' , to_date('07-02-1970' , 'MM-DD-YYYY') , to_date('02-04-1980' , 'MM-DD-YYYY')  , 3.25  , null );
insert into tab_t values ( 3 , 'LA3' , 'FI3' , to_date('02-05-1980' , 'MM-DD-YYYY') , to_date('02-04-1980' , 'MM-DD-YYYY')  , 10250.00  , 'myfirst@email.com' );
insert into tab_t values ( 4 , 'LA4' , 'FI4' , to_date('11-01-2009' , 'MM-DD-YYYY') , null , 90000  , 'mywork@email.com' );

SQL> Select * from tab_t;

Here is the SQL to list the first non null email address and we will filter the results where rn =1 in the second image below.

Listing 2

SELECT
t.*,
FIRST_VALUE(email) IGNORE NULLS OVER(
PARTITION BY t.id
ORDER BY
coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
) as  fv_email,
ROW_NUMBER() OVER(
PARTITION BY t.id
ORDER BY
coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
) as rn
FROM
tab_t t

Listing 3

Select * from
(
SELECT
t.*,
FIRST_VALUE(email) IGNORE NULLS OVER(
PARTITION BY t.id
ORDER BY
coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
) as fv_email,
ROW_NUMBER() OVER(
PARTITION BY t.id
ORDER BY
coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
) as rn
FROM
tab_t t
)
Where rn = 1

Looks like , the SQL works fine.

Let us add few more records , where the first active record has null email address.

insert into tab_t values
 ( 5 , ‘LA5’ , ‘FI5’ , to_date(‘10–01–1992’ , ‘MM-DD-YYYY’) , null , 1000.00 , null);
insert into tab_t values 
( 5 , 'LA5' , 'FI5' , to_date('12-24-2000' , 'MM-DD-YYYY') , to_date('02-15-2002' , 'MM-DD-YYYY')  , 7.25 , 'email.com' ); 
insert into tab_t values 
( 5 , 'LA5' , 'FI5' , to_date('05-16-1999' , 'MM-DD-YYYY') ,  to_date('11-15-2000' , 'MM-DD-YYYY') , 5.25 , null );

Let us execute the SQL in Listing 2 and see the results.

Here is the result after executing the SQL in Listing 3.

Obviously , our SQL did not address all the scenarios . We need the email.com as the first non null email address for Employee 5. This is because , how the “window” is defined . By default , the window is from “unbounded rows preceding” to the “CURRENT ROW” . Let us change the “Window” to “UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING” as seen below.

Listing 3

SELECT
t.*,
FIRST_VALUE(email) IGNORE NULLS OVER(
PARTITION BY t.id
ORDER BY
coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as fv_email,
ROW_NUMBER() OVER(
PARTITION BY t.id
ORDER BY
coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
) as rn
FROM
tab_t t

Listing 4

SQL with filter only the first record.

Select * from
(
SELECT
     t.*,
     FIRST_VALUE(email) IGNORE NULLS OVER(
         PARTITION BY t.id
         ORDER BY
             coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
     ) as  fv_email,
     ROW_NUMBER() OVER(
         PARTITION BY t.id
         ORDER BY
             coalesce(end_dt,TO_DATE('01-JAN-2050') ) DESC,start_dt DESC
     ) as rn 
 FROM
     tab_t t
) 
Where rn = 1

Now , with changing the “window “ , we are able to get the correct results.

Comments welcome.

Originally published at https://zahirmohideen.blogspot.com/2018/10/windowing-clause-why-it-matters.html