在Postgres中使用动态基础进行累积添加 [英] Cumulative adding with dynamic base in Postgres

问题描述

我在Postgres中有以下情况(我正在使用 9.4.1 ).

I have the following scenario in Postgres (I'm using 9.4.1).

我有一个这种格式的表:

I have a table of this format:

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，例如

What I then have is a threshold numeric field in another table which should be used to label each row of test. For every value which is >= threshold I want to have that record marked as true but if it is true it should reset subsequent counts to 0 at that point, e.g.

数据集:

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的阈值，我希望得到的输出为:

With a threshold of 50 I would like to get the output as:

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查询中执行此操作?到目前为止，我已经尝试过使用窗口功能.

Is it possible to do this in a single SQL query? So far I have experimented with using a window function.

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设置为适当的值.

As you can see I have got it to the point where I have a cumulative frequency and suspect that the tweaking of rows between x preceding and current row may be what I'm looking for. I just can't work out how to perform the reset, i.e. set x, in the above to the appropriate value.

推荐答案

创建您自己的聚合函数，可以用作窗口函数.

Create your own aggregate function, which can be used as window function.

这比人们想象的要容易:

It's easier than one might think:

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
);

然后:

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

结果完全符合要求.

db<>小提琴此处
_{Old sqlfiddle}

db<>fiddle here
_{Old sqlfiddle}

要使其适用于任何阈值和任何(数字)数据类型，以及允许NULL值:

To make it work for any thresholds and any (numeric) data type, and also 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，例如:

Then, to call with a limit of, say, 110 with any numeric type:

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<>小提琴此处
_{Old sqlfiddle}

db<>fiddle here
_{Old sqlfiddle}

在您的情况下，由于val是在NOT NULL中定义的，因此我们不必为 NULL 值辩护.如果可以使用NULL，则将f_sum_cap()定义为 STRICT ，它会起作用，因为(

In your case we don't have to defend against NULL values since val is defined NOT NULL. If NULL can be involved, define f_sum_cap() as STRICT and it works because (per documentation):

如果状态转换函数被声明为严格"，则它不能 用空输入调用.有了这样的转换功能，聚合 执行的行为如下.输入值为空的行是 忽略(不调用该函数，并且先前的状态值为 保留)[...]

If the state transition function is declared "strict", then it cannot be called with null inputs. With such a transition function, aggregate execution behaves as follows. Rows with any null input values are ignored (the function is not called and the previous state value is retained) [...]

函数和聚合都采用另外一个参数.对于多态变体，它可以可以是硬编码的数据类型，也可以是与前导参数相同的多态类型.

Both function and aggregate take one more argument. For the polymorphic variant it can be a hard coded data type or the same polymorphic type as the leading arguments.

关于多态函数:

• 函数中的初始数组用于聚合多个维数组

请注意使用无类型的字符串文字，而不是数字文字，默认使用integer！

Note the use of untyped string literals, not numeric literals, which would default to integer!

这篇关于在Postgres中使用动态基础进行累积添加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！