Как сохранить стоимость и вычислить на основе группы покупки первого месяца следующую сумму покупки в sql

я хотел бы создать удержания клиентов.

Итак, у меня есть эта таблица:

id date purchase
1 2020-01 200
2 2020-12 100
2 2020-03 150
3 2020-03 200
1 2020-07 120
1 2020-08 300
3 2020-05 250

и я хочу этот вывод:

purchase month first purchase month total
0 2020-01 200
6 2020-01 320
7 2020-01 620
0 2020-03 350
4 2020-03 600
11 2020-03 700

Клиенты, разместившие свой первый заказ в январе 2020 года, потратили 200 в месяц 0 (то есть в январе 2020 года) и 120 (320-200) в месяце 6 (то есть в июле 2020 года).

Клиенты, разместившие свой первый заказ в марте 2020 года, потратили 350 в месяц 0 (то есть в марте 2020 года) и 250 (600–350) в месяце 4 (то есть в мае 2020 года).

Заранее спасибо за помощь


sql database sqlite retention
person Max Mamazita    schedule 28.05.2021    source источник
comment
Помните, что jQuery и Query не совпадают при выборе тегов.   -  person Carsten Løvbo Andersen    schedule 28.05.2021

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете легко сделать это с помощью оконной функции и обычного табличного выражения.

Операторы схемы и вставки:

 create table purchases(id int, date date, purchase int);
 insert into purchases values(1,    '2020-01',  200);
 insert into purchases values(2,    '2020-12',  100);
 insert into purchases values(2,    '2020-03',  150);
 insert into purchases values(3,    '2020-03',  200);
 insert into purchases values(1,    '2020-07',  120);
 insert into purchases values(1,    '2020-08',  300);
 insert into purchases values(3,    '2020-05',  250);

Запрос:

with cte as
     ( 
       select id,date,purchase,
       min(date)over(partition by id) FirstPurchaseMonth from purchases
     )
     ,cte2 as
     (
       select substr(date,6,2)-substr(firstpurchasemonth,6,2) Purchasemonth, max(FirstPurchaseMonth)firstpurchasemonth, 
       purchase,sum(purchase)total from cte
       group by firstpurchasemonth,substr(date,6,2)-substr(firstpurchasemonth,6,2) 
     )
     select purchasemonth,firstpurchasemonth,sum(total)over(partition by firstpurchasemonth order by purchasemonth)total
     from cte2

Выход:

Purchasemonth firstpurchasemonth total
0 2020-01 200
6 2020-01 320
7 2020-01 620
0 2020-03 350
2 2020-03 600
9 2020-03 700

db‹fiddle здесь

person Kazi Mohammad Ali Nur    schedule 28.05.2021