Могу ли я использовать два вектора символов в операторе соединения sqldf?

Я провожу sqldf объединение 3 разных таблиц данных. Мой текущий рабочий код выглядит так:

AltSuitRaw <- data.table(sqldf('select RealAlt.*, SpdSpSuit * SpdSpT as SpdSpSuitT, SpdIncSuit * SpdIncT as SpdIncSuitT, SpdGrowSuit * SpdGrT as SpdGrowSuitT,
        RzbSpSuit * RzbSpT as RzbSpSuitT, RzbIncSuit * RzbIncT as RzbIncSuitT, RzbGrowSuit * RzbGrT as RzbGrowSuitT,
        FMSSpSuit * FmsSpT as FmsSpSuitT, FMSIncSuit * FmsIncT as FmsIncSuitT, FMSGrowSuit * FMSGrT as FmsGrowSuitT,
        BhsSpSuit * BhsSpT as BhsSpSuitT, BhsIncSuit * BhsIncT as BhsIncSuitT, BhsGrowSuit * BhsGrT as BhsGrowSuitT,
        BrtSpSuit * BRTsp as BrtSpSuitT, BrtIncSuit * BRTinc as BrtIncSuitT, BrtGrSuit * BRTgr as BrtGrowSuitT,
        CcfSpSuit * CCFsp as CcfSpSuitT, CcfIncSuit * CCFinc as CcfIncSuitT, CcfGrSuit * Ccfgr as CcfGrowSuitT,
        GsfSpSuit * GSFsp as GsfSpSuitT, GsfIncSuit * GSFinc as GsfIncSuitT, GsfGrSuit * GSFgr as GsfGrowSuitT,
        RbtSpSuit * RBTsp as RbtSpSuitT, RbtIncSuit * RBTinc as RbtIncSuitT, RbtGrSuit * RBTgr as RbtGrowSuitT,
        SmbSpSuit * SMBsp as SmbSpSuitT, SmbIncSuit * SMBinc as SmbIncSuitT, SmbGrSuit * SMBgr as SmbGrowSuitT,
        StbSpSuit * STBsp as StbSpSuitT, StbIncSuit * STBinc as StbIncSuitT, StbGrSuit * STBgr as StbGrowSuitT,
        HbcSpSuit * HBCsp as HbcSpSuitT, HbcIncSuit * HBCinc as HbcIncSuitT, HbcGrSuit * HBCgr as HbcGrowSuitT,
        AtActSuit * ATha as AtActSuitT, AtInfSuit * ATinf as AtInfSuitT,
        LcActSuit * LCha as LcActSuitT, LcInfSuit * LCha as LcInfSuitT,
        TnActSuit * TNha as TnActSuitT, TnInfSuit * TNinf as TnInfSuitT,
        WdActSuit * WDha as WdActSuitT, WdInfSuit * WDinf as WdInfSuitT
        from RealAlt 
        left join SpecSuitTemp using (Temp)
        left join AltSuitDates using (Month)'))

Как видите, это очень большой и, на мой взгляд, громоздкий кусок кода. Небольшое изменение в именах столбцов SpecSuitTemp или AltSuitDates нарушит приведенный выше код. Что я надеялся сделать, так это выполнить соединение, используя вектор символов имен столбцов, чтобы, если они изменятся, это не имело значения, что-то вроде следующего:

AltSuitRawt <- data.table(sqldf('select RealAlt.*, SpecSuitTemp[tcoln] * AltSuitDates[dcoln] as newcn from RealAlt left join SpecSuitTemp using (Temp) left join AltSuitDates using (Month)'))

Где

newcn <- paste0(names(SpecSuitTemp),"T"); newcn <- newcn[-1]
tcoln <- names(SpecSuitTemp); tcoln <- tcoln[-1]
dcoln <- names(AltSuitDates); dcoln <- dcoln[-1]

Однако это утверждение не работает. Я не знаю sql, но мне удалось заставить работать большую инструкцию, приведенную выше, с большой помощью коллеги.

Мой вопрос: могу ли я выполнить оператор соединения sqldf, используя векторы символов имен столбцов? Я подумал, может быть, мне следует обернуть свое заявление lapply, но не уверен, что это возможно. Любая помощь, ресурсы или предложения приветствуются.

Вывод моих данных:

RealAlt <- structure(list(Alternative = c("A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A"), TraceID = c("h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1", "h00s1"), WaterYear = c(2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L), Month = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L), Location = c("GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "GCD"), Temp = c(9.8, 8.7, 8.5, 8.5, 8.9, 9.3, 9.8, 10.8, 11.2, 11.6, 11.3, 11.3, 10.1, 8.6, 8.1, 9.1, 9.5, 10.1, 10.7, 11.6)), .Names = c("Alternative", "TraceID", "WaterYear", "Month", "Location", "Temp"), class = c("data.table", "data.frame"), row.names = c(NA, -20L))
SpecSuitTemp <- structure(list(Temp = c(8, 8.1, 8.2, 8.3, 8.4, 8.5, 8.6, 8.7, 8.8, 8.9, 9, 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 9.7, 9.8, 9.9, 10, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 11, 11.1, 11.2, 11.3, 11.4, 11.5, 11.6, 11.7, 11.8, 11.9, 12), SpdSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), SpdIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), SpdGrowSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), RzbSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), RzbIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), RzbGrowSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), FMSSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), FMSIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), FMSGrowSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), BhsSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), BhsIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), BhsGrowSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), BrtSpSuit = c(0.333333333, 0.366666667, 0.4, 0.433333333, 0.466666667, 0.5, 0.533333333, 0.566666667, 0.6, 0.633333333, 0.666666667, 0.7, 0.733333333, 0.766666667, 0.8, 0.833333333, 0.866666667, 0.9, 0.933333333, 0.966666667, 1, 0.975, 0.95, 0.925, 0.9, 0.875, 0.85, 0.825, 0.8, 0.775, 0.75, 0.725, 0.7, 0.675, 0.65, 0.625, 0.6, 0.575, 0.55, 0.525, 0.5), BrtIncSuit = c(0, 0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5, 0.55, 0.6, 0.65, 0.7, 0.75, 0.8, 0.85, 0.9, 0.95, 1, 0.99, 0.98, 0.97, 0.96, 0.95, 0.94, 0.93, 0.92, 0.91, 0.9, 0.89, 0.88, 0.87, 0.86, 0.85, 0.84, 0.83, 0.82, 0.81, 0.8), BrtGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), CcfSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), CcfIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), CcfGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), GsfSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), GsfIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), GsfGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), RbtSpSuit = c(0, 0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5, 0.55, 0.6, 0.65, 0.7, 0.75, 0.8, 0.85, 0.9, 0.95, 1, 0.966666667, 0.933333333, 0.9, 0.866666667, 0.833333333, 0.8, 0.766666667, 0.733333333, 0.7, 0.666666667, 0.633333333, 0.6, 0.566666667, 0.533333333, 0.5, 0.466666667, 0.433333333, 0.4, 0.366666667, 0.333333333), RbtIncSuit = c(0.333333333, 0.366666667, 0.4, 0.433333333, 0.466666667, 0.5, 0.533333333, 0.566666667, 0.6, 0.633333333, 0.666666667, 0.7, 0.733333333, 0.766666667, 0.8, 0.833333333, 0.866666667, 0.9, 0.933333333, 0.966666667, 1, 0.98, 0.96, 0.94, 0.92, 0.9, 0.88, 0.86, 0.84, 0.82, 0.8, 0.78, 0.76, 0.74, 0.72, 0.7, 0.68, 0.66, 0.64, 0.62, 0.6), RbtGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), SmbSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), SmbIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), SmbGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), StbSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), StbIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), StbGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), HbcSpSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), HbcIncSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), HbcGrSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), AtActSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), AtInfSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), LcActSuit = c(0,  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), LcInfSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), TnActSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), TnInfSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), WdActSuit = c(1, 0.95, 0.9, 0.85, 0.8, 0.75, 0.7, 0.65, 0.6, 0.55, 0.5, 0.45, 0.4, 0.35, 0.3, 0.25, 0.2, 0.15, 0.1, 0.05, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), WdInfSuit = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.033333333, 0.066666667, 0.1, 0.133333333, 0.166666667, 0.2, 0.233333333, 0.266666667, 0.3, 0.333333333)), .Names = c("Temp", "SpdSpSuit", "SpdIncSuit", "SpdGrowSuit", "RzbSpSuit", "RzbIncSuit", "RzbGrowSuit", "FMSSpSuit", "FMSIncSuit", "FMSGrowSuit", "BhsSpSuit", "BhsIncSuit", "BhsGrowSuit", "BrtSpSuit", "BrtIncSuit", "BrtGrSuit", "CcfSpSuit", "CcfIncSuit", "CcfGrSuit", "GsfSpSuit", "GsfIncSuit", "GsfGrSuit", "RbtSpSuit", "RbtIncSuit", "RbtGrSuit", "SmbSpSuit", "SmbIncSuit", "SmbGrSuit", "StbSpSuit", "StbIncSuit", "StbGrSuit", "HbcSpSuit", "HbcIncSuit", "HbcGrSuit", "AtActSuit", "AtInfSuit", "LcActSuit", "LcInfSuit", "TnActSuit", "TnInfSuit", "WdActSuit", "WdInfSuit"), class = c("data.table", "data.frame"), row.names = c(NA, -41L))
AltSuitDates <- data.table(structure(list(Month = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 10L, 11L,12L), SpdSpT = c(NA, NA, NA, NA, NA, 1L, 1L, NA, NA, NA), SpdIncT = c(NA,NA, NA, NA, NA, 1L, 1L, NA, NA, NA), SpdGrT = c(1L, 1L, 1L, 1L,1L, 1L, 1L, 1L, 1L, 1L), RzbSpT = c(NA, NA, NA, 1L, 1L, 1L, NA,NA, NA, NA), RzbIncT = c(NA, NA, NA, 1L, 1L, 1L, NA, NA, NA,NA), RzbGrT = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), FmsSpT = c(NA,NA, 1L, 1L, NA, NA, NA, NA, NA, NA), FmsIncT = c(NA, NA, 1L,1L, 1L, NA, NA, NA, NA, NA), FMSGrT = c(1L, 1L, 1L, 1L, 1L, 1L,1L, 1L, 1L, 1L), BhsSpT = c(NA, NA, NA, 1L, 1L, 1L, NA, NA, NA,NA), BhsIncT = c(NA, NA, NA, 1L, 1L, 1L, NA, NA, NA, NA), BhsGrT = c(1L,1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), BRTsp = c(1L, 1L, 1L, NA,NA, NA, NA, 1L, 1L, 1L), BRTinc = c(1L, 1L, 1L, 1L, NA, NA, NA,1L, 1L, 1L), BRTgr = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), CCFsp = c(NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, NA), CCFinc = c(NA,NA, NA, NA, 1L, 1L, 1L, NA, NA, NA), CCFgr = c(1L, 1L, 1L, 1L,1L, 1L, 1L, 1L, 1L, 1L), GSFsp = c(NA, NA, NA, NA, 1L, 1L, 1L,NA, NA, NA), GSFinc = c(NA, NA, NA, NA, 1L, 1L, 1L, NA, NA, NA), GSFgr = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), RBTsp = c(1L,1L, 1L, 1L, 1L, 1L, 1L, NA, NA, NA), RBTinc = c(1L, 1L, 1L, 1L,1L, 1L, 1L, NA, NA, NA), RBTgr = c(1L, 1L, 1L, 1L, 1L, 1L, 1L,1L, 1L, 1L), SMBsp = c(NA, NA, NA, 1L, 1L, 1L, 1L, NA, NA, NA), SMBinc = c(NA, NA, NA, 1L, 1L, 1L, 1L, NA, NA, NA), SMBgr = c(1L,1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), STBsp = c(NA, NA, NA, NA,NA, 1L, 1L, NA, NA, NA), STBinc = c(NA, NA, NA, NA, NA, 1L, 1L,NA, NA, NA), STBgr = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), HBCsp = c(NA, NA, NA, 1L, 1L, 1L, NA, NA, NA, NA), HBCinc = c(NA,NA, NA, 1L, 1L, 1L, NA, NA, NA, NA), HBCgr = c(1L, 1L, 1L, 1L,1L, 1L, 1L, 1L, 1L, 1L), ATha = c(1L, 1L, 1L, 1L, 1L, 1L, 1L,1L, 1L, 1L), ATinf = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), LCha = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), LCinf = c(1L,1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), TNha = c(1L, 1L, 1L, 1L,1L, 1L, 1L, 1L, 1L, 1L), TNinf = c(1L, 1L, 1L, 1L, 1L, 1L, 1L,1L, 1L, 1L), WDha = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L),WDinf = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, NA, NA, NA)), .Names = c("Month","SpdSpT", "SpdIncT", "SpdGrT", "RzbSpT", "RzbIncT", "RzbGrT","FmsSpT", "FmsIncT", "FMSGrT", "BhsSpT", "BhsIncT", "BhsGrT","BRTsp", "BRTinc", "BRTgr", "CCFsp", "CCFinc", "CCFgr", "GSFsp","GSFinc", "GSFgr", "RBTsp", "RBTinc", "RBTgr", "SMBsp", "SMBinc","SMBgr", "STBsp", "STBinc", "STBgr", "HBCsp", "HBCinc", "HBCgr","ATha", "ATinf", "LCha", "LCinf", "TNha", "TNinf", "WDha", "WDinf"), class = c("data.table", "data.frame"), row.names = c(NA, -10L))

Изменить: добавлены ожидаемые выходные данные

AltSuitRaw <- data.table(structure(list(Alternative = c("A", "A", "A", "A", "A", "A"),TraceID = c("h00s1", "h00s1", "h00s1", "h00s1", "h00s1","h00s1"), WaterYear = c(2013L, 2013L, 2013L, 2013L, 2013L,2013L), Month = 1:6, Location = c("GCD", "GCD", "GCD", "GCD","GCD", "GCD"), Temp = c(9.8, 8.7, 8.5, 8.5, 8.9, 9.3), SpdSpSuitT = c(NA,NA, NA, NA, NA, "0.0"), SpdIncSuitT = c(NA, NA, NA, NA, NA,"0.0"), SpdGrowSuitT = c(0, 0, 0, 0, 0, 0), RzbSpSuitT = c(NA,NA, NA, "0.0", "0.0", "0.0"), RzbIncSuitT = c(NA, NA, NA,"0.0", "0.0", "0.0"), RzbGrowSuitT = c(0, 0, 0, 0, 0, 0),FmsSpSuitT = c(NA, NA, "0.0", "0.0", NA, NA), FmsIncSuitT = c(NA,NA, "0.0", "0.0", "0.0", NA), FmsGrowSuitT = c(0, 0, 0, 0,0, 0), BhsSpSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0"), BhsIncSuitT = c(NA,NA, NA, "0.0", "0.0", "0.0"), BhsGrowSuitT = c(0, 0, 0, 0,0, 0), BrtSpSuitT = c(0.933333333, 0.566666667, 0.5, NA,NA, NA), BrtIncSuitT = c(0.9, 0.35, 0.25, 0.25, NA, NA),BrtGrowSuitT = c(0, 0, 0, 0, 0, 0), CcfSpSuitT = c(NA, NA,NA, NA, "0.0", "0.0"), CcfIncSuitT = c(NA, NA, NA, NA, "0.0","0.0"), CcfGrowSuitT = c(0, 0, 0, 0, 0, 0), GsfSpSuitT = c(NA,NA, NA, NA, "0.0", "0.0"), GsfIncSuitT = c(NA, NA, NA, NA,"0.0", "0.0"), GsfGrowSuitT = c(0, 0, 0, 0, 0, 0), RbtSpSuitT = c(0.9,0.35, 0.25, 0.25, 0.45, 0.65), RbtIncSuitT = c(0.933333333,0.566666667, 0.5, 0.5, 0.633333333, 0.766666667), RbtGrowSuitT = c(0,0, 0, 0, 0, 0), SmbSpSuitT = c(NA, NA, NA, "0.0", "0.0","0.0"), SmbIncSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0"),SmbGrowSuitT = c(0, 0, 0, 0, 0, 0), StbSpSuitT = c(NA, NA,NA, NA, NA, "0.0"), StbIncSuitT = c(NA, NA, NA, NA, NA, "0.0"), StbGrowSuitT = c(0, 0, 0, 0, 0, 0), HbcSpSuitT = c(NA,NA, NA, "0.0", "0.0", "0.0"), HbcIncSuitT = c(NA, NA, NA,"0.0", "0.0", "0.0"), HbcGrowSuitT = c(0, 0, 0, 0, 0, 0),AtActSuitT = c(0, 0, 0, 0, 0, 0), AtInfSuitT = c(0, 0, 0,0, 0, 0), LcActSuitT = c(0, 0, 0, 0, 0, 0), LcInfSuitT = c(0,0, 0, 0, 0, 0), TnActSuitT = c(0, 0, 0, 0, 0, 0), TnInfSuitT = c(0,0, 0, 0, 0, 0), WdActSuitT = c(0.1, 0.65, 0.75, 0.75, 0.55,0.35), WdInfSuitT = c(0, 0, 0, 0, 0, 0)), .Names = c("Alternative","TraceID", "WaterYear", "Month", "Location", "Temp", "SpdSpSuitT","SpdIncSuitT", "SpdGrowSuitT", "RzbSpSuitT", "RzbIncSuitT", "RzbGrowSuitT","FmsSpSuitT", "FmsIncSuitT", "FmsGrowSuitT", "BhsSpSuitT", "BhsIncSuitT","BhsGrowSuitT", "BrtSpSuitT", "BrtIncSuitT", "BrtGrowSuitT","CcfSpSuitT", "CcfIncSuitT", "CcfGrowSuitT", "GsfSpSuitT", "GsfIncSuitT","GsfGrowSuitT", "RbtSpSuitT", "RbtIncSuitT", "RbtGrowSuitT","SmbSpSuitT", "SmbIncSuitT", "SmbGrowSuitT", "StbSpSuitT", "StbIncSuitT","StbGrowSuitT", "HbcSpSuitT", "HbcIncSuitT", "HbcGrowSuitT","AtActSuitT", "AtInfSuitT", "LcActSuitT", "LcInfSuitT", "TnActSuitT","TnInfSuitT", "WdActSuitT", "WdInfSuitT"), class = c("data.table","data.frame"), row.names = c(NA, -6L))

r sqldf
person duHaas    schedule 11.04.2014    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вместо того, чтобы записывать длинную часть выбора, сгенерируйте ее в sel, как показано ниже:

library(sqldf)

nms1 <- names(SpecSuitTemp)[-1]
nms2 <- names(AltSuitDates)[-1]
sel <- toString(paste0(nms1, " * ", nms2, " as ", nms1, "T"))

fn$sqldf('select $sel 
          from RealAlt 
          left join SpecSuitTemp using (Temp)
          left join AltSuitDates using (Month)')
person G. Grothendieck    schedule 11.04.2014
comment
Это почти доводит меня до цели. Он точно соответствует, используя all.equal, последним 41 столбцу AltSuitRaw, но отсутствует первые 6. Я подумаю об этом и посмотрю, не смогу ли я получить полное решение из вашего кода, спасибо. - person duHaas; 11.04.2014
comment
Я добавил ответ, основанный на вашем коде. Спасибо за помощь. Функции toString, fn$ и $variable внутри fn$sqldf весьма полезны; Мне определенно нужно изучить их больше. - person duHaas; 12.04.2014

arrow_upward
0
arrow_downward

Благодаря Г. Гротендику я смог полностью ответить на этот вопрос

tcoln <- names(SpecSuitTemp)[-1]
dcoln <- names(AltSuitDates)[-1]
newcn <- toString(paste0(tcoln, " * ", dcoln, " as ", tcoln, "T"))

AltSuitRaw <- data.table(fn$sqldf('select RealAlt.*, $newcn from RealAlt left join SpecSuitTemp using (Temp) left join AltSuitDates using (Month)'))

Добавление в оператор RealAlt.*, перед $newcn дает мне следующий результат:

AltSuitRawt <- data.table(structure(list(Alternative = c("A", "A", "A", "A", "A", "A","A", "A", "A", "A"), TraceID = c("h00s1", "h00s1", "h00s1", "h00s1","h00s1", "h00s1", "h00s1", "h20s3", "h20s3", "h20s3"), WaterYear = c(2013L,2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2034L, 2034L, 2034L), Month = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 10L, 11L, 12L), Location = c("GCD","GCD", "GCD", "GCD", "GCD", "GCD", "GCD", "RM225", "RM225", "RM225"), Temp = c(9.8, 8.7, 8.5, 8.5, 8.9, 9.3, 9.8, 14.7, 12.9, 10.9), SpdSpSuitT = c(NA, NA, NA, NA, NA, "0.0", "0.0", NA, NA, NA), SpdIncSuitT = c(NA, NA, NA, NA, NA, "0.0", "0.0", NA, NA,NA), SpdGrowSuitT = c(0, 0, 0, 0, 0, 0, 0, 0.3375, 0.1125, 0),RzbSpSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0", NA, NA, NA,NA), RzbIncSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0", NA,NA, NA, NA), RzbGrowSuitT = c(0, 0, 0, 0, 0, 0, 0, 0.116666667,0, 0), FMSSpSuitT = c(NA, NA, "0.0", "0.0", NA, NA, NA, NA,NA, NA), FMSIncSuitT = c(NA, NA, "0.0", "0.0", "0.0", NA,NA, NA, NA, NA), FMSGrowSuitT = c(0, 0, 0, 0, 0, 0, 0, 0.116666667,0, 0), BhsSpSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0", NA,NA, NA, NA), BhsIncSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0",NA, NA, NA, NA), BhsGrowSuitT = c(0, 0, 0, 0, 0, 0, 0, 0,0, 0), BrtSpSuitT = c(0.933333333, 0.566666667, 0.5, NA,NA, NA, NA, 0, 0.275, 0.775), BrtIncSuitT = c(0.9, 0.35,0.25, 0.25, NA, NA, NA, 0.53, 0.71, 0.91), BrtGrSuitT = c(0,0, 0, 0, 0, 0, 0, 0.9, 0.3, 0), CcfSpSuitT = c(NA, NA, NA,NA, "0.0", "0.0", "0.0", NA, NA, NA), CcfIncSuitT = c(NA,NA, NA, NA, "0.0", "0.0", "0.0", NA, NA, NA), CcfGrSuitT = c(0,0, 0, 0, 0, 0, 0, 0, 0, 0), GsfSpSuitT = c(NA, NA, NA, NA,"0.0", "0.0", "0.0", NA, NA, NA), GsfIncSuitT = c(NA, NA,NA, NA, "0.0", "0.0", "0.0", NA, NA, NA), GsfGrSuitT = c(0,0, 0, 0, 0, 0, 0, 0, 0, 0), RbtSpSuitT = c(0.9, 0.35, 0.25,0.25, 0.45, 0.65, 0.9, NA, NA, NA), RbtIncSuitT = c(0.933333333,0.566666667, 0.5, 0.5, 0.633333333, 0.766666667, 0.933333333,NA, NA, NA), RbtGrSuitT = c(0, 0, 0, 0, 0, 0, 0, 0.675, 0.225,0), SmbSpSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0", "0.0",NA, NA, NA), SmbIncSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0","0.0", NA, NA, NA), SmbGrSuitT = c(0, 0, 0, 0, 0, 0, 0, 0,0, 0), StbSpSuitT = c(NA, NA, NA, NA, NA, "0.0", "0.0", NA,NA, NA), StbIncSuitT = c(NA, NA, NA, NA, NA, "0.0", "0.0",NA, NA, NA), StbGrSuitT = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),HbcSpSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0", NA, NA, NA,NA), HbcIncSuitT = c(NA, NA, NA, "0.0", "0.0", "0.0", NA,NA, NA, NA), HbcGrSuitT = c(0, 0, 0, 0, 0, 0, 0, 0.175, 0,0), AtActSuitT = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), AtInfSuitT = c(0,0, 0, 0, 0, 0, 0, 0, 0, 0), LcActSuitT = c(0, 0, 0, 0, 0,0, 0, 0, 0, 0), LcInfSuitT = c(0, 0, 0, 0, 0, 0, 0, 0, 0,0), TnActSuitT = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), TnInfSuitT = c(0,0, 0, 0, 0, 0, 0, 0, 0, 0), WdActSuitT = c(0.1, 0.65, 0.75,0.75, 0.55, 0.35, 0.1, 0, 0, 0), WdInfSuitT = c(0, 0, 0,0, 0, 0, 0, NA, NA, NA)), .Names = c("Alternative", "TraceID","WaterYear", "Month", "Location", "Temp", "SpdSpSuitT", "SpdIncSuitT","SpdGrowSuitT", "RzbSpSuitT", "RzbIncSuitT", "RzbGrowSuitT","FMSSpSuitT", "FMSIncSuitT", "FMSGrowSuitT", "BhsSpSuitT", "BhsIncSuitT","BhsGrowSuitT", "BrtSpSuitT", "BrtIncSuitT", "BrtGrSuitT", "CcfSpSuitT","CcfIncSuitT", "CcfGrSuitT", "GsfSpSuitT", "GsfIncSuitT", "GsfGrSuitT","RbtSpSuitT", "RbtIncSuitT", "RbtGrSuitT", "SmbSpSuitT", "SmbIncSuitT","SmbGrSuitT", "StbSpSuitT", "StbIncSuitT", "StbGrSuitT", "HbcSpSuitT","HbcIncSuitT", "HbcGrSuitT", "AtActSuitT", "AtInfSuitT", "LcActSuitT","LcInfSuitT", "TnActSuitT", "TnInfSuitT", "WdActSuitT", "WdInfSuitT"), class = c("data.table", "data.frame"), row.names = c(NA, -10L))

При использовании all.equal два data.tables равны, за исключением некоторых несоответствий в именах заголовков столбцов в зависимости от того, написаны ли некоторые имена с заглавной буквы или нет.

person duHaas    schedule 11.04.2014