Ограниченная текущая сумма в Bigquery

Использование javascript UDF может помочь. Поскольку то, что вы на самом деле хотите, - это способ рассчитать текущую сумму, следуя нескольким правилам (т.е. добавить каждое положительное число и сохранить максимум суммы или 10, однако, если число отрицательное, сбросьте и вычтите из суммы). Эта логика может быть инкапсулирована в UDF и применена к массиву, который объединяет все суммы до заданного дня.

Я не совсем уверен, как это будет масштабироваться для вашего набора данных из 5,6 млн строк, но для приведенного выше примера он вычисляет желаемую ограниченную текущую сумму.

#standardSQL
CREATE TEMP FUNCTION calculate_sum(arr ARRAY<INT64>)
RETURNS INT64
LANGUAGE js AS """
  var sum = 0;
  for (var element in arr) {
    if (+arr[element] >= 0) {
     sum += +arr[element];
     sum = Math.min(sum, 10);
    } else if (+arr[element] < 0) {
     sum += +arr[element]
    }
  }
  return sum;
""";

WITH temp AS (
  SELECT 1 id, DATE("2020-01-01") date ,  2 AS amount UNION ALL
  SELECT 2, DATE("2020-01-03"),  5 UNION ALL
  SELECT 3, DATE("2020-01-08"),  10 UNION ALL
  SELECT 4, DATE("2020-01-12"),  -9 UNION ALL
  SELECT 5, DATE("2020-01-12"),  3
),
base AS (
SELECT date, amount, 
ARRAY_AGG(amount) OVER(ORDER BY id, date) AS t
FROM temp)
SELECT date, amount, calculate_sum(t) capped_sum FROM base

Что даст