在SQL(红移)中创建范围(x)的CTE ?

Question

我想创建一个临时变量或CTE，列名为weeks，值为[1，…52]

SQL (Redshift)等价于range(52)是什么?

在python中我可以写

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

但是我不能在我的上下文中使用Python，所以我需要完全在SQL中完成。

Answer 1

这可以通过递归CTE完成- https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

它看起来像

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;

=========================================

为查询添加了一些迂腐的细节，以防您的环境的某些方面混淆了查询:

with recursive week_no(n) as (
select 1::int as n 
union all
select n + 1 as n
from week_no wn
where wn.n < 52
)
select n 
from week_no;