sql - PostgreSQL 中的循环

Question

我有一个包含以下列的表格:(client_id, start_contract_date, end_contract_date)

每个客户都有一个 start_contract_date 但有些客户的 end_contract_date 为 NULL，因为他们今天可能仍然活跃。

如果我们检查某个日期 D，如果 D 在 start_contract_date 和 end_contract_date 之间(或者 start_contract_date <= D，如果 end_contract_date 为 NULL。)则客户端是事件的。

我想统计从 2016 年到今天，每年每个月有多少活跃客户。我的问题是我不知道如何在月份和年份上循环。

我有一个部分解决方案。我可以计算特定年份的特定月份有多少活跃客户。

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)
;

那么，我怎样才能运行类似于

的嵌套 for 循环

FOR y IN 2016..2017

    FOR m IN 1..12

我想要的输出是

Year , Month , Count
2016 , 1 , 234
2016 , 2 , 54
…
2017 , 12 , 543

Answer 1

使用函数 generate_series()生成任意系列的月份，例如:

select extract(year from d) as year, extract(month from d) as month
from generate_series('2017-11-01'::date, '2018-02-01', '1 month') d

 year | month 
------+-------
 2017 |    11
 2017 |    12
 2018 |     1
 2018 |     2
(4 rows)

使用上面的函数和date_trunc()从日期中提取年月值:

select extract(year from d) as year, extract(month from d) as month, count(id_client)
from generate_series('2016-01-01'::date, '2019-03-01', '1 month') d
left join my_table
    on date_trunc('month', start_contract_date) <= date_trunc('month', d)
    and (end_contract_date is null or date_trunc('month', end_contract_date) >= date_trunc('month', d))
group by d
order by d

另请注意，您查询中的条件包含逻辑错误。