时间戳

首页 > 解决方案 > ORACLE "connect by" 到 POSTGRESQL 的转换

postgresql - ORACLE "connect by" 到 POSTGRESQL 的转换

问题描述

如何将以下 SQL 转换/重写为 PostgreSQL？我不确定如何在 postgreSQL中转换connect by leveland 。listagg

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

标签： postgresql

解决方案

不确定group by nbr应该达到什么目的 - 据我所知，这没有任何意义。

Oracle 中的复杂connect by level可以用generate_series()Postgres 中的简单替换。

因此，以下将生成 11 个时间戳2021-08-11 01:30:00值2021-08-11 06:30:00：

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)

推荐阅读