mysql - 查询动态日期间隔

Question

给定包含产品可用性信息的状态表，我如何选择对应于最近 20 天内产品处于事件状态的第 1 天的日期？

是的，我知道这个问题很难理解。我认为另一种表达方式是:我想知道每种产品在过去 20 天内售出多少次，这意味着该产品可能已经活跃多年，但我只想知道销售额从最近 20 天算起，它的状态为“活跃”。

这在服务器端很容易实现(即从数据库中获取任何产品集合，迭代它们，在状态表上执行 n+1 查询等)，但我有成千上万的项目，所以这是势在必行的出于性能原因，在 SQL 中执行此操作。

表:产品

+-------+-----------+
|   id  |   name    |
+-------+-----------+
|   1   |   Apple   |
|   2   |   Banana  |
|   3   |   Grape   |
+-------+-----------+

表:状态

+-------+-------------+---------------+---------------+
|   id  |     name    |   product_id  |   created_at  |
+-------+-------------+---------------+---------------+
|   1   |   active    |            1  |   2018-01-01  |
|   2   |   inactive  |            1  |   2018-02-01  |
|   3   |   active    |            1  |   2018-03-01  |
|   4   |   inactive  |            1  |   2018-03-15  |
|   6   |   active    |            1  |   2018-04-25  |
|   7   |   active    |            2  |   2018-03-01  |
|   8   |   active    |            3  |   2018-03-10  |
|   9   |   inactive  |            3  |   2018-03-15  |
+-------+-------------+---------------+---------------+

表:项目(订购的产品)

+-------+---------------+-------------+
|   id  |   product_id  |   order_id  |
+-------+---------------+-------------+
|   1   |            1  |          1  |
|   2   |            1  |          2  |
|   3   |            1  |          3  |
|   4   |            1  |          4  |
|   5   |            1  |          5  |
|   6   |            2  |          3  |
|   7   |            2  |          4  |
|   8   |            2  |          5  |
|   9   |            3  |          5  |
+-------+---------------+-------------+

表:订单

+-------+---------------+
|   id  |   created_at  |
+-------+---------------+
|   1   |   2018-01-02  |
|   2   |   2018-01-15  |
|   3   |   2018-03-02  |
|   4   |   2018-03-10  |
|   5   |   2018-03-13  |
+-------+---------------+

我希望我的最终结果看起来像这样:

+-------+-----------+----------------------+--------------------------------+
|   id  |   name    |  recent_sales_count  |  date_to_start_counting_sales  |
+-------+-----------+----------------------+--------------------------------+
|   1   |   Apple   |                   3  |                    2018-01-30  |
|   2   |   Banana  |                   0  |                    2018-04-09  |
|   3   |   Grape   |                   1  |                    2018-03-10  |
+-------+-----------+----------------------+--------------------------------+

所以这就是我所说的最近 20 个活跃日的意思，例如苹果:

• 上次激活时间为“2018-04-25”。那是 4 天前。

• 在此之前，它自“2018-03-15”以来一直处于非事件状态，因此直到“2018-04-25”的所有这些天都不算数。

• 在此之前，它自“2018-03-01”起就处于事件状态。距离“2018-03-15”还有 14 天。

• 在此之前，自“2018-02-01”以来处于非事件状态。

• 最后，它自“2018-01-01”以来一直处于事件状态，因此它应该只计算从“2018-”开始的缺失的2 天 (4 + 14 + 2 = 20) 02-01'，导致 date_to_start_counting_sales = '2018-01-30'。

• 有了“2018-01-30”这个日期，我就可以统计过去 20 天的 Apple 订单:3。

希望这是有道理的。

这是一个fiddle使用上面提供的数据。

Answer 1

我有一个标准的 SQL 解决方案，它不像您在 MySQL 5 上那样使用任何窗口函数

我的解决方案需要 3 个堆叠 View 。

使用 CTE 会更好，但您的版本不支持它。堆叠 View 也是如此...我不喜欢堆叠 View 并总是尽量避免它，但有时您别无选择，因为 MySQL 不接受 View 的 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
);  

最终的 View 产生了这个:

| 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 |

因此它聚合了所有产品的所有活跃期，它计算了每个时期的天数，以及自当前日期以来所有过去活跃期的累计天数。

然后我们可以查询这个最终 View 以获得每个产品的所需日期。我为您的 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