sql - 如何不扫描所有记录以按复杂条件查询前几行

Question

我有一个表格和这样的数据:

create table AmountObjects
(
  objectId integer,
  unixTimestamp integer,
  amount integer,

  PRIMARY KEY  
  (
      [objectId] ASC,
      [unixTimestamp] ASC  
  )
);

insert into AmountObjects values (1, 1, 33);
insert into AmountObjects values (1, 2, 33);
insert into AmountObjects values (1, 3, 33);
insert into AmountObjects values (1, 4, 33);
insert into AmountObjects values (1, 5, 33);
insert into AmountObjects values (1, 6, 33);
insert into AmountObjects values (1, 7, 33);
insert into AmountObjects values (1, 8, 33);
insert into AmountObjects values (1, 9, 33);
insert into AmountObjects values (1, 10, 33);

我想查询按日期和累计金额过滤的最后记录，但像这样的查询按对象扫描所有记录:

select 
    a.objectId,
    a.unixTimestamp,
    a.amount,
    s.total
from AmountObjects a
cross apply
(
    select sum(amount) total from AmountObjects stat 
    where a.unixTimestamp <= stat.unixTimestamp and a.objectId = stat.objectId 
) s
where 
    unixTimestamp >= 9
    or s.total <= 150

我的问题是:如何在不按对象扫描所有数据的情况下查询数据？

谢谢

Answer 1

我能想到的唯一方法是将向后运行的总数运行两次。一次获取应忽略的目标时间戳(与 TOP 1 短路)，然后再次获取高于该值的运行总计(使用查找仅获取上方行的范围那个)。

除非您有很高比例的行要忽略，否则这不太可能比仅计算所有内容的运行总计并丢弃不需要的内容的更简单方法有所改进。

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