unisci due COUNT in una query SQL

Question

Devo fare una query per mostrare new_customers X clients_cancellations

con questa query posso ottenere i new_customers per mese:

seleziona count(start_date), to_char(start_date, 'MM') come monthNumber, to_char(start_date, 'YY') come yearNumber
dal cliente dove data_inizio non è nullo
raggruppa per to_char(data_inizio, 'MM'), to_char(data_inizio, 'AA')
ordina per annoNumero, meseNumero;

con quest'altro, posso ottenere le cancellazioni dei clienti per mese:

seleziona count(cancellation_date), to_char(cancellation_date, 'MM') come monthNumber, to_char(cancellation_date, 'YY') come yearNumber
dal cliente dove cancel_date non è nullo
raggruppa per to_char(cancellation_date, 'MM'), to_char(cancellation_date, 'YY')
ordina per annoNumero, meseNumero;

entrambe le query restituiscono qualcosa del tipo:

contare| numeromese |numeroanno
1 | 1 | 20
7 | 2 | 20
5 | 3 | 20

ma vorrei qualcosa del genere:

conteggio_clienti|conteggio_clienti| numeromese |numeroanno
 0 |1 | 1 | 20
 0 |7 | 2 | 20
 1 |0 | 3 | 20
 0 |1 | 4 | 20
 5 |7 | 5 | 20
 1 |5 | 6 | 20

Ho già provato quest'altra query:

seleziona 'start' come tipo, count(start_date) come count, to_char(start_date, 'MM') come monthNumber, to_char(start_date, 'YY') come yearNumber
dal cliente dove data_inizio non è nullo
raggruppa per to_char(data_inizio, 'MM'), to_char(data_inizio, 'AA')
unione
seleziona 'cancellazione' come tipo, count(cancellation_date) come counta, to_char(cancellation_date, 'MM') come monthNumber, to_char(cancellation_date, 'YY') come yearNumber
dal cliente dove cancel_date non è nullo
raggruppa per to_char(cancellation_date, 'MM'), to_char(cancellation_date, 'YY')
ordina per annoNumero, meseNumero;

Il risultato è "ok":

digitare |nuovi | numeromese |numeroanno
 inizio |1 | 1 | 20
 cancellazione |1 | 1 | 20
 cancellazione |7 | 2 | 20
 inizio |3 | 3 | 20
 cancellazione |1 | 4 | 20
 inizio |7 | 5 | 20
 inizio |5 | 6 | 20

Ma dovrò eseguire alcune operazioni sul codice per ottenere ciò di cui ho bisogno.

Come posso unire queste due query in una? Sto usando postgreslq.

Answer 1

Un'opzione annulla il pivot delle righe, quindi le aggrega. In Postgres, lo esprimeresti con un join laterale:

select
    sum(x.customer_in_count)  as customer_in_count,
    sum(x.customer_out_count) as customer_out_count
    to_char(x.dt, 'MM') as monthNumber, 
    to_char(x.dt, 'YY') as yearNumber
from customer c
cross join lateral (values
    (c.start_date, 1, 0), (c.cancellation_date, 0, 1)
) as x(dt, customer_in_count, customer_out_count)
where x.dt is not null
group by monthNumber, yearNumber
order by monthNumber, yearNumber