如何计算Presto中每第n行的窗口函数

Question

我使用的表包含时间序列数据，每个用户每分钟都有一行。

我想在N个日历日的滚动窗口上计算一些聚合函数

这是通过实现的

SELECT
SOME_AGGREGATE_FUN(col) OVER (
PARTITION BY user_id
ORDER BY timestamp
ROWS BETWEEN (60 * 24 * N) PRECEDING AND CURRENT ROW
) as my_col
FROM my_table

然而，我只对每天的结果感兴趣。

即，我希望仅在00:00:00计算窗口，但我希望窗口本身包含要传递到我的聚合函数中的所有逐分钟数据。

现在我正在做这个：

WITH agg_results AS (
SELECT
SOME_AGGREGATE_FUN(col) OVER (
PARTITION BY user_id
ORDER BY timestamp_col
ROWS BETWEEN (60 * 24 * N) PRECEDING AND CURRENT ROW
)
FROM my_table
)
SELECT * FROM agg_results
WHERE 
timestamp_col = DATE_TRUNC('day', "timestamp_col")

这在理论上是可行的，但它需要多做60*24次计算，导致查询速度非常慢。

从本质上讲，我正试图找到一种方法，使右侧窗口绑定的跳过基于条件的行。或者，如果实现起来更简单，每第n行(因为我每天都有固定数量的行(。

Answer 1

我认为窗口函数不可能做到这一点。您可以切换到子查询，假设您的聚合函数也作为常规聚合函数工作(即，没有OVER()子句(：

select 
timestamp_col,
(
select some_aggregate_fun(t1.col)
from my_table t1
where 
t1.user_id = t.user_id
and t1.timestamp_col >= t.timestamp_col - interval '1' day 
and t1.timestamp_col <= t.timestamp_col
)
from my_table t
where timestamp_col = date_trunc('day', timestamp_col)

不过，我不确定这是否会比您最初的查询执行得更好；您可能需要根据实际数据集进行评估。

您可以将interval '1' day更改为要使用的实际间隔。