Как эмулировать SQL-раздел "по-русски" в R?

Ответ 1

Пакет data.table, особенно начиная с версии 1.8.1, предлагает большую часть функциональности раздела в терминах SQL. rank(x, ties.method = "min") в R похож на Oracle RANK(), и существует способ использования факторов (описанных ниже) для имитации функции DENSE_RANK(). Способ имитировать ROW_NUMBER должен быть очевиден до конца.

Вот пример: Загрузите последнюю версию data.table из R-Forge:

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

p.s. Привет, Мэтью Доуль.

LEAD и LAG

Для имитации LEAD и LAG начните с ответа здесь. Я бы создал переменную ранга на основе порядка идентификаторов внутри групп. Это не было бы необходимо с поддельными данными, как указано выше, но если идентификаторы не находятся в последовательном порядке внутри групп, это может сделать жизнь немного сложнее. Итак, вот некоторые новые поддельные данные с непоследовательными идентификаторами:

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

Ответ 2

В data.table v1.9.5+ реализована функция frank() (для быстрого ранга). frank() полезен в интерактивных сценариях, где frankv() позволяет легко программировать с помощью.

Он реализует каждую операцию, доступную в base::rank. Кроме того, преимущества следующие:

• frank() работает в списке, data.frames и data.tables в дополнение к атомным векторам.

• Мы можем указать для каждого столбца, следует ли вычислять ранг по возрастанию или убыванию.

• Он также реализует тип ранга dense в дополнение к другим типам в base.

• Вы можете использовать - в столбце символов, чтобы ранжировать по порядку уменьшения.

Вот иллюстрация всех вышеперечисленных пунктов, используя те же самые данные. 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 rank:

  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 vignette для более подробной информации о .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

Ответ 3

Мне нравится data.table столько же, сколько следующий парень, но это не всегда необходимо. data.table всегда будет быстрее, но даже для умеренно больших наборов данных, если количество групп довольно невелико, plyr будет по-прежнему работать адекватно.

Что BenBarnes сделал с помощью data.table, можно сделать так же компактно (но, как я уже отмечал раньше, возможно, более медленным во многих случаях) с помощью 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))))

хотя вы теряете некоторые синтаксические тонкости в этом последнем случае.

Ответ 4

Я не думаю, что существует прямой эквивалент функций Oracle Analytic. Вероятно, Плир сможет достичь некоторых аналитических функций, но не всех непосредственно. Я уверен, что R может реплицировать каждую функцию отдельно, но я не думаю, что есть один пакет, который сделает все это.

Если вам нужна определенная операция, которую вы должны выполнить в R, тогда выполните некоторые действия в поисковой системе, и если вы выберете пустой, задайте конкретный вопрос здесь, в StackOverflow.