ROW_NUMBER() and Mathematical functions in BigQuery

Question

I couldn't find an answer to this specific to BigQuery. Basically I want to be able to count ROW_NUMBER in a query and then modify the row number with basic math operators (add, subtract, etc) without using a subquery.

I know the following code works, but to me it seems unnecessary to use a subquery just to append basic math operators

SELECT
  id,
  title,
  language,
  rn+5
FROM
(
  SELECT
    id,
    title,
    language,
    ROW_NUMBER() OVER() AS rn
  FROM [publicdata:samples.wikipedia] LIMIT 1000
)

However when I try to add the plus sign without the subquery I get an error

SELECT
  id,
  title,
  language,
  ROW_NUMBER() OVER() +5  AS rn_offset,
FROM [publicdata:samples.wikipedia] LIMIT 1000

Is what I'm trying to do possible in BigQuery without a subquery, or is my syntax in error?

Thanks, Onji

Answer 1

This is a limitation of BigQuery's legacy SQL - various computations cannot be composed without using a subselect. It is unfortunate, but unlikely to be changed at this point. This is just one example among many.

However, you can do this in standard SQL without a subquery:

SELECT
  id,
  title,
  language,
  ROW_NUMBER() OVER() +5  AS rn_offset
FROM `publicdata.samples.wikipedia` LIMIT 1000

If limitations like this annoy you while using legacy SQL, I suggest looking into using standard SQL.