0

I am using RSQLite, DBI, and dbplyr packages. I'm watching this YouTube video by TechTFQ.

library(dbplyr)
library(RSQLite)
library(DBI)

mtcars <- tibble::as_tibble(mtcars, rownames = 'car')  
sql_mtcars <- mtcars

con <- RSQLite::dbConnect(SQLite(), ":memory:")
dplyr::copy_to(con, sql_mtcars)

sql_mtcars_db <- tbl(con, "sql_mtcars")

sql_mtcars_db %>%
  dplyr::select(car, mpg, wt) %>%
  dplyr::show_query()

dbListTables(con)

I'm getting a syntax error with this line.

DBI::dbGetQuery(con, '
SELECT *
  row_number() over(partition by cyl) AS rn
FROM sql_mtcars 
  ')

Error: near "row_number": syntax error

I'm certain that the SQL syntax I'm writing here is the same as what I'm watching in the YouTube video, except for the differences in the dataframe that I'm using and the variables that I'm calling.

What is the error that I'm running into? Is it the case that the dbplyr SQL doesn't have the full range of SQL verbs, or is it using a different SQL syntax?

I'm not even sure how to find a a key for the SQL syntax to use in this method. Asking for help through ?DBI, ?dbplyr or ?RSQLite is not much help because there doesn't seem to be much in the way of documentation about the SQL verbs, and I haven't found a very comprehensive vignette to study.

hachiko
  • 671
  • 7
  • 20
  • Can you try with the github version of `RSQLite` as window functions are new in Sqlite – akrun Apr 17 '22 at 18:55
  • Ok I'm trying to install this: devtools::install_github("r-dbi/RSQLite") RStudio is stuck on that line of code for five minutes now – hachiko Apr 17 '22 at 19:02
  • Error: Failed to install 'RSQLite' from GitHub: lazy-load database '/Library/Frameworks/R.framework/Versions/4.0/Resources/library/cli/R/cli.rdb' is corrupt – hachiko Apr 17 '22 at 19:10
  • I had similar issue in installing the github version. Thanks for trying – akrun Apr 17 '22 at 19:11
  • ah ok I thought it was just on my end. Was about to restart computer to see – hachiko Apr 17 '22 at 19:11
  • 3
    The CRAN version of RSQLite 2.2.12 has SQLite 3.82.2 and that has row_number. This works for me on R version 4.1.3 Patched (2022-03-10 r81883) "Windows 10 x64 (build 19044)" `library(sqldf); packageVersion("RSQLite"); sqldf("select sqlite_version()"); sqldf("SELECT *, row_number() over (partition by cyl) AS rn FROM mtcars")` > – G. Grothendieck Apr 17 '22 at 21:26
  • I tried your suggested code and it seemed to work! I'm not sure what it means that this version works: sqldf("SELECT mpg, wt, row_number() over (partition by cyl) AS rn FROM mtcars") – hachiko Apr 18 '22 at 11:23
  • and this version doesn't work: DBI::dbGetQuery(con, ' SELECT mpg, wt, row_number() over (partition by cyl) AS rn FROM mtcars ') – hachiko Apr 18 '22 at 11:23
  • Except maybe the sql involved with dbGetQuery doesn't have window functions and the sql with sqldf does? – hachiko Apr 18 '22 at 11:24
  • 1
    sqldf uses RSQLite so if you use sqldf or RSQLite directly it should work. Maybe there is a problem somewhere else. Run the same sqldf command as in my last comment but add the `verbose=TRUE` argument and sqldf will display what RSQLite commands it is running and then try those. – G. Grothendieck Apr 18 '22 at 11:31

1 Answers1

0

It looks like you are missing a comma after the *. The following works as expected:

DBI::dbGetQuery(con, '
SELECT *,
  row_number() over(partition by cyl) AS rn
FROM sql_mtcars 
  ')

Note the addition of a comma after the * and before the row_number.

Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41