LAG with condition-CodePudding

I want to get a value from the previous row that matches a certain condition. For example: here I want for each row to get the timestamp from the last event = 1. I feel I can do it without joins with LAG and PARTITION BY with CASE but I am not able to crack it. Please help.

CodePudding user response：

Here is one approach using analytic functions:

WITH cte AS (
    SELECT *, COUNT(CASE WHEN event = 1 THEN 1 END) OVER
                  (PARTITION BY customer_id ORDER BY ts) cnt
    FROM yourTable
)

SELECT ts, customer_id, event,
       MAX(CASE WHEN event = 1 THEN ts END) OVER
           (PARTITION BY customer_id, cnt) AS desired_result
FROM cte
ORDER BY customer_id, ts;

Demo

We can articulate your problem by saying that your want the desired_result column to contain the most recent timestamp value when the event was 1. The count (cnt) in the CTE above computes a pseudo group of records for each time the event is 1. Then we simply do a conditional aggregation over customer and pseudo group to find the timestamp value.

CodePudding user response：

One more approach with "one query":

with data as 
(
select sysdate - 0.29 ts, 111 customer_id, 1 event from dual union all
select sysdate - 0.28 ts, 111 customer_id, 2 event from dual union all
select sysdate - 0.27 ts, 111 customer_id, 3 event from dual union all
select sysdate - 0.26 ts, 111 customer_id, 1 event from dual union all
select sysdate - 0.25 ts, 111 customer_id, 1 event from dual union all
select sysdate - 0.24 ts, 111 customer_id, 2 event from dual union all
select sysdate - 0.23 ts, 111 customer_id, 1 event from dual union all
select sysdate - 0.22 ts, 111 customer_id, 1 event from dual
)
select 
    ts, event, 
    last_value(case when event=1 then ts end) ignore nulls 
        over (partition by customer_id order by ts) desired_result,
    max(case when event=1 then ts end) 
        over (partition by customer_id order by ts) desired_result_2
from data
order by ts

Edit: As suggested by MatBailie the max(case...) works as well and is a more general approach. The "last_value ... ignore nulls" is Oracle specific.