Cannot pass LAG or LEAD result to integer
I am trying to perform a calculation based on the results of a LAG or LEAD function.
The encapsulation of the function in the casting INTEGER () function seems to be causing the problem with the OVER function internally and throws the following error:
Unrecognized analytic function: INT64 cannot be used with OVER () clause
Below is some basic code that works fine, but when I add a function, it throws an error:
LEAD (hits.hitNumber, 1) OVER (PARTITION by fullvisitorID, visitid, visitnumber ORDER BY hits.hitNumber DESC) as nextHit
The code I used to generate this error looks like this:
INTEGER (LEAD (hits.hitNumber, 1)) OVER (PARTITION by fullvisitorID, visitid ORDER BY hits.hitNumber DESC) as nextHit
Also the following does not work:
INTEGER (LEAD (hits.hitNumber, 1) OVER (PARTITION by fullvisitorID, visitid ORDER BY hits.hitNumber DESC)) as nextHit
Found "OVER" "OVER" "at line 8, column 36. Expected:") "
Do I really need to make this subquery to make this work, or is there another solution?
source to share
2 possible solutions:
As Jordan says, bring the INTERGER () scroll inside LEAD ():
SELECT LEAD(INTEGER(hits.hitNumber), 1) OVER (PARTITION BY fullvisitorID, visitid, visitnumber ORDER BY hits.hitNumber DESC) as nextHit
FROM [dataset.ga_sessions_20140107]
Or, as in your proposal, with a subquery:
SELECT INTEGER(nextHit) FROM (
SELECT LEAD(hits.hitNumber, 1) OVER (PARTITION BY fullvisitorID, visitid, visitnumber ORDER BY hits.hitNumber DESC) as nextHit
FROM [dataset.ga_sessions_20140107]
)
source to share