Saya mengalami kesulitan dalam membuat statistik dengan jumlah langganan yang sedang berlangsung per bulan

Saya punya langganan meja

id | created_at          | cancelled_at
----------------------------------------
 1 | 2020-12-29 13:56:12 | null
 2 | 2021-02-15 01:06:25 | 2021-04-21 19:35:31
 3 | 2021-03-22 02:42:19 | null
 4 | 2021-04-21 19:35:31 | null

Dan statistik akan terlihat sebagai berikut:

month   | count
---------------
12/2020 | 1     -- #1
01/2021 | 1     -- #1
02/2021 | 2     -- #1 + #2
03/2021 | 3     -- #1 + #2 + #3
04/2021 | 3     -- #1 + #3 + #4, not #2 since it ends that month
05/2021 | 3     -- #1 + #3 + #4

Sejauh ini saya dapat membuat daftar semua bulan yang saya butuhkan statistik untuk:

select generate_series(min, max, '1 month') as "month"
from (
    select date_trunc('month', min(created_at)) as min,
    now() as max
    from subscriptions
) months;

Dan dapatkan jumlah langganan yang tepat untuk bulan tertentu

select sum(
    case 
        when
            make_date(2021, 04, 1) >= date_trunc('month', created_at) 
            and make_date(2021, 04, 1); < date_trunc('month', coalesce(cancelled_at, now() + interval '1 month'))
        then 1
        else 0
    end
) as total
from subscriptions
-- returns 3

Tapi saya berjuang menggabungkan mereka bersama-sama... akan OVER (yang saya tidak berpengalaman dengan) akan berguna bagi saya? saya menemukan Hitung total kumulatif di Postgresql tetapi kasusnya berbeda (tanggal sudah diperbaiki).. atau apakah pendekatan yang tepat untuk menggunakan fungsi dengan FOR entah bagaimana?

1
rorymac 26 Mei 2021, 02:59

1 menjawab

Jawaban Terbaik

Anda dapat menggunakan generate_series() untuk menghasilkan bulan dan kemudian subquery yang berkorelasi untuk menghitung yang aktif:

select yyyymm,
       (select count(*)
        from subscriptions s
        where s.created_at < gs.yyyymm + interval '1 month' and
              (s.cancelled_at > gs.yyyymm + interval '1 month' or s.cancelled_at is null)
       ) as count
from generate_series('2020-12-01'::date, '2021-05-01'::date, interval '1 month'
                    ) gs(yyyymm);
1
Gordon Linoff 26 Mei 2021, 00:26