r - 使用 dplyr 选择 sqlite 数据库的子集

Question

我正在尝试使用 dplyr 在 sqlite 数据库中提取行的子集。由于 slice 不适用于 tbl_sql 对象，我正在使用窗口函数 row_number。但是我收到以下错误:

Source: sqlite 3.8.6  
[/Library/Frameworks/R.framework/Versions/3.2/Resources/library/dplyr/db/nycflights13.sqlite]
Error in sqliteSendQuery(con, statement, bind.data) : 
error in statement: no such function: ROW_NUMBER

dplyr 版本 0.4.3.9000，RSQLite 版本 1.0.0。可重现的例子:

library(dplyr)
library(nycflights13)

flights_sqlite <- tbl(nycflights13_sqlite(), "flights")

filter(flights_sqlite, row_number(month) == 1L) %>% collect()

Answer 1

可能有更高效、更快速的方法，但 head 似乎可以胜任。

To extract first n rows, for instance first 10 records:

head(flights_sqlite, 10) %>% collect() 

输出:

   year month day dep_time dep_delay arr_time arr_delay carrier tailnum flight origin dest air_time distance hour minute
1  2013     1   1      517         2      830        11      UA  N14228   1545    EWR  IAH      227     1400    5     17
2  2013     1   1      533         4      850        20      UA  N24211   1714    LGA  IAH      227     1416    5     33
3  2013     1   1      542         2      923        33      AA  N619AA   1141    JFK  MIA      160     1089    5     42
4  2013     1   1      544        -1     1004       -18      B6  N804JB    725    JFK  BQN      183     1576    5     44
5  2013     1   1      554        -6      812       -25      DL  N668DN    461    LGA  ATL      116      762    5     54
6  2013     1   1      554        -4      740        12      UA  N39463   1696    EWR  ORD      150      719    5     54
7  2013     1   1      555        -5      913        19      B6  N516JB    507    EWR  FLL      158     1065    5     55
8  2013     1   1      557        -3      709       -14      EV  N829AS   5708    LGA  IAD       53      229    5     57
9  2013     1   1      557        -3      838        -8      B6  N593JB     79    JFK  MCO      140      944    5     57
10 2013     1   1      558        -2      753         8      AA  N3ALAA    301    LGA  ORD      138      733    5     58

A percentage of the first rows

head(flights_sqlite, nrow(flights_sqlite)*0.1) %>% collect() 

To subset any specific number of rows. For instance rows 578 and 579:

head(flights_sqlite, nrow(flights_sqlite))[578:579, ] %>% collect()

输出:

    year month day dep_time dep_delay arr_time arr_delay carrier tailnum flight origin dest air_time distance hour minute
578 2013     1   1     1701        -9     2026        11      AA  N3FUAA    695    JFK  AUS      247     1521   17      1
579 2013     1   1     1701         1     1856        16      UA  N418UA    689    LGA  ORD      144      733   17      1