无法使用 sqldf 在 R 中的函数上使用 rank() [英] Unable to use rank() over functions in R using sqldf

问题描述

arm<-as.data.frame(matrix(c(1,1,1,2,2,6,7,4,9,10),ncol=2))

colnames(arm)<-c("a","b")

这是我在 R 中创建的数据集.

This is a dataset I created in R.

现在我想对 b 列进行排名并按 a 列进行分组.

Now I want to rank the column b and group by column a.

无论我对语法进行什么更改(例如添加 []、"等...)，以下代码都会引发此错误

The following piece of code is throwing this error, no matter what changes I make to the syntax(like adding [], "", etc...)

sqliteSendQuery(con, statement, bind.data) 中的错误:语句中的错误:接近(":语法错误

我使用的是sqldf"包.

I was using "sqldf" package.

arm2<-sqldf("select a,
         b,
         rank() over (partition by a order by b) as rank1 
         from arm")

然后我安装了 RH2 包，它开始抛出以下错误:

Then I installed the RH2 package and it started to throw the following error:

Error in .verify.JDBC.result(s, "Unable to execute JDBC statement ", statement) : Unable to execute JDBC statement select a,乙，rank() over (part by a order by b) 作为 rank1from arm(未找到函数rank"；SQL语句:选择一个，乙，rank() over (part by a order by b) 作为 rank1来自 arm [90022-175])

如何在R的sqldf包中对sql函数使用rank()?

How to use rank() over function of sql in sqldf package of R?

推荐答案

sqldf 使用不支持 rank() 函数的 SQLite - 见这里.从你从 H2 得到的错误消息来看，它也没有，尽管它是当前计划.

sqldf uses SQLite which does not support the rank() function - see here. From the error message you got from H2, it does not either, though it is currently planned.

sqldf 可以使用 PostgreSQL 而不是 SQLite，后者确实支持 rank():参见此处 以获取示例.您发布的代码应该可以工作.

sqldf has capability to use PostgreSQL rather than SQLite, which does support rank(): see here for an example. Your code as posted should then work.

如果您不想使用 PostgreSQL，您可以使用 SQLite 和 sqldf 以正确的顺序获取数据:

If you don;t want to use PostgreSQL, you can get the data out in the right order with SQLite and sqldf using:

sqldf("select a, b from arm 
          order by a, b", drv = "SQLite")

但是排名栏比较难——看一些相关的答案:1，2、3

but the ranking column is more difficult - see some related answers: 1, 2, 3

因为你已经在 R 中，你可以使用 dplyr，一个原生的 R 包:

Since you are already in R, you could use dplyr, a native R package:

library(dplyr)
arm %>% group_by(a) %>%
        mutate(rank = rank(b))

或者 data.table，一个更快的选择:

Or data.table, a faster alternative:

library(data.table)
setDT(arm)[ , rank := rank(b), by = a]

这篇关于无法使用 sqldf 在 R 中的函数上使用 rank()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！