Hacker News Reader

I've generally used `PARTITION BY` to achieve the same effect in big query (similar to what was suggested in the article for postgres).

Does the `ARRAY_AGG` approach offer any advantages?

View on HN

I assume you mean `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) as row_number` with a `WHERE row_number = 1`. We ran into issues with memory usage (i.e. queries erroring because of too high memory usage).

The `ARRAY_AGG` approach uses a simple heap, so it's as efficient as MIN/MAX because it can be computed in parallel. ROW_NUMBER however needs all the rows on one machine to number them properly. ARRAY_AGG combination is associative whereas ROW_NUMBER combination isn't.

That makes sense, I might have a play with the array agg approach. I'm especially curious if it has any impact on slot time consumed

Reply on news.ycombinator.com