有关Quick BI中新建字段被引用时出错问题分析

一、问题描述

零售订单数量	卷烟订单数量	非烟订单数量	同时购买卷烟和非烟订单数量	购买卷烟中购买非烟比例
16316	3263	14272	1219	37.36%

备注：

（1）卷烟订单：零售订单中有卷烟商品的订单

（2）非烟订单：零售订单中有非烟商品的订单

（3）同时购买卷烟和非烟订单：零售订单中既有卷烟又有非烟商品的订单，同时购买卷烟和非烟订单数量=卷烟订单数量+非烟订单数量-零售订单数量

（4）购买卷烟中购买非烟比例=同时够买卷烟和非烟订单数 / 卷烟订单数

在Quick BI数据集中

（1）新建计算字段“零售订单数量=lod_include{[类型]:COUNT(distinct [流水号]):[类型]='零售单'}”

（2）新建计算字段“卷烟订单数量=lod_include{[是否卷烟]:COUNT(distinct [流水号]):[是否卷烟]='卷烟'}”

（3）新建计算字段“非烟订单数量=lod_include{[是否卷烟]:COUNT(distinct [流水号]):[是否卷烟]='非烟'}”

（4）新建计算字段“同时购买卷烟和非烟订单数量=[卷烟订单数量]+[非烟订单数量]-[零售订单数量]”

（5）新建计算字段“购买卷烟中购买非烟的比例=[同时购买卷烟和非烟订单数量]/[卷烟订单数量]”

在Quick BI仪表板中添加指标图，输入结果显然是不正确的。

二、问题分析

查看SQL代码

SELECT

  LOD_TP_0.`LOD_8AB699CEB93538` + LOD_TP_1.`LOD_926F669A54CB6C` - LOD_TP_2.`LOD_F08CAF0E524E3E` / LOD_TP_0.`LOD_8AB699CEB93538` AS LOD_0

FROM

  (

    SELECT

      '-' AS T_ALA_2_

    FROM

      `terminaldata2`.`sales` AS ASE_T_1_

    LIMIT

      0, 1

  ) AS LOD_TM

  CROSS JOIN (

    SELECT

      SUM(LOD_TR.`LOD_measure_result`) AS LOD_8AB699CEB93538

    FROM

      (

        SELECT

          AST_T_3_.`是否卷烟` AS LOD_280795B8AB3CAB

        FROM

          `terminaldata2`.`sales` AS ASE_T_1_

          LEFT JOIN `terminaldata2`.`products` AS AST_T_3_ ON ASE_T_1_.`商品条码` = AST_T_3_.`商品条码`

        GROUP BY

          AST_T_3_.`是否卷烟`

      ) AS LOD_TL

      INNER JOIN (

        SELECT

          AST_T_3_.`是否卷烟` AS LOD_280795B8AB3CAB,

          COUNT(

            distinct ASE_T_1_.`流水号`

          ) AS LOD_measure_result

        FROM

          `terminaldata2`.`sales` AS ASE_T_1_

          LEFT JOIN `terminaldata2`.`products` AS AST_T_3_ ON ASE_T_1_.`商品条码` = AST_T_3_.`商品条码`

        WHERE

          AST_T_3_.`是否卷烟` = '卷烟'

        GROUP BY

          AST_T_3_.`是否卷烟`

      ) AS LOD_TR ON LOD_TL.`LOD_280795B8AB3CAB` = LOD_TR.`LOD_280795B8AB3CAB`

  ) AS LOD_TP_0

  CROSS JOIN (

    SELECT

      SUM(LOD_TR.`LOD_measure_result`) AS LOD_926F669A54CB6C

    FROM

      (

        SELECT

          AST_T_3_.`是否卷烟` AS LOD_280795B8AB3CAB

        FROM

          `terminaldata2`.`sales` AS ASE_T_1_

          LEFT JOIN `terminaldata2`.`products` AS AST_T_3_ ON ASE_T_1_.`商品条码` = AST_T_3_.`商品条码`

        GROUP BY

          AST_T_3_.`是否卷烟`

      ) AS LOD_TL

      INNER JOIN (

        SELECT

          AST_T_3_.`是否卷烟` AS LOD_280795B8AB3CAB,

          COUNT(

            distinct ASE_T_1_.`流水号`

          ) AS LOD_measure_result

        FROM

          `terminaldata2`.`sales` AS ASE_T_1_

          LEFT JOIN `terminaldata2`.`products` AS AST_T_3_ ON ASE_T_1_.`商品条码` = AST_T_3_.`商品条码`

        WHERE

          AST_T_3_.`是否卷烟` = '非烟'

        GROUP BY

          AST_T_3_.`是否卷烟`

      ) AS LOD_TR ON LOD_TL.`LOD_280795B8AB3CAB` = LOD_TR.`LOD_280795B8AB3CAB`

  ) AS LOD_TP_1

  CROSS JOIN (

    SELECT

      SUM(LOD_TR.`LOD_measure_result`) AS LOD_F08CAF0E524E3E

    FROM

      (

        SELECT

          ASE_T_1_.`类型` AS LOD_601A3BCEB54372

        FROM

          `terminaldata2`.`sales` AS ASE_T_1_

        GROUP BY

          ASE_T_1_.`类型`

      ) AS LOD_TL

      INNER JOIN (

        SELECT

          COUNT(

            distinct ASE_T_1_.`流水号`

          ) AS LOD_measure_result,

          ASE_T_1_.`类型` AS LOD_601A3BCEB54372

        FROM

          `terminaldata2`.`sales` AS ASE_T_1_

        WHERE

          ASE_T_1_.`类型` = '零售单'

        GROUP BY

          ASE_T_1_.`类型`

      ) AS LOD_TR ON LOD_TL.`LOD_601A3BCEB54372` = LOD_TR.`LOD_601A3BCEB54372`

  ) AS LOD_TP_2

LIMIT

  0, 1

在上述代码中，第3行代码“LOD_TP_0.`LOD_8AB699CEB93538` + LOD_TP_1.`LOD_926F669A54CB6C` - LOD_TP_2.`LOD_F08CAF0E524E3E` / LOD_TP_0.`LOD_8AB699CEB93538` AS LOD_0”中百分比计算公式的分子中显然少了一个括号。

也就是说在quick bi新建计算字段“购买卷烟中购买非烟的比例=[同时购买卷烟和非烟订单数量]/[卷烟订单数量]”时，quick bi 并没有把[同时购买卷烟和非烟订单数量]作为一个整体进行处理，而是直接引用过去了，结果导致计算结果出错。

三、优化建议

在代码“LOD_TP_0.`LOD_8AB699CEB93538` + LOD_TP_1.`LOD_926F669A54CB6C` - LOD_TP_2.`LOD_F08CAF0E524E3E`”两边加上括号，再运行结果正常。

在quick bi数据集中，将字段“购买卷烟中购买非烟的比例=[同时购买卷烟和非烟订单数量]/[卷烟订单数量]”，更改为“购买卷烟中购买非烟的比例=([同时购买卷烟和非烟订单数量])/[卷烟订单数量]”,再执行计算，结果正常。

四、总结

在Quick BI新引用自建计算字段去新建字段时，最好在引用的自建字段两边加个括号。