Блог

Я пытаюсь рассчитать ежемесячный повторяющийся доход в базе данных SQL Server для заказов на подписку.

Получение некоторых подробностей из Baremetrics о MRR

Как он рассчитывается?

Допустим, у вас есть 10 клиентов, каждый из которых платит вам по 10 долларов в месяц. Вот как вы могли бы рассчитать MRR.

Все не ежемесячные планы нормализуются на ежемесячные. Итак, если клиент платит вам 120 долларов по годовому плану, мы добавляем 10 долларов к MRR на следующие 12 месяцев. Отлично!

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

 DateCreated OrderID     Price   Months
2013-03-05  1           49.9500 12
2013-04-01  2           5.9500  1
2013-04-08  3           7.1100  12
2013-05-15  4           42.9500 3
2013-06-12  5           13.9850 6
2013-06-27  6           7.1100  6
2013-07-29  7           21.9250 3
 

Теперь для MRR. Я ожидаю, что у меня будет два столбца: первый с датой начала месяца и года (например, 01-03-2013), а второй с общим доходом на основе определения MRR.

Например, OrderID 1 следует разделить на 12 и добавить результат к текущему и следующим 11 месяцам. OrderID 2 должно быть все в этом месяце, поскольку план подписки рассчитан на 1 месяц и продолжается.

Есть идеи, как я могу продолжить с остальным?

Пример результата:

 Month       MRR
01-03-2013  4.1625
01-04-2013  4.1625   5.95   0.5925
01-05-2013  4.1625   0.5925   14.31 
01-06-2013  4.1625   0.5925   14.31   2.33   1.185
01-07-2013  4.1625   0.5925   14.31   2.33   1.185
01-08-2013  4.1625   0.5925   2.33   1.185   7.30
01-09-2013  4.1625   0.5925   2.33   1.185   7.30
01-10-2013  4.1625   0.5925   2.33   1.185   7.30
01-11-2013  4.1625   0.5925   2.33   1.185
01-12-2013  4.1625   0.5925
01-01-2014  4.1625   0.5925
01-02-2014  4.1625   0.5925
01-03-2014  0.5925
 

Цены для добавления в месяц:

 49.95/12 = 4.1625
5.95/1 = 5.95
7.11/12 = 0.5925
42.95/3 = 14.31
13.985/6 = 2.33
7.11/6 = 1.185
21.925/3 = 7.30
 

Я оставил операции в столбце MRR в результате, чтобы было понятно. Очевидно, что результатом должна быть их сумма.

Создайте таблицу ввода:

 CREATE TABLE orders
(
    DateCreated datetime,
    OrderID int,
    Price float,
    Months int
);

INSERT INTO orders 
VALUES (2013-03-05, 1, 49.9500, 12),
       (2013-04-01, 2, 5.9500, 1),
       (2013-04-08, 3, 7.1100, 12),
       (2013-05-15, 4, 42.9500, 3),
       (2013-06-12, 5, 13.9850, 6),
       (2013-06-27, 6, 7.1100, 6),
       (2013-07-29, 7, 21.92500, 3)
 

Я использовал рекурсивный CTE, чтобы попытаться получить полный диапазон месяцев, который вам когда-либо понадобится, но я скорректировал его до 1-го числа месяца, после чего я присоединился, исходя из того, применим ли заказ к этому месяцу, учитывая тот факт, что он остается актуальным в течение n-1 последующих месяцев (где n- это ваши «Месяцы») — затем я собрал все данные в JOINER (которые вы также можете выбрать *, чтобы просмотреть их), затем я объединил JOINER в суммирование по месяцам.

Я скорректировал все даты до первого числа месяца для работы

 WITH MTHS AS (SELECT dateADD(day, -DAY(MIN(datecreated))   1, MIN(datecreated)) MD, (SELECT MAX(dateadd(month,o2.months, o2.datecreated)) FROM ORDERS o2) AS MXD FROM Orders 
                UNION ALL
                SELECT DATEADD(month,1,MD), MTHS.MXD  FROM MTHS WHERE DATEADD(month,1,MD) < MTHS.mxd),
    JOINER AS
            (
                select MD, ord.price / ord.months mlyprice from MTHS  
                                    JOIN ORDERS ord ON dateadd(day, -day(ord.datecreated)   1, ord.datecreated) 
                                    BETWEEN 
                                            dateadd(month,1-ord.months,MTHS.md ) 
                                            AND  
                                            MTHS.md 
            )
    SELECT J.MD,SUM(mlyprice)  FROM JOINER J GROUP BY J.MD;
 

Я думаю, что это выглядит многообещающе

     CREATE TABLE orders
(
    DateCreated datetime,
    OrderID int,
    Price float,
    Months int
);

INSERT INTO orders 
VALUES ('2013-03-05', 1, 49.9500, 12),
       ('2013-04-01', 2, 5.9500, 1),
       ('2013-04-08', 3, 7.1100, 12),
       ('2013-05-15', 4, 42.9500, 3),
       ('2013-06-12', 5, 13.9850, 6),
       ('2013-06-27', 6, 7.1100, 6),
       ('2013-07-29', 7, 21.92500, 3)


    ;WITH MTHS AS (SELECT dateADD(day, -DAY(MIN(datecreated))   1, MIN(datecreated)) MD, (SELECT MAX(dateadd(month,o2.months, o2.datecreated)) FROM ORDERS o2) AS MXD FROM Orders 
                    UNION ALL
                    SELECT DATEADD(month,1,MD), MTHS.MXD  FROM MTHS WHERE DATEADD(month,1,MD) < MTHS.mxd),
        JOINER AS
                (
                    select MD, ord.price / ord.months mlyprice from MTHS  
                                        JOIN ORDERS ord ON dateadd(day, -day(ord.datecreated)   1, ord.datecreated) 
                                        BETWEEN 
                                                dateadd(month,1-ord.months,MTHS.md ) 
                                                AND  
                                                MTHS.md 
                )
        SELECT J.MD,SUM(mlyprice)  FROM JOINER J GROUP BY J.MD