java postgresql插件_For LOOP in PostgreSQL

最新推荐文章于 2024-07-01 07:30:00 发布

咯嗯

最新推荐文章于 2024-07-01 07:30:00 发布

阅读量151

点赞数

文章标签： java postgresql插件

本文链接：https://blog.csdn.net/weixin_35988038/article/details/114926212

版权

I have a table with the following columns: (client_id, start_contract_date, end_contract_date)

Every client has a start_contract_date but some clients have a NULL for end_contract_date since they may still be active today.

If we check for a certain date D, a client is active if D is between start_contract_date and end_contract_date (or start_contract_date <= D, if end_contract_date is NULL.)

I want to count, for each month of each year, over 2016 until today, how many customers are active. My problem is that I do not know how to LOOP on the months and years.

I have a partial solution. I can count how many active clients for a specific month of a specific year.

SELECT 2016 as year , 7 as month, count(id_client)

FROM table

WHERE

EXTRACT(year from start_contract_date)<=2016

AND EXTRACT(month from start_contract_date)<=7

AND (EXTRACT(year from end_contract_date)>=2016 OR end_contract_date IS NULL)

AND (EXTRACT(month from end_contract_date)>=7 OR end_contract_date IS NULL)

;

So, how can I run a nested for loop that would be something like

FOR y IN 2016..2017

FOR m IN 1..12