SQL - Window function to get values from previous row where value is not null

Question

I am using Exasol, in other DBMS it was possible to use analytical functions such LAST_VALUE() and specify some condition for the ORDER BY clause withing the OVER() function, like:

select ...
LAST_VALUE(customer) 
OVER (PARTITION BY ID ORDER BY date_x DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) as the_last

Unfortunately I get the following error:

ERROR: [0A000] Feature not supported: windowing clause (Session: 1606983630649130920)

the same do not happen if instead of AND 1 PRECEDING I use: CURRENT ROW.

Basically what I wanted is to get the last value according the Order by that is NOT the current row. In this example it would be the $customer of the previous row.

I know that I could use the LAG(customer,1) OVER ( ...) but the problem is that I want the previous customer that is NOT null, so the offset is not always 1...

How can I do that?

Many thanks!

Answer 1

Does this work?

select lag(customer) over (partition by id
                           order by (case when customer is not null then 1 else 0 end),
                                    date
                          )

You can do this with two steps:

select t.*, 
       max(customer) over (partition by id, max_date) as max_customer
from (select t.*,
             max(case when customer is not null then date end) over (partition by id order by date) as max_date
      from t
     ) t;