oracle转mysql过程中的sum over处理

项目需要，原来的系统很多的统计分析，被要求把数据库从oracle转为mysql，这是很坑的，mysql的统计分析比较弱，很多oracle有的函数，mysql没有，需要想方设法改造或者编写函数实现。

在修改的过程中，很多小的问题点都解决了，就剩下一个sum over的实现。sum over用来做统计总数很好用，省得再写一遍或者嵌套一层。

由于代码中的很多求总和的字段输出的行数不多，最后考虑直接用java的反射机制来简单实现oracle中sum over统计。代码没考虑太多通用性，只是根据自己的需求编写的，实现如下：

package xx.xx.xx.utils;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.List;import org.apache.log4j.Logger;import xx.xx.xx.stat.vmodel.FlowSortData;public class O2mUtils {private static final Logger logger = Logger.getLogger(Thread            .currentThread().getStackTrace()[1].getClassName());private static String format = "%.0f";/** * 设置保留2位小数，每次使用前调用，用完恢复 */public static void setFormat() {format = "%.2f";}private static void resetFormat() {format = "%.0f";}/** * 获取get方法、set方法 * @param o 获取的类对象 * @param fieldFrom get字段名 * @param FieldTo set字段名 * @return */private static Method[] getMethod(Object o, String ...strings) {int length = strings.length;Method[] methods = new Method[length];try {Class<?> clazz = o.getClass();Field fields[] = clazz.getDeclaredFields();for(Field f : fields) {if(f.getName().equals(strings[0])) {String fieldName = f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);methods[0] = clazz.getMethod("get" + fieldName);}if(f.getName().equals(strings[1])) {String fieldName = f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);Class<?> paramType = f.getType();methods[1] = clazz.getMethod("set" + fieldName, paramType);}if(length == 3 && f.getName().equals(strings[2])) {// 获取get方法String fieldName = f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);methods[2] = clazz.getMethod("get" + fieldName);}}} catch (Exception e) {logger.error("反射获取方法异常", e);}return methods;}/** * oracle的sum over() 累加实现 * @param list 设置列表 * @param fieldFrom 累加字段 * @param fieldTo 累加和字段 */public static void sum_over(List<?> list, String fieldFrom, String fieldTo) {Method[] getField = getMethod(list.get(0), fieldFrom, fieldTo);if(getField != null && getField.length == 2) {try {Double total = 0.0;int i = 0;for(; i < list.size(); i++) {Double value = Double.valueOf(getField[0].invoke(list.get(i)).toString());total += value;}for(Object o : list) {getField[1].invoke(o, new Object[]{String.format(format, total)});}} catch (Exception e) {logger.error("取值、赋值异常", e);}}resetFormat();}/** * oracle的sum over(order by) 累加实现 * @param list 设置列表 * @param fieldFrom 累加字段 * @param fieldTo 累加和字段 * @param reverse 正序反序 */public static void sum_over_order(List<?> list, String fieldFrom, String fieldTo, boolean reverse) {Method[] getField = getMethod(list.get(0), fieldFrom, fieldTo);if(getField != null && getField.length == 2) {try {int size = list.size();Double[] total = new Double[size];int i = 0, j = 0;for(; i < size; i++) {total[i] = 0.0;}if(reverse){for(i = 0; i < size; i++) {for(j = i; j < size; j++) {Double value = Double.valueOf(getField[0].invoke(list.get(i)).toString());total[j] += value;}}}else {for(i = size -1; i >= 0; i--) {for(j = i; j >= 0; j--) {Double value = Double.valueOf(getField[0].invoke(list.get(i)).toString());total[j] += value;}}}for(i = 0; i < size; i++) {getField[1].invoke(list.get(i), new Object[]{String.format(format, total[i])});}} catch (Exception e) {logger.error("取值、赋值异常", e);}}resetFormat();}/** * oracle的sum over(partition by) 累加实现 * @param list 设置列表 * @param fieldPartition partition by 字段 * @param fieldFrom 累加字段 * @param fieldTo 累加和字段 */public static void sum_over_partition(List<?> list, String fieldPartition, String fieldFrom, String fieldTo) {Method[] getField = getMethod(list.get(0), fieldFrom, fieldTo, fieldPartition);if(getField != null && getField.length == 3) {try {int size = list.size();int i = 0, j = 0, strPos = 0;Double total = 0.0;String key = null;for(; i < size; i++) {if(key == null) {key = getField[2].invoke(list.get(i)).toString();}if(!getField[2].invoke(list.get(i)).toString().equals(key)) { // 遇到下一个累加for(j = strPos; j < i; j++) {getField[1].invoke(list.get(j), new Object[]{String.format(format, total)});}key = getField[2].invoke(list.get(i)).toString();total = 0.0;strPos = i;}Double value = Double.valueOf(getField[0].invoke(list.get(i)).toString());total += value;}for(j = strPos; j < i; j++) {getField[1].invoke(list.get(j), new Object[]{String.format(format, total)});}} catch (Exception e) {logger.error("取值、赋值异常", e);}}resetFormat();}public static void main(String[] args) {List<FlowSortData> list = new ArrayList<FlowSortData>();int i = 0;for(i = 0; i < 5; i++) {FlowSortData d = new FlowSortData();d.setNettype("1111");d.setFlow("100");list.add(d);}for(i = 0; i < 5; i++) {FlowSortData d = new FlowSortData();d.setNettype("2222");d.setFlow("150");list.add(d);}for(i = 0; i < 5; i++) {FlowSortData d = new FlowSortData();d.setNettype("3333");d.setFlow("50");list.add(d);}//O2mUtils.sum_over(list, "flow", "totalflow");//O2mUtils.sum_over_order(list, "flow", "totalflow", false);O2mUtils.setFormat();O2mUtils.sum_over_partition(list, "nettype", "flow", "totalflow");for(FlowSortData o : list) {System.out.println(o.getNettype() + " , " + o.getFlow() + " , " +o.getTotalflow());}}}

package xx.xx.xx.stat.vmodel;public class FlowSortData {private String nettype;private String flow;private String totalflow;public String getNettype() {return nettype;}public void setNettype(String nettype) {this.nettype = nettype;}public String getFlow() {return flow;}public void setFlow(String flow) {this.flow = flow;}public String getTotalflow() {return totalflow;}public void setTotalflow(String totalflow) {this.totalflow = totalflow;}}