从R中的SQLite中的表中收集（）

I am trying to use the collect() function from dplyr to query a table (comp_fleet) in SQLite. The following code works:

我试图使用dplyr中的collect（）函数来查询SQLite中的表（comp_fleet）。以下代码有效：

Cposns <- tbl(db_con,"comp_fleet") %>% 
  arrange(mmsi, desc(timestamp))%>% 
  dplyr::filter(!is.na(lat)) %>% collect()

However when I try top_n(), I get an error:

但是，当我尝试top_n（）时，我收到一个错误：

Cposns <- tbl(db_con,"comp_fleet") %>% 
  arrange(mmsi, desc(timestamp))%>% 
  dplyr::filter(!is.na(lat)) %>% 
  top_n(1,timestamp) %>% collect()

Error: Window function `rank()` is not supported by this database

Since this is a huge table, I was hoping to carry out all aggregation functions within the database before collecting. Is there some way of using the top_n() function prior to collect()? Or something equivalent to it?

由于这是一个巨大的表，我希望在收集之前在数据库中执行所有聚合功能。有没有办法在collect（）之前使用top_n（）函数？或者相当于它的东西？

1 个解决方案

#1

Thanks to @Scarabee, this is what works:

感谢@Scarabee，这是有效的：

Cposns <- tbl(db_con,"comp_fleet") %>%
  arrange(mmsi, desc(timestamp))%>%
  dplyr::filter(!is.na(lat)) %>% group_by(mmsi) %>%
  do(head(., n = 1)) %>% collect()