有关Quick BI中lod_fixed函数中以MAX()作为过滤条件报错

一、Quick BI中的lod_fixed函数

lod_fixed{维度1[,维度2]...:聚合表达式[:过滤条件]}

作用：使用指定维度进行计算而不引用任何其他维度。其中，

1. 维度1[,维度2]...：声明维度，指定聚合表达式要连接到的一个或多个维度。使用逗号分隔各个维度。
2. 聚合表达式：聚合表达式是所执行的计算，用于定义目标维度。
3. 过滤条件：可以通过对前面的声明维度设定条件进行过滤，非必须。

二、问题描述

在quick bi数据集中新建计算字段“月度总结存数量= lod_fixed{[月份]:SUM([结存数量]):[月份]=MAX([月份])} ”。

???????? 在quick bi 仪表板中添加指标卡，并将“月度总结存数量”设为看板指标，结果数据查询失败。

报错详情

traceId:afe791fd-19fc-4928-b853-87066950cb28;

errMsg:数据源执行SQL失败:java.lang.RuntimeException: SQL execute error by datasource... java.sql.SQLException: Invalid use of group function com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) io.grpc.stub.ServerCalls$UnaryServerCallHandler$UnaryServerCallListener.onHalfClose(ServerCalls.java:182)

三、原因分析

查看quick bi中的SQL代码：

SELECT

? LOD_TP_0.`LOD_04537845B26433` AS LOD_0

FROM

? (

??? SELECT

????? '-' AS T_ALA_2_

??? FROM

????? `terminaldata2`.`account` AS ATN_T_1_

??? LIMIT

????? 0, 1

? ) AS LOD_TM

? CROSS JOIN (

??? SELECT

????? SUM(LOD_TR.`LOD_measure_result`) AS LOD_04537845B26433

??? FROM

????? (

??????? SELECT

????????? ATN_T_1_.`月份` AS LOD_2C07D23FC7BBAA

??????? FROM

????????? `terminaldata2`.`account` AS ATN_T_1_

??????? GROUP BY

????????? ATN_T_1_.`月份`

????? ) AS LOD_TL

????? INNER JOIN (

??????? SELECT

????????? SUM(

??????????? ATN_T_1_.`结存数量`

????????? ) AS LOD_measure_result,

????????? ATN_T_1_.`月份` AS LOD_2C07D23FC7BBAA

??????? FROM

????????? `terminaldata2`.`account` AS ATN_T_1_

??????? WHERE

????????? ATN_T_1_.`月份` = MAX(ATN_T_1_.`月份`)

??????? GROUP BY

????????? ATN_T_1_.`月份`

????? ) AS LOD_TR ON LOD_TL.`LOD_2C07D23FC7BBAA` = LOD_TR.`LOD_2C07D23FC7BBAA`

? ) AS LOD_TP_0

LIMIT

? 0, 1

在SQL中由于where子句的执行顺序是在select子句的前面，所以不能将聚合函数直接用在where子句后面，而在第63~65行代码“WHERE ATN_T_1_.`月份` = MAX(ATN_T_1_.`月份`)”这句代码中where子句中直接使用了聚合函数，所以会导致错误产生。?

四、优化建议

??? 虽然在SQL中where子句后面不能直接跟聚合函数，但是可以通过子查询来解决，比如上面的代码“WHERE ATN_T_1_.`月份` = MAX(ATN_T_1_.`月份`)”改为“WHERE? ATN_T_1_.`月份` = (SELECT MAX( `terminaldata2`.`account` .`月份`) FROM? `terminaldata2`.`account` )”，再执行就可以了。

五、总结

在SQL中where子句后面不能直接跟聚合函数，但是可以通过子查询，在子查询中使用聚合函数。