Запрос с динамическими интервалами дат

Не уверен, с какой версией MySql вы работаете, но если вы можете использовать 8.0, эта версия имеет множество функций, которые делают вещи немного более выполнимыми (CTE, row_number (), раздел и т. д.).

Я бы порекомендовал создать представление, подобное этому Пример DB-Fiddle, вызвать представление на стороне сервера и выполнить итерацию программно. Есть способы сделать это в SQL, но было бы сложно писать, тестировать и, вероятно, было бы менее эффективно.

Предположения:

1. Продукты нельзя продавать в неактивные диапазоны дат.
2. В таблице Statuses всегда будет чередоваться активный / неактивный / активный статус для каждого продукта. Т.е. нет диапазонов дат, в которых определенный продукт одновременно активен и неактивен.

Посмотреть Результаты:

+------------+-------------+------------+-------------+
| product_id | active_date | end_date   | days_active |
+------------+-------------+------------+-------------+
| 1          | 2018-01-01  | 2018-02-01 | 31          |
+------------+-------------+------------+-------------+
| 1          | 2018-03-01  | 2018-03-15 | 14          |
+------------+-------------+------------+-------------+
| 1          | 2018-04-25  | 2018-04-29 | 4           |
+------------+-------------+------------+-------------+
| 2          | 2018-03-01  | 2018-04-29 | 59          |
+------------+-------------+------------+-------------+
| 3          | 2018-03-10  | 2018-03-15 | 5           |
+------------+-------------+------------+-------------+

Вид:

CREATE OR REPLACE VIEW days_active AS (
WITH active_rn 
     AS (SELECT *, Row_number() 
                    OVER ( partition BY NAME, product_id 
                    ORDER BY created_at) AS rownum 
         FROM   statuses
         WHERE name = 'active'),
     inactive_rn 
     AS (SELECT *, Row_number() 
                    OVER ( partition BY NAME, product_id 
                    ORDER BY created_at) AS rownum 
         FROM   statuses
         WHERE name = 'inactive') 
SELECT x1.product_id, 
       x1.created_at AS active_date, 
       CASE WHEN x2.created_at IS NULL 
            THEN Curdate()
            ELSE x2.created_at 
       END AS end_date, 
       CASE WHEN x2.created_at IS NULL 
             THEN Datediff(Curdate(), x1.created_at) 
            ELSE  Datediff(x2.created_at,x1.created_at) 
        END AS days_active 
FROM   active_rn x1 
       LEFT OUTER JOIN inactive_rn x2 
                    ON x1.rownum = x2.rownum 
                       AND x1.product_id = x2.product_id ORDER  BY 
x1.product_id);

У меня есть стандартное решение SQL, которое не использует никаких оконных функций, как в MySQL 5.

Мое решение требует 3 представлений с накоплением.

Было бы лучше с CTE, но ваша версия его не поддерживает. То же самое и со сложенными представлениями ... Я не люблю складывать представления и всегда стараюсь избегать этого, но иногда у вас нет другого выбора, потому что MySQL не принимает подзапросы в предложении FROM для представлений.

CREATE VIEW VIEW_product_dates AS
(
        SELECT product_id, created_at AS active_date,
                (
                    SELECT created_at
                    FROM statuses ti
                    WHERE name = 'inactive' AND ta.created_at < ti.created_at AND ti.product_id=ta.product_id
                    GROUP BY product_id
                ) AS inactive_date
        FROM statuses ta
        WHERE name = 'active'
);

CREATE VIEW VIEW_product_dates_days AS
(
    SELECT product_id, active_date, inactive_date, datediff(IFNULL(inactive_date, SYSDATE()),active_date) AS nb_days
    FROM VIEW_product_dates
);

CREATE VIEW VIEW_product_dates_days_cumul AS
(
    SELECT product_id, active_date, ifnull(inactive_date,sysdate()) AS inactive_date,  nb_days,
         IFNULL((SELECT SUM(V2.nb_days) + V1.nb_days
                 FROM VIEW_product_dates_days V2
                 WHERE V2.active_date >= IFNULL(V1.inactive_date, SYSDATE()) AND V1.product_id=V2.product_id
                ),V1.nb_days) AS cumul_days
    FROM  VIEW_product_dates_days V1
);  

Окончательный вид производит следующее:

| product_id |          active_date |        inactive_date | nb_days | cumul_days |
|------------|----------------------|----------------------|---------|------------|
|          1 | 2018-01-01T00:00:00Z | 2018-02-01T00:00:00Z |      31 |         49 |
|          1 | 2018-03-01T00:00:00Z | 2018-03-15T00:00:00Z |      14 |         18 |
|          1 | 2018-04-25T00:00:00Z | 2018-04-29T11:28:39Z |       4 |          4 |
|          2 | 2018-03-01T00:00:00Z | 2018-04-29T11:28:39Z |      59 |         59 |
|          3 | 2018-03-10T00:00:00Z | 2018-03-15T00:00:00Z |       5 |          5 |

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

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

SET @cap_days = 20 ;

SELECT PD.id, Pd.name, 
       SUM(CASE WHEN o.created_at > PD.date_to_start_counting_sales THEN 1 ELSE 0 END) AS recent_sales_count  ,
       PD.date_to_start_counting_sales
FROM
(
    SELECT p.*,
           (CASE WHEN LowerCap.max_cumul_days IS NULL 
                 THEN ADDDATE(ifnull(HigherCap.min_inactive_date,sysdate()),(-@cap_days))
                 ELSE 
                 CASE WHEN LowerCap.max_cumul_days < @cap_days AND  HigherCap.min_inactive_date IS NULL
                      THEN ADDDATE(ifnull(LowerCap.max_inactive_date,sysdate()),(-LowerCap.max_cumul_days))
                      ELSE ADDDATE(ifnull(HigherCap.min_inactive_date,sysdate()),(LowerCap.max_cumul_days-@cap_days))
                 END
            END) as date_to_start_counting_sales
    FROM products P
    LEFT JOIN
    (
        SELECT product_id, MAX(cumul_days) AS max_cumul_days, MAX(inactive_date) AS max_inactive_date
        FROM VIEW_product_dates_days_cumul
        WHERE cumul_days <= @cap_days
        GROUP BY product_id
    ) LowerCap ON P.id=LowerCap.product_id
    LEFT JOIN 
    (
        SELECT product_id, MIN(cumul_days) AS min_cumul_days, MIN(inactive_date) AS min_inactive_date
        FROM VIEW_product_dates_days_cumul
        WHERE cumul_days > @cap_days
        GROUP BY product_id
    ) HigherCap ON P.id=HigherCap.product_id
) PD
LEFT JOIN items i ON PD.id =  i.product_id
LEFT JOIN orders o ON o.id = i.order_id 
GROUP BY PD.id, Pd.name, PD.date_to_start_counting_sales

Возврат

| id |   name | recent_sales_count | date_to_start_counting_sales |
|----|--------|--------------------|------------------------------|
|  1 |  Apple |                  3 |         2018-01-30T00:00:00Z |
|  2 | Banana |                  0 |         2018-04-09T20:43:23Z |
|  3 |  Grape |                  1 |         2018-03-10T00:00:00Z |

FIDDLE: http://sqlfiddle.com/#!9/804f52/24