Row number in BigQuery?

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

Solution 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)

Solution 2:

Another HACK would be to go along the lines of:

SELECT *
FROM UNNEST(ARRAY(
    SELECT myColumn FROM myTable
)) AS myValue WITH OFFSET off

This gives you a resultset with 2 colums: myValue and off.

Benefit of this is that you could also use off in WHERE clauses create a non deterministic LIMIT, e.g. WHERE off < (SELECT SUM(amount) FROM mySecondTable)

Note that I do not consider this a viable alternative for large amounts of data. But it might suit your use case.