Блог

У меня есть данные, которые выглядят следующим образом, суммированные остатки по клиентам за определенные месяцы (сентябрь 1 2021,12:00 означает только сентябрь)

 client_id|balance_month|running_balance
20|September 1,2021,12:00 AM|$1000.00
20|August 1,2021,12:00 AM|$900.00
20|July 1,2021,12:00 AM|$600.00
20|April 1,2021,12:00 AM|$400.00
10|September 1,2021,12:00 AM|-$500.00
10|August 1,2021,12:00 AM|$900.00
10|July 1,2021,12:00 AM|$600.00
10|April 1,2021,12:00 AM|$400.00
 

Я хочу получить самый текущий баланс для каждого клиента и увидеть эту разницу по сравнению с прошлым месяцем, два месяца назад, три месяца назад и более четырех месяцев назад. (Сентябрь 2021 года по состоянию на этот пост). Общая сумма каждого ведра для данного клиента должна равняться текущему балансу.

 client_id|0to30|30to60|60to90|90to120|120plus
20       |$100 |$300  |$200  |$0     |$400
10       |$0   |$0    |$0    |$0     |$0
 

Таким образом, общая сумма ($100 $300 $200 $0 $400) для каждого клиента добавляется сумма к текущему непогашенному балансу (1000 долларов США или 0 долларов США, если баланс отрицательный). Кроме того, у клиентов может не быть остатков средств за предыдущий месяц (август), показанных в исходных данных, тогда это будет просто последний остаток за месяц до этого.

Как я могу эффективно выполнить это в postgresql?

Вы можете отсчитать месяцы назад и использовать условную агрегацию:

 select client_id,
       (max(running_balance) filter (where seqnum = 1)  -
        max(running_balance) filter (where seqnum = 2)
       ) as month_1,
       (max(running_balance) filter (where seqnum = 2)  -
        max(running_balance) filter (where seqnum = 3)
       ) as month_2,
       (max(running_balance) filter (where seqnum = 3)  -
        max(running_balance) filter (where seqnum = 4)
       ) as month_3,
       max(running_balance) filter (where seqnum = 4) as month_4
from (select t.*,
             row_number() over (partition by client_id order by balance_month desc) as seqnum
      from t
     ) t
group by client_id;
 

Примечание: Если вы хотите, чтобы это было для явных месяцев, просто используйте месяцы в filter :

 select client_id,
       (max(running_balance) filter (where balance_month::date = '2021-09-01')  -
        max(running_balance) filter (where balance_month::date = '2021-08-01')
       ) as month_1,
       (max(running_balance) filter (where balance_month::date = '2021-08-01')  -
        max(running_balance) filter (where balance_month::date = '2021-07-01')
       ) as month_2,
       (max(running_balance) filter (where balance_month::date = '2021-07-01')  -
        max(running_balance) filter (where balance_month::date = '2021-06-01')
       ) as month_3,
       max(running_balance) filter (where balance_month::date = '2021-06-01') as month_4
from t
group by client_id;