代码之家 › 专栏 › 技术社区 › gregmac

计算连续行之间的增量和的有效方法?

tsql sql

gregmac · 技术社区 · 15 年前

我有一个由时间戳值和绝对(米)值组成的数据集。有时,仪表值重置为零,这意味着我必须迭代并逐个计算一个增量,然后将其相加,得到给定周期的总计。

例如:

Timestamp      Value
2009-01-01     100
2009-01-02     105
2009-01-03     120
2009-01-04     0 
2009-01-05     9

这里的总数是29,计算如下:

(105 - 100) + (120 - 105) + (0) + (9 - 0) = 29

我正在为此使用MS-SQL Server,并对任何建议开放。

现在,我用一个光标来完成这项工作,它检查delta是否为负,然后汇总:

DECLARE CURSOR curTest CURSOR FAST_FORWARD FOR
    SELECT value FROM table ORDER BY timestamp
OPEN curTest
DECLARE @delta bigint, @current bigint, @last bigint
SET @delta = 0

FETCH curTest INTO @current
WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@current IS NOT NULL) AND (@current > 0) 
    BEGIN
        IF (@last IS NOT NULL) AND (@current > @last)
            SET @delta = @delta + (@current - @last)
        SET @last = @current

        FETCH curTest INTO @current
    END
END

CLOSE curTest
DEALLOCATE curTest

最好能得到如下数据集:

Timestamp      Value    LastValue
2009-01-01     100      NULL
2009-01-02     105      100
2009-01-03     120      105
2009-01-04     0        120
2009-01-05     9        0

因此,很容易获取delta、筛选(value>lastvalue)和执行sum()。

我试过:

SELECT m1.timestamp, m1.value, 
  ( SELECT TOP 1 m2.value FROM table WHERE m2.timestamp < m1.timestamp ORDER BY m2.timestamp DESC ) as LastValue
FROM table

但事实证明,这比光标要慢:当我在SQL Studio中一起运行这些操作时,如果启用了“显示执行计划”,则这项操作的相对成本是100%(使用7或8个操作——在时间戳的聚集索引扫描中占大多数),而光标是0%(使用3个操作)。

(为了简单起见,我在这里不展示的是,我有几个不同的数字集,在这个表中也有一个外键-所以总是有一个WHERE子句限制特定的集合。我有几个地方可以同时计算给定时间段内多个集合的这些总数,因此它成为性能瓶颈。非光标方法也可以很容易地修改为按键分组并一次返回所有集-但在我的测试中,这实际上比多次运行光标还要慢,因为Group By和Sum()操作有额外的开销,但总的来说,总体速度要慢一些。)

3 回复 | 直到 15 年前

spender 15 年前

完全一样…

create table #temp ([timestamp] date,value int);
insert into #temp (timestamp,value) values ('2009-01-01',100)
insert into #temp (timestamp,value) values ('2009-01-02',105)
insert into #temp (timestamp,value) values ('2009-01-03',120)
insert into #temp (timestamp,value) values ('2009-01-04',0)
insert into #temp (timestamp,value) values ('2009-01-05',9);

with numbered as
(
    select ROW_NUMBER() over (order by timestamp) id,value from #temp
)
select sum(n1.value-n2.value) from numbered n1 join numbered n2 on n1.id=n2.id+1 where n1.value!=0

drop table #temp;

根据规定,结果为29。

Rob Farley 15 年前

从第\行开始,然后重新加入您自己。

with numbered as
(
SELECT value, row_number() over (order by timestamp) as Rownum 
FROM table
)
select sum(n1.value - n2.value)
from numbered n1
  join
  numbered n2  on n1.Rownum = n2.Rownum +1

事实上。。。你只想增加…因此,在其中放入一个WHERE子句,并说“where n1.value>n2.value”。

还有…确保我把它们放在正确的地方…我刚把它从-1改为+1,因为我想我把它翻了。

容易的!

抢劫

Kirk Broadhurst 15 年前

您的算法中有太多不必要的连接。

计算每个仪表读数与其后续仪表读数之间的差异是浪费资源。作为一个现实世界的例子,想象一下,如果我的电力公司每天都把我的电表读数为我用了多少电,然后把每天的值加起来决定我每月的总用电量,这是没有意义的。他们只需根据起始值和结束值确定总数!

只需计算第一次和最后一次读数之间的差异,并调整以说明“重置”。你的公式简单地变成:

total value = (final value) - (initial value) 
                 + (miscellaneous reductions in value, i.e. resets)
total value = (9) - (100) + (120)
            = 29

找到最终值和初始值是很简单的。只需找到“重置”期间“米”减少的总量,并将其添加到总量中。除非有比测量记录更多的重置记录,否则这将总是更有效的。

借用斯宾德的解决方案,可以通过以下公式计算“重置”值:

create table...

select sum(n1.value-n2.value) from numbered n1 join numbered n2 
     on n1.id=n2.id+1 where n1.value=0  //note value=0 rather than value!=0