sql - 在sqldf/SQLite中缩放/平均中心/行为变量？

Question

我正在尝试使用R中的sqldf包将一个变量的中心（aka行为，比例）按3个维度表示：年，月和区域。

这正是我要使用plyr软件包执行的操作：

## create example data
set.seed(145)
v = Sys.Date()-seq(1,425)
regions = LETTERS[1:6]
VAR1_DATA = as.data.frame(expand.grid(v,regions))
VAR1_DATA$VAR1 = rpois(nrow(VAR1_DATA), 4) + runif(nrow(VAR1_DATA), 25,35)
names(VAR1_DATA) = c("DATE","REG","VAR1")


## mean center VAR1 by year, month and region using plyr:
lapply(c('chron','plyr'), require, character.only=T)
table1 = cbind(MONTH = months(as.POSIXlt(VAR1_DATA[,'DATE'])),
            YEAR = years(as.POSIXlt(VAR1_DATA[,'DATE'])),
            VAR1_DATA)
table2 = ddply(table1, c('YEAR','MONTH','REG'), transform, MEAN.V1 = mean(VAR1), DEMEANED.V1 = VAR1 - mean(VAR1))
head(table2)

##      MONTH YEAR       DATE REG     VAR1  MEAN.V1 DEMEANED.V1
## 1 December 2011 2011-12-31   A 30.03605 34.69316  -4.6571064
## 2 December 2011 2011-12-30   A 31.69130 34.69316  -3.0018600
## 3 December 2011 2011-12-29   A 35.46342 34.69316   0.7702634
## 4 December 2011 2011-12-28   A 32.09727 34.69316  -2.5958876
## 5 December 2011 2011-12-27   A 36.51519 34.69316   1.8220386
## 6 December 2011 2011-12-26   A 35.65338 34.69316   0.9602236

现在，我想使用SQLite / SQL复制上面的结果。下面是我当前用来尝试完成此操作的SQLite代码（警告：下面的代码不起作用！）。我将其包含在此处以说明我的SQLish思想过程：

require(sqldf)

sqldf("
       SELECT
       strftime('%m', t1.DATE) AS 'MONTH', 
       strftime('%Y', t1.DATE) AS 'YEAR',
       t1.DATE,
       t1.REG,
       t1.VAR1,
       t2.MVAR1 AS 'MO_AVG_VAR1',
       (t1.VAR1-t2.MVAR1) AS 'DEMEANED_VAR1',
       FROM VAR1_DATA AS t1,
       (
           SELECT
           DATE,
           REG,
           avg(VAR1) AS MVAR1,
           FROM VAR1_DATA
           GROUP BY strftime('%Y', DATE), strftime('%m', DATE), REG
       ) AS t2
      WHERE t1.REGION = t2.REGION
      AND t1.DATE = t2.DATE
      GROUP BY strftime('%Y', t1.DATE), strftime('%m', t1.DATE), t1.REGION
      ORDER BY YEAR, MONTH, REG
      ;")

问题：这种计算是否可以在SQLite / sqldf中进行-如果可以，如何进行？如果答案还提供（稍微修改？）“常规SQL”（即mySQL，PostgreSQL等）实现，则可加分。

非常感谢！

Answer 1

试试这个：

## set order so we can compare it later

table2 <- table2[order(table2$DATE, table2$REG), ]

## use a single SQL statement

s1 <- "select 
          rowid, 
          *, 
          strftime('%Y-%m', DATE * 3600 * 24, 'unixepoch') AS 'YM' 
       from VAR1_DATA"
s2a <- "select a.*, 
         avg(b.VAR1) 'MEAN.V1', 
         a.VAR1 - avg(b.VAR1) 'DEMEANED.V1'
       from ($s1) a, ($s1) b using (YM, REG)
       group by a.rowid
       order by a.DATE, a.REG"
# substitute s1 into s2a giving the single sql statement:
#    cat(fn$identity(s2a), "\n")
tab2 <- fn$sqldf(s2a)

# ensure they compare to the plyr solution
all.equal(table2$MEAN.V1, tab2$MEAN.V1) # TRUE
all.equal(table2$DEMEANED.V1, tab2$DEMEANED.V1) # TRUE

相同，但使用两个SQL语句：

# s1 is as above
tab1 <- sqldf(s1)
s2b <- "select a.*, 
         avg(b.VAR1) 'MEAN.V1', 
         a.VAR1 - avg(b.VAR1) 'DEMEANED.V1'
       from tab1 a, tab1 b using (YM, REG)
       group by a.rowid
       order by a.DATE, a.REG"
tab2 <- sqldf(s2b)

# ensure they compare to the plyr solution
all.equal(table2$MEAN.V1, tab2$MEAN.V1) # TRUE
all.equal(table2$DEMEANED.V1, tab2$DEMEANED.V1) # TRUE

注意：以上已根据注释进行了完全修订。