SQL sum of negative values until condition (positive value is seen) is met, then restart the summati-CodePudding

I would like to get the total of negative row values until there is a positive value (here column change in table below). After another negative value in the column, the sum should start accumulating the negative row values again until there is another positive value and so on. Given the following table:

 ───── ───────── ───────────────────────── 
| id  | change  | start_time              |
 ───── ───────── ───────────────────────── 
| B   | -7.4    | 2022-05-21 11:59:54.0   |
| A   | -0.8    | 2022-05-21 22:48:47.0   |
| A   | -61.5   | 2022-05-22 09:25:22.0   |
| B   | -5.3    | 2022-05-22 11:32:14.0   |
| A   | -0.3    | 2022-05-22 12:18:49.0   |
| B   | -0.1    | 2022-05-22 12:44:05.0   |
| B   | -0.5    | 2022-05-22 12:55:38.0   |
| B   | -7.2    | 2022-05-22 13:10:20.0   |
| B   | 0.0     | 2022-05-22 13:55:04.0   |
| B   | 34.8    | 2022-05-22 13:55:54.0   |
| A   | 0.0     | 2022-05-22 15:31:47.0   |
| A   | -0.1    | 2022-05-22 15:57:19.0   |
| A   | 0.0     | 2022-05-22 15:58:17.0   |
| A   | -0.1    | 2022-05-22 17:51:38.0   |
| A   | 8.1     | 2022-05-22 17:55:01.0   |
| A   | -1.9    | 2022-05-22 17:57:26.0   |
| A   | 0.0     | 2022-05-22 18:06:03.0   |
| A   | 10.1    | 2022-05-22 18:06:08.0   |
| A   | -3.0    | 2022-05-22 20:34:26.0   |
 ───── ───────── ─────────────────────────

The desired result should show the total negative of each sum sorted by id:

 ───── ───────── 
| id  | change  |
 ───── ───────── 
| B   | -20.5   |
| A   | -62.8   |
| A   | -1.9    |
| A   | -3      |
 ───── ─────────

So far, I have only managed to generate a running total of positive and negative values after the first positive value was seen. Since the last value of B is positive the sum is Null. The first sum of A shows 5.19 summing up all (positive and negative) values until the end. However the sum should only run until the next positive value is detected and should only sum up negative values.

 ───── ──────────────────── 
| id  | total_neg_change   |
 ───── ──────────────────── 
| B   | null               |
| A   | 5.199999999999999  |
| A   | -3.0               |
 ───── ────────────────────

This output was achieved by running the following code:

SELECT id, total_neg_change
FROM(
SELECT
    id,
    change,
    SUM(change) OVER(PARTITION BY id ORDER BY start_time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) AS total_neg_change
FROM test_table)
WHERE  change > 0

I assume the "unbounded following" creates the cumulative sum. How can I change this to get my desired result above?

CodePudding user response：

This is a gaps and islands problem, where each island is defined by having the same id along with streak of negative numbers. Here is one approach:

WITH cte AS (
    SELECT *, SUM(change > 0) OVER (PARTITION BY id ORDER BY start_time) grp
    FROM yourTable
)

SELECT id, SUM(change) AS total_neg_change
FROM cte
WHERE change < 0
GROUP BY id, grp
ORDER BY MIN(start_time);