如何在BigQuery中计算特定范围内的滚动平均值

Question

我有一个类似下面的BigQuery表，其中的数据不一定以一致的速率记录：

| timestamp               | value |
|-------------------------|-------|
| 2022-10-01 00:03:00 UTC | 2.43  |
| 2022-10-01 00:17:00 UTC | 4.56  |
| 2022-10-01 00:36:00 UTC | 3.64  |
| 2022-10-01 00:58:00 UTC | 2.15  |
| 2022-10-01 01:04:00 UTC | 2.90  |
| 2022-10-01 01:13:00 UTC | 5.88  |
...                       ...

我想计算某个时间段内value的滚动平均值(作为一个新列(，例如前12小时。我知道在固定数量的行上进行比较简单，我也尝试过使用LAG和TIMESTAMP_SUB函数来选择正确的值进行平均，但我对SQL还很陌生，所以我甚至不确定这是否是正确的方法。

有人知道怎么做吗？谢谢

Answer 1

请使用窗口函数。

您需要将日期和小时列计算为整数。为此，我们取unix日期并将其乘以24小时。然后我们加上一天中的小时数。我们忽略夏令时。

WITH
tbl AS (SELECT 10* rand() as val, timestamp_add(snapshot_date,interval cast(rand()*5000 as int64) minute) as timestamps FROM UNNEST(GENERATE_Timestamp_ARRAY("2021-01-01 00:00:00","2023-01-01 0:00:00",INTERVAL 1 hour)) AS snapshot_date)
SELECT
*,
unix_date(date(timestamps))*24+extract(hour from timestamps) as dummy_time,
avg(val) over WIN1_range as rolling_avg,
sum(1) over WIN1_range as values_in_avg
FROM
tbl
window WIN1_range as (order by unix_date(date(timestamps))*24+extract(hour from timestamps) range between 12 PRECEDING and current row)

Answer 2

BigQuery简化了窗口函数范围框架的规范：

提示：如果要使用带日期的范围，请将ORDER BY与UNIX_DATE()函数一起使用。如果要使用带时间戳的范围，请使用UNIX_SECONDS()、UNIX_MILLIS()或UNIX_MICROS()函数。

这里，我们可以在对分区中的记录进行排序时简单地使用unix_seconds()，并相应地将12小时的间隔指定为秒：

select ts, val,
avg(value) over(
order by unix_seconds(ts)
range between 12 * 60 * 60 preceding and current row
) as avg_last_12_hours
from mytable

现在假设我们想要过去两天的平均值，我们将使用unix_date()：

select ts, val,
avg(value) over(
order by unix_date(ts)
range between 2 preceding and current row
) as avg_last_12_hours
from mytable