Solved: Bigquery Column xyz contains an analytic function, which is not allowed in GROUP BY
When you are using Window functions in Bigquery, and if you try to perform a group by on the window function generated column. Bigquery will throw the below error:
Column x contains an analytic function, which is not allowed in GROUP BY
\n\nExample:
SELECT\n DISTINCT UserId,\n COUNT(date) OVER (PARTITION BY UserId) AS Days\n FROM\n `project.Dataset.Users`\n GROUP BY\n Days
Solution:
GROUP BY
cannot be applied to the variable whose value will be determined by a window function. The solution to this issue is to move the GROUP BY
to the outer query. The outer query can do the grouping required.
Like shown below:
SELECT
COUNT(UserId) AS Users,
Days
FROM (
SELECT
DISTINCT UserId,
COUNT(date) OVER (PARTITION BY UserId) AS Days
FROM
`project.Dataset.Users`
)
GROUP BY Days
victor
posted onEnjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment