Как назначать строки разным когортам на основе критериев агрегирования

У меня есть список транзакций в таблице postgresql, и мне нужно разбить их на группы в зависимости от того, когда они произошли, и превысило ли общее количество транзакций пороговое значение.

«Когорта» здесь определяется по последнему дню месяца и по тому, был ли достигнут порог в 100 долларов.

Пример: «Когорта» становится «Когортой» в последний день месяца, когда пакет транзакций >= 100 долларов США.

ОБРАЗЕЦ ДАННЫХ:

|TRANS_DATE|AMOUNT|

2018-01-01 | $10
2018-01-15 | $10
2018-01-30 | $50
2018-02-27 | $80
2018-03-05 | $101
2018-04-05 | $1
2018-05-15 | $80
2018-06-05 | $1
2018-07-26 | $18

Учитывая эти данные, я ожидаю, что результаты совокупного запроса будут следующими:

DATE | AMOUNT | COHORT

2018-02-28 | $150 | 1
2018-03-31 | $101 | 2
2018-07-31 | $100 | 3

Я продолжаю думать, что мне понадобится какой-то цикл для этой проблемы, но я не верю, что это возможно.

Я пробовал вещи, похожие на:

with st as 
(
select distinct(date_trunc('month', "date") + interval '1 month' - interval '1 day') as date,
sum(amount) over (order by date_trunc('month', date) + interval '1 month' - interval '1 day') as total
from a1
order by 1  
)
select st.*
, case when lag(total) over (order by date) <= 100 then 1 end as cohort1 
, floor(total/100)
from st

что ты уже испробовал?

— 30.05.2019 02:22

sql postgresql

30.05.2019 02:19

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Это довольно сложно. Я почти уверен, что вам нужны рекурсивные CTE, потому что вы наталкиваетесь на границу, а затем начинаете сначала.

Попробуй это:

with tt as (
      select date_trunc('mon', trans_date) as mon, sum(amount) as amount,
             lead(sum(amount)) over (order by min(trans_date)) as next_amount,
             row_number() over (order by min(trans_date)) as seqnum
      from t
      group by 1
     ),
     cte as (
      select mon, amount, seqnum, 1 as cohort, (amount >= 100) as is_new_cohort
      from tt
      where seqnum = 1
      union all
      select tt.mon,
             (case when is_new_cohort then tt.amount else cte.amount + tt.amount end) as amount,  
             tt.seqnum,
             (case when is_new_cohort then cohort + 1 else cohort end) as cohort,
             ( (case when is_new_cohort then tt.amount else cte.amount + tt.amount end) >= 100) as is_new_cohort
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select cohort, max(amount), max(cte.mon + interval '1 month' - interval '1 day') as mon
from cte
group by 1
order by 1;

Здесь — это рабочий пример db<>.

30.05.2019 03:03