如何“通过分区”中的R模仿SQL？(How to emulate SQL “partition by

我怎样才能做到像甲骨文ROW_NUMBER（），RANK（）解析函数，或DENSE_RANK（）函数（见http://www.orafaq.com/node/55一个R数据帧）？ 该CRAN包“plyr”非常接近，但仍然是不同的。

我认为，每个函数的功能有可能在特设的方式来实现。 但我最关心的是性能。 这将是很好的避免使用加入或索引访问，内存和速度的缘故。

Answer 1:

该data.table包，尤其是1.8.1版本开始，提供了很多的分区在SQL方面的功能。 rank(x, ties.method = "min")中的R是类似于Oracle RANK()并有使用因子（如下所述）以模拟的方式DENSE_RANK()函数。 以模仿的方式ROW_NUMBER应该在年底明显。

这里有一个例子：加载最新版本的data.table从R-锻造：

install.packages("data.table",
  repos= c("http://R-Forge.R-project.org", getOption("repos")))

library(data.table)

创建一些示例数据：

set.seed(10)

DT<-data.table(ID=seq_len(4*3),group=rep(1:4,each=3),value=rnorm(4*3),
  info=c(sample(c("a","b"),4*2,replace=TRUE),
  sample(c("c","d"),4,replace=TRUE)),key="ID")

> DT
    ID group       value info
 1:  1     1  0.01874617    a
 2:  2     1 -0.18425254    b
 3:  3     1 -1.37133055    b
 4:  4     2 -0.59916772    a
 5:  5     2  0.29454513    b
 6:  6     2  0.38979430    a
 7:  7     3 -1.20807618    b
 8:  8     3 -0.36367602    a
 9:  9     3 -1.62667268    c
10: 10     4 -0.25647839    d
11: 11     4  1.10177950    c
12: 12     4  0.75578151    d

排序每个ID通过减小value内group （注意-在前面value来表示递减顺序）：

> DT[,valRank:=rank(-value),by="group"]
    ID group       value info valRank
 1:  1     1  0.01874617    a       1
 2:  2     1 -0.18425254    b       2
 3:  3     1 -1.37133055    b       3
 4:  4     2 -0.59916772    a       3
 5:  5     2  0.29454513    b       2
 6:  6     2  0.38979430    a       1
 7:  7     3 -1.20807618    b       2
 8:  8     3 -0.36367602    a       1
 9:  9     3 -1.62667268    c       3
10: 10     4 -0.25647839    d       3
11: 11     4  1.10177950    c       1
12: 12     4  0.75578151    d       2

对于DENSE_RANK()与价值的关系暂时排名，你可以在值转换为一个因素，然后返回底层的整数值。 例如，排名每个ID基于info内group （比较infoRank与infoRankDense ）：

DT[,infoRank:=rank(info,ties.method="min"),by="group"]
DT[,infoRankDense:=as.integer(factor(info)),by="group"]

R> DT
    ID group       value info valRank infoRank infoRankDense
 1:  1     1  0.01874617    a       1        1             1
 2:  2     1 -0.18425254    b       2        2             2
 3:  3     1 -1.37133055    b       3        2             2
 4:  4     2 -0.59916772    a       3        1             1
 5:  5     2  0.29454513    b       2        3             2
 6:  6     2  0.38979430    a       1        1             1
 7:  7     3 -1.20807618    b       2        2             2
 8:  8     3 -0.36367602    a       1        1             1
 9:  9     3 -1.62667268    c       3        3             3
10: 10     4 -0.25647839    d       3        2             2
11: 11     4  1.10177950    c       1        1             1
12: 12     4  0.75578151    d       2        2             2

PS喜马修Dowle。

---

超前和滞后

对于模仿超前和滞后，开始与提供的答案在这里 。 我将创建一个基于ID的组内的顺序上排名变量。 这不会是必要的与上面的假数据，但如果ID是不按顺序组中，那么这将让生活有点难度。 因此，这里是与非顺序编号一些新的假数据：

set.seed(10)

DT<-data.table(ID=sample(seq_len(4*3)),group=rep(1:4,each=3),value=rnorm(4*3),
  info=c(sample(c("a","b"),4*2,replace=TRUE),
  sample(c("c","d"),4,replace=TRUE)),key="ID")

DT[,idRank:=rank(ID),by="group"]
setkey(DT,group, idRank)

> DT
    ID group       value info idRank
 1:  4     1 -0.36367602    b      1
 2:  5     1 -1.62667268    b      2
 3:  7     1 -1.20807618    b      3
 4:  1     2  1.10177950    a      1
 5:  2     2  0.75578151    a      2
 6: 12     2 -0.25647839    b      3
 7:  3     3  0.74139013    c      1
 8:  6     3  0.98744470    b      2
 9:  9     3 -0.23823356    a      3
10:  8     4 -0.19515038    c      1
11: 10     4  0.08934727    c      2
12: 11     4 -0.95494386    c      3

然后让前面的1个记录的值，使用group和idRank变量和减去1从idRank并使用multi = 'last'的说法。 从上面的两个条目记录中获得的价值，减去2 。

DT[,prev:=DT[J(group,idRank-1), value, mult='last']]
DT[,prev2:=DT[J(group,idRank-2), value, mult='last']]

    ID group       value info idRank        prev      prev2
 1:  4     1 -0.36367602    b      1          NA         NA
 2:  5     1 -1.62667268    b      2 -0.36367602         NA
 3:  7     1 -1.20807618    b      3 -1.62667268 -0.3636760
 4:  1     2  1.10177950    a      1          NA         NA
 5:  2     2  0.75578151    a      2  1.10177950         NA
 6: 12     2 -0.25647839    b      3  0.75578151  1.1017795
 7:  3     3  0.74139013    c      1          NA         NA
 8:  6     3  0.98744470    b      2  0.74139013         NA
 9:  9     3 -0.23823356    a      3  0.98744470  0.7413901
10:  8     4 -0.19515038    c      1          NA         NA
11: 10     4  0.08934727    c      2 -0.19515038         NA
12: 11     4 -0.95494386    c      3  0.08934727 -0.1951504

为LEAD，添加适当的偏移到idRank变量，并切换到multi = 'first' ：

DT[,nex:=DT[J(group,idRank+1), value, mult='first']]
DT[,nex2:=DT[J(group,idRank+2), value, mult='first']]

    ID group       value info idRank        prev      prev2         nex       nex2
 1:  4     1 -0.36367602    b      1          NA         NA -1.62667268 -1.2080762
 2:  5     1 -1.62667268    b      2 -0.36367602         NA -1.20807618         NA
 3:  7     1 -1.20807618    b      3 -1.62667268 -0.3636760          NA         NA
 4:  1     2  1.10177950    a      1          NA         NA  0.75578151 -0.2564784
 5:  2     2  0.75578151    a      2  1.10177950         NA -0.25647839         NA
 6: 12     2 -0.25647839    b      3  0.75578151  1.1017795          NA         NA
 7:  3     3  0.74139013    c      1          NA         NA  0.98744470 -0.2382336
 8:  6     3  0.98744470    b      2  0.74139013         NA -0.23823356         NA
 9:  9     3 -0.23823356    a      3  0.98744470  0.7413901          NA         NA
10:  8     4 -0.19515038    c      1          NA         NA  0.08934727 -0.9549439
11: 10     4  0.08934727    c      2 -0.19515038         NA -0.95494386         NA
12: 11     4 -0.95494386    c      3  0.08934727 -0.1951504          NA         NA

Answer 2:

从data.table v1.9.5+ ，功能frank() 快速排序）已经实施。 frank()是在交互的场景，其中，作为有用frankv()允许容易地与编程。

它实现了在现有的每个操作base::rank 。 此外，其优点是：

• frank()上列表中 ，data.frames和data.tables除了原子向量操作。

• 我们可以指定，对于每一列，无论是排名应该增加或减少的顺序进行计算。

• 它还实现等级型dense除了其他类型的base 。

• 您可以使用-通过减少订单中的字符列，以及军阶。

下面是一个使用相同的data.table上述所有点的例示DT从@BenBarnes'（优）柱。

数据：

require(data.table)
set.seed(10)
sample_n <- function(x, n) sample(x, n, replace=TRUE)
DT <- data.table(
        ID = seq_len(4*3),
        group = rep(1:4,each=3),
        value = rnorm(4*3),
        info = c(sample_n(letters[1:2], 8), sample_n(letters[3:4], 4)))

在单柱：

• 计算dense排名：

   DT[, rank := frank(value, ties.method="dense"), by=group] 

您还可以使用其他方法min ， max ， random ， average和first 。

• 在递减顺序：

   DT[, rank := frank(-value, ties.method="dense"), by=group] 

• 使用frankv ，类似frank ：

   # increasing order frankv(DT, "value", ties.method="dense") # decreasing order frankv(DT, "value", order=-1L, ties.method="dense") 

在多列

您可以使用.SD ，代表数据的子集 ，包含对应于该组数据。 见介绍data.table HTML小品更多关于.SD 。

• 通过等级info, value列，同时通过分组group ：

   DT[, rank := frank(.SD, info, value, ties.method="dense"), by=group] 

• 使用-指定递减顺序：

   DT[, rank := frank(.SD, info, -value, ties.method="dense"), by=group] 

• 你也可以使用-直接在字符列

   DT[, rank := frank(.SD, -info, -value, ties.method="dense"), by=group] 

您可以使用frankv类似，并提供列cols参数，并通过该列应使用排名顺序order参数。

---

小基准与比较base::rank ：

set.seed(45L)
x = sample(1e4, 1e7, TRUE)
system.time(ans1 <- base::rank(x, ties.method="first"))
#    user  system elapsed 
#  22.200   0.255  22.536 
system.time(ans2 <- frank(x, ties.method="first"))
#    user  system elapsed 
#   0.745   0.014   0.762 
identical(ans1, ans2) # [1] TRUE

Answer 3:

我喜欢data.table尽可能未来的家伙，但它并不总是必要的。 data.table总是会更快，但即使是中等大的数据集，如果组的数量相当小，plyr将仍然执行充分。

什么班·巴恩斯使用并data.table S可刚做尽可能紧凑（但正如我之前在很多情况下可能较慢注意）使用plyr：

library(plyr)                
ddply(DT,.(group),transform,valRank = rank(-value))
ddply(DT,.(group),transform,valRank = rank(info,ties.method = "min"),
                            valRankDense = as.integer(factor(info)))

即使没有在所有加载一个额外的包：

do.call(rbind,by(DT,DT$group,transform,valRank = rank(-value)))
do.call(rbind,by(DT,DT$group,transform,valRank = rank(info,ties.method = "min"),
                                        valRankDense = as.integer(factor(info))))

虽然你失去了一些细微的语法在这最后的情况。

Answer 4:

我不认为有一个直接等同于Oracle的分析功能。 Plyr将有可能能够实现某些分析功能，但并不是所有的直接。 我敢肯定，R可以单独复制的每个功能，但我不认为有一个单一的包，会做这一切。

如果您需要在R键实现特定的操作，然后做一些google搜索，如果你拿出空的，在这里问一个具体问题在计算器上。

文章来源: How to emulate SQL “partition by” in R?