Search code examples
sqldb2lag

Can Db2 LAG function refer to itself?

I'm trying to put information to identify GROUP ID by replicating this Excel formula:

IF(OR(A2<>A1,AND(B2<>"000",B1="000")),D1+1,D1)

This formula is written when my cursor is in "D2", meaning I've referred to the newly added column value in the previous row to generate the current value.

I'd like to this with Db2 SQL, but I'm not sure how to because I'll need to do LAG function on the column I'm going to add and referring their value.

enter image description here

Kindly advise if having better way to do.

Thanks.

Solution

  • You need nested OLAP-functions, assuming ORDER BY SERIAL_NUMBER, EVENT_TIMESTAMP returns the order shown in Excel:

    with cte as 
 (
    select ...
       case --IF(OR(A2<>A1,AND(B2<>"000",B1="000"))
         when (lag(OPERATION)
              over (order by SERIAL_NUMBER, EVENT_TIMESTAMP) = '000'
              and OPERATION <> '000')
           or lag(SERIAL_NUMBER,1,'')
              over (order by SERIAL_NUMBER, EVENT_TIMESTAMP) <> SERIAL_NUMBER
         then 1
         else 0
       end as flag -- start of new group
    from tab
 ) 
select ...
   sum(flag)
   over (order by SERIAL_NUMBER, EVENT_TIMESTAMP
         rows unbounded preceding) as GROUP_ID
from cte