Microsoft中的SQL/SAS等效代码-r

Question

我想在Rev R/Microsoft-r中转换我的一个Proc SQL/SAS代码

下面是我的示例代码

proc sql;
create table GENDER_YEAR as
select YEAR,GENDER,count(distinct CARD_NO) as CM_COUNT,sum(SPEND) as TOTAL_SPEND, sum(case when SPEND GT 0 then 1 else 0 end) as NO_OF_TRANS
from ABC group by YEAR,GENDER;
quit;

我尝试在Rev R中使用以下代码

library("RevoPemaR")
byGroupPemaObj <- PemaByGroup()

GENDER_cv_grouped <- pemaCompute(pemaObj = byGroupPemaObj, data = Merchant_Trans,groupByVar = "GENDER",computeVars = c("LOCAL_SPEND"),fnList = list(sum = list(FUN = sum, x = NULL)))

它一次只计算一件事，但我需要不同计数CARD_NO，SUM of segment和No of no zero行数作为每段年份和性别的Trans。

输出应如下所示

YEAR  GENDER CM_COUNT TOTAL_SPEND NO_OF_TRANS
YEAR1 M      23        120        119
YEAR1 F      21        110        110
YEAR2 M      20        121        121
YEAR2 F      35        111        109

期待这方面的帮助。

Answer 1

最简单的方法是将列连接到单个列中并使用该列。似乎大多数dplyrXdf和RevoPemaR都还不支持带2个变量的group by。

这样做的方法是在顶部添加一个rxDataStep，它首先创建这个变量，然后按它分组。其中的一些近似代码如下：

library("RevoPemaR")
byGroupPemaObj <- PemaByGroup()

rxDataStep(inData = Merchant_Trans, outFile = Merchant_Trans_Groups,
           transform = list(year_gender = paste(YEAR, GENDER,))

GENDER_cv_grouped <- pemaCompute(pemaObj = byGroupPemaObj,
    data = Merchant_Trans_Groups, groupByVar = "GENDER",
    computeVars = c("LOCAL_SPEND"),
    fnList = list(sum = list(FUN = sum, x = NULL)))

请注意，据我所知，总体上有3种方法可以在RevR中执行groupBy。每一个都有它的优点和缺点。

1. rxSplit -这实际上会为您想要的每个组创建不同的XDF文件。这可以与RAM一起使用，其中的因子指定哪些组应该是created.
2. RevoPemaR's PemaByGroup -这假设每个组的数据可以存储在splitByFactor中。这是一个合理的假设。它还需要按GroupBy列对原始Xdf文件进行排序。而且它只支持按1 column.
3. dplyrXdf's group_by分组--这是流行的dplyr包的翻版。它有许多变量操作方法-所以使用类似dplyr的语法编写rxSplit和rxDataStep的不同方式。它还仅支持1列进行分组。

这三种方法目前都只支持一个变量组操作。因此，它们都需要对数据进行一些预处理才能使用。

Answer 2

这里有一个使用dplyrXdf的简单解决方案。与数据帧不同，dplyr提供的n_distinct()汇总函数不适用于xdf文件，因此这是一个两步汇总:首先包含card_no作为分组变量，然后计算card_no的数量。

首先，生成一些示例数据：

library(dplyrXdf)     # also loads dplyr
set.seed(12345)

df <- expand.grid(year=2000:2005, gender=c("F", "M")) %>%
    group_by(year, gender) %>%
    do(data.frame(card_no=sample(20, size=10, replace=TRUE),
                  spend=rbinom(10, 1, 0.5) * runif(10) * 100))

xdf <- rxDataStep(df, "ndistinct.xdf", overwrite=TRUE)

现在调用summarise两次，利用第一个summarise将从分组变量列表中删除card_no这一事实：

smry <- xdf %>%
    mutate(trans=spend > 0) %>%
    group_by(year, gender, card_no) %>%
    summarise(n=n(), total_spend=sum(spend), no_of_trans=sum(trans)) %>%
    summarise(cm_count=n(), total_spend=sum(total_spend), no_of_trans=sum(no_of_trans))

as.data.frame(smry)    

   #year gender cm_count total_spend no_of_trans
#1  2000      F       10   359.30313           6
#2  2001      F        8   225.89571           3
#3  2002      F        7   332.58365           6
#4  2003      F        5   333.72169           5
#5  2004      F        7   280.90448           5
#6  2005      F        9   254.37680           5
#7  2000      M        8   309.77727           6
#8  2001      M        8   143.70835           2
#9  2002      M        8   269.64968           5
#10 2003      M        8   265.27049           4
#11 2004      M        9    99.73945           3
#12 2005      M        8   178.12686           6

验证这与在原始数据帧上运行dplyr链得到的结果(模数行排序)相同：

df %>%
    group_by(year, gender) %>%
    summarise(cm_count=n_distinct(card_no), total_spend=sum(spend), no_of_trans=sum(spend > 0)) %>%
    arrange(gender, year)

    #year gender cm_count total_spend no_of_trans
   #<int> <fctr>    <int>       <dbl>       <int>
#1   2000      F       10   359.30313           6
#2   2001      F        8   225.89571           3
#3   2002      F        7   332.58365           6
#4   2003      F        5   333.72169           5
#5   2004      F        7   280.90448           5
#6   2005      F        9   254.37680           5
#7   2000      M        8   309.77727           6
#8   2001      M        8   143.70835           2
#9   2002      M        8   269.64968           5
#10  2003      M        8   265.27049           4
#11  2004      M        9    99.73945           3
#12  2005      M        8   178.12686           6