下面的SQL允许我查看指定日期的活动订阅，指定日期是我手动输入@report_date的。 取而代之的是，我想展示我们过去两年的活跃订户的图表，因此我想能够按过去两年的每一天分组。 但是，我不确定如何动态更改@report_date，以便每天都运行查询。
set @report_date = '2019-09-06';
(SELECT
COUNT(DISTINCT s.customer_id) AS 'Active Subscribers'
FROM
subscriptions AS s
WHERE
s.created_date < @report_date
AND s.customer_id NOT IN (SELECT customer_id
FROM cancellations
WHERE cancellation_date < @report_date));
订阅表:
customer_id | created_date
1 2019-09-04
2 2019-09-04
3 2019-09-05
取消表:
customer_id | cancellation_date
1 2019-09-05
2 2019-09-08
4 2019-09-09
如有任何帮助，
谢了。
最有效的方法是使用累加:
with t as (
select created_date as dt, 1 as inc
from subscriptions s
union all
select cancellation_date, -1 as inc
from cancellations c
)
select t.dt, sum(inc) as change_on_date,
sum(sum(inc)) over (order by t.dt)
from t
group by dt;
