sql - 连续付款的客户

Question

我不确定这是否可以在 PostgreSQL 上完成，我有一个包含 customer_id 和 paid_at(他们付款的月份)的表在过去 6 个月(自 2018 年 1 月起)连续付款，以及自首次付款之日起连续付款的那些公司 (min(paid_on))？

     customer_id    paid_on
       14535    01/04/2018
       21828    01/10/2017
       52159    01/10/2017
       35033    01/02/2018
       1686     01/08/2016
       7347     01/02/2018
       33721    01/01/2018
       25789    01/07/2017
       62237    01/01/2018
       46184    01/02/2018

Answer 1

示例数据:

create table payments(customer_id int, paid_on date);
insert into payments values
    (1, '2018-03-01'),
    (1, '2018-04-01'),
    (1, '2018-06-01'),
    (1, '2018-07-01'),
    (2, '2018-01-01'),
    (2, '2018-04-01'),
    (2, '2018-05-01'),
    (2, '2018-06-01'),
    (2, '2018-07-01'),
    (3, '2018-03-01'),
    (3, '2018-04-01');

该查询为您提供有关上次付款月份的信息以及上一个系列中连续付款月份的数量以及所有付款月份的数量:

select 
    customer_id, 
    max(paid_on) as last_payment, 
    count(*) filter (where sum = 0) as consecutive_months,
    count(*) as all_months
from (
    select 
        customer_id, paid_on, 
        sum(grp) over w
    from (
        select 
            customer_id, paid_on, 
            (paid_on <> lag((paid_on- '1 month'::interval)::date, 1, paid_on) over w)::int as grp
        from payments
        window w as (partition by customer_id order by paid_on desc)
        ) s
    window w as (partition by customer_id order by paid_on desc)
    ) s
group by 1

 customer_id | last_payment | consecutive_months | all_months 
-------------+--------------+--------------------+------------
           1 | 2018-07-01   |                  2 |          4
           2 | 2018-07-01   |                  4 |          5
           3 | 2018-04-01   |                  2 |          2
(3 rows)