Row number in BigQuery?

Question

Is there any way to get row number for each record in BigQuery? (From the specs, I haven't seen anything about it) There is a NTH() function, but that applies to repeated fields.

There are some scenarios where row number is not necessary in BigQuery, such as the use of TOP() or LIMIT function. However, I need it to simulate some analytical functions, such as a cumulative sum(). For that purpose I need to identify each record with a sequential number. Any workaround on this?

Thanks in advance for your help!

Leo

Answer 1

2018 update: If all you want is a unique id for each row

#standardSQL SELECT GENERATE_UUID() uuid  , *  FROM table 

2018 #standardSQL solution:

SELECT   ROW_NUMBER() OVER() row_number, contributor_username,   count FROM (   SELECT contributor_username, COUNT(*) count   FROM `publicdata.samples.wikipedia`   GROUP BY contributor_username   ORDER BY COUNT DESC   LIMIT 5) 

---

But what about "Resources exceeded during query execution: The query could not be executed in the allotted memory. OVER() operator used too much memory.."

Ok, let's reproduce that error:

SELECT *, ROW_NUMBER() OVER()  FROM `publicdata.samples.natality`  

Yes - that happens because OVER() needs to fit all data into one VM - which you can solve with PARTITION:

SELECT *, ROW_NUMBER() OVER(PARTITION BY year, month) rn  FROM `publicdata.samples.natality`  

---

"But now many rows have the same row number and all I wanted was a different id for each row"

Ok, ok. Let's use partitions to give a row number to each row, and let's combine that row number with the partition fields to get an unique id per row:

SELECT *   , FORMAT('%i-%i-%i', year, month, ROW_NUMBER() OVER(PARTITION BY year, month)) id FROM `publicdata.samples.natality`  

---

The original 2013 solution:

Good news: BigQuery now has a row_number function.

Simple example:

SELECT [field], ROW_NUMBER() OVER() FROM [table] GROUP BY [field] 

More complex, working example:

SELECT   ROW_NUMBER() OVER() row_number,   contributor_username,   count, FROM (   SELECT contributor_username, COUNT(*) count,   FROM [publicdata:samples.wikipedia]   GROUP BY contributor_username   ORDER BY COUNT DESC   LIMIT 5)