Postgres 中动态基数的累积添加

我在 Postgres 中有以下场景（我正在使用9.4.1).

我有一个这种格式的表：

create table test(
    id serial,
    val numeric not null,
    created timestamp not null default(current_timestamp),
    fk integer not null
);

然后我拥有的是threshold numeric另一个表中的字段应用于标记每一行test。对于每个值>= threshold我想将该记录标记为true但如果是的话true它应该在此时将后续计数重置为 0，例如

数据集：

insert into test(val, created, fk)
  (100, now() + interval '10 minutes', 5),
  (25,  now() + interval '20 minutes', 5),
  (30,  now() + interval '30 minutes', 5),
  (45,  now() + interval '40 minutes', 5),
  (10,  now() + interval '50 minutes', 5);

当阈值为 50 时，我希望得到的输出为：

100 -> true (as 100 > 50) [reset]
25  -> false (as 25 < 50)
30  -> true (as 25 + 30 > 50) [reset]
45  -> false (as 45 < 50)
10  -> true (as 45 + 10 > 50)

是否可以在单个 SQL 查询中完成此操作？到目前为止，我已经尝试过使用窗函数 http://www.postgresql.org/docs/current/static/tutorial-window.html.

select t.*,
       sum(t.val) over (
         partition by t.fk order by t.created
       ) as threshold_met
from test t
where t.fk = 5;

正如你所看到的，我已经达到了累积频率的地步，并怀疑对rows between x preceding and current row可能就是我正在寻找的。我只是不知道如何执行重置，即设置x，将上面的值改为适当的值。

创建您自己的聚合函数 https://www.postgresql.org/docs/current/sql-createaggregate.html，可以用作窗函数。

专门的聚合函数

这比人们想象的要容易：

CREATE OR REPLACE FUNCTION f_sum_cap50 (numeric, numeric)
  RETURNS numeric LANGUAGE sql AS
'SELECT CASE WHEN $1 > 50 THEN 0 ELSE $1 END + $2';

CREATE AGGREGATE sum_cap50 (numeric) (
  sfunc    = f_sum_cap50
, stype    = numeric
, initcond = 0
);

Then:

SELECT *, sum_cap50(val) OVER (PARTITION BY fk
                               ORDER BY created) > 50 AS threshold_met 
FROM   test
WHERE  fk = 5;

结果完全符合要求。

db<>fiddle
_{Old sqlfiddle http://sqlfiddle.com/#!17/78ea6/1}

通用聚合函数

为了让它工作任何阈值 and 任何（数字）数据类型，并且allow NULL values:

CREATE OR REPLACE FUNCTION f_sum_cap (anyelement, anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql STRICT AS
$$SELECT CASE WHEN $1 > $3 THEN '0' ELSE $1 END + $2;$$;

CREATE AGGREGATE sum_cap (anyelement, anyelement) (
  sfunc    = f_sum_cap
, stype    = anyelement
, initcond = '0'
);

然后，以任何数字类型的限制（例如 110）进行调用：

SELECT *
     , sum_cap(val, '110') OVER (PARTITION BY fk
                                 ORDER BY created) AS capped_at_110
     , sum_cap(val, '110') OVER (PARTITION BY fk
                                 ORDER BY created) > 110 AS threshold_met 
FROM   test
WHERE  fk = 5;

db<>fiddle
_{Old sqlfiddle http://sqlfiddle.com/#!17/b8894/2}

解释

在你的情况下，我们不必防御NULL值自val被定义为NOT NULL. If NULL可以参与、定义f_sum_cap() as STRICT它之所以有效是因为（根据文档 https://www.postgresql.org/docs/current/sql-createaggregate.html):

如果状态转换函数被声明为“严格”，那么它不能 使用空输入调用。有了这样的转换函数，聚合 执行行为如下。具有任何空输入值的行是 被忽略（该函数未被调用并且先前的状态值是 保留）[...]

函数和聚合都多了一个参数。为了多态性 https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC变体它可以是硬编码数据类型或多态类型作为主要参数。

关于多态函数：

• 聚合多维数组的函数中的初始数组 https://stackoverflow.com/questions/9832973/initial-array-in-function-to-aggregate-multi-dimensional-array/9845460#9845460

注意使用无类型字符串文字，不是数字文字，默认为integer!