CODEWITHSUNDEEP
sqloracle-databasesumwindow-functionsrow-number
Charles
Charles

Reputation: 27

SQL Conditional Row Number Reset

Suppose I have below user actions. I am looking for an oldest uninterrupted action on accepting document type XX.

  1. Delete action will consider an interruption
  2. If the user already accepted XX but follows by accepting A. it will be interruption.

I am actually looking for liner action accepting XX but it has to be oldest and whenever there's delete we need to reset the baseline.

T11 is the expected result.

EVENTTIME    ACTION      DOCUMENTTYPE   
--------------------------------------
T1           Accept      A
T2           Accept      XX
T3           Delete      XX
T4           Accept      A
T5           Accept      XX
T6           Accept      XX
T7           Delete      XX
T8           Accept      A
T9           Accept      XX
T10          Accept      A
T11          Accept      XX
T12          Accept      XX
T13          Accept      XX

Notes:

  1. T2 is not the result because T3 interrupt the timeline.
  2. T5 is not the result because T7 interrupt the timeline.
  3. T9 is not the result because T10 accepting A interrupt the liner XX acceptance.
  4. T11 is the result because after above timeline reset. It's the oldest acceptance of XX.

I am thinking row number plus extra grouping(cumulative sum) query solution. Any ideas?

Upvotes: 0

Views: 409

Answers (2)

dnoeth
dnoeth

Reputation: 60472

This returns the 1st row of the last group of Accept/XX rows:

WITH flag_actions AS
 (
   SELECT t.*
     -- used to filter in next step: previous row was not Accept/XX = 1st row of group
     ,Lag(CASE WHEN ACTION = 'Accept' AND DOCUMENTTYPE = 'XX' THEN 0 ELSE 1 END, 1, 1)
      Over (PARTITION BY ???
            ORDER BY EVENTTIME) AS flag
   FROM t
 )
,find_latest AS
 (
   SELECT flag_actions.*
      -- used to filter in next step
     ,Row_Number()
      Over (PARTITION BY ??? 
            ORDER BY EVENTTIME DESC) AS rn
   FROM flag_actions
   WHERE ACTION = 'Accept' -- last row for each group of Accept/XX rows
     AND DOCUMENTTYPE = 'XX'
     AND flag = 1
 )
SELECT * 
FROM find_latest
WHERE rn = 1

I doubt you can get that result without nested OLAP-Functions.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

One method uses not exists:

select min(eventtime)
from t
where not exists (select 1
                  from t t2
                  where t2.eventtime > t.eventtime and t2.DOCUMENTTYPE <> 'XX'
                 );

Another fun method tuses window functions:

select min(eventtime)
from (select t.*,
             row_number() over (order by eventtime desc) as seqnum,
             row_number() over (partition by documenttype order by eventtime desc) as seqnum_dt
      from t
     ) t
where documenttype = 'XX' and
      seqnum = seqnum_dt;

This uses the fact that the final rows that have 'XX' have the same sequential number based only on eventtime or based on eventtime when partitioned by dcoumenttype.

Upvotes: 1

Related Questions