1.问题背景
数学上除法除数为0是不允许的，所以在数据库设计时，有的数据库会对这种除数为0做特殊的处理，MySQL是当除数为0时结果为null
MySQL处理
但是在PostgreSQL中会报错
PostgreSQL处理
我们在做即席查询时，需要建计算字段，底层数据库是PostgreSQL，而计算字段的公式可以是任意的，可以是表中任意字段的组合，可以加减乘除或者函数等，而且建立这个计算字段是客户设置的，客户不一定能考虑到除数不能为有可能是0的字段，所以遇到这种情况可能出现报错。
例如表A中有字段fd1,fd2，建立计算字段fd3，公式为fd1/fd2,那么整个的查询sql为：
select fd1,fd2,(fd1/fd2) as fd3 from A;
在正常情况下该语句是没有问题的，但是当fd2出现为0的值时，整个sql执行会报错，而且这个公式不是固定的，客户可以随时改动。
但是不管是MySQL还是PostgreSQL，当除数为null时，都做了特殊处理，返回都是null
MySQL除数为null处理
PostgreSQL除数为null处理
所以我们的解决思路就是当除数为0时将其替换成null即可。
2.解决思路
方案1：POSTSQL 自定义函数
CREATE OR REPLACE FUNCTION DIV(NUMERIC,NUMERIC)
RETURNS NUMERIC AS $BODY$
SELECT CASE WHEN $2 = 0 THEN null ELSE $1/$2 END;
$BODY$
LANGUAGE SQL;
前端交互不用改变，客户还是配置公式fd1/fd2,在后端进行处理，后端在解析/运算符时，替换成DIV函数
fd1/fd2 -> DIV(fd1,fd2)
难点：fd1和fd2如果是嵌套这种复杂的，解析这个公式并且识别到除数和被除数，并且进行替换，整个过程就不是一个简单的事情，所以这种方案不合适。
方案2：配置分母时需要增加函数
NULLIF函数
NULLIF(arg1,arg2)
函数解析：如果两个参数相等返回null,否则返回第一个参数，这个函数可以把0替换成null
1.前端交互需要修改，客户在配置计算字段时，如果该字段配置成了除数并且可能存在为0的情况，那么分母必须是要加NULLIF
[fd1]/NULLIF([fd2],0)
缺点：需要客户去控制，但是客户不一定知道这个限制，出错的可能性很大。
2.前端不用改变，后端解析出分母后进行替换
难点：解析出分母比较复杂，要考虑的场景较多，可能会有各种隐藏的bug
方案3：自定义操作符
结合方案1，因为PostgreSQL是可以自定义操作符的
create operator // (procedure=DIV, leftarg=numeric, rightarg=numeric);
前端交互不用改变，后端在解析/运算符时，替换成自定义的符号(这个符号可以任意定义，但是不要和其他符号冲突)，这样在sql语句中，解析到这样的符号就可以按照自定义的操作进行
问题：自定义的操作符是不满足加减乘除等运算规律的（先乘除后加减），所以这种方案是不可行的。
3.解决方案
其实解决思路不外部从以下几点出发
1.需要有框架能解析公式，能解析出是除法运算operator和左右表达式；
2.通过对右边表达式NULLIF包装，NULLIF(fd2,0)，所以包装后的表达式应该为：fd1/NULLIF(fd2,0)，整个sql变为了：
select fd1,fd2,(fd1/NULLIF(fd2,0)) as fd3 from A;
3.一些其他细节的考虑
表达式解析框架使用druid
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
具体代码直接贴出来，仅供参考：
public static void main(String[] args) {
PGExprParser parser = new PGExprParser("sum(cast(a/b as varchar))");
SQLExpr sqlExpr = parser.expr();
System.out.println(resolveExpr(sqlExpr));
}
private static String resolveExpr(SQLExpr sqlExpr) {
// 复合表达式，需要解析子节点
if (sqlExpr instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr opExpr = (SQLBinaryOpExpr) sqlExpr;
String resolvedSql = resolveComposition(opExpr);
return resolvedSql;
}
// sql表达式存在方法调用
else if (sqlExpr instanceof SQLMethodInvokeExpr) {
SQLMethodInvokeExpr expr = (SQLMethodInvokeExpr) sqlExpr;
StringBuffer buf = new StringBuffer();
if (expr.getOwner() != null) {
expr.getOwner().output(buf);
buf.append(".");
}
buf.append(expr.getMethodName());
buf.append("(");
for (int i = 0, size = expr.getArguments().size(); i < size; ++i) {
if (i != 0) {
buf.append(",");
}
buf.append(resolveExpr(expr.getArguments().get(i)));
}
buf.append(")");
return buf.toString();
}
// 存在cast函数的方法调用
else if (sqlExpr instanceof SQLCastExpr) {
SQLCastExpr sqlCastExpr = (SQLCastExpr) sqlExpr;
SQLExpr childExpr = sqlCastExpr.getExpr();
StringBuffer buf = new StringBuffer();
buf.append("cast (");
buf.append(resolveExpr(childExpr));
buf.append(" as ").append(sqlCastExpr.getDataType().getName()).append(")");
return buf.toString();
}
else {
return processOutput(sqlExpr);
}
}
private static String resolveComposition(SQLBinaryOpExpr opExpr) {
SQLBinaryOperator operator = opExpr.getOperator();
SQLExpr left = opExpr.getLeft();
SQLExpr right = opExpr.getRight();
if (SQLBinaryOperator.Divide == operator) {
return surroundByBrackets(resolveExpr(left) + operator.name + surroundByNULLIfZero(resolveExpr(right)));
} else {
return surroundByBrackets(resolveExpr(left) + operator.name + resolveExpr(right));
}
}
/**
* @param sql 必须是数值类型
*/
public static String surroundByNULLIfZero(String sql) {
return "NULLIF(" + sql + ",0)";
}
/**
* @param sql 必须是字符串类型
*/
public static String surroundByNULLIfBlank(String sql) {
return "NULLIF(" + sql + ",'')";
}
/**
* 把sql用小括号包围并返回
*/
public static String surroundByBrackets(String sql) {
return "(" + sql + ")";
}
private static String processOutput(SQLExpr sqlExpr) {
StringBuffer buf = new StringBuffer();
sqlExpr.output(buf);
return buf.toString();
}
运行结果：
sum(cast ((a/NULLIF(b,0)) as varchar))
网友评论