PostgreSQL - how to loop over a parameter to make union of different queries of the same table

Question

I need to count the number of events still occurring in a given year, provided these events started before the given year and ended after it. So, the query I'm using now is just a pile of unparametrized queries and I'd like to write it out with a loop on a parameter:

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

So, I have ONE table that must be filtered according to one parameter that is both part of the select statement and the where clause

The general aspect of the "atomic" query is then

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

Can anyone help me put this in a more formal loop?

Thanks a lot, I hope I was clear enough.

Please provide sample data and desired results.

Gordon Linoff
– Gordon Linoff

2021-05-21 11:27:28 +00:00
Commented May 21, 2021 at 11:27 — Gordon Linoff
– Gordon Linoff, Commented May 21, 2021 at 11:27

Gordon Linoff · Accepted Answer · 2021-05-21 11:32:30Z

1

You seem to want events that span entire years. Perhaps a simple way is to generate the years, then use join and aggregate:

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;

answered May 21, 2021 at 11:32

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ale J. Caua Over a year ago

Great! Thanks a lot! This is actually a brilliant approach I've never thought of, and most important it works fine and fast. By now I'm not making this the accepted answer just because I'd like to see other solutions that make actual use of a loop, so that I might reuse them in other occasions.

Collectives™ on Stack Overflow

PostgreSQL - how to loop over a parameter to make union of different queries of the same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related