我的位置：首页 › 大数据分析-亿信BI › BI-心得体验 › 浅析BI计算中的几个小规则

浅析BI计算中的几个小规则

复制链接 | 收藏本帖

4541

: tangmq 数据达人Lv4

发表于2019-9-10 14:15

楼主

本帖最后由 tangmq 于 2019-9-10 15:52 编辑

（说明：此文测试依据为BI47，不代表所有亿信产品计算规则）
第一部分，关于报表计算的内连接、左连接、全连接
设计了一个非常简单的表，求2014年10月按机构类型分组的费用指标、同期费用指标；数据条件，仅存2014年10月和2014年09月两个月的数据，所以本期有数据，同期没有数据。针对这样一种数据现状，不同的连接规则，出来的结果不同。如下图所示，三个表格完全一样的取数和过滤，唯一不同的是计算关联规则：表一内连接，表二左连接，表三全连接；看到的结果也完全不同

表一内连接计算sql
从sql中看到，本期费用和同期费用，是两段sql子查询组成，两个子查询通过(a.A2=b.A2)关联，由于b.A2结果为空，所以内连接之后，结果也为空。

<div> select a.B2 as B2,b.C2 as C2,a.A2 as A2
from (
select sum(a.FY) as B2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
where (a.BBQ_='201410')
group by SUBSTR(a.TJ_TYPE,1,1)) a
,(
select sum(a.FY) as C2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
where (a.BBQ_='201310')
group by SUBSTR(a.TJ_TYPE,1,1)) b
where (<font color="#ff0000">(a.A2=b.A2)</font>)</div>

复制代码

表二左连接计算sql 最后的关联语句是 A.A2=B.A2(+) 有别于内连接(a.A2=b.A2)所以出来的结果能明显看到差异
（此处左连接，可以关联到数据，因为左连接的主体是当期费用。如果如果把同期费用放左边，当期费用放右边，左连接一下，计算结果同表一，因为同期没有数据，把没有结果集的结果集当左连接的主体，关联后的结果集，即是空）

<div>select a.B2 as B2,b.C2 as C2,a.A2 as A2
from (
select sum(a.FY) as B2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
where (a.BBQ_='201410')
group by SUBSTR(a.TJ_TYPE,1,1)) a
,(
select sum(a.FY) as C2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
where (a.BBQ_='201310')
group by SUBSTR(a.TJ_TYPE,1,1)) b
where (<font color="#ff0000">A.A2=B.A2(+)</font>)</div>

复制代码

表三全连接计算sql ，可以看到两段子查询的结果是union all并且两段sql子查询中，都先预处理，把当前另一个指标值赋值0.00，最后去做全连接
这也就是为什么表二同期值为空，而表三的同期值为0.00
（全连接的情况下，不管指标先后顺序，都能关联到计算结果。）

select sum(B20) as B20,sum(C21) as C21,A2 as A2
from (
select sum(a.FY) as B20<font color="#ff0000">,0.0 as C21</font>, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
where (a.BBQ_='201410')
group by SUBSTR(a.TJ_TYPE,1,1)
<font color="#ff0000">union all</font>
select <font color="#ff0000">0.0 as B20,</font>sum(a.FY) as C21, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
where (a.BBQ_='201310')
group by SUBSTR(a.TJ_TYPE,1,1)
) un_
group by A2

复制代码

那么，如何设置报表、指标的内连接、左连接和全连接呢？
方法一：主题集属性中设置计算规则。此处的设计是对主题集全局有效。

方法二：指标上定义链接规则，此处设计的优先级高于主题集的规则设置；

第二部分，关于报表计算的先连接、后连接
BI中经常有这样的场景，报表中的指标，从两个不同的主题表取数，并且两个表之间需要进行关联运算。此时就会涉及到源表先关联后分组，还是报表上先分组然后再基于结果集关联。这两种方式，其实是需要从需求场景出发来进行分析和应用的。需求场景千千万哈，此处的介绍化繁为简，简单介绍某场景下的实现方法。
场景一：必须先连接后分组
如果报表的目标指标，需要通过多个主题表的指标通过运算得到，此时必须先连接后分组，例如业务指标C=主题表A.指标1+主题表B.指标1

如上图所示，FY（费用表）和SRZC（收入支出表），是两个独立的主题表，报表上要需要按机构类型统计费用+收入+支出，即来自不同的主题表的指标需要通过运算后，根据其中一个主题表的分组维度进行分组查询。

如果此时没有对两个主题表定义关联关系，计算就很可能出现如下提示，提醒你定义两个表之间的关联关系，因为系统认为你的这两个表需要关联，再分组

此时如果定义一下两个表的关联关系，如下图
(关联关系在主题集属性中可添加，报表编辑/左上角菜单/高级/关联关系中，也可以添加，表内优先级高于主题集)

添加如上关联关系之后，就可以正常计算了，计算的时候，两个表的关联语句的规则就是你定义的关联逻辑，计算sql如下：

select sum(a.FY+b.ZSR+b.ZZC) as B2,a.BBQ_ as A2
from FACT_FY_458 a
,FACT_SRZC b
where ((A.BBQ_=B.BBQ_(+)) AND (A.USERID_=B.USERID_(+)))
group by a.BBQ_

复制代码

场景二：可以先分组后连接，也可以先连接后分组
如果报表的目标指标独立运算，但是需要有相同的业务分组，该情况先分组后连接、先连接后分组，都可实现（具体实现，可根据事情情况选择合适合理的方案）
例如，还是上文的FY和SRZC，这两张表为例，因为这两张表有很多相同的维度字段，此时，如果不定义关联关系，依然可以正常计算，自定进行先分组后关联，实现结果集的关联运算

计算sql语句如下：

select a.B2 as B2,b.C2 as C2,b.D2 as D2,a.A2 as A2
from (
select sum(a.FY) as B2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
group by SUBSTR(a.TJ_TYPE,1,1)) a
,(
select sum(a.ZSR) as C2,sum(a.ZZC) as D2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_SRZC a
group by SUBSTR(a.TJ_TYPE,1,1)) b
where ((a.A2=b.A2))

复制代码

如果定义两个表的关联关系，也可以勾选先连接，实现先连接后分组

select sum(a.FY) as B2,sum(b.ZSR) as C2,sum(b.ZZC) as D2, SUBSTR(a.TJ_TYPE,1,1) as A2
from FACT_FY_458 a
,FACT_SRZC b
where ((A.BBQ_=B.BBQ_(+)) AND (A.USERID_=B.USERID_(+)))
group by SUBSTR(a.TJ_TYPE,1,1)

复制代码

场景三：必须先分组后连接
两个主题表，不存在任何关联关系，当时需要通过某种分组后，根据各自分组后的结果，进行关联（即，结果集关联）
假设FY和SRZC不存在任何关联关系，现在就要对机构类型第一级别进行各自分组后进行关联，表样设计如下：

生成的sql语句如下：