sql - 求和直到达到阈值,然后重置计数器
问题描述
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。
解决方案
使用用户定义的聚合
现场测试: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 boolean 可以使用 cast operator 将 true 转换为 1,将 false 转换为 0 ::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.com 上测试该 plpgsql 代码。你可以,在你的机器上。
推荐阅读
- php - PHP:如何仅检索特定客户的 Stripe 支付意图
- fonts - 如何使用 nativescript 检查字体大小移动设备
- c++ - 随机数生成器的性能因平台而异
- php - 如何正确修复.htaccess laravel 5.5?
- c++ - 积分转换的运行时检查
- python - 这个python代码中这个POST方法的json请求是否正确?
- python - 为什么我的用于检测图像旋转的卷积模型会为每张图片预测相同的类别?
- swift - 在测试中使用 UserDefaults.standard 的后果是什么?
- html - html中的链接问题
- r - 使用 dplyr 创建一个字符变量,其值取决于先前的变量(它们的名称和值)