求和，直到达到阈值，然后重置计数器 [英] Sum until threshold value reached and then reset the counter

问题描述

user_id | date                 | distance
1       | 2019-04-09 00:00:00  | 2
1       | 2019-04-09 00:00:30  | 5
1       | 2019-04-09 00:01:00  | 3
1       | 2019-04-09 00:01:45  | 7
1       | 2019-04-09 00:02:30  | 6
1       | 2019-04-09 00:03:00  | 1

如何求和下一行的总和，直到达到阈值并再次重置计数器。

How do I sum distance over next row until threshold point is reached and reset the counter again.

例如，如果阈值为10，我试图获得以下输出：

For instance if the threshold value is 10 I am trying to get the following output:

1       | 2019-04-09 00:00:00  | 2
1       | 2019-04-09 00:00:30  | 7            (2 + 5)
1       | 2019-04-09 00:01:00  | 10           ( 7 + 3 )
1       | 2019-04-09 00:01:45  | 7            RESET
1       | 2019-04-09 00:02:30  | 13           (7 + 6 )
1       | 2019-04-09 00:03:00  | 1            RESET

但是我所能实现的是通过以下查询获取累积距离：

But all I could achieve is get cumulative distance with following query:

SELECT *，总和（距离）超过（按日期升序排列）为running_distance FROM表；

我正在使用PostgreSQL。

I am using PostgreSQL.

推荐答案

使用用户定义的聚合

实时测试： http://sqlfiddle.com/#!17/16716/2

SELECT *, sum_with_reset(distance, 10) over (order by date asc) as running_distance 
FROM tbl;

用户定义的汇总sum_with_reset定义：

create or replace function sum_reset_accum(
    _accumulated numeric, _current numeric, _threshold numeric
)
returns numeric as
$$
    select case when _accumulated >= _threshold then
        _current
    else
        _current + _accumulated
    end    
$$ language sql;


create aggregate sum_with_reset(numeric, numeric)
(
    sfunc = sum_reset_accum,
    stype = numeric,
    initcond = 0
);

数据

CREATE TABLE tbl
    ("user_id" int, "date" timestamp, "distance" int)
;

INSERT INTO tbl
    ("user_id", "date", "distance")
VALUES
    (1, '2019-04-09 00:00:00', 2),
    (1, '2019-04-09 00:00:30', 5),
    (1, '2019-04-09 00:01:00', 3),
    (1, '2019-04-09 00:01:45', 7),
    (1, '2019-04-09 00:02:30', 6),
    (1, '2019-04-09 00:03:00', 1)
;

输出：

| user_id |                 date | distance | running_distance |
|---------|----------------------|----------|------------------|
|       1 | 2019-04-09T00:00:00Z |        2 |                2 |
|       1 | 2019-04-09T00:00:30Z |        5 |                7 |
|       1 | 2019-04-09T00:01:00Z |        3 |               10 |
|       1 | 2019-04-09T00:01:45Z |        7 |                7 |
|       1 | 2019-04-09T00:02:30Z |        6 |               13 |
|       1 | 2019-04-09T00:03:00Z |        1 |                1 |

单线：

create or replace function sum_reset_accum(
    _accumulated numeric, _current numeric, _threshold numeric
)
returns numeric as
$$
    select _current + _accumulated * (_accumulated < _threshold)::int
$$ language 'sql';

Postgres布尔值可以使用强制转换运算符将true转换为1，将false转换为0 :: int 。

Postgres boolean can cast true to 1, false to 0 by using cast operator ::int.

您也可以使用 plpgsql 语言：

create or replace function sum_reset_accum(
    _accumulated numeric, _current numeric, _threshold numeric
)
returns numeric as
$$begin
    return _current + _accumulated * (_accumulated < _threshold)::int;
end$$ language 'plpgsql';

请注意，您无法在sqlfiddle.com上创建plpgsql函数，因此无法在sqlfiddle上测试该plpgsql代码.com。您可以在自己的机器上。

Note that you cannot create plpgsql function on sqlfiddle.com, so you cannot test that plpgsql code on sqlfiddle.com. You can, on your machine though.

这篇关于求和，直到达到阈值，然后重置计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！