在Redshift上使用start_date和end_date累计打开订阅

Question

我正在尝试编写一个查询，该数据将允许我在Redshift中每天统计活动订阅的数量。

我有如下表：

sub_id | start_date | end_date 
    --------------------------------------- 
    20001 | 2017-09-01 | NULL 
    20002 | 2017-08-01 | 2017-08-29 
    20003 | 2016-01-01 | 2017-04-25 
    20004 | 2016-07-01 | 2017-09-03 

我希望能状态时，两个日期许多订阅如何积极之间的每个日期，使得：

 date | active_subs 
    ------------------------ 
    2016-06-30 | 1 
    2016-07-01 | 2 
    ...  | 
    2017-04-24 | 2 
    2017-04-25 | 1 
    ...  | 
    2017-07-31 | 1 
    2017-08-01 | 2 
    ...  | 
    2017-08-28 | 2 
    2017-08-29 | 1 
    2017-08-30 | 1 
    2017-08-31 | 1 
    2017-09-01 | 2 
    2017-09-02 | 2 
    2017-09-03 | 1 

我有一个查询可以从中查询每天一行，日期的表名和相关列为date.ref_date（使用YYYY-MM-DD格式）

我写这个查询使用窗口函数还是有更好的方法？

感谢

Answer 1

如果我理解正确的话，你并不需要，也不窗口功能，连接（除了日期表）或累计计数。你可以这样做：

SELECT t.date, 
     COUNT(s.sub_id) as active_subs 
FROM dateTable t 
LEFT JOIN YourTable s 
ON(t.dateCol between s.start_date 
       AND COALESCE(s.end_date,<Put A late date here>)) 
GROUP BY t.date 

Answer 2

我会做这样的：

with cte as (
     select start_date as dte, 1 as inc 
     from t 
     union all 
     select coalesce(end_date, current_date), -1 as inc 
     from t 
    ) 
select dte, 
     sum(sum(inc)) over (order by dte) 
from cte 
group by dte 
order by dte; 

有可能是关闭的情况的一个错误，这取决于你是否指望给出的日期或第二天停止。