下面的SQL允许我查看指定日期的活动订阅，指定日期是我手动输入@report_date的。 取而代之的是，我想展示我们过去两年的活跃订户的图表，因此我想能够按过去两年的每一天分组。 但是，我不确定如何使@report_date在过去的一年中每天循环。
遗憾的是，取消表背后的逻辑值得商榷。 每次客户重新激活他们的订阅时，取消行都会被更新，将“重新激活”设置为1。 如果客户第二次取消，则在取消表中生成一个新行，将“重新激活”设置为默认值0。 因此，要查找在@report_date被取消的某人，“reactivated”必须设置为0，或者如果他们已经重新激活，那么他们的reactivated_date必须在@report_date之后。
set @report_date = '2020-06-11';
SELECT
@report_date AS Date,
COUNT(DISTINCT s.customer_id) AS 'Active Subscribers'
FROM
subscriptions AS s
WHERE
(authorized = 1 OR authorized = 0)
AND s.created_date < @report_date
AND s.customer_id NOT IN (SELECT customer_id
FROM cancellations
WHERE (reactivated = 0 OR reactivated_date > @report_date)
AND cancellation_date < @report_date);
订阅表:
customer_id | created_date | authorized
1 2020-06-06 1
2 2020-06-07 1
3 2020-06-08 -1
4 2020-06-08 1
取消表:
customer_id | cancellation_date | reactivated | reactivation_date
1 2020-06-09 1 2020-06-10
2 2020-06-12 0 NULL
4 2020-06-10 1 2020-06-12
电流输出:
Date | Active Subscribers
2020-06-11 1
所需结果:
Date | Active Subscribers
2020-06-12 2
2020-06-11 1
2020-06-10 1
etc.
假设您的数据是一致的，一个选项使用Union All，窗口函数和聚合:
select date, sum(is_active = 1) active_subscribers
from (
select
customer_id,
date,
sum(sum(active_cnt)) over(partition by customer_id order by date) is_active
from (
select customer_id, created_date date, 1 active_cnt from subscriptions where autorized in (0, 1)
union all
select customer_id, cancellation_date, -1 from cancellations where reactivated = 1
union all
select customer_id, reactivation_date, 1 from cancellations where reactivated = 1
) t
group by customer_id, date
) t
group by date
order by date
