通过在 SQL 中按日期范围聚合来计算流失率

Question

我正在尝试从具有customer_id，组，日期的数据中计算流失率。聚合将按 ID、组和日期进行。流失公式(customers in previous cohort - customers in last cohort)/customers in previous cohort

上一个群组中的客户是指 28 天内的同类群组 最近群组中的客户是指过去 28 天内的同类群组

我不确定如何按日期范围汇总它们以计算流失率。

下面是我按日期范围从 SQL 组复制的示例数据：

Date        Group    Customer_id
2014-03-01  A         1
2014-04-02  A         2
2014-04-03  A         3
2014-05-04  A         3
2014-05-05  A         6
2015-08-06  A         1
2015-08-07  A         2
2014-08-29  XXXX      2
2014-08-09  XXXX      3
2014-08-10  BB        4
2014-08-11  CCC       3
2015-08-12  CCC       2
2015-03-13  CCC       3
2014-04-14  CCC       5
2014-04-19  CCC       4
2014-08-16  CCC       5
2014-08-17  CCC       3
2014-08-18  XXXX      2
2015-01-10  XXXX      3
2015-01-20  XXXX      4
2014-08-21  XXXX      5
2014-08-22  XXXX      2
2014-01-23  XXXX      3
2014-08-24  XXXX      2
2014-02-25  XXXX      3
2014-08-26  XXXX      2
2014-06-27  XXXX      4
2014-08-28  XXXX      1
2014-08-29  XXXX      1
2015-08-30  XXXX      2
2015-09-31  XXXX      3

目标是通过上面给出的公式计算 2014 年至 2015 年间每 28 天一次的流失率。因此，它将通过滚动 28 天并通过公式计算流失来聚合数据。

以下是我尝试按日期范围汇总数据的内容：

SELECT COUNT(distinct customer_id) AS count_ids, Group,
DATE_SUB(CAST(Date AS DATE), INTERVAL 56 DAY) AS Date_min,
DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY) AS Date_max
FROM churn_agg
GROUP BY count_ids, Group, Date_min, Date_max

希望有人能帮助我进行聚合和流失计算。我想简单地扣除聚合count_ids，以从 28 天后的下一个聚合count_ids中扣除它。因此，这将是对相同列值 (count_ids( 的连续扣除。我不确定我是否必须使用滚动窗口或简单聚合来查找流失。

Answer 1

经@jarlh更正，不是 2015-09-31，而是 2015-09-30 您可以使用它来创建 28 天日历：

create table daysby28  (i int, _Date date);
insert into daysby28  (i, _Date)
SELECT i, cast('01-01-2014'as date) + i*INTERVAL '28 day'
from generate_series(0,50) i
order by 1;

使用他随小提琴一起发送@jarlh churn_agg表创建后，通过此查询，您可以得到所需的内容：

with cte as
(
select count(Customer) as TotalCustomer, Cohort, CohortDateStart From
(  
select distinct a.Customer_id as Customer, b.i as Cohort, b._Date as CohortDateStart
from churn_agg a left join daysby28 b on a._Date >=  b._Date and a._Date <  b._Date + INTERVAL '28 day' 
) a
group by Cohort, CohortDateStart
)
select a.CohortDateStart, 
1.0*(b.TotalCustomer - a.TotalCustomer)/(1.0*b.TotalCustomer) as Churn from cte a
left join cte b on a.cohort > b.cohort
and not exists(select 1 from cte c where c.cohort > b.cohort and c.cohort < a.cohort)
order by 1  

大家的小提琴就在这里