我在SQL中有一个包含日期和收入列的收入表.我想创建一个防止负收入值的逻辑,以便将任何负收入值设置为0,并从随后几个月的收入中减go 负金额,直到完全抵消为止.
例如,如果我有以下收入数据:
Date | Revenue |
---|---|
2018-09-01 | 1200 |
2018-08-01 | 400 |
2018-07-01 | -1000 |
2018-06-01 | 800 |
2018-05-01 | 600 |
2018-04-01 | 200 |
2018-03-01 | -200 |
2018-02-01 | 400 |
2018-01-01 | 200 |
预期输出应为:
Date | Revenue |
---|---|
2018-09-01 | 600 |
2018-08-01 | 0 |
2018-07-01 | 0 |
2018-06-01 | 800 |
2018-05-01 | 600 |
2018-04-01 | 0 |
2018-03-01 | 0 |
2018-02-01 | 400 |
2018-01-01 | 200 |
下面是一些入门代码
WITH revenue_table AS (
SELECT '2018-09-01' AS date, 1200 AS revenue UNION ALL
SELECT '2018-08-01' AS date, 400 AS revenue UNION ALL
SELECT '2018-07-01' AS date, -1000 AS revenue UNION ALL
SELECT '2018-06-01' AS date, 800 AS revenue UNION ALL
SELECT '2018-05-01' AS date, 600 AS revenue UNION ALL
SELECT '2018-04-01' AS date, 200 AS revenue UNION ALL
SELECT '2018-03-01' AS date, -200 AS revenue UNION ALL
SELECT '2018-02-01' AS date, 400 AS revenue UNION ALL
SELECT '2018-01-01' AS date, 200 AS revenue
)
select * from revenue_table
我不知道该如何处理这个问题.有没有人能建议一个解决方案,或者提供一些实现这一逻辑的示例代码?
- try 计算负值和减go 正值的滚动和
- 递归,但看起来很慢,而且没有真正起作用