SQL 汇总总和仅净出负行

Question

我正在尝试根据日期汇总产品值(value)。下面的示例从 20,000 开始，加 5,000，然后减去 7,000。结果应该是吃完整个 5,000，然后进入前面的正行。这将删除 5,000 行。

我认为这就像执行按日期降序排序的总和窗口函数一样简单。但是，正如您在下面看到的，我想在任何保持正数的行停止求和，然后移至下一行。

我无法理解 SQL 中的逻辑来完成这项工作。在我看来，它应该是:

SUM(Value) OVER (PARTITION BY Product, (positive valued rows) ORDER BY Date DESC)

但是可能连续有多个正值行，而负值行可能会吃掉所有这些行，或者可能连续有多个负值。

This post看起来很有希望，但我认为如果负值大于正值，逻辑将不起作用。

拥有:

+------------+----------------+-------+
|    Date    |    Product     | Value |
+------------+----------------+-------+
| 01/13/2015 | Prod1          | 20000 |
| 08/13/2015 | Prod1Addition1 |  5000 |
| 12/13/2015 | Prod1Removal   | -7000 |
| 02/13/2016 | Prod1Addition2 |  2000 |
| 03/13/2016 | Prod1Addition3 |  1000 |
| 04/13/2016 | Prod1Removal   | -1500 |
+------------+----------------+-------+

想要:

+------------+----------------+-------+
|    Date    |    Product     | Value |
+------------+----------------+-------+
| 01/13/2015 | Prod1          | 18000 |
| 02/13/2016 | Prod1Addition2 |  1500 |
+------------+----------------+-------+

Answer 1

我只能想到一个递归的cte方案

; with 
cte as
(
    select  Date, Product, Value, rn = row_number() over (order by Date)
    from    yourtable
),
rcte as
(
    select  Date, Product, Value, rn, grp = 1
    from    cte 
    where   rn  = 1
    union all
    select  Date    = case when r.Value < 0 then c.Date else r.Date end, 
        Product = case when r.Value < 0 then c.Product else r.Product end, 
        c.Value, 
        c.rn, 
        grp = case when r.Value < 0 then r.grp + 1 else r.grp end
    from    rcte r
        inner join cte c    on  r.rn    = c.rn - 1
)
select  Date, Product, Value = sum(Value)
from    rcte
group by Date, Product, grp     
order by Date