做最具实战的
数据科学职业社区

sqldf包 | 通过运行SQL语句来操作数据框的R包

sqldf::sqldf – R Functions Collection

Usage

sqldf(SQL)

Arguments

Name                  SQL
Description         SQL语句

Similar functions

Loading …

Note

http://code.google.com/p/sqldf/ sqldf是一个通过运行SQL语句来操作数据框的R包。用户把数据框名作为表名,后台自动生成一个数据库,数据框自动载入到数据库中,然后运行SQL语句,将查询结果送回至R,并且自动清除数据库。 sqldf支持SQLite(作为缺省的RMDBS)、H2,PostgreSQL,SQLite和H2是无需客户端,而是以R包RSQLite和RH2。 一个简单的例子,使用BOD数据

library(sqldf)
sqldf("select * from BOD where Time > 4")

其中自动完成: 1. 数据库的设置 2. 创建表 3. 导入和导出数据 4. 将返回的数据转换为合适的类 下面是一个使用H2数据库的例子

library(RH2)
library(sqldf)
sqldf("select * from iris limit 5")
# SQL函数必须大写
sqldf("select COUNT(*) from iris")
sqldf("select Species, COUNT(*) from iris group by Species")
DF <- data.frame(a = 1:5, b = letters[1:5])
sqldf("select * from DF")
sqldf("select AVG(a) mean, VAR_SAMP(a) var from DF")

常见问题 1. sqldf是如何处理类和因子的?如果输出结果的列名和输入数据库的列名相同,则把输入数据框的此列的列赋予结果列。如果输入数据框有两个以上的列名与输出结果的某个列名相同,则此自动寻找不进行。当在sqldf中设置method=”raw”时,自动匹配也不进行。如果输出结果列与输入数据框的一个为factor类的列相同。则输入数据框列的level被赋予输出列。输出结果列的level只取在其中存在的。如下例

> a5r <- subset(farms, Mois == 'M1')
> a6r <- subset(farms, Mois == 'M2')
>
> # rbind
> a7r <- rbind(a5r, a6r)
> a7s <- sqldf("select * from a5r union all select * from a6r")
> str(a7r) #Mois列的原始所有level
'data.frame':   11 obs. of  4 variables:
 $ Mois  : Factor w/ 4 levels "M1","M2","M4",..: 1 1 1 1 1 1 1 2 2 2 ...
 $ Manag : Factor w/ 4 levels "BF","HF","NM",..: 4 1 2 2 2 1 3 4 4 1 ...
 $ Use   : Factor w/ 3 levels "U1","U2","U3": 2 2 1 2 3 3 1 2 2 1 ...
 $ Manure: Factor w/ 5 levels "C0","C1","C2",..: 5 3 3 3 4 2 1 5 5 2 ...
> str(a7s)  #Mois列经过查询后存在的level
'data.frame':   11 obs. of  4 variables:
 $ Mois  : Factor w/ 2 levels "M1","M2": 1 1 1 1 1 1 1 2 2 2 ...
 $ Manag : Factor w/ 4 levels "BF","HF","NM",..: 4 1 2 2 2 1 3 4 4 1 ...
 $ Use   : Factor w/ 3 levels "U1","U2","U3": 2 2 1 2 3 3 1 2 2 1 ...
 $ Manure: Factor w/ 5 levels "C0","C1","C2",..: 5 3 3 3 4 2 1 5 5 2 ...

sqldf支持Date,POSIXt(POSIXct和POSIXlt)和chron(dates, times)类,其他时间类不支持。 2. 为什么sqldf会改变一些变量的名字?点号在SQL中是一个操作符,所以会被替换成为下划线,所以在下面的例子中,Sepal.Length在SQL中需要写成Sepal_Length。

> head(iris, 3)
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
> sqldf("select Sepal_Length, Sepal_Width from iris limit 3")
  Sepal_Length Sepal_Width
1          5.1         3.5
2          4.9         3.0
3          4.7         3.2

3. 为什么sqldf(“select var(x) from Df”)不能正常运行? sqldf中的SQL语句必须是合法的,其中默认的包含SQLite函数和一些扩展的SQLite函数。如果libspatial-1.dll的路径在PATH环境变量中设置,则可以使用更多的函数。R函数在SQL语句中是无法使用的。 4. sqldf是如何处理Date类变量的 H2数据库提供处理Date类的函数

> library(RH2) # driver support for dates was added in RH2 version 0.1-2
> library(sqldf)
> test1 <- data.frame(sale_date = as.Date(c("2008-08-01", "2031-01-09",
+ "1990-01-03", "2007-02-03", "1997-01-03", "2004-02-04")))
> as.numeric(test1[[1]])
[1] 14092 22288  7307 13547  9864 12452
> sqldf("select MAX(sale_date) from test1")
  MAX..sale_date..
1       2031-01-09

SQLite中并没有对Date类的支持。是以数字格式从R中传递到SQLite的。

> library(sqldf)
> DF <- data.frame(a = Sys.Date() + 1:5, b = 1:5)
> DF
          a b
1 2009-07-31 1
2 2009-08-01 2
3 2009-08-02 3
4 2009-08-03 4
5 2009-08-04 5
> Sys.Date() + 2
[1] "2009-08-01"
> s <- sprintf("select * from DF where a >= %d", Sys.Date() + 2)
> s  # 时间是以数字存储
[1] "select * from DF where a >= 14457"
> sqldf(s)
          a b
1 2009-08-01 2
2 2009-08-02 3
3 2009-08-03 4
4 2009-08-04 5

> # to compare against character string store a as character
> DF2 <- transform(DF, a = as.character(a))
> sqldf("select * from DF2 where a >= '2009-08-01'") #比较字符串
          a b
1 2009-08-01 2
2 2009-08-02 3
3 2009-08-03 4
4 2009-08-04 5

在实际的SQLite中,数字格式的时间是以字符串形式存储,如下

> library(sqldf)
> test1 <- data.frame(sale_date = as.Date(c("2008-08-01", "2031-01-09",
+ "1990-01-03", "2007-02-03", "1997-01-03", "2004-02-04")))

> as.numeric(test1[[1]])
[1] 14092 22288  7307 13547  9864 12452

> sqldf("select max(sale_date) from test1")
 max(sale_date)
1         9864.0

问题的原因是RSQLite并不能自动的处理时间变量,一个解决的方法是使用SQLite的时间和日期函数

> library(zoo)
> sqldf("select max(julianday(sale_date)) sale_date from test1")
   sale_date
1 2031-01-09

julianday函数将时间字符串转变为数字型 5. 我有一个tcl确实的错误信息 sqldf使用gsubfn函数来解析,gsubfn可以在tcltk包没有安装下工作。使用如下语句查看tcltk是否已经配置

capabilities()[["tcltk"]]

如果TRUE,gsubfn使用tcltk,如果FALSE,gsubfn使用R替换函数如果tcltk没有正确安装,但是capabilities()[[“tcltk”]]为TRUE,则可以强制gsubfn使用R

options(gsubfn.engine = "R")

6. 当表名或者列名相同,只是大小写不同时会有什么问题? SQL是大小写不敏感的,所以会有警告信息 7. MySQL怎样? sqldf支持数个数据库,数据库可以在drv=中指定。如果没有指定,则会检查全局条件sqldf.driver,如果RMySQL,RpgSQL或者RH2已经载入,则会使用载入的数据库,否则使用SQLite,如果有数据库载入,但是不想使用,则在options(sqldf.driver=)中显示指定。 8. 我如何知道SQLite表的格式,数据库中有哪些表,哪些数据库被使用了?

> # a. what is the layout of the BOD table?
> sqldf("pragma table_info(BOD)")
  cid   name type notnull dflt_value pk
1   0   Time REAL       0       <NA>  0
2   1 demand REAL       0       <NA>  0

> # b. which tables are in current database and what is their layout?
> sqldf(c("select * from BOD", "select * from sqlite_master"))
   type name tbl_name rootpage
1 table  BOD      BOD        2
                                                    sql
1 CREATE TABLE `BOD` \n( "Time" REAL,\n\tdemand REAL \n)

> # c. which databases are attached?  (This says only 'main' is attached.)
> sqldf("pragma database_list")
  seq name file
1   0 main

9. SQLite和H2数据库有何不同?当RH2包载入后,sqldf使用H2数据库,和SQLite相比,H2支持Date类的处理,其他一些函数如VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP,XML函数和CSVREAD。下面这些命令是H2特有的

library(RH2) # this package contains the H2 database and an R driver
library(sqldf)
sqldf("select AVG(demand) mean, STDEV_POP(demand) from BOD where Time > 4")
sqldf('select Species, "Sepal.Length" from iris limit 3') # Sepal.Length has dot
sqldf("show databases")
sqldf("show tables from INFORMATION_SCHEMA")
sqldf("select * from INFORMATION_SCHEMA.SETTINGS")
sqldf("show tables")
sqldf("show columns from BOD")

当使用H2时 SQL语句是区分大小写的,所以函数必须大写 如果列名为Sepal.Length不会被转换为Sepal_Length,前者是合法的 引号是严格区分的, 文件对象不被支持 SQL语句在sqldf中只支持select, show和call(SQLite中全部支持) 例子 1. 排序和限制在默认的SQLite数据库中,变量中的点号替换成下划线

> library(sqldf)
> sqldf("select * from iris order by Sepal_Length desc limit 3")

  Sepal_Length Sepal_Width Petal_Length Petal_Width   Species
1          7.9         3.8          6.4         2.0 virginica
2          7.7         3.8          6.7         2.2 virginica
3          7.7         2.6          6.9         2.3 virginica

2. 计算均值和分组

> sqldf("select Species, avg(Sepal_Length) from iris group by Species")

     Species avg(Sepal_Length)
1     setosa             5.006
2 versicolor             5.936
3  virginica             6.588

3. 嵌套查询

> sqldf("select iris.Species '[Species]',
+       avg(Sepal_Length) '[Avg of SLs > avg SL]'
+    from iris,
+         (select Species, avg(Sepal_Length) SLavg
+         from iris group by Species) SLavg
+    where iris.Species = SLavg.Species
+       and Sepal_Length > SLavg
+    group by iris.Species")

   [Species] [Avg of SLs > avg SL]
1     setosa              5.313636
2 versicolor              6.375000

3  virginica              7.159091
注:数据科学网遵循行业规范,任何转载的稿件都会明确标注作者和来源,若标注有误或遗漏,请联系主编邮箱:admin@idatacamp.com
数据科学 » sqldf包 | 通过运行SQL语句来操作数据框的R包
分享到: 更多 (0)

吐槽 抢沙发

评论前必须登录!