1. SUMPRODUCT函数
主要可用于计算加权和
2.函数用法
SUMPRODUCT(array1, [array2])
在给定的数组中,将数组间对应的元素相乘,并返回乘积之和。
-
array: 可选。多个数字数组。
3. 函数示例
主要可用于计算加权和。例如:SUMPRODUCT([1,2,3],[0.1,0.2,0.3]),相当于 1×0.1 + 2×0.2 + 3×0.3=1.4
4. 代码实战
首先我们在function包下创建math包,在math包下创建SumProductFunction类,代码如下:
package com.ql.util.express.self.combat.function.math;
import com.ql.util.express.Operator;
import com.ql.util.express.self.combat.exception.FormulaException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;
import java.util.regex.Pattern;
/**
* 类描述:SUMPRODUCT函数
*
* @author admin
* @version 1.0.0
* @date 2023/11/24 10:57
*/
public class SumProductFunction extends Operator {
public SumProductFunction(String name) {
this.name = name;
}
@Override
public Object executeInner(Object[] lists) throws Exception {
if (lists.length == 0) {
throw new FormulaException("操作数异常");
}
// 判断数组中的内容是不是数字,若不是数字抛出异常
boolean checkNumeric = checkArgs(lists);
// 最终的数组在sum中
BigDecimal res = BigDecimal.ZERO;
if (checkNumeric) { //如果是数字,就进行加权求和
// 栈,先进后出
Stack<List<Object>> stack = new Stack<>();
for (int i=0;i<lists.length;i++) {
Object[] sub = (Object[])lists[i];
List<Object> stackList = new ArrayList<Object>();
for (int j=0;j<sub.length;j++) {
Object ele = sub[j];
stackList.add(ele);
}
stack.push(stackList);
}
// 取出来栈顶元素
List<Object> one = stack.pop();
List<Object> mulVal = new ArrayList<>();
Stack<List<Object>> sum = new Stack<>();
while (!stack.isEmpty()) {
List<Object> two = stack.pop();
List<Object> calVal = cal(two,one);
sum.push(calVal);
mulVal.addAll(calVal);
one = calVal;
}
List<Object> nums = sum.pop();
for (int i=0;i<nums.size();i++) {
res= res.add(new BigDecimal(nums.get(i)+""));
}
}
return res;
}
//判断数组中的内容是不是数字,若不是数字抛出异常
private boolean checkArgs(Object[] lists) throws FormulaException {
boolean rst = false;
for (int i=0;i<lists.length;i++) {
Object[] sub = (Object[])lists[i];
for (int j=0;j<sub.length;j++) {
Object ele = sub[j];
boolean isnum = StrHelper.isNumber(ele.toString());
if (!isnum) {
rst = isnum;
throw new FormulaException("数组内容非数字:"+ele);
} else {
rst = isnum;
}
}
}
return rst;
}
private List<Object> cal(List<Object> one,List<Object> two) {
List<Object> res = new ArrayList<>();
for (int i=0;i<one.size();i++) {
// 转成高精度
Object n1 = one.get(i);
BigDecimal bigDecimal1 = new BigDecimal(n1+"");
for (int j=0;j<two.size();j++) {
Object n2 = two.get(j);
BigDecimal bigDecimal2 = new BigDecimal(n2+"");
if (i == j) {
BigDecimal n3 = bigDecimal1.multiply(bigDecimal2);
res.add(n3);
}
}
}
return res;
}
static class StrHelper {
public static boolean isNumber(String text) {
// 正则表达式匹配数字的格式
String numberPattern = "^[+-]?\\d+(\\.\\d+)?(E\\d+)?$";
// 判断文本是否匹配数字的格式
boolean isMatch = Pattern.matches(numberPattern, text);
return isMatch;
}
}
}
把SumProductFunction类注册到公式函数入口类中,代码如下:
package com.ql.util.express.self.combat.ext;
import com.ql.util.express.ExpressRunner;
import com.ql.util.express.IExpressResourceLoader;
import com.ql.util.express.parse.NodeTypeManager;
import com.ql.util.express.self.combat.function.logic.*;
import com.ql.util.express.self.combat.function.math.*;
/**
* 类描述: 仿简道云公式函数实战入口类
*
* @author admin
* @version 1.0.0
* @date 2023/11/21 15:29
*/
public class FormulaRunner extends ExpressRunner {
public FormulaRunner() {
super();
}
public FormulaRunner(boolean isPrecise, boolean isTrace) {
super(isPrecise,isTrace);
}
public FormulaRunner(boolean isPrecise, boolean isStrace, NodeTypeManager nodeTypeManager) {
super(isPrecise,isStrace,nodeTypeManager);
}
public FormulaRunner(boolean isPrecise, boolean isTrace, IExpressResourceLoader iExpressResourceLoader, NodeTypeManager nodeTypeManager) {
super(isPrecise,isTrace,iExpressResourceLoader,nodeTypeManager);
}
@Override
public void addSystemFunctions() {
// ExpressRunner 的内部系统函数
super.addSystemFunctions();
// 扩展公式函数
this.customFunction();
}
/***
* 自定义公式函数
*/
public void customFunction() {
// 逻辑公式函数
this.addLogicFunction();
// 数学公式函数
this.addMathFunction();
}
public void addLogicFunction() {
// AND函数
this.addFunction("AND",new AndFunction("AND"));
// IF函数
this.addFunction("IF",new IfFunction("IF"));
// IFS函数
this.addFunction("IFS",new IfsFunction("IFS"));
// XOR函数
this.addFunction("XOR",new XorFunction("XOR"));
// TRUE函数
this.addFunction("TRUE",new TrueFunction("TRUE"));
// FALSE函数
this.addFunction("FALSE",new FalseFunction("FALSE"));
// NOT函数
this.addFunction("NOT",new NotFunction("NOT"));
// OR函数
this.addFunction("OR",new OrFunction("OR"));
}
public void addMathFunction() {
// ABS函数
this.addFunction("ABS",new AbsFunction("ABS"));
// AVERAGE函数
this.addFunction("AVERAGE",new AvgFunction("AVERAGE"));
// CEILING函数
this.addFunction("CEILING",new CeilingFunction("CEILING"));
// RADIANS函数
this.addFunction("RADIANS",new RadiansFunction("RADIANS"));
// COS函数
this.addFunction("COS",new CosFunction("COS"));
// COT函数
this.addFunction("COT",new CotFunction("COT"));
// COUNT函数
this.addFunction("COUNT",new CountFunction("COUNT"));
// COUNTIF函数
this.addFunction("COUNTIF",new CountIfFunction("COUNTIF"));
// FIXED函数
this.addFunction("FIXED",new FixedFunction("FIXED"));
// FLOOR函数
this.addFunction("FLOOR",new FloorFunction("FLOOR"));
// INT函数
this.addFunction("INT",new IntFunction("INT"));
// LARGE函数
this.addFunction("LARGE",new LargeFunction("LARGE"));
// LOG函数
this.addFunction("LOG",new LogFunction("LOG"));
// MAX函数
this.addFunction("MAX",new MaxFunction("MAX"));
// MIN函数
this.addFunction("MIN",new MinFunction("MIN"));
// MOD函数
this.addFunction("MOD",new ModFunction("MOD"));
// POWER函数
this.addFunction("POWER",new PowerFunction("POWER"));
// PRODUCT函数
this.addFunction("PRODUCT",new ProductFunction("PRODUCT"));
// RAND函数
this.addFunction("RAND",new RandFunction("RAND"));
// ROUND函数
this.addFunction("ROUND",new RoundFunction("ROUND"));
// SIN函数
this.addFunction("SIN",new SinFunction("SIN"));
// SMALL函数
this.addFunction("SMALL",new SmallFunction("SMALL"));
// SQRT函数
this.addFunction("SQRT",new SqrtFunction("SQRT"));
// SUM函数
this.addFunction("SUM",new SumFunction("SUM"));
// SUMIF函数
this.addFunction("SUMIF",new SumIfFunction("SUMIF"));
// SUMIFS函数
this.addFunction("SUMIFS",new SumIfsFunction("SUMIFS"));
// SUMPRODUCT函数
this.addFunction("SUMPRODUCT",new SumProductFunction("SUMPRODUCT"));
}
}
创建测试用例
package com.ql.util.express.self.combat;
import com.ql.util.express.DefaultContext;
import com.ql.util.express.self.combat.ext.FormulaRunner;
import org.junit.Test;
/**
* 类描述: 实战测试类
*
* @author admin
* @version 1.0.0
* @date 2023/11/21 15:45
*/
public class CombatTest {
@Test
public void SUMPRODUCT() throws Exception{
FormulaRunner formulaRunner = new FormulaRunner(true,true);
// 创建上下文
DefaultContext<String, Object> context = new DefaultContext<>();
String express = "SUMPRODUCT([aa,bb,cc],[dd,ee,ff])";
context.put("aa",1);
context.put("bb",2);
context.put("cc",3);
context.put("dd",0.1);
context.put("ee",0.2);
context.put("ff",0.3);
Object object = formulaRunner.execute(express, context, null, true, true);
System.out.println(object);
}
}
运行结果