Missing analytics function using PARTITION BY in bigquery

3
votes

I'm trying to use the LAG function in combination with PARTITION BY to calculate the difference in time stamps.

SELECT
 uuid,
 event_info,
 TIMESTAMP_TO_MSEC(event_time) - LAG(TIMESTAMP_TO_MSEC(event_time),1) OVER (PARTITION BY userId ORDER BY event_time ASC)  
FROM
 [DataSet.TableName]
ORDER BY
 uuid,
 event_time

But that generates an error from bigquery saying "Error: Missing function in Analytic Expression at: 4.95 - 4.153"

However, if I split the line 4th line into two parts as

 TIMESTAMP_TO_MSEC(event_time) as time,
 LAG(TIMESTAMP_TO_MSEC(event_time),1) OVER (PARTITION BY userId ORDER BY event_time ASC) as previousTime,

Then it runs just fine. The work around would be to use the 'fix' above, and then wrap the entire query in another query so that I can perform the calculation as 

 previousTime - time as difference

But that seems a bit hacky and other SQL variants don't have a problem with the query.

Does anyone have any suggestions?

Thanks, Brad

1
google-bigquery

1 Answers

3
votes

This should be fixed in BigQuery's standard SQL dialect - https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql

Note, that instead of TIMESTAMP_TO_MSEC function in legacy SQL, you will need to use equivalent UNIX_MILLIS function.