CODEWITHSUNDEEP
google-bigquery
Lior
Lior

Reputation: 1467

BigQuery - counting number of events within a sliding time frame

I would like to count the number of events within a sliding time frame.

For example, say I would like to know how many bids were in the last 1000 seconds for the Google stock (GOOG).

I'm trying the following query:

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'

The logic is as follow: the partition window (by symbol) is ordered with the bid time (leaving alone the bid date for sake of simplicity). For each window (defined by the row at the "head" of the window) I would like to count the number of rows which have start_time that is less than 1000 seconds than the "head" row time.

I'm trying to use max(start_time) to get the top row in the window. This doesn't seem to work and I get an error:

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

Is it possible to have two an analytic functions in one column (both count and max in this case)? Is there a different solution to the problem presented?

Upvotes: 2

Views: 3165

Answers (1)

Ziv
Ziv

Reputation: 96

Try using the range function. 

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

I used market_center just as a counter, additional fields can be used as well.

Note: the RANGE function is not documented in BigQuery Query Reference, however it's a standard SQL function which appears to work in this case

Upvotes: 8

Related Questions