BigQuery rank() function - Resources exceeded

Question

I have a table with 7 columns and ~8.5 mil rows. I'm attempting to select into a destination table with "Allow large results" checked. Note I've already had to decompose a larger query into multiple steps because of resource exceeded errors.

SELECT 
    col1,
    col2,
    col3,
    col4,
    RANK() OVER (PARTITION BY col1 ORDER BY col4 DESC) rank
FROM
[dataset.table]

This returns a "resources exceeded" error.

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2013-08-01T20:41:52

Due to the current implementation of the window functions, these errors are kind of expected when trying to run window functions over big datasets (as in this case, that requires the large results flag).

While these limitations are in place, I would suggest running the query in multiple steps, as in:

SELECT col1, col2, col3, col4, RANK() OVER (PARTITION BY col1 ORDER BY col4 DESC) rank FROM [dataset.table]
WHERE ABS(HASH(col1)) % 4 = 0

(replace 0 with 1, 2, and 3 to complete the whole process - or 4 with a bigger number if resources still exceeded)

BigQuery rank() function - Resources exceeded

1 Answers