Sort operator used for PARTITION BY used too much memory BigQuery Error

I am trying to average score grouping by a week number, so that i can display the average, along with a filtered average in the same graph, however running the below query is returning an error.

Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. Sort operator used for PARTITION BY used too much memory..

Select
AVG(table.score) OVER (PARTITION BY cal.weeknum) as AVGScore
FROM 
    (Select CONCAT ((CAST(EXTRACT(YEAR FROM exam.start)AS 
    STRING))," ",(CAST(EXTRACT(WEEK(Monday) FROM meeting.start)AS 
    STRING))) AS WeekNum 
    FROM `dbo.data_source.exam` as 
    exam) as cal,
`dbo.data_source.table` as table

Solution:

Your problem is not the partition by. Your problem is the comma in the FROM clause. You are using standard SQL, so this is a cross join.

It is unclear what the right join conditions are. But you can start by removing the comma and using proper, explicit JOIN syntax with the join conditions in the ON clause.

dan-irving

posted on 08 Nov 18