如何不扫描所有记录以按复杂条件查询顶部行 [英] 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,
      [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);

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

I want to query last records filtered by date and cumulative amount, but queries like this scan all records by object:

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

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

谢谢

推荐答案

我能想到的唯一方法是向后运行总计两次.一次获得要忽略的目标时间戳(与TOP 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 (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 

这篇关于如何不扫描所有记录以按复杂条件查询顶部行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！