SQL每天以30天的频率选择流失的客户

Question

目标是选择在 2016 日历年的每一天之前的滚动 30 天期间内未进行购买的不同 customer_id 的计数。我已在数据库中创建了一个要加入的日历表。

这是一个供引用的示例表，假设您的客户订单已标准化，如下所示:

+-------------+------------+----------+
| customer_id | date       | order_id |
+-------------+------------+----------+
| 123         | 01/25/2016 | 1000     |
+-------------+------------+----------+
| 123         | 04/27/2016 | 1025     |
+-------------+------------+----------+
| 444         | 02/02/2016 | 1010     |
+-------------+------------+----------+
| 521         | 01/23/2016 | 998      |
+-------------+------------+----------+
| 521         | 01/24/2016 | 999      |
+-------------+------------+----------+  

目标输出实际上是一个日历，其中 2016 年的每一天都有 1 行，并计算当天有多少客户“流失”，这意味着他们的最后一次购买是在该天之前 30 天或更长时间那一年。最终输出将如下所示:

+------------+--------------+
| date       | lapsed_count |
+------------+--------------+
| 01/01/2016 | 0            |
+------------+--------------+
| 01/02/2016 | 0            |
+------------+--------------+
| ...        | ...          |
+------------+--------------+
| 03/01/2016 | 12           |
+------------+--------------+
| 03/02/2016 | 9            |
+------------+--------------+
| 03/03/2016 | 7            |
+------------+--------------+  

2015 年不存在此数据，因此不可能统计 2016 年 1 月 1 日的流失客户数量，因为那是可能进行购买的第一天。

因此，对于 customer_id #123，他们于 01/25/2016 和 04/27/2016 购买。他们应该有 2 次失误计数，因为他们的购买间隔超过 30 天。一次失误发生在 2016 年 2 月 24 日，另一次失误发生在 2016 年 5 月 27 日。
Customer_id#444 仅购买了一次，因此他们应该在 2016 年 2 月 2 日之后的 30 天内(即 2016 年 2 月 3 日)有一次失效计数。
Customer_id#521 很棘手，因为他们购买的频率为 1 天，我们不会计算 03/02/2016 的第一次购买，因此从他们最后一次购买 03/03/2016 开始只有一次失误。失效计数将于 2016 年 4 月 2 日(+30 天)进行。

Answer 1

如果您有日期表，这是一种昂贵的方法:

select date,
       sum(case when prev_date < date - 30 then 1 else 0 end) as lapsed
from (select c.date, o.customer_id, max(o.date) as prev_date
      from calendar c cross join
           (select distinct customer_id from orders) c left join
           orders o
           on o.date <= c.date and o.customer_id = c.customer_id
      group by c.date, o.customer_id
     ) oc
group by date;

对于每个日期/客户对，它确定客户在该日期之前进行的最新购买。然后，它使用此信息来计算失效次数。

说实话，这可能在少数日期上效果很好，但不适用于全年。