google-bigquery - 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 小于“头部”行时间 1000 秒的行数。

我正在尝试使用 max(start_time) 获取窗口中的第一行。这似乎不起作用，我得到一个错误:

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

是否可以在一列中有两个分析函数(在本例中为 count 和 max)？是否有不同的解决方案来解决所提出的问题？

Answer 1

尝试使用range 函数。

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 用作计数器，也可以使用其他字段。

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