Quick BI中有关IF语句中使用Lod函数问题分析

一、问题描述

已知3~5月的进销存数据，现统计各月份的总结存数量最大的前5个品牌，当不指定月份时，显示的是5月末的总结存数量。新建计算字段“总结存数量=if(count(distinct [月份])=1,sum([结存数量]),lod_include{[月份]:SUM([结存数量]):[月份]='5月'})”，即当”月份”个数为1时，直接对“结存数量”求和，当“月份”个数不为1时，只对“5月”的“结存数量“求和。

1）当不指字月份时，显示结果正常，如下图

2）当指定月份为“5月”时，显示结果也正常，如下图

3）当指定月份为”3月”或”4月”时，数据返回为空，显示结果不正确，如下图

二、原因分析

查看当指定月份为”3月”时SQL代码：

SELECT

  LOD_TM.`LOD_7D5CED68F83735` AS LOD_0,

  IF(

    LOD_TM.`temp_calculation_0` = 1,

    LOD_TM.`temp_calculation_1`,

    LOD_TP_0.`LOD_4C2968B366F62E`

  ) AS LOD_1

FROM

  (

    SELECT

      A_T_2_.`卷烟品牌` AS LOD_7D5CED68F83735,

      SUM(

        A_T_1_.`结存数量`

      ) AS temp_calculation_1,

      COUNT(

        DISTINCT A_T_1_.`月份`

      ) AS temp_calculation_0

    FROM

      `终端经营数据`.`商品月台账` AS A_T_1_

      LEFT JOIN `终端经营数据`.`商品管理` AS A_T_2_ ON A_T_1_.`商品条码` = A_T_2_.`商品条码`

    WHERE

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

      AND A_T_1_.`当月是否在销` = '在销'

      AND A_T_1_.`月份` = '3月'

    GROUP BY

      A_T_2_.`卷烟品牌`

    LIMIT

      0, 10000

  ) AS LOD_TM

  INNER JOIN (

    SELECT

      LOD_TL.`LOD_7D5CED68F83735` AS LOD_7D5CED68F83735,

      LOD_TR.`LOD_measure_result` AS LOD_4C2968B366F62E

    FROM

      (

        SELECT

          A_T_2_.`卷烟品牌` AS LOD_7D5CED68F83735,

          A_T_1_.`月份` AS LOD_2C07D23FC7BBAA

        FROM

          `终端经营数据`.`商品月台账` AS A_T_1_

          LEFT JOIN `终端经营数据`.`商品管理` AS A_T_2_ ON A_T_1_.`商品条码` = A_T_2_.`商品条码`

        WHERE

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

          AND A_T_1_.`当月是否在销` = '在销'

          AND A_T_1_.`月份` = '3月'

        GROUP BY

          A_T_2_.`卷烟品牌`,

          A_T_1_.`月份`

      ) AS LOD_TL

      INNER JOIN (

        SELECT

          A_T_2_.`卷烟品牌` AS LOD_7D5CED68F83735,

          SUM(

            A_T_1_.`结存数量`

          ) AS LOD_measure_result,

          A_T_1_.`月份` AS LOD_2C07D23FC7BBAA

        FROM

          `终端经营数据`.`商品月台账` AS A_T_1_

          LEFT JOIN `终端经营数据`.`商品管理` AS A_T_2_ ON A_T_1_.`商品条码` = A_T_2_.`商品条码`

        WHERE

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

          AND A_T_1_.`当月是否在销` = '在销'

          AND A_T_1_.`月份` = '3月'

          AND A_T_1_.`月份` = '5月'

        GROUP BY

          A_T_2_.`卷烟品牌`,

          A_T_1_.`月份`

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

      AND LOD_TL.`LOD_2C07D23FC7BBAA` = LOD_TR.`LOD_2C07D23FC7BBAA`

    GROUP BY

      LOD_TL.`LOD_7D5CED68F83735`

  ) AS LOD_TP_0 ON LOD_TM.`LOD_7D5CED68F83735` = LOD_TP_0.`LOD_7D5CED68F83735`

其中上述代码第19行~55行代码返回的是“count(distinct [月份])=1”即指定月份（“3月”）各品牌结存数量，如下图1：

其中上述代码第61行~141行，返回的是当指定月份为“3月”时，“count(distinct [月份])”不等于1时，各品牌结存数量（返回结果为空），结果如下图2：

然后由上述第59行代码可知图1与图2是用“INNER JOIN”进行的内连接，结果必然为空。考虑到当不指定月份时，图1包含了5月的结存数量，所以此处的”INNER JOIN”应改为左外连接“LEFT JOIN”。经测试，修改后结果显示正常。

三、再思考

此处的INNER JOIN更理想的修改应为“FULL JOIN”全外连接，可惜MySQL不支持“FULL JOIN”。