LAG and the first NULL

Question

I have the below table:

id    date       cust
1     3/13/2019  
1     3/14/2019  Johnson
1     3/19/2019

I want to create a column that captures the last cust entry and partition by id.

I have the below..

select *
,case
 when a.cust is not null then a.cust
 else lag(a.cust) over partition by a.id order by a.date)
 end lst_cust
from A

Results:

id  date        cust
 1   3/13/2019
 1   3/14/2019  Johnson
 1   3/19/2019  Johnson

How do I capture "Johnson" for row one?

I was thinking about using lead too but not sure how to embed both in the case expression and if that's what I'm looking for. Or LAST_VALUE with nulls first but can't see to get it to work.

Simple forms of `LEAD()` and `LAG()` will capture values from adjacent rows only. What if you have 5 rows, or 7, or 9 or 99? — The Impaler, Apr 15 '19 at 13:58
But isn't capturing John for the first entry a logical error if you want the customer of the last business day for every entry? What is it that you really want? — kutschkem, Apr 15 '19 at 13:58

score 2 · Accepted Answer · answered Apr 15 '19 at 14:06

last_value is a good idea, just add window clause:

select id, date_, 
       nvl(cust, last_value(cust) ignore nulls over (partition by id order by date_ 
                 rows between unbounded preceding and unbounded following)) cust
  from a
  order by id, date_

^demo

score 0 · Answer 2 · answered Apr 15 '19 at 13:58

I think you want both lead() and lag():

select a.*,
       coalesce(a.cust, 
                lag(a.cust) over partition by a.id order by a.date),
                lead(a.cust) over partition by a.id order by a.date)
               ) as lst_cust
from A;

You may also want an ignore nulls in there, if you can have more than one null in a row.

LAG and the first NULL

2 Answers2