时间戳

首页 > 解决方案 > PostgreSQL - 如何循环参数以合并同一张表的不同查询

sql - PostgreSQL - 如何循环参数以合并同一张表的不同查询

问题描述

我需要计算给定年份仍然发生的事件数量，前提是这些事件在给定年份之前开始并在之后结束。所以，我现在使用的查询只是一堆未参数化的查询，我想用一个参数循环写出来：

select
  count("starting_date" ) as "number_of_events" ,'2020' "year"
  from public.table_of_events
  where "starting_date" < '2020-01-01' and  ("closing_date" > '2020-12-31')
union all
select
  count("starting_date" ) as "number_of_events" ,'2019' "year"
  from public.table_of_events
  where "starting_date" < '2019-01-01' and  ("closing_date" > '2019-12-31')
union all
select
  count("starting_date" ) as "number_of_events" ,'2018' "year"
  from public.table_of_events
  where "starting_date" < '2018-01-01' and  ("closing_date" > '2018-12-31')
...
...
...
and so on for N years

所以，我有一张表，必须根据一个参数进行过滤，该参数既是select语句的一部分又是where子句的一部分

“原子”查询的一般方面是

select
  count("starting_date" ) as "number_of_events" , **PARAMETER** "year"
  from public.table_of_events
  where "starting_date" < **PARAMETER**  and  ("closing_date" > **PARAMETER** )
union all

谁能帮我把它放在一个更正式的循环中？

非常感谢，我希望我足够清楚。

标签： sqlpostgresqlloopsfor-loopparameters

解决方案

您似乎想要跨越整年的事件。也许一个简单的方法是生成年份，然后使用join和聚合：

select gs.yyyy, count(e.starting_date)
from public.table_of_events e left join
     generate_series('2015-01-01'::date,
                     '2021-01-01'::date,
                     interval '1 year'
                    ) as gs(yyyy)
     on e.starting_date < gs.yyyy and
        e.closing_date >= gs.yyyy + interval '1 year'
group by gs.yyyy;

推荐阅读