ORACLE“conversion by”到POSTGRESQL转换

Question

如何将下面的SQL转换/重写为PostgreSQL？我不知道如何在connect by level和listagg中转换postgreSQL。

select listagg(dt,'  ') within group (order by lvl), NBR
from 
(
select level lvl,
case when level=1 then TO_CHAR(a.dt2,'MM/DD/YYYY HH24:MI:SS')
else
TO_CHAR(a.dt2+(1/1440*30*(level-1)),'MM/DD/YYYY HH24:MI:SS')
end
dt,10 NBR from 
(select to_date('08/11/2021 18:30:00','MM/DD/YYYY HH24:MI:SS') dt1,to_date('08/11/2021 01:30:00','MM/DD/YYYY HH24:MI:SS') dt2 from dual) a
start with level=0
connect by level<=1+(to_date('08/11/2021 18:30:00','MM/DD/YYYY HH24:MI:SS')-to_date('08/11/2021 13:30:00','MM/DD/YYYY HH24:MI:SS'))*24*2)
GROUP BY NBR;

输出：

08/11/2021 01:30:00
08/11/2021 02:00:00
08/11/2021 02:30:00
08/11/2021 03:00:00
08/11/2021 03:30:00
08/11/2021 04:00:00
08/11/2021 04:30:00
08/11/2021 05:00:00
08/11/2021 05:30:00
08/11/2021 06:00:00
08/11/2021 06:30:00

Answer 1

我不确定group by nbr应该实现什么--据我所知，这是没有意义的。

Oracle中复杂的connect by level可以在Postgres中用一个简单的generate_series()代替。

因此，下面将生成从2021-08-11 01:30:00到2021-08-11 06:30:00的11个时间戳值

select g.dt
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') as g(dt)

然后，可以使用string_agg()将其聚合回字符串中。

select string_agg(to_char(dt, 'dd/mm/yyyy hh24:mi:ss'), ' '), 10 as nbr
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') as g(dt)

如果需要生成的行数，可以使用with ordinality子句获得：

select string_agg(to_char(dt, 'dd/mm/yyyy hh24:mi:ss'), ' '), max(idx) as nbr
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') with ordinality as g(dt,idx)