1

I have a question about running totals, I know there are several approaches. However I have slight modification of it and I'm struggling to find the right way.

So I have orders, each order has its unique id and quantity. Quantity can go up (buying) and down (selling). Ids go in order but are not 'dense', so there might be a gap between two subsequent orders. I need to select all orders that represent the oldest position. Position is a continuous range of orders, overall sum of those is 0, example (id, qty): (1, 1) (2,2) (3,-3) (4,1) (5,-1) - there are two positions, the oldest position consists of orders with ids 1,2,3.

My current approach:

Schema

CREATE TABLE [dbo].[orders](
    [id] [int] NULL,
    [qty] [int] NULL
) ON [PRIMARY]

Solution

create table #or (id int, qty int, rn int)
create clustered index ix_orid on #or(rn)
insert into #or
select *, ROW_NUMBER() over(order by id) as rn from dbo.orders;

with position as (
select o1.*, o1.qty as s from #or o1 where rn = 1
union all
select o1.id, o1.qty, o1.rn, o1.qty + position.s   
from #or o1
join position
on o1.rn = position.rn + 1
where position.s > 0
)
select * from position 
option (maxrecursion 0)

drop table #or

My concern is that I create big temporary table, for all orders. And all time goes into creating this table. I have about ~3.000.000 rows in dbo.orders and ~15.000 is the result set. My current idea is to limit insert into #or using top hint with reasonable amount, and run it several times until we are done. But I wonder if it can be done easier?

3

1 Answer 1

1

As suggested in comments, I can get the latest id of the order in a single position by watching running total goes to zero. Then I can just select orders in range

declare @latestId int;
WITH T1
     AS (SELECT *,
                SUM(qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS total
         FROM   [dbo].[orders])
SELECT top (1) @latestId = id
FROM   T1
WHERE  total <= 0
order by id

select * from [dbo].[orders] where 1 <= id and id <= @latestId

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.