postgresql - pg函数内的for循环
问题描述
我有以下 pg 脚本
create or replace function ended_jobs()
returns table(
"Time Range" timestamptz,
"Ended Jobs" numeric
) as $$
begin
return query SELECT date_trunc('HOUR',date_interval) as "Time Range", sum(COALESCE(end_count,0)) "Ended Jobs"
FROM "job_start_end_rollups"
right join ( (select current_timestamp - interval '0 HOUR' date_interval)
union all (select current_timestamp - interval '1 HOUR' date_interval)
union all (select current_timestamp - interval '2 HOUR' date_interval)
union all (select current_timestamp - interval '3 HOUR' date_interval)
union all (select current_timestamp - interval '4 HOUR' date_interval)
union all (select current_timestamp - interval '5 HOUR' date_interval)
union all (select current_timestamp - interval '6 HOUR' date_interval)
union all (select current_timestamp - interval '7 HOUR' date_interval)
union all (select current_timestamp - interval '8 HOUR' date_interval)
union all (select current_timestamp - interval '9 HOUR' date_interval)
union all (select current_timestamp - interval '10 HOUR' date_interval)
union all (select current_timestamp - interval '11 HOUR' date_interval)
union all (select current_timestamp - interval '12 HOUR' date_interval)
union all (select current_timestamp - interval '13 HOUR' date_interval)
union all (select current_timestamp - interval '14 HOUR' date_interval)
union all (select current_timestamp - interval '15 HOUR' date_interval) )
date_intervals on date_trunc('HOUR', TIMESTAMP WITH TIME ZONE 'epoch' + chunk_start_time * interval '1 millisecond' ) = date_trunc('HOUR',date_interval)
GROUP BY date_part('HOUR',TIMESTAMP WITH TIME ZONE 'epoch' + chunk_start_time * interval '1 millisecond'),date_interval
ORDER BY date_interval LIMIT 16;
end;
$$
LANGUAGE 'plpgsql';
运行上述 pg 脚本后,我可以获得数据
select * from ended_jobs()
如何在右连接中编写“for循环”以避免多个“联合全部”,像这样
.......
right join ( (select current_timestamp - interval '0 HOUR' date_interval)
for i in 1..16 loop
union all (select current_timestamp - concat(i,' HOUR')::INTERVAL date_interval)
end loop;
........
解决方案
您可以generate_series()
为此使用:
FROM "job_start_end_rollups"
right join generate_series(current_timestamp - interval '15 HOUR',
current_timestamp,
interval '1 hour') as date_intervals(date_interval)
ON ....
您也可以将其date_trunc()
移入:
right join (
select date_trunc('HOUR',di) as date_interval
from generate_series(current_timestamp - interval '15 HOUR',
current_timestamp,
interval '1 hour') as t(di)
) as date_intervals ON .... = date_intervals.date_interval
推荐阅读
- python - Binder Jupyter notebook 缺少来自 environment.yml 的一些依赖项
- speech-recognition - 在树莓派上录制的音频具有更高的速度和很多破损
- r - 使用 Stat_smooth 将指数最佳拟合曲线绘制到 ggplot2
- apache-spark - SparkSession 在单元测试之间没有正确关闭
- html - 我如何让文字总是一起出现?引导程序
- typescript - 当 Observables 序列完成时通知
- nightwatch.js - NightwatchJS:自定义命令不会因错误而失败
- haskell - 在 Haskell 中异步读取行
- algorithm - 树贪婪方法的顶点覆盖
- java - 带有 Java 8 lambda 和 Optional 的子类