问题描述
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.
推荐答案
使用
实时测试:
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.
这篇关于求和,直到达到阈值,然后重置计数器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!