时间戳

首页 > 解决方案 > 根据 ID 创建新的 Event_ID，并在日期列上有滑动窗口

sql - 根据 ID 创建新的 Event_ID，并在日期列上有滑动窗口

问题描述

想象一下我有一张像

ID 日期
1 2021-01-01
1 2021-01-05
1 2021-01-17
1 2021-02-01
1 2021-02-18
1 2021-02-28
1 2021-03-30
2 2021-01-01
2 2021-01-14
2 2021-02-15

我想选择此表上的所有数据，但要创建一个具有新 Event_ID 的新列。事件定义为在 15 天的时间范围内具有相同 ID 的所有行。问题是我希望时间框架移动 - 就像前 3 行一样：第 2 行在第 1 行的 15 天内（因此它们属于同一事件）。第 3 行在第 2 行的 15 天内（但距离第 1 行更远），但我希望将其添加到与以前相同的事件中。（注意：表格没有像示例中那样排序，只是为了方便）。

输出应该是

ID 日期 事件 ID
1 2021-01-01 1
1 2021-01-05 1
1 2021-01-17 1
1 2021-02-01 1
1 2021-02-18 2
1 2021-02-28 2
1 2021-03-30 3
2 2021-01-01 4
2 2021-01-14 4
2 2021-02-15 5

我也可以在 R 中使用 data.table 来完成（取决于效率/性能）

标签： sqlroraclesliding-window

解决方案

一个r解决方案可能是使用来自的dplyr方法和rleid功能data.table

library(dplyr)
library(data.table)
df %>% group_by(ID) %>%
  mutate(Date = as.Date(Date)) %>% #mutating Date column as Date
  arrange(ID, Date) %>% #arranging the rows in order
  mutate(Event = if_else(is.na(Date - lag(Date)), Date - Date, Date - lag(Date)),
         Event = paste(ID, cumsum(if_else(Event > 15, 1, 0)), sep = "_")) %>%
  ungroup() %>% #since the event numbers are not to be created group-wise
  mutate(Event = rleid(Event))
# A tibble: 9 x 3
     ID Date       Event
  <int> <date>     <int>
1     1 2021-01-01     1
2     1 2021-01-05     1
3     1 2021-01-17     1
4     1 2021-02-15     2
5     1 2021-02-28     2
6     1 2021-03-30     3
7     2 2021-01-01     4
8     2 2021-01-14     4
9     2 2021-02-15     5

推荐阅读