sql - 求和直到达到阈值然后重置计数器

Question

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

如何在达到阈值点之前对下一行的距离求和并再次重置计数器。

例如，如果阈值为 10，我将尝试获得以下输出:

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

但我所能实现的就是通过以下查询获得累积距离:

SELECT *, sum(distance) over (order by date asc) as running_distance FROM table;

我正在使用 PostgreSQL。

Answer 1

使用user-defined aggregate

现场测试: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 bool 值可以通过使用转换运算符 ::int 将 true 转换为 1，将 false 转换为 0。

你也可以使用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.com 上测试该 plpgsql 代码。你可以，不过在你的机器上。