在BigQuery中的滚动窗口上使用NTILE函数(滚动入库/滚动分位数)
我有一个简单的表，包含时间列(1分钟频率)和另一个浮点值列，让我们称它为A列。我的目标是在BigQuery中创建一个执行以下操作的查询：
对于每一行，查看前24小时和之后24小时的窗口，并获得作为窗口中心的特定行的相关“存储桶号”(如在NTILE(5)函数中)。
我正在尝试实现一个“滚动分位数”--有点类似于“滚动分位数”(类似于熊猫Rolling Quantile)，但使用的是分位数。我可以在Python语言中使用pd.DataFrame.rolling.apply和一个定制函数来打包并返回值，但我想使用BigQuery来完成。
我在组合NTILE函数和滚动窗口时遇到了问题。
我能够创建滚动窗口(在文档中称为Analytic function )，并使用以下命令执行简单的聚合，如average
SELECT Time, AVG(A)
OVER (ORDER BY UNIX_SECONDS(Time) ASC RANGE BETWEEN 86400 PRECEDING AND 86400 FOLLOWING) as rolling_avg_24_hours_each_side
FROM `my_project.my_table`(86400来自一小时内的3600秒* 24小时。有没有使用魔术数字的替代方法？)
在我的整个表中使用NTILE(5)函数非常简单，但是我不能在滚动窗口中使用它。
正如Numbering functions的文档所述，不允许使用window_frame_clause，因此我不能将RANGE...语句与NTILE结合使用。
因此，我的主要问题是如何将NTILE函数与滚动窗口相结合，或者是否有其他解决方案来实现滚动窗口的“滚动入库”？
回答 2
Stack Overflow用户
发布于 2021-08-26 10:50:02
据我所知，在BigQuery中没有简单的方法可以做到这一点。下面的方法并不简单，但它可能适用于您的情况。首先，将每个窗口所需的行集合在一起：
select t.time, t.a, array_agg(t2.a) as window_a
from `my_project.my_table` t join
`my_project.my_table` t2
on t2.time >= date_add(t.time, interval -24 hour) and
t2.time < date_add(t.time, interval 24 hour)
group by t.time, t.a;然后手动计算平铺：
with t as (
select t.time, t.a, array_agg(t2.a) as window_a
from `my_project.my_table` t join
`my_project.my_table` t2
on t2.time >= datetime_add(t.time, interval -24 hour) and
t2.time < datetime_add(t.time, interval 24 hour)
group by t.time, t.a
)
select t.*,
(select ceiling(min(case when t.a = a2 then seqnum end) * 5.0 / max(cnt))
from (select a2,
row_number() over (order by a2) as seqnum,
count(*) over () as cnt
from unnest(t.window_a) a2
) w
) as rolling_tile
from t;Stack Overflow用户
发布于 2021-09-05 08:30:41
Gordon Linoff的答案有效，通过使用他的滚动窗口技巧，我成功地直接应用了NTILE函数，而不是像建议的那样“手动”计算它。
第一部分保持不变，只是创建窗口。接下来，我们可以在表之间的CROSS JOIN上应用NTILE函数：
select t.time, t.a, a2 as value, NTILE(5) OVER (PARTITION BY t.time ORDER BY a2) as bin, from t CROSS JOIN unnest(t.window_a) a2结果是一个包含一些重复行的表。根据我的需要，我可以只使用Min(bin)和DISTINCT消除重复项。
我的完整问题是：
with t as (
select t.time, t.a, array_agg(t2.a) as window_a
from `my_project.my_table` t join
`my_project.my_table` t2
on t2.time >= datetime_add(t.time, interval -24 hour) and
t2.time < datetime_add(t.time, interval 24 hour)
group by t.time, t.a
)
select distinct time, Min(bin) from (select t.time, t.a, a2 as value, NTILE(5) OVER (PARTITION BY t.time ORDER BY a2) as bin, from t CROSS JOIN unnest(t.window_a) a2)https://stackoverflow.com/questions/68934877
复制相似问题
腾讯云开发者
