How Do Not Scan All Records To Query Top Rows By Complex Condition

I have a table and data like this: create table AmountObjects ( objectId integer, unixTimestamp integer, amount integer, PRIMARY KEY ( [objectId] ASC, [uni

Solution 1:

The only way I can think of doing this involves running the backwards running total twice. Once to get the target timestamp below which should be ignored (short circuited with the TOP 1) and then again to get the running totals for values above that (uses a seek to only get the range of rows above that).

Unless you have a very high proportion of rows to ignore this is unlikely to be an improvement over the simpler approach of just calculating the running total for everything and discarding what you don't need.

WITH DistinctObjects
     AS (SELECTDISTINCT objectId
         FROM   AmountObjects a),
     MinTimeStampsByObjectId
     AS (SELECTdo.objectId,
                ca.minUnixTimeStamp
         FROM   DistinctObjects do
                CROSS APPLY (SELECT ISNULL((SELECT TOP 1 unixTimeStamp
                                            FROM   (SELECT *,
                                                           SUM(ao.amount)
                                                             OVER (
                                                               ORDERBY ao.unixTimeStamp DESC) AS total
                                                    FROM   AmountObjects ao
                                                    WHERE  ao.objectId = do.objectId) d
                                            WHERE  total > 150ORDERBY d.unixTimeStamp DESC), -1))ca(minUnixTimeStamp))
SELECT ca2.*
FROM   MinTimeStampsByObjectId mts
       CROSS APPLY (SELECT *,
                           SUM(ao.amount)
                             OVER (
                               ORDERBY ao.unixTimeStamp DESC) AS total
                    FROM   AmountObjects ao
                    WHERE  ao.objectId = mts.objectId
                           AND ao.unixTimeStamp > IIF(mts.minUnixTimeStamp > 8,8,mts.minUnixTimeStamp)) ca2 

Solution 2:

This should implement the same logic and be more efficient:

select a.*from (select a.objectId, a.unixTimestamp, a.amount,
             sum(a.amount) over (partitionby a.objectId orderby a.unixTimeStamp desc) as total
      from AmountObjects a
     ) a
where unixTimestamp >=9or total <=150;

However, it will still scan all the rows.