mysql - 如何按行号提取 tbl_mysql 或 tbl_sqlite 数据框的子集？

Question

据我了解 dplyr 包的 slice 函数的帮助页面，可以像这样选择 SQL tbl 或 SQLite tbl 的行:

filter(mtcars, row_number() == 1L)

但是当我尝试时出现错误:

dat1 <- data.frame(
  Name = c("Joe", "Bill", "Jim", "Kate"),
  Value = c(10.1, 9, 7.7, -3),
  Indic = c(0L, 1L, 2L, 2L),
  Status = c(TRUE, TRUE, FALSE, FALSE)
)
#### Create SQLite database ####
library(dplyr)
my_database <- src_sqlite("db_sqlite_file", create = TRUE) 
#### Put data in database ####
copy_to(my_database, dat1, temporary = FALSE)
#### Connect to database ####
my_db <- src_sqlite("db_sqlite_file", create = FALSE)
#### Querying the database ####
dd <- tbl(my_db, "dat1")
ddextract <- filter(dd, row_number() == 1L)

错误信息出现在这里:

> ddextract  
Source: sqlite 3.8.6 [db_sqlite_file]
Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such function: ROW_NUMBER

MySQL同样的问题:

my_db_sql <- src_mysql("mysql_dplyr", password="mypassword") 
dd <- tbl(my_db_sql, "dat1")
ddextract <- filter(dd, row_number() == 1L)

错误:

> ddextract
Source: mysql 5.5.49-0ubuntu0.14.04.1 [root@localhost:/mysql_dplyr]
Error in .local(conn, statement, ...) : 
  could not run statement: FUNCTION mysql_dplyr.ROW_NUMBER does not exist

当然我可以用行号创建一个新列，但我想知道为什么 filter(mtcars, row_number() == 1L) 不起作用。

Answer 1

你可以试试这个:

ddextract <- dd %>% collect() %>% filter(row_number() == 1L)