создание зацикленного SQL-запроса с использованием RODBC в R

Прежде всего - спасибо, что нашли время, чтобы просмотреть мой вопрос, независимо от того, ответите вы или нет!

Я пытаюсь создать функцию, которая перебирает мой df и запрашивает необходимые данные из SQL, используя пакет RODBC в R. Однако у меня возникают проблемы с настройкой запроса, поскольку параметр запроса изменяется на каждой итерации (пример ниже)

Итак, мой df выглядит так:

     ID     Start_Date    End_Date    
     1       2/2/2008      2/9/2008
     2       1/1/2006      1/1/2007
     1       5/7/2010      5/15/2010
     5       9/9/2009      10/1/2009

Как мне указать дату начала и дату окончания в моей программе sql?

вот что у меня есть до сих пор:

 data_pull <- function(df) {
    a <- data.frame()
    b <- data.frame()

    for (i in df$id)
{ 
    dbconnection <- odbcDriverConnect(".....")

    query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")

    a <- sqlQuery(dbconnection, paste(query))
    b <- rbind(b,a)
 }
  return(b)
 }

Однако это ни о чем не спрашивает. Я считаю, что это как-то связано с тем, как я указываю дату начала и окончания итерации.

Если кто-нибудь может помочь в этом, мы будем очень признательны. Если вам нужны дополнительные пояснения, пожалуйста, не стесняйтесь спрашивать!


r rodbc
person yungpadewon    schedule 03.01.2019    source источник
comment
Никогда не используйте paste конкатенацию для SQL-запросов, рискуя в лучшем случае синтаксическими ошибками, в худшем — внедрением SQL (преднамеренно или нет). ). Вместо этого используйте RODBCext (связанный вопрос: stackoverflow.com/q/16178640/3358272) (если только RODBC не начал делать это напрямую , я действительно удивлен, что они еще не внедрили его, поскольку это довольно важно для хороших практик БД). cran.r-project.org/web/packages/RODBCext/ виньетки/   -  person r2evans    schedule 03.01.2019
comment
спасибо @r2evans, я изучу это и попытаюсь реализовать это.   -  person yungpadewon    schedule 03.01.2019
comment
Во-вторых, не используйте повторяющиеся rbind, как это, это снизит производительность в долгосрочной перспективе (каждый вызов rbind создает полную копию всех предшествующих и добавляемых данных). Поскольку RODBCext::sqlExecute напрямую поддерживает векторизацию с помощью фрейма (см. cran.r- project.org/web/packages/RODBCext/vignettes/, раздел 2.3.2), цикл вообще не нужен.   -  person r2evans    schedule 03.01.2019
comment
Да, rbind запуск внутри цикла приводит к квадратичному копированию. Всегда избегайте роста объектов в циклах   -  person Parfait    schedule 03.01.2019
comment
спасибо, ребята @Parfait, я попробую переписать это   -  person yungpadewon    schedule 03.01.2019

Ответы (1)


arrow_upward
1
arrow_downward

Пара проблем с синтаксисом возникает из-за текущей настройки:

  1. ЦИКЛ: вы не перебираете все строки фрейма данных, а только значения атомарного идентификатора в одном столбце, df$ID. В том же цикле вы передаете целые векторы df$Start_Date и df$End_Date в конкатенацию запроса.

  2. ДАТЫ: ваши форматы дат не соответствуют большинству форматов дат баз данных «ГГГГ-ММ-ДД». И все же некоторые другие, такие как Oracle, требуют преобразования строки в данные: TO_DATE(mydate, 'YYYY-MM-DD').

Пара вышеупомянутых проблем с производительностью/лучшими практиками:

  1. ПАРАМЕТРИЗАЦИЯ: Хотя параметризация не требуется по соображениям безопасности, поскольку ваши значения не генерируются пользовательским вводом, который может внедрить вредоносный код SQL, для удобства сопровождения и удобства чтения рекомендуется использовать параметризованные запросы. Следовательно, подумайте о том, чтобы сделать это.

  2. РАСТУЩИЕ ОБЪЕКТЫ: Согласно , программистам R следует избегать увеличения многомерных объектов, таких как фреймы данных, внутри цикла, что может привести к чрезмерному копированию в памяти. Вместо этого создайте список фреймов данных для rbind один раз вне цикла.

При этом вы можете избежать необходимости зацикливания или перечисления, сохранив свой фрейм данных в виде таблицы базы данных, а затем присоединив ее к итоговой таблице для отфильтрованного импорта запроса на соединение. Это предполагает, что пользователь вашей базы данных имеет привилегии CREATE TABLE и DROP TABLE.

# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
          Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
          End_Date = as.Date(End_Date, format="%m/%d/%Y")
})

# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)

# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance 
      FROM Table t
      INNER JOIN myRData r 
        ON r.ID = t.ID 
        AND t.Date BETWEEN r.Start_Date AND r.End_Date"

final_df <- sqlQuery(dbconnection, q)
person Parfait    schedule 03.01.2019
comment
Большое спасибо, что нашли время, чтобы пройти через это. Я очень ценю это. - person yungpadewon; 03.01.2019
comment
Без проблем. С Новым годом и удачного кодинга! - person Parfait; 04.01.2019