sql - 如何按周选择总活跃列表数？

Question

我想每周从活跃的公寓列表中获取计数。该表看起来像这样(除了更长):

id      created_at          delisted_at
2318867 2014-11-12 18:57:44 Null
2329665 2014-11-14 4:36:32  Null
1431098 2014-07-25 5:45:03  Null
1930123 2014-09-28 10:10:46 2014-09-28 10:10:45
2490774 2014-12-05 0:08:47  Null

要获得一周的活跃列表，您必须检查 created_at <= end_of_week和 delisted_at > end_of_week .

结果表需要更长的版本:

Week        Number of Active Listings
5/1/2016    3024
5/8/2016    11234
5/15/2016   11234

我还想按月而不是按周生成另一个结果表。

如何编写查询来实现此行为？

Answer 1

这里是月份的例子。首先使用 generate_series() 生成月份列表。剩下的只是连接和聚合:

select g.mon_start, g.mon_end, count(a.id) as numActives
from (select g.mon_start, g.mon_start + interval '1 month' as mon_end
      from generate_series('2016-01-01'::timestamp, '2016-06-01'::timestamp, interval '1 month'
                          ) g(mon_start)
     ) g left join
     actives a
     on a.created_at < g.mon_end and
        (a.delisted_at >= mon_end or a.delisted_at is null)
group by g.mon_start, g.mon_end
order by g.mon_start, g.mon_end;

类似的想法也适用数周。