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

本文介绍了求和，直到达到阈值，然后重置计数器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 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.

推荐答案

使用用户定义的聚合

实时测试： sqlfiddle/#!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上创建plpgsql函数，因此无法在sqlfiddle上测试该plpgsql代码。您可以在自己的机器上。

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