在Postgres SQL中选择正的聚合值并忽略负 [英] Selecting positive aggregate value and ignoring negative in Postgres SQL

问题描述

我必须应用某种转换 fn（argument）。在这里，参数等于值，但当它为负数时不等于。当您获得第一个负值 时，您将等待，直到它与连续的值相加并且该和变为正数。然后执行 fn（argument）。参见我要获取的表：

I must apply a certain transformation fn(argument). Here argument is equal to value, but not when it is negative. When you get a first negative value, then you "wait" until it sums up with consecutive values and this sum becomes positive. Then you do fn(argument). See the table I want to get:

value      argument 
---------------------
  2           2      
  3           3      
 -10          0      
  4           0
  3           0
  10          7
  1           1

我本可以对所有值求和，并对总和应用 fn ，但是 fn 对于不同的行可能有所不同，因此必须知道行号以选择具体的fn。

I could have summed all values and apply fn to the sum, but fn can be different for different rows and it is essential to know the row number to choose a concrete fn.

想要一个Postgres SQL解决方案，看起来适合窗口函数，但是我还没有足够的经验来编写能做到这一点的表达式。不幸的是，实际上，我是 SQL思维的新手。我想可以通过命令式命令轻松完成，但是我还不想编写存储过程。

As want a Postgres SQL solution, looks like window functions fit, but I am not experienced enough to write expression that does that yet. In fact, I am new to "thinking in sql", unfortunately. I guess that can be easily done in an imperative way, but I do not want to write a stored procedure yet.

推荐答案

I假设我来晚了，但这可能对某人有帮助：

I suppose I'm late, but this may help someone:

select
    value,
    greatest(0, value) as argument
from your_table;

这篇关于在Postgres SQL中选择正的聚合值并忽略负的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！