在 Postgres SQL 中选择正聚合值并忽略负聚合值

Question

我必须应用某种转换fn(argument)。这里的argument等于value，但当它为负数时则不然。当您获得第一个负值时，您将“等待”，直到它与连续值相加并且该总和变为正数。然后你执行fn(argument)。请参阅我想要获得的表格：

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

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

由于想要一个 Postgres SQL 解决方案，看起来窗口函数适合，但我还没有足够的经验来编写执行此操作的表达式。事实上，不幸的是，我对“用sql思考”很陌生。我想这可以通过命令式方式轻松完成，但我还不想编写存储过程。

Answer 1

我想我迟到了，但这可能对某人有帮助：

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

Answer 2

这并不真正适合任何预定义的聚合函数。您可能需要自己编写。请注意，在 postgresql 中，聚合函数可以用作窗口函数，事实上，从 9.0 开始，这是用 C 以外的任何语言编写窗口函数的唯一方法。

您可以编写一个函数来跟踪“求和”值的状态，但如果当前“和”为正，它总是返回输入值，而当“和”为负时，它只继续添加。然后您只需取该总和或零中的较大者即可。简而言之：

-- accumulator function: first arg is state, second arg is input
create or replace function ouraggfunc(int, int)
 returns int immutable language plpgsql as $
begin
  raise info 'ouraggfunc: %, %', $1, $2; -- to help you see what's going on
  -- get started by returning the first value ($1 is null - no state - first row)
  if $1 is null then
    return $2;
  end if;
  -- if our state is negative, we're summing until it becomes positive
  -- otherwise, we're just returning the input
  if $1 < 0 then
    return $1 + $2;
  else
    return $2;
  end if;
end;
$;

您需要创建一个聚合函数来调用此累加器：

create aggregate ouragg(basetype = int, sfunc = ouraggfunc, stype = int);

这定义了聚合将整数作为输入并将其状态存储为整数。

我将您的示例复制到表中：

steve@steve@[local] =# create table t(id serial primary key, value int not null, argument int not null);
NOTICE:  CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE
steve@steve@[local] =# copy t(value, argument) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2    2
>> 3    3
>> -10  0
>> 4    0
>> 3    0
>> 10   7
>> 1    1
>> \.

现在您可以通过使用带有窗口子句的聚合函数来生成这些值：

steve@steve@[local] =# select value, argument, ouragg(value) over(order by id) from t;
INFO:  ouraggfunc: <NULL>, 2
INFO:  ouraggfunc: 2, 3
INFO:  ouraggfunc: 3, -10
INFO:  ouraggfunc: -10, 4
INFO:  ouraggfunc: -6, 3
INFO:  ouraggfunc: -3, 10
INFO:  ouraggfunc: 7, 1
 value | argument | ouragg
-------+----------+--------
     2 |        2 |      2
     3 |        3 |      3
   -10 |        0 |    -10
     4 |        0 |     -6
     3 |        0 |     -3
    10 |        7 |      7
     1 |        1 |      1
(7 rows)

因此，如您所见，最后一步是您需要获取函数的输出（如果它是正数） ，或零。这可以通过包装查询或编写一个函数来完成：

create function positive(int) returns int immutable strict language sql as
$ select case when $1 > 0 then $1 else 0 end $;

现在：

select value, argument, positive(ouragg(value) over(order by id)) as raw_agg from t

这会生成您在问题中指定的函数的参数。