Блог

Я очень новичок в SQL, поэтому приношу свои извинения, если это простой вопрос, я ничего не нашел во время поиска, но, возможно, я пропустил очевидные условия поиска.

Я пытаюсь загрузить все данные о транзакциях для набора муниципальных облигаций, для которых у меня есть список CUSIP, который в настоящее время хранится в виде файла .txt с одним CUSIP на строку. Онлайн-версия WRDS позволяет пользователю загружать такой текстовый файл для извлечения своих данных.

Я хотел бы автоматизировать этот процесс в R и следовал руководству WRDS по настройке SQL-запросов в R. В конечном итоге я буду использовать что-то вроде

 res <- dbSendQuery(wrds, "select *
               from msrb.msrb
               where cusip IN ???")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data
  

Как мне на самом деле вставить мой список CUSIP в запрос? Слишком длинный, чтобы было возможно напрямую перечислить каждый CUSIP. Могу ли я как-то ссылаться на .txt-файл или, по крайней мере, вектор символов в R или что-то в этом роде? Есть ли лучший подход?

Я думаю, что есть два эффективных способа программного выполнения IN (...) в SQL, и один способ, который популярен, но рискован (и я обычно не рекомендую его).

1. Использование привязки параметров. Это практично до некоторого субъективного предела; может быть реальное ограничение на количество параметров, которые DBI можно привязать, но я этого не знаю; Я не знаю, часто ли реализации SQL ограничивают количество значений, которые вы можете поместить в буквальный IN (...) оператор (я только что протестировал PG11 с 5000, нетпроблема). В какой-то момент может быть более эффективным или желательным использовать вариант 2 ниже. Однако, если мы говорим о количестве десятков, попробуйте это.

    cusips <- c(...) # vector of CUSIPs
   params <- paste(paste0("$", seq_along(cusips)), collapse = ",")
   ret <- DBI::dbGetQuery(con,
     paste("select * from msrb.msrb where cusip in (", params, ")"),
     params = as.list(cusips))
     

   Использование ($1, $2, $3) специфично для postgres; другие СУБД могут использовать другую номенклатуру, в том числе (?,?,?) (sql server и другие).

2. Загрузите идентификаторы во временную таблицу и выполните запрос к ней. (Это также можно частично использовать, если вы получаете идентификаторы для использования из другого запроса, просто обновите внутренний SQL, чтобы отразить ваш другой запрос.)

    cusips <- c(...) # vector of CUSIPs
   tmptbl <- paste0("tmptable_", paste(sample(9), collapse = ""))
   DBI::dbWriteTable(con, tmptbl, data.frame(cusip = cusips))
   DBI::dbGetQuery(con,
     paste("select * from msrb.msrb where cusip in",
           "(select cusip from", tmptbl, ")"))
     

   или объединение с временной таблицей с

    DBI::dbGetQuery(con,
     paste("select msrb.* from ", tmptbl, "t",
           "left join msrb on t.cusip = msrb.cusip"))
     

3. В целом, я убежденный сторонник использования привязки параметров, поскольку это обойдет стороной любую форму SQL-инъекции, будь то злонамеренная или случайная. Однако, если вы спешите, вы можете сформировать IN (...) его самостоятельно. Вы можете использовать glue::glue_sql , чтобы убедиться, что всегда используются правильные кавычки (для вашей конкретной СУБД); если нет, часто безопасно использовать одинарные кавычки.

    cusips <- c(...) # vector of CUSIPs
   params <- paste("(", paste(sQuote(cusips), collapse = ","), ")")
   # or
   params <- glue::glue_sql("({cusips*})", .con = con)
   DBI::dbGetQuery(con, paste("select * from msrb.msrb where cusip in", params))
     

   Обратите внимание, что это glue::glue_sql обеспечивает * обозначение. От ?glue::glue_sql :

   Если вы поместите ‘*’ в конце выражения объединения, значения будут свернуты запятыми. Это полезно, например, для оператора SQL IN.

Для всех трех методов я использовал более прямой DBI::dbGetQuery , но вы все равно можете использовать DBI::dbSendQuery / DBI::dbFetch two-step, если предпочитаете.

В зависимости от размера вашей msrb таблицы, а также ее индексов, эти запросы могут не соответствовать всем оптимизациям. Если это так, рассмотрите возможность добавления к запросу на основе рекомендаций вашего администратора базы данных.