Taking a Random Sample From Each Group in Big Query

user_5709764 0
Viewed 5925

I'm trying to figure out what is the best way to take a random sample of 100 records for each group in a table in Big Query.

For example, I have a table where column A is a unique recordID, and column B is the groupID to which the record belongs. For every distinct groupID, I would like to take a random sample of 100 recordIDs. Is there a simple way to complete this?

google-bigquery random
2 Answers

Had a similar need, namely cluster sampling, over 400M and more columns but hit Exceeded resources... error when using ROW_NUMBER().

If you don't need RAND() because your data is unordered anyway, this performs quite well (<30s in my case):

SELECT ARRAY_AGG(x LIMIT 100) 
FROM yourtable x 
GROUP BY groupId

You can:

  • decorate with UNNEST() if front-end cannot render nested records
  • add ORDER BY groupId to find/confirm patterns more quickly
user_65113610
Related