在R数据框中转换间隔外部联接SQL [英] Convert Interval Outer Join SQL in R data frame
本文介绍了在R数据框中转换间隔外部联接SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
之前,我在Python中遇到相同的问题:转换间隔外部联接到Python Pandas Dataframe中的SQL
,我想在R中完成。
I have a same problem in Python before: Convert Interval Outer Join SQL in Python Pandas Dataframe and I would like to do it in R.
我正在转换Oracle SQL外部间隔联接到R。下面是Oracle SQL:
I'm converting an Oracle SQL outer interval join into R. Below is the Oracle SQL:
WITH df_interval AS
(SELECT '1' id,
'AAA' interval,
1000 begin,
2000 end
FROM DUAL
UNION ALL
SELECT '1' id,
'BBB' intrvl,
2100 begin,
3000 end
FROM DUAL
UNION ALL
SELECT '2' id,
'CCC' intrvl,
3100 begin,
4000 end
FROM DUAL
UNION ALL
SELECT '2' id,
'DDD' intrvl,
4100 begin,
5000 end
FROM DUAL),
df_point AS
(SELECT '1' id, 'X1' point, 1100 mid FROM DUAL
UNION ALL
SELECT '1' id, 'X2' point, 2050 mid FROM DUAL
UNION ALL
SELECT '1' id, 'X3' point, 3200 mid FROM DUAL
UNION ALL
SELECT '2' id, 'X4' point, 4200 mid FROM DUAL
UNION ALL
SELECT '2' id, 'X5' point, 5500 mid FROM DUAL)
SELECT pt.id,
point,
mid,
interval
FROM df_interval it RIGHT OUTER JOIN df_point pt ON pt.id = it.id AND pt.mid BETWEEN it.begin AND it.end
我期望这样的结果:
ID point mid interval
0 1 X1 1100 AAA
1 1 X2 2050 NaN
2 1 X3 3200 NaN
3 2 X4 4200 DDD
4 2 X5 5500 NaN
是否有人可以帮助我?
推荐答案
以下是使用 data.table
软件包的选项:
Here is an option using data.table
package:
library(data.table)
setDT(df_interval)
setDT(df_point)
df_interval[df_point, on=.(id, begin<=mid, end>=mid),
.(ID=id, point=i.point, mid=i.mid, interval=x.interval)]
输出:
ID point mid interval
1: 1 X1 1100 AAA
2: 1 X2 2050 <NA>
3: 1 X3 3200 <NA>
4: 2 X4 4200 DDD
5: 2 X5 5500 <NA>
数据:
df_interval <- data.frame(id=c(1,1,2,2),
interval=c('AAA','BBB','CCC','DDD'),
begin=c(1000,2100,3100,4100),
end=c(2000,3000,4000,5000))
df_point <- data.frame(id=c(1,1,1,2,2),
point=c('X1','X2','X3','X4','X5'),
mid=c(1100,2050,3200,4200,5500))
这篇关于在R数据框中转换间隔外部联接SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文