SQL中的有界累积总和 [英] Bounded cumulative sum in SQL

问题描述

如何使用SQL计算列上的累加总和，以使累加总和始终保持在上限/下限之内.下限为-2，上限为10的示例，显示了规则的累加总和和有界的累加总和.

How can I use SQL to compute a cumulative sum over a column, so that the cumulative sum always stays within upper/lower bounds. Example with lower bound -2 and upper bound 10, showing the regular cumulative sum and the bounded cumulative sum.

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

请参见 https://codegolf.stackexchange.com/Questions/61684/计算向量的有界累计和，用于某些(非SQL)有界累积和的示例.

See https://codegolf.stackexchange.com/questions/61684/calculate-the-bounded-cumulative-sum-of-a-vector for some (non SQL) examples of a bounded cumulative sum.

推荐答案

您可以(几乎)始终使用 cursor 来实现所具有的任何累积逻辑.该技术非常常规，因此一旦掌握即可轻松解决各种问题.

You can (almost) always use a cursor to implement whatever cumulative logic you have. The technique is quite routine so can be used to tackle a variety of problems easily once you get it.

要注意的一件事:在这里我就地更新表，因此 [id] 列必须是唯一索引的.

One specific thing to note: Here I update the table in-place, so the [id] column must be uniquely indexed.

(在SQL Server 2017最新的Linux docker映像上测试)

(Tested on SQL Server 2017 latest linux docker image)

测试数据集

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              |

这篇关于SQL中的有界累积总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！