Finding sequence starting from a particular number in Big query

Question

How can we achieve the same functionality as of 'SEQUENCE' in provided in Netezza?

Please find below the link demonstrating the functionality I would like to achieve in Big query :

[https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_create_sequence.html][1]

I have reviewed RANK() but this is not solving my purpose to the core. Any leads would be appreciated.

Answer 1

in BigQuery Standard SQL you can find two function that can help you here -

GENERATE_ARRAY(start_expression, end_expression\[, step_expression\])

and

GENERATE_DATE_ARRAY(start_date, end_date\[, INTERVAL INT64_expr date_part\])

For example, below code

#standardSQL
SELECT sequence
FROM UNNEST(GENERATE_ARRAY(1, 10, 1)) AS sequence   

produces result as

sequence     
1    
2    
3    
4    
5    
6    
7    
8    
9    
10