当前位置: 首页 > 面试题库 >

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

米嘉禧
2023-03-14
问题内容
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布尔值可以使用cast操作符将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代码。您可以,但是在您的机器上。



 类似资料:
  • 问题内容: 我有一个带有timedeltas的pandas DataFrame,作为在单独的列中以毫秒表示的这些delta的累积和。下面提供了一个示例: 我希望能够提供CumSum [ms]的最大值,之后,累积总和将再次从0开始。例如,如果在上面的示例中最大值为3000,结果将如下所示: 我已经探索过使用模运算符,但是只有当结果的总和等于所提供的限制时(即500%500的cum [ms]等于零),

  • 问题内容: 我要从值列表中创建一个新的值列表,直到它们加起来。 我是Python的新手,但我相信最好使用while循环。 问题答案: 数组使这个变得简单

  • 问题内容: 以以下聚合查询为例: 我有兴趣知道什么时候任何CustomerName的平均TransactionAmount(stats.avg)高于该客户所有购买的某个阈值,一旦我索引了将使我的平均值超过该阈值的文档,便会知道。似乎percolator设计用于或多或少地将文档与规则匹配,但是我找不到使用percolator匹配基于聚合结果的规则的任何好例子。 这可能吗?渗滤器是最好的解决方案吗?还

  • 我想在找到第一个值时终止流的执行。然而,当我运行下面的代码时,它显示,即使第一个方法中存在值,也会调用两个方法。 我登记了文件: 表示 如果存在值,则返回{@code true},否则返回{@code false}。 返回描述此流的第一个元素的{@link Optional},如果流为空,则返回空的{@code Optional}。如果流没有遭遇顺序,那么可以返回任何元素。 所以它满足了第一个条件

  • 这是打算在Java中使用的。想象以下示例输入: 我想匹配至少一个数字后面的所有字母字符。 所需输出:

  • 问题内容: 我的总体覆盖范围已达到阈值,但仍将构建标记为不稳定。有人知道原因吗? 哈科科报告 问题答案: 好的,根据我发现的链接,覆盖范围应该在最小和最大覆盖范围之内。 请参阅链接 https://issues.jenkins- ci.org/browse/JENKINS-16903- 报告相同的意外行为。 https://groups.google.com/forum/#!topic/jenki