BigQuery - 计算滑动时间范围内的事件数

Question

我想计算滑动时间范围内的事件数量。

例如，假设我想知道在过去 1000 秒内对 Google 股票 （GOOG） 进行了多少出价。

我正在尝试以下查询：

SELECT 
  symbol,
  start_date,
  start_time,
  bid_price,
  count(if(max(start_time)-start_time<1000,1,null)) over (partition by symbol order by start_time asc) cnt
FROM [bigquery-samples:nasdaq_stock_quotes.quotes] 
 where symbol = 'GOOG'

逻辑如下：分区窗口（按符号）与出价时间一起排序（为简单起见，不考虑出价日期）。对于每个窗口（由窗口"头部"处的行定义），我想计算start_time比"head"行时间小于 1000 秒的行数。

我正在尝试使用 max（start_time） 来获取窗口中的第一行。这似乎不起作用，我收到一个错误：

Error: MAX is an analytic function and must be accompanied by an OVER clause.

是否可以在一列中有两个分析函数（在这种情况下包括计数和最大值）？对于所提出的问题，是否有不同的解决方案？

Answer 1

尝试使用范围函数。

SELECT  
 symbol,  
 start_date,  
 start_time,  
 bid_price,  
 count(market_center) over (partition by symbol order by start_time RANGE 1000 PRECEDING) cnt  
FROM [bigquery-samples:nasdaq_stock_quotes.quotes]   
where symbol = 'GOOG'  
order by 2, 3  

我只是将market_center用作计数器，也可以使用其他字段。

注意：RANGE 函数未记录在 BigQuery 查询参考中，但它是一个标准的 SQL 函数，在这种情况下似乎有效