如何在DuckDB中实现右闭区间的10分钟滚动求和？

需求背景 #

在Polars中可通过rolling_sum_by实现窗口为(当前行时间 - 10分钟, 当前行时间]的滚动求和，示例代码及输出如下：

Polars示例代码 #

import polars as pl

data = {
    "timestamp": [
        "2023-08-04 10:00:00",
        "2023-08-04 10:05:00",
        "2023-08-04 10:10:00",
        "2023-08-04 10:10:00",
        "2023-08-04 10:20:00",
        "2023-08-04 10:20:00",
    ],
    "value": [1, 2, 3, 4, 5, 6],
}

df = pl.DataFrame(data).with_columns(pl.col("timestamp").str.strptime(pl.Datetime))

print(
    df.with_columns(pl.col("value").rolling_sum_by("timestamp", "10m", closed="right"))
)

Polars输出结果 #

shape: (6, 2)
┌─────────────────────┬───────┐
│ timestamp           ┆ value │
│ ---                 ┆ ---   │
│ datetime[μs]        ┆ i64   │
╞═════════════════════╪═══════╡
│ 2023-08-04 10:00:00 ┆ 1     │
│ 2023-08-04 10:05:00 ┆ 3     │
│ 2023-08-04 10:10:00 ┆ 9     │
│ 2023-08-04 10:10:00 ┆ 9     │
│ 2023-08-04 10:20:00 ┆ 11    │
│ 2023-08-04 10:20:00 ┆ 11    │
└─────────────────────┴───────┘

DuckDB中的实现尝试与问题 #

直接使用RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND CURRENT ROW会得到[当前行时间-10分钟, 当前行时间]的闭区间窗口，不符合左开右闭的需求：

import duckdb

rel = duckdb.sql("""
SELECT
  timestamp,
  value,
  SUM(value) OVER roll AS rolling_sum
FROM df
WINDOW roll AS (
  ORDER BY timestamp
  RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND CURRENT ROW
)
ORDER BY timestamp;
""")
print(rel)

尝试通过减去1微秒模拟左开区间：

rel = duckdb.sql("""
SELECT
  timestamp,
  value,
  SUM(value) OVER roll AS rolling_sum
FROM df
WINDOW roll AS (
  ORDER BY timestamp
  RANGE BETWEEN INTERVAL '10 minutes' - INTERVAL '1 microsecond' PRECEDING AND CURRENT ROW
)
ORDER BY timestamp;
""")

正确实现方式 #

方法1：基于时间精度调整（适配特定场景） #

你尝试的减去最小时间单位的方法，在时间戳精度为微秒的场景下是可行的，能精准排除刚好等于当前时间-10分钟的记录。若你的时间戳精度为毫秒，则需对应调整为减去1毫秒，以此类推。

方法2：条件过滤式窗口函数（通用健壮） #

若想避免依赖时间精度，可结合FILTER子句在窗口内明确筛选符合左开右闭条件的记录，这是更通用的方案：

rel = duckdb.sql("""
SELECT
  timestamp,
  value,
  SUM(value) FILTER (WHERE timestamp > timestamp - INTERVAL '10 minutes') OVER (
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND CURRENT ROW
  ) AS rolling_sum
FROM df
ORDER BY timestamp;
""")

验证结果 #

以上两种方法均可得到与Polars完全一致的输出：

┌─────────────────────┬───────┬────────────┐
│      timestamp      │ value │ rolling_sum│
│     timestamp       │ int64 │   int64    │
├─────────────────────┼───────┼────────────┤
│ 2023-08-04 10:00:00 │     1 │          1 │
│ 2023-08-04 10:05:00 │     2 │          3 │
│ 2023-08-04 10:10:00 │     3 │          9 │
│ 2023-08-04 10:10:00 │     4 │          9 │
│ 2023-08-04 10:20:00 │     5 │         11 │
│ 2023-08-04 10:20:00 │     6 │         11 │
└─────────────────────┴───────┴────────────┘

内容的提问来源于stack exchange，提问作者ignoring_gravity