sql - 基于周的事件枢轴

Question

我的源表是这样的:

event   start   end
event1  21/01/2019  15/02/2019
event2  01/01/2019  08/01/2019
event3  15/01/2019  13/02/2019
event4  07/01/2019  18/03/2019

我想要实现的是一年中每个特定周发生的事件的每周摘要。像这样:

week    events
1   1
2   2
3   2
4   3
...

由于我完全一无所知，因此非常感谢任何关于如何做到这一点的提示。我正在运行 PostgreSQL 9.6。

Answer 1

您必须按如下方式使用窗口函数:

select distinct w1.weeknum, 
    count(e1.id) over(partition by w1.weeknum) as eventqty
from (select generate_series(1, 52, 1) AS weeknum) w1
left join eventlog e1
on extract(week from startdate)<=w1.weeknum and extract(week from enddate)>=w1.weeknum

Here是工作示例。