CODEWITHSUNDEEP
sql
Juergen
Juergen

Reputation: 3743

SQL: Get running row delta for records

Let's say we have this table with columns RowID and Call:

RowID  Call   DesiredOut
1       A        0
2       A        0
3       B        
4       A        1
5       A        0
6       A        0
7       B
8       B
9       A        2
10      A        0

I want to SQL query the last column DesiredOut as follows: Each time Call is 'A' go back until 'A' is found again and count the number of records which are in between two 'A' entries.

Example: RowID 4 has 'A' and the nearest predecessor is in RowID 2. Between RowID 2 and RowID 4 we have one Call 'B', so we count 1.

Is there an elegant and performant way to do this with ANSI SQL?

Upvotes: 2

Views: 1275

Answers (5)

TommCatt
TommCatt

Reputation: 5636

Believe it or not, this will be pretty fast if the two columns are indexed.

select  r1.RowID, r1.CallID, isnull( R1.RowID - R2.RowID - 1, 0 ) as DesiredOut
from    RollCall R1
left join RollCall R2
    on  R2.RowID =(
        select  max( RowID )
        from    RollCall
        where   RowID < R1.RowID
            and CallID = 'A')
    and R1.CallID = 'A';

Here is the Fiddle.

Upvotes: 1

user330315
user330315

Reputation:

Here is another solution using window functions:

with flagged as (
  select *,
         case
            when call = 'A' and lead(call) over (order by rowid) <> 'A' then 'end'
            when call = 'A' and lag(call) over (order by rowid) <> 'A' then 'start'
          end as change_flag
  from calls
)
select t1.rowid,
       t1.call,
       case 
         when change_flag = 'start' then rowid - (select max(t2.rowid) from flagged t2 where t2.change_flag = 'end' and t2.rowid < t1.rowid) - 1
         when call = 'A' then 0
       end as desiredout
from flagged t1
order by rowid;

The CTE first marks the start and end of each "A"-Block and the final select then uses these markers to get the difference between the start of one block and the end of the previous one.

If the rowid is not gapless, you can easily add a gapless rownumber inside the CTE to calculate the difference.

I'm not sure about the performance though. I wouldn't be surprised if Gordon's answer is faster.

SQLFiddle example: http://sqlfiddle.com/#!15/e1840/1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270493

I would approach this by first finding the rowid of the previous "A" value. Then count the number of values in-between.

The following query implements this logic using correlated subqueries:

select t.*,
       (case when t.call = 'A'
             then (select count(*)
                   from table t3
                   where t3.id < t.id and t3.id > prevA
                  )
        end) as InBetweenCount
from (select t.*,
             (select max(rowid)
              from table t2
              where t2.call = 'A' and t2.rowid < t.rowid
             ) as prevA
      from table t
     ) t;

If you know that rowid is sequential with no gaps, you can just use subtraction instead of a subquery for the calculation in the outer query.

Upvotes: 2

Andomar
Andomar

Reputation: 238196

You could use a query to find the previous Call = A row. Then, you could count the number of rows between that row and the current row:

select  RowID
,       `Call`
,       (
        select  count(*)
        from    YourTable t2
        where   RowID < t1.RowID
                and RowID > coalesce(
                    (
                    select  RowID
                    from    YourTable t3
                    where   `Call` = 'A'
                            and RowID < t1.RowID
                    order by
                            RowID DESC
                    limit 1
                    ),0)
        )
from    YourTable t1

Example at SQL Fiddle.

Upvotes: 1

frlan
frlan

Reputation: 7270

You could do something like that: 

SELECT a.rowid - b.rowid 
FROM table as a, 
     (SELECT rowid FROM table where rowid < a.rowid order by rowid) as b
WHERE <something>
ORDER BY a.rowid

As I cannot say which DBMS you are using this is more kind of pseudo code which could work based on your system.

Upvotes: 0

Related Questions