SQL-Server-R-Services-Samples/FraudDetection/R/03-create-risk-table.R at master · MsSQLGirl/SQL-Server-R-Services-Samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
####################################################################################################
## Compute context
####################################################################################################
connection_string <- "Driver=SQL Server;
Server=[Server name];
Database=[Database name];
UID=[User name];
PWD=[Password]"
sql_share_directory <- paste("c:\\AllShare\\", Sys.getenv("USERNAME"), sep = "")
dir.create(sql_share_directory, recursive = TRUE)
sql <- RxInSqlServer(connectionString = connection_string,
                     shareDir = sql_share_directory)
local <- RxLocalSeq()

###################################################################################################
## create risk table
###################################################################################################
create_risk_table <- function(in_table, out_table){
  library(data.table)
  data <- rxImport(in_table)
  Calc_single_Risks <- function(dataframe, xcolumn, label, smooth1, smooth2){

    newname = make.names(xcolumn)

    DT <- data.table(dataframe) # make the data frame as a data.table object

    setnames(DT, xcolumn, newname)

    #frd_count <- DT[, sum(label), by=columns]
    #caution: please exclude pre-fraud with label =2
    #equivalent to the sql language: select sum(label) as frd_count from DT group by xcolumn
    #calculate fraud count for each level of a particular column
    cmdStr = sprintf("frd_count <- DT[, sum(%s), by=%s]", label, newname)
    eval(parse(text=cmdStr))

    #nf_count <- DT[, sum(label), by=columns]
    #calculate non-fraud count for each level of a particular column
    cmdStr = sprintf("nf_count <- DT[, sum(1-%s), by=%s]", label, newname)
    eval(parse(text=cmdStr))

    #merge fraud count and non-fraud count by each level name
    Merge_Table <- merge(x=frd_count, y=nf_count, by=newname, all=TRUE, sort=TRUE)
    Merge_Table <- as.data.frame(Merge_Table)

    #smoothed fraud rate of each level
    Odds <- (Merge_Table['V1.x']+smooth1)/(Merge_Table['V1.y'] + Merge_Table['V1.x'] +smooth2)
    #log of smoothed odds ratio
    risk <- log(Odds/(1-Odds))

    #generate the key: xcolumn_levelname
    key = paste(xcolumn,Merge_Table[,newname], sep="_")
    Risk_Frame <- cbind(key,risk)
    colnames(Risk_Frame) <- c("key", "risk")

    return(Risk_Frame)
  }

  Calc_Risks<-function(ycolname, xcolnames, dataset1)
  {
    data.set =data.frame()
    for(name in xcolnames)
    {
      risk.tab.data <- Calc_single_Risks(dataset1,name,ycolname, 10, 100)
      data.set= rbind(data.set, risk.tab.data)
    }
    return(data.set)
  }

  ycolname <- "Label"
  #assume all columns except the  target column will create risk table
  risk_vars <- c("transactionCurrencyCode", "localHour", "ipState", "ipPostcode",
                 "ipCountryCode", "browserLanguage", "shippingPostalCode", "shippingState",
                 "shippingCountry", "accountPostalCode", "accountState", "accountCountry",
                 "paymentBillingPostalCode", "paymentBillingState",
                 "paymentBillingCountryCode", "Label")
  xcolnames = risk_vars[risk_vars != ycolname]
  subset_data <- subset(data, select = risk_vars)
  calculated_risks <- Calc_Risks(ycolname,xcolnames,subset_data)

  data <- calculated_risks
  #data_file_path <- file.path(tempdir(), "data.csv")
  #write.csv(x = data,
  #         file = data_file_path,
  #         row.names = FALSE)
  #data_text <- RxTextData(file = data_file_path)
  rxDataStep(inData = data,
             outFile = out_table,
             overwrite = TRUE)
}

rxSetComputeContext(sql)
clean_training_table <- RxSqlServerData(table = "clean_training",
                                        connectionString = connection_string)
risk_table <- RxSqlServerData(table = "risk_table",
                              connectionString = connection_string)
rxExec(create_risk_table,
       in_table = clean_training_table,
       out_table = risk_table)
####################################################################################################
## Cleanup
####################################################################################################
rm(list = ls())