sql-server - SQL中的有界累积和

Question

如何使用 SQL 计算列的累积和，以便累积和始终保持在上限/下限内。下限 -2 和上限 10 的示例，显示常规累积和和有界累积和。

id      input 
-------------
 1       5   
 2       7   
 3     -10   
 4     -10   
 5       5   
 6      10   

结果：

id    cum_sum    bounded_cum_sum  
---------------------------------
  1       5          5     
  2      12         10    
  3       2          0
  4      -8         -2
  5      -3          3     
  6       7         10

有关有界累积和的一些（非 SQL）示例，请参阅https://codegolf.stackexchange.com/questions/61684/calculate-the-bounded-cumulative-sum-of-a-vector 。

标签： sql-servercumulative-sum

Answer 1

您可以（几乎）始终使用 acursor来实现您拥有的任何累积逻辑。该技术非常常规，因此一旦掌握，就可以轻松解决各种问题。

需要注意的一件事是：这里我就地更新了表，因此[id]必须对列进行唯一索引。

（在 SQL Server 2017 最新的 linux docker 镜像上测试）

测试数据集

use [testdb];
if OBJECT_ID('testdb..test') is not null
    drop table testdb..test;

create table test (
    [id] int,
    [input] int,
);

insert into test (id, input)
values (1,5), (2,7), (3,-10), (4,-10), (5,5), (6,10);

解决方案

/* A generic row-by-row cursor solution */

-- First of all, make [id] uniquely indexed to enable "where current of" 
create unique index idx_id on test(id);

-- append answer columns
alter table test 
    add [cum_sum] int,
        [bounded_cum_sum] int;

-- storage for each row
declare @id int,
        @input int,
        @cum_sum int, 
        @bounded_cum_sum int;
-- record accumulated values
declare @prev_cum_sum int = 0,
        @prev_bounded_cum_sum int = 0;

-- open a cursor ordered by [id] and updatable for assigned columns
declare cur CURSOR local
for select [id], [input], [cum_sum], [bounded_cum_sum]
    from test
    order by id
for update of [cum_sum], [bounded_cum_sum];
open cur;

while 1=1 BEGIN

    /* fetch next row and check termination condition */
    fetch next from cur 
        into @id, @input, @cum_sum, @bounded_cum_sum;

    if @@FETCH_STATUS <> 0
        break;

    /* program body */

    -- main logic
    set @cum_sum = @prev_cum_sum + @input;
    set @bounded_cum_sum = @prev_bounded_cum_sum + @input;
    if @bounded_cum_sum > 10 set @bounded_cum_sum=10
    else if @bounded_cum_sum < -2 set @bounded_cum_sum=-2;

    -- write the result back
    update test 
        set [cum_sum] = @cum_sum,
            [bounded_cum_sum] = @bounded_cum_sum
        where current of cur;

    -- setup for next row
    set @prev_cum_sum = @cum_sum;
    set @prev_bounded_cum_sum = @bounded_cum_sum;
END

-- cleanup
close cur;
deallocate cur;

-- show
select * from test;

结果

|   | id | input | cum_sum | bounded_cum_sum |
|---|----|-------|---------|-----------------|
| 1 | 1  | 5     | 5       | 5               |
| 2 | 2  | 7     | 12      | 10              |
| 3 | 3  | -10   | 2       | 0               |
| 4 | 4  | -10   | -8      | -2              |
| 5 | 5  | 5     | -3      | 3               |
| 6 | 6  | 10    | 7       | 10              |