将变量值作为参数传递给sqldf

I have a data set with id and dates. I take the today date into a variable.

我有一个id和日期的数据集。我把今天的日期变成一个变量。

today=Sys.date()

Now I want to calculate the latest data in my data set which is less than today date.

现在我想计算我的数据集中的最新数据,该数据小于今天的日期。

Date=sqldf(paste0("SELECT * FROM DATA WHERE MAX(DATE) <",TODAY))

I'm getting error and not able to resolve it.

我收到错误而无法解决问题。

1 个解决方案

#1

You probably need single quotes around the value returned from Sys.Date(). This should work

您可能需要围绕从Sys.Date()返回的值的单引号。这应该工作

TODAY <- Sys.Date()
sqldf(paste0("SELECT * FROM DATA",
        " WHERE DATE < '", TODAY, "'",
        " ORDER BY DATE DESC LIMIT 1"))

This would generate the following raw query:

这将生成以下原始查询:

SELECT *
FROM DATA
WHERE DATE < '2018-03-29'
ORDER BY DATE DESC
LIMIT 1

This query will return the most recent record in your data happening before today.

此查询将返回您今天之前发生的数据中的最新记录。

Note that building a SQL string using raw concatenation like is generally bad. But if you are just doing it from your R console for some data analysis, then it should be acceptable.

请注意,使用原始连接构建SQL字符串通常很糟糕。但是,如果你只是从R控制台进行一些数据分析,那么它应该是可以接受的。

data

DATA <- data.frame(DATE = c(Sys.Date() + 5:10, Sys.Date() - 5:10))