有关Quick BI中当返回值为空值，行标题消失问题

一、Quick BI中的lod_ include函数

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

作用：将表达式中的维度一起作为分组依据进行订算。其中，

1)??? 维度1[,维度2]... ：声明维度，指定聚合表达式要连接到的一个或多个维度。使用逗号分隔各个维度。

2)??? 聚合表达式：聚合表达式是所执行的计算，用于定义目标维度。

3)??? 过滤条件：可以通过对前面的声明维度设定条件进行过滤，非必须。

二、问题描述

在Quick BI数据集中新建计算字段“退货订单数量= lod_include{[类型]:COUNT(distinct [流水号]):[类型]='退货单'}”

在quick BI仪表板中添加交叉表，以“月份”为行，以“退货订单数量”为列，结果数据返回为空。

既然返回为空，修改计算字段，当返回为空值时，指定返回为0，“退货订单数量=IFNULL(lod_include{[类型]:COUNT(distinct [流水号]):[类型]='退货单'},0)“，结果在仪表板中，数据返回仍然空。

三、原因分析

查看“退货订单数量= lod_include{[类型]:COUNT(distinct [流水号]):[类型]='退货单'}”计算时SQL代码：

SELECT

? LOD_TM.`LOD_2C07D23FC7BBAA` AS LOD_0,

? LOD_TP_0.`LOD_39C923F96BA15C` AS LOD_1

FROM

? (

??? SELECT

????? ASE_T_1_.`月份` AS LOD_2C07D23FC7BBAA

??? FROM

????? `terminaldata2`.`sales` AS ASE_T_1_

??? GROUP BY

????? ASE_T_1_.`月份`

??? ORDER BY

????? LOD_2C07D23FC7BBAA ASC

??? LIMIT

????? 0, 20

? ) AS LOD_TM

? INNER JOIN (

??? SELECT

????? LOD_TL.`LOD_2C07D23FC7BBAA` AS LOD_2C07D23FC7BBAA,

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

??? FROM

????? (

??????? SELECT

????????? ASE_T_1_.`月份` AS LOD_2C07D23FC7BBAA,

????????? ASE_T_1_.`类型` AS LOD_601A3BCEB54372

??????? FROM

????????? `terminaldata2`.`sales` AS ASE_T_1_

??????? GROUP BY

????????? ASE_T_1_.`月份`,

????????? ASE_T_1_.`类型`

????? ) AS LOD_TL

????? INNER JOIN (

??????? SELECT

????????? COUNT(

??????????? distinct ASE_T_1_.`流水号`

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

????????? ASE_T_1_.`月份` AS LOD_2C07D23FC7BBAA,

????????? ASE_T_1_.`类型` AS LOD_601A3BCEB54372

??????? FROM

????????? `terminaldata2`.`sales` AS ASE_T_1_

??????? WHERE

????????? ASE_T_1_.`类型` = '退货单'

??????? GROUP BY

????????? ASE_T_1_.`月份`,

????????? ASE_T_1_.`类型`

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

????? AND LOD_TL.`LOD_601A3BCEB54372` = LOD_TR.`LOD_601A3BCEB54372`

??? GROUP BY

????? LOD_TL.`LOD_2C07D23FC7BBAA`

? ) AS LOD_TP_0 ON LOD_TM.`LOD_2C07D23FC7BBAA` = LOD_TP_0.`LOD_2C07D23FC7BBAA`



SELECT

? COUNT(1) AS query_count

FROM

? (

??? SELECT

????? LOD_TM.`LOD_2C07D23FC7BBAA` AS LOD_0,

????? LOD_TP_0.`LOD_39C923F96BA15C` AS LOD_1

??? FROM

????? (

??????? SELECT

????????? ASE_T_1_.`月份` AS LOD_2C07D23FC7BBAA

??????? FROM

????????? `terminaldata2`.`sales` AS ASE_T_1_

??????? GROUP BY

????????? ASE_T_1_.`月份`

????? ) AS LOD_TM

????? INNER JOIN (

??????? SELECT

????????? LOD_TL.`LOD_2C07D23FC7BBAA` AS LOD_2C07D23FC7BBAA,

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

??????? FROM

????????? (

??????????? SELECT

????????????? ASE_T_1_.`月份` AS LOD_2C07D23FC7BBAA,

????????????? ASE_T_1_.`类型` AS LOD_601A3BCEB54372

??????????? FROM

????????????? `terminaldata2`.`sales` AS ASE_T_1_

??????????? GROUP BY

????????????? ASE_T_1_.`月份`,

????????????? ASE_T_1_.`类型`

????????? ) AS LOD_TL

???????? ?INNER JOIN (

??????????? SELECT

????????????? COUNT(

??????????????? distinct ASE_T_1_.`流水号`

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

????????????? ASE_T_1_.`月份` AS LOD_2C07D23FC7BBAA,

????????????? ASE_T_1_.`类型` AS LOD_601A3BCEB54372

??????????? FROM

????????????? `terminaldata2`.`sales` AS ASE_T_1_

??????????? WHERE

????????????? ASE_T_1_.`类型` = '退货单'

??????????? GROUP BY

????????????? ASE_T_1_.`月份`,

????????????? ASE_T_1_.`类型`

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

????????? AND LOD_TL.`LOD_601A3BCEB54372` = LOD_TR.`LOD_601A3BCEB54372`

??????? GROUP BY

????????? LOD_TL.`LOD_2C07D23FC7BBAA`

????? ) AS LOD_TP_0 ON LOD_TM.`LOD_2C07D23FC7BBAA` = LOD_TP_0.`LOD_2C07D23FC7BBAA`

? ) AS a

上述SQL代码中，第63行、165行“INNER JOIN“为内连接，取两张表的交集。以第63行为例，即取两张表（左表LOD_TL和右表LOD_TR）中的交集，由于右表LOD_T为空，所以内连接后交集也为空。

四、优化建议

当右表中没有匹配的记录时，也要显示左表中的记录（如左表LOD_TL中的”月份“），应该使用外左外连接LEFT JOIN，即左外连接以左表为主表，左表中的记录将会全部表示出来,右表只会显示符合过滤条件的记录，右表记录中不足的地方均为NULL。因此，需要将第63行、165行”INNER JOIN“改为”LEFT JOIN“既左外连接即可。

五、总结