title: Find standard deviation of first differences of series defined with GROUP BY using RSQLite
body: <p>In SQLite I would like to find the standard deviation of the first differences of a (logged) series that I define with <code>GROUP BY</code>. My data provider gives me a daily price series, but I would like to find annualized daily volatility (the standard deviation of daily returns -- first difference of the natural log of the series -- over each year). I can bring the data to R, then use <code>ddply()</code>, but I would like to do this entirely in SQLite. I tried the <code>difference()</code> function from the <a href="http://cran.r-project.org/web/packages/RSQLite.extfuns/RSQLite.extfuns.pdf" rel="nofollow"><code>RSQLite.extfunctions</code> package</a>, but my usage is wrong. I expected it to work like <code>diff()</code> in R, but I can't find much documentation.</p>
  
  <p>This generates some data.</p>
  
  <pre><code>stocks &lt;- 5
  years &lt;- 5
  list.n &lt;- as.list(rep(252, stocks * years))
  list.mean &lt;- as.list(rep(0, stocks * years))
  list.sd &lt;- as.list(abs(runif(stocks * years, min = 0, max = 0.1)))
  list.po &lt;- as.list(runif(n = stocks, min = 25, max = 100)) 
  list.ret &lt;- mapply(rnorm, n = list.n, mean = list.mean, sd = list.sd, SIMPLIFY = F)
  my.price &lt;- function(po, ret) po * exp(cumsum(ret))
  list.price &lt;- mapply(my.price, po = list.po, ret = list.ret, SIMPLIFY = F)
  gvkey &lt;- rep(seq(stocks), each = 252 * years)
  day &lt;- rep(seq(252), n = stocks * years)
  fyr &lt;- rep(seq(years), n = stocks, each = 252)
  data.dly &lt;- data.frame(gvkey, fyr, day, p = unlist(list.price))
  </code></pre>
  
  <p>Here is how I would do it with <code>ddply()</code> and the result.</p>
  
  <pre><code># I could do this easily with ddply and subset
  library(plyr)
  data.dly &lt;- ddply(data.dly, .(gvkey, fyr), transform, vol = sd(diff(log(p))))
  data.ann &lt;- subset(data.dly, day == 252)
  head(data.ann)
       gvkey fyr day         p         vol
  252      1   1 252  86.08568 0.077287182
  504      1   2 252  43.32113 0.066741862
  756      1   3 252  68.69734 0.084419564
  1008     1   4 252  75.37267 0.006003969
  1260     1   5 252  17.53583 0.083688727
  1512     2   1 252 168.44656 0.035959492
  </code></pre>
  
  <p>And here is my (failed) SQLite attempt and error.</p>
  
  <pre><code># but I can't figure it out in SQLite
  library(RSQLite)
  library(RSQLite.extfuns)
  db &lt;- dbConnect(SQLite())
  init_extensions(db)
  [1] TRUE
  dbWriteTable(db, name = "data_dly", value = data.dly)
  [1] TRUE
  temp &lt;- dbGetQuery(db, "SELECT stdev(difference(log(p))) FROM data_dly GROUP BY gvkey, fyr ORDER BY gvkey, fyr, day")
  Error in sqliteExecStatement(con, statement, bind.data) : 
    RS-DBI driver: (error in statement: wrong number of arguments to function difference())
  </code></pre>
  
  <p>Does <code>difference()</code> need a comma separated list of numbers? Can I do this entirely in SQLite? Or do I need to perform in R? Thanks!</p>