I'd like to create a temp variable or CTE with column name weeks and values [1,...52]

What's the SQL (Redshift) equivalent of range(52)?

In python I could do

import pandas as pd 
data = {'weeks':list(range(1,53))}
weeks = pd.DataFrame(data)

But I can't make use of Python in my context, so I need to accomplish this purely in SQL.

CodePudding user response：

This can be done with a recursive CTE - https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

It will look something like

with recursive week_no(n) as (
select 1 as n 
union all
select n   1 as n
from week_no
where n < 52
)
select n 
from week_no