protected double cacluateGroupRSquare(
DataSet dataSet,
String tableName,
String labelName,
LinearRegressionModelDB model,
String groupValue,
StringBuilder sb_notNull)
throws OperatorException {
// String notnull=sb_notNull.toString().substring(beginIndex, endIndex);
// notnull.replace("where", " and ");
double RSquare = 0.0;
StringBuffer RSquareSQL = new StringBuffer();
StringBuffer avgSQL = new StringBuffer();
avgSQL
.append(" select avg(")
.append(labelName)
.append(") from ")
.append(tableName)
.append(sb_notNull)
.append(" and ")
.append(StringHandler.doubleQ(groupbyColumn))
.append("=")
.append(StringHandler.singleQ(groupValue));
double avg = 0.0;
try {
itsLogger.debug(classLogInfo + ".cacluateRSquare():sql=" + avgSQL);
ResultSet rs = st.executeQuery(avgSQL.toString());
if (rs.next()) {
avg = rs.getDouble(1);
}
} catch (SQLException e) {
itsLogger.error(e.getMessage(), e);
return Double.NaN;
}
StringBuffer predictedValueSQL = new StringBuffer();
predictedValueSQL.append(model.generatePredictedString(dataSet));
RSquareSQL.append("select 1 - sum((")
.append(predictedValueSQL)
.append("-")
.append(labelName)
.append(")*(")
.append(predictedValueSQL)
.append("-")
.append(labelName)
.append("))*1.0/sum((")
.append(labelName)
.append("-(")
.append(avg)
.append("))*(")
.append(labelName)
.append("-(")
.append(avg)
.append("))) from ")
.append(tableName)
.append(sb_notNull)
.append(" and ")
.append(StringHandler.doubleQ(groupbyColumn))
.append("=")
.append(StringHandler.singleQ(groupValue));
try {
itsLogger.debug("LinearRegressionImpPGGP.cacluateRSquare():sql=" + RSquareSQL);
ResultSet rs = st.executeQuery(RSquareSQL.toString());
if (rs.next()) {
RSquare = rs.getDouble(1);
}
} catch (SQLException e) {
itsLogger.error(e.getMessage(), e);
return Double.NaN;
}
return RSquare;
}
protected StringBuffer createSSQLLGroup(
DataSet dataSet,
String tableName,
Column label,
String[] columnNames,
HashMap<String, Double[]> coefficients2,
StringBuilder sb_notNull) {
StringBuffer predictedY = new StringBuffer("( case ");
StringBuffer countString = new StringBuffer(" (case ");
for (String groupValue : coefficients2.keySet()) {
predictedY
.append(" when ")
.append(StringHandler.doubleQ(groupbyColumn))
.append("=")
.append(StringHandler.singleQ(groupValue))
.append(" then ")
.append(coefficients.get(groupValue)[coefficients.get(groupValue).length - 1]);
for (int i = 0; i < columnNames.length; i++) {
predictedY
.append("+")
.append(coefficients2.get(groupValue)[i])
.append("*\"")
.append(columnNames[i])
.append("\"");
}
if (groupCount.get(groupValue) > (columnNames.length + 1)) {
countString
.append(" when ")
.append(StringHandler.doubleQ(groupbyColumn))
.append("=")
.append(StringHandler.singleQ(groupValue))
.append(" then ")
.append(groupCount.get(groupValue))
.append(" - ")
.append(columnNames.length + 1);
} else {
countString
.append(" when ")
.append(StringHandler.doubleQ(groupbyColumn))
.append("=")
.append(StringHandler.singleQ(groupValue))
.append(" then ")
.append(" null ");
dataErrorList.add(groupValue);
}
}
countString.append(" end )::double precision");
predictedY.append(" end ");
String labelName = StringHandler.doubleQ(label.getName());
predictedY.append(")");
StringBuffer sSQL = new StringBuffer("select sqrt(");
sSQL.append("sum((")
.append(labelName)
.append(" - ")
.append(predictedY)
.append(")*1.0*(")
.append(labelName)
.append(" - ")
.append(predictedY)
.append("))/")
.append("( ")
.append(countString)
.append(")");
sSQL.append("),")
.append(StringHandler.doubleQ(groupbyColumn))
.append(" from ")
.append(tableName)
.append(" ")
.append(sb_notNull)
.append(" group by ")
.append(StringHandler.doubleQ(groupbyColumn));
return sSQL;
}
