Обновление методов data.table для объединения наборов данных. Ниже приведены примеры для каждого типа соединения. Существует два метода: один из [.data.table
при передаче второй data.table в качестве первого аргумента для подмножества, другой способ - использовать функцию merge
, которая отправляется в быстрый метод data.table.
Обновление от 2016-04-01 - и это не шутка в апреле! В версии 1.9.7 версии data.table теперь могут использовать существующий индекс, который значительно сокращает время соединения. Ниже кода и эталона НЕ используются индексы data.table при объединении. Если вы ищете соединение в режиме реального времени, вы должны использовать индексы data.table.
df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2L, 4L, 7L), State = c(rep("Alabama", 2), rep("Ohio", 1))) # one value changed to show full outer join
library(data.table)
dt1 = as.data.table(df1)
dt2 = as.data.table(df2)
setkey(dt1, CustomerId)
setkey(dt2, CustomerId)
# right outer join keyed data.tables
dt1[dt2]
setkey(dt1, NULL)
setkey(dt2, NULL)
# right outer join unkeyed data.tables - use `on` argument
dt1[dt2, on = "CustomerId"]
# left outer join - swap dt1 with dt2
dt2[dt1, on = "CustomerId"]
# inner join - use `nomatch` argument
dt1[dt2, nomatch=0L, on = "CustomerId"]
# anti join - use `!` operator
dt1[!dt2, on = "CustomerId"]
# inner join
merge(dt1, dt2, by = "CustomerId")
# full outer join
merge(dt1, dt2, by = "CustomerId", all = TRUE)
# see ?merge.data.table arguments for other cases
Ниже контрольных баз тестов R, sqldf, dplyr и data.table. Бенчмарк тестирует неблокированные / неиндексированные наборы данных. Вы можете получить еще лучшую производительность, если используете свои ключи data.tables или индексы с sqldf. Base R и dplyr не имеют индексов или ключей, поэтому я не включил этот сценарий в эталон. Бенчмаркинг выполняется по наборам массивов 5M-1, в столбце соединения есть 5M-2 общих значения, поэтому каждый сценарий (слева, справа, полный, внутренний) может быть протестирован, а объединение все еще не является тривиальным для выполнения.
library(microbenchmark)
library(sqldf)
library(dplyr)
library(data.table)
n = 5e6
set.seed(123)
df1 = data.frame(x=sample(n,n-1L), y1=rnorm(n-1L))
df2 = data.frame(x=sample(n,n-1L), y2=rnorm(n-1L))
dt1 = as.data.table(df1)
dt2 = as.data.table(df2)
# inner join
microbenchmark(times = 10L,
base = merge(df1, df2, by = "x"),
sqldf = sqldf("SELECT * FROM df1 INNER JOIN df2 ON df1.x = df2.x"),
dplyr = inner_join(df1, df2, by = "x"),
data.table = dt1[dt2, nomatch = 0L, on = "x"])
#Unit: milliseconds
# expr min lq mean median uq max neval
# base 15546.0097 16083.4915 16687.117 16539.0148 17388.290 18513.216 10
# sqldf 44392.6685 44709.7128 45096.401 45067.7461 45504.376 45563.472 10
# dplyr 4124.0068 4248.7758 4281.122 4272.3619 4342.829 4411.388 10
# data.table 937.2461 946.0227 1053.411 973.0805 1214.300 1281.958 10
# left outer join
microbenchmark(times = 10L,
base = merge(df1, df2, by = "x", all.x = TRUE),
sqldf = sqldf("SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.x = df2.x"),
dplyr = left_join(df1, df2, by = c("x"="x")),
data.table = dt2[dt1, on = "x"])
#Unit: milliseconds
# expr min lq mean median uq max neval
# base 16140.791 17107.7366 17441.9538 17414.6263 17821.9035 19453.034 10
# sqldf 43656.633 44141.9186 44777.1872 44498.7191 45288.7406 47108.900 10
# dplyr 4062.153 4352.8021 4780.3221 4409.1186 4450.9301 8385.050 10
# data.table 823.218 823.5557 901.0383 837.9206 883.3292 1277.239 10
# right outer join
microbenchmark(times = 10L,
base = merge(df1, df2, by = "x", all.y = TRUE),
sqldf = sqldf("SELECT * FROM df2 LEFT OUTER JOIN df1 ON df2.x = df1.x"),
dplyr = right_join(df1, df2, by = "x"),
data.table = dt1[dt2, on = "x"])
#Unit: milliseconds
# expr min lq mean median uq max neval
# base 15821.3351 15954.9927 16347.3093 16044.3500 16621.887 17604.794 10
# sqldf 43635.5308 43761.3532 43984.3682 43969.0081 44044.461 44499.891 10
# dplyr 3936.0329 4028.1239 4102.4167 4045.0854 4219.958 4307.350 10
# data.table 820.8535 835.9101 918.5243 887.0207 1005.721 1068.919 10
# full outer join
microbenchmark(times = 10L,
base = merge(df1, df2, by = "x", all = TRUE),
#sqldf = sqldf("SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.x = df2.x"), # not supported
dplyr = full_join(df1, df2, by = "x"),
data.table = merge(dt1, dt2, by = "x", all = TRUE))
#Unit: seconds
# expr min lq mean median uq max neval
# base 16.176423 16.908908 17.485457 17.364857 18.271790 18.626762 10
# dplyr 7.610498 7.666426 7.745850 7.710638 7.832125 7.951426 10
# data.table 2.052590 2.130317 2.352626 2.208913 2.470721 2.951948 10
Вы можете использовать агрегацию. Я бы рекомендовал передавать ингредиенты в виде списка VALUES()
, а не строк. Однако, с вашей конструкцией:
SELECT r.Name, r.Preperation_Time, r.Author
FROM recipes r LEFT JOIN
RecipeIngredients ri
ON ri.Recipe_ID = r.Recipe_ID LEFT JOIN
Ingredients i
ON i.Ingredient_ID = ri.Ingredient_ID AND
i.Name IN (" + ingredientString + ")"
GROUP BY r.Name, r.Preperation_Time, r.Author
HAVING COUNT(*) = COUNT(i.Ingredient_Id); -- all match