基于data.table的时间序列高效连接优化及合并查询问询

我有两个不同时间维度（1小时和5分钟）的货币价格数据集，均包含High、Low、Open、Close字段。每条1小时维度的数据对应一个Target_price（目标价格）和交易方向Direction（取值为1或-1）：

• 当Direction=1时，需在5分钟数据中找到第一个满足low ≤ Target_price的记录；
• 当Direction=-1时，需在5分钟数据中找到第一个满足high ≥ Target_price的记录。

我附上了演示代码，但处理10-20年跨度的数据时，连接操作速度极慢。想请教两个问题：

1. 是否存在更高效的实现方式？
2. 当前代码将多空交易分为两次连接，能否合并为单次连接（仅作学习用途）？

# Load required packages
library(data.table)
library(dplyr)

# Define timeframes for the data
Start <- as.POSIXct("2016-01-01 00:00:00")
End <- as.POSIXct("2022-01-01 23:55:00")

Hours <- floor(as.numeric(difftime(End,Start,units = "hours"))) + 1
Minutes <- floor(as.numeric(difftime(End,Start,units = "mins")) / 5) + 1

# Create the Hourly data
set.seed(123)
hourly_prices <- data.table(
  datetime = seq(Start, End, by = "hour"),
  open = rnorm(Hours, mean = 100, sd = 1),
  high = rnorm(Hours, mean = 101, sd = 1),
  low = rnorm(Hours, mean = 99, sd = 1),
  close = rnorm(Hours, mean = 100, sd = 1),
  Direction = sample(c(1,-1),Hours,replace = T)) %>%
  .[,Target_price := ifelse(Direction == -1,rnorm(.N, mean = 104, sd = 1),rnorm(.N,mean = 97,sd = 1))]

# Create the 5-minute data
set.seed(456)
minute_prices <- data.table(
  datetime = seq(Start, End, by = "5 min"),
  open = rnorm(Minutes, mean = 100, sd = 1),
  high = rnorm(Minutes, mean = 101, sd = 1),
  low = rnorm(Minutes, mean = 99, sd = 1),
  close = rnorm(Minutes, mean = 100, sd = 1),
  Position = seq_len(Minutes))

# Join the two data.tables to find the first point at which price passes the target levels
hourly_prices[(Direction == 1),Location := minute_prices[.SD, on = .(datetime > datetime, low <= Target_price),mult = "first",x.Position]]
hourly_prices[(Direction == -1),Location := minute_prices[.SD, on = .(datetime > datetime, high >= Target_price),mult = "first",x.Position]]

一、更高效的实现方式 #

原代码的核心问题是每次连接都要扫描全量5分钟数据，针对大时间跨度的数据集，推荐以下优化手段：

1. 索引+时间范围预筛选 #

给5分钟数据的datetime字段建立索引，快速定位到每条小时数据之后的5分钟子集，避免全表扫描：

# 给minute_prices的datetime建立主键索引
setkey(minute_prices, datetime)

然后按小时分组处理，只在当前小时之后的子集中查找目标记录：

hourly_prices[, Location := {
  # 快速筛选当前小时之后的5分钟数据
  post_hour_data = minute_prices[datetime > .BY$datetime]
  # 根据方向判断取第一个符合条件的Position
  if (.BY$Direction == 1) {
    post_hour_data[low <= .BY$Target_price, Position[1]]
  } else {
    post_hour_data[high >= .BY$Target_price, Position[1]]
  }
}, by = .(datetime, Direction, Target_price)]

2. 内存与数据精简 #

10-20年的5分钟数据量可达百万级，建议：

• 只保留必要字段：比如仅保留datetime、high、low、Position，减少内存占用；
• 分批次处理小时数据：将小时数据按年份/季度拆分，分批处理后再合并结果。

3. 滚动连接优化 #

利用data.table的滚动连接特性，预先计算每个时间点的阈值，进一步提升效率：

# 给hourly_prices标记对应的阈值类型
hourly_prices[, threshold := Target_price]
hourly_prices[, price_col := ifelse(Direction == 1, "low", "high")]

# 滚动查找第一个满足条件的记录
hourly_prices[, Location := {
  col = .BY$price_col
  val = .BY$threshold
  minute_prices[datetime > .BY$datetime][get(col) <= val | (col == "high" & get(col) >= val), Position[1]]
}, by = .(datetime, price_col, threshold, Direction)]

二、合并为单次连接的实现 #

要合并单次连接，可以在连接的条件中动态判断方向对应的逻辑，利用data.table的i.前缀引用小时数据的字段：

setkey(minute_prices, datetime)

# 单次连接实现：动态判断多空条件
hourly_prices[, Location := minute_prices[.SD, on = .(datetime > datetime),
  # 根据当前行的Direction选择对应的比较逻辑
  (Direction == 1 & low <= Target_price) | (Direction == -1 & high >= Target_price),
  mult = "first",
  x.Position
]]

这个方法通过在连接的过滤条件中整合多空逻辑，实现了单次连接。不过需要注意，大数据量下的效率可能不如分批次预筛选，但完全满足学习需求。

内容的提问来源于stack exchange，提问作者Phil