假设我有一张表,如下所示:
--------------------------------------------------
| Type | Incident ID | Date of incident|
--------------------------------------------------
| A | 1 | 2022-02-12 |
| A | 2 | 2022-02-14 |
| A | 3 | 2022-02-14 |
| A | 4 | 2022-02-14 |
| A | 5 | 2022-02-16 |
| A | 6 | 2022-02-17 |
| A | 7 | 2022-02-19 |
| A | 8 | 2022-02-19 |
| A | 7 | 2022-02-19 |
| A | 8 | 2022-02-19 |
... ... ...
| B | 1 | 2022-02-12 |
| B | 2 | 2022-02-12 |
| B | 3 | 2022-02-13 |
... ... ...
--------------------------------------------------
这是不同类型事件的列表.每个事件都有类型、ID和发生日期.这只是帮助理解我的目标的一个例子.
我想要的是-对于给定的范围,例如5天-这些事件的滚动总和将成为的最大值:
所以我会从前5天内的所有元素开始,并累积出现的次数:6.
2022-02-12 - 2022-02-17: 6
通过开始将窗口滚动一天,第一天的所有元素将从总和中删除,在本例中为-1,并且不会添加第二天的元素.下一个值将是5.
2022-02-13 - 2022-02-18: 5
6>;5.因此,6天仍然是5天窗口内事件的最大发生次数.
在完整的时间范围内继续.
这并不是很难实现,但对于数百万个元素,我将如何以非常有效的方式做到这一点?简而言之:我想创建一个固定日期范围(例如5天)的移动窗口,计算此窗口的所有出现次数,并给出任何窗口达到的最大值.
顺便说一句,我正在使用SQLALCHEMY,但我也会对纯SQL感兴趣.
合适的测试集如下所示:
test_data_small = {'Id': [1, 2, 3, 4, 5,
6, 7, 8, 9, 10,
0, 1, 2, 3],
'Type': ['A', 'A', 'A', 'A',
'A', 'A', 'A', 'A',
'A', 'A', 'B', 'B',
'B', 'B'],
'Date': [
'2022-02-12', '2022-02-14',
'2022-02-14', '2022-02-14',
'2022-02-16', '2022-02-17',
'2022-02-19', '2022-02-19',
'2022-02-19', '2022-02-19',
'2022-02-16', '2022-02-12',
'2022-02-12', '2022-02-13']
}
我正在通过SQLALCHEMIY连接到一个表,如下所示:
incidents = select(
incidents.c.type,
incidents.c.id,
incidents.c.date
).subquery()
result = self.connection.execute(incidents).fetchall()
在纯SQL中有可能实现吗?也许我应该用Pandas 来装一扇滚动窗?