CODEWITHSUNDEEP
sqlgoogle-bigquery
cky_stew
cky_stew

Reputation: 51

Standard SQL: Resetting sum over dependent on result

Using Standard SQL in Google Big Query. I'm trying to calculate a running total of a balance that can't ever be in a negative.

In a picture here is my problem. I have row and input - I'm trying to get Desired output for each row.

enter image description here

The rule is that I need a running total of Input - but if input goes into negative, then it should effectively reset the input from that point onwards.

I tried putting in some reset markers to partition by these negatives, but then I'd have an issue where I couldn't get passed recurring negatives if they didn't put the overall count into the negative. (It would reset again on the -2 in the picture).

I feel like I'm being really stupid here. Any help would be appreciated.

Thanks

Upvotes: 2

Views: 173

Answers (1)

Sabri Karagönen
Sabri Karagönen

Reputation: 2365

As Gordon says in the comments, it's not possible without a script.
So you can find the script here:

DECLARE update_index INT64 DEFAULT 0;

CREATE TEMP TABLE t1 AS
SELECT index, input, 0 as reset, 0 as reset_group, SUM(input) OVER (ORDER BY index) AS cumsum
FROM UNNEST([4,1,1,-12,1,3,-2,1 ]) AS input WITH OFFSET index;

LOOP
SET update_index = (SELECT MIN(index) FROM t1 WHERE cumsum < 0);
IF update_index IS NULL 
THEN 
  LEAVE; 
END IF;

UPDATE t1
SET reset = 1
WHERE index = update_index;

CREATE OR REPLACE TEMP TABLE t1 AS
SELECT *, SUM(IF(reset = 1, 0, input)) OVER (PARTITION BY reset_group ORDER BY index) AS cumsum
FROM (
  SELECT index, input, reset, SUM(reset) OVER (ORDER BY index) as reset_group
  FROM t1
);

END LOOP;

SELECT *
FROM t1
ORDER BY index;

Upvotes: 1

Related Questions