使用Kyligence Enterprise实现同比环比计算

作者：何京珂
编辑：Sammi

同比环比分析是商业中常用的分析手段，传统计算同比环比的方法是在ETL阶段预先计算好同比环比指标，然而这种方法一不方便业务部门自主定义同比环比指标，开发周期过长无法快速响应业务需求，二不同颗粒度的同比环比度量过多，在使用Kylin Cube时会造成度量过多而延长构建时间，影响Cube性能。为了解决上述问题，本文将展现 Kyligence Enterprise 之上计算同比环比的两种方法。

Kyligence Enterprise大数据智能分析平台是基于Apache Kylin的，在超大数据集上提供亚秒级分析能力的企业级数据仓库产品。

进行同比环比等计算有两种方式一种是在对 Kylin Cube进行查询时利用窗口函数进行计算，另一种是利用BI工具中提供的函数进行表级计算，下面我们分别详细介绍下面两种计算方法。

1. 准备工作

首先我们用 Kyligence Enterprise 自带的learn_kylin样例数据集建立一个数据模型，其中事实表Kylin_sales中的Price将是本例中着重计算同比环比的字段。

Figure 1

创建相应的Cube并构建Cube, Cube中定义的sum(price)度量将被用来计算同比环比。

Figure 2

Figure 3

注：在使用 Kyligence Enterprise 计算同比环比时无需在Cube中定义额外的同比环比度量，在查询时直接使用函数计算即可。如在本例中需要对price做同比环比计算，只需要在Cube中有price的度量 Sum（price）即可。

2. 使用 Kyligence Enterprise计算环比

在 Kyligence Enterprise 的分析页面测试Lag函数：

在本例中我们首先计算每月的销量情况，随后我们可以利用 Kyligence Enterprise 提供的窗口函数Lag计算获得上个月的销量数据：

LAG(value, offset, DEFAULT) OVER ()

这个函数的功能就是返回与当前行向前偏移n行的目标行的数值，如LAG(sum(price), 1) OVER () 即可以获得前一行的销量数据。

而月环比的计算公式为 （当月销量-上月销量）／上月销量，转成SQL就是：

(sum(price)-lag(sum(price),1) OVER ())/lag(sum(price),1) OVER ()

于是利用如下SQL我们就可以实现月环比分析：

select c.month_id
,sum(price) as sales
,lag(sum(price),1) over () as sales_LM
,(sum(price)-lag(sum(price),1) over ())/lag(sum(price),1) over ()
as sales_MOM_PERCENTAGE
from KYLIN_Sales s
join KYLIN_CAL_DT c
on s.part_dt=c.cal_dt
group by month_id
order by month_id

结果如下：

Figure 4

3. 使用 Kyligence Enterprise计算同比

类似的我们可以用Lag倒推12个月来计算同比：

lag(sum(price),12) over ()
同比计算的公式为（当月销量-去年当月销量）／去年当月销量，转成SQL就是:

(sum(price)-lag(sum(price),12) over ())/lag(sum(price),12) over ()

于是我们可以用SQL计算出

select c.month_id
,sum(price) as sales
,lag(sum(price),12) over () as sales_LY
,(sum(price)-lag(sum(price),12) over ())/lag(sum(price),12) over ()
as sales_YOY_PERCENTAGE
from KYLIN_Sales s
join KYLIN_CAL_DT c
on s.part_dt=c.cal_dt
group by month_id
order by month_id

在分析页面获得结果如下：

Figure 5

Figure 6

可以看到由于目前数据集中的数据是由2012年开始的，因此2012年的数据就无法计算出同比，同比数据从2013年才开始有返回结果。

4. 将计算结果应用于 BI 分析

将同比环比的分析合并到同一个 SQL 并在 BI 工具端使用，即可开始对已计算好的同比环比结果进行分析。

select c.month_id
,sum(price) as sales
,lag(sum(price),1) over () as sales_LM
,(sum(price)-lag(sum(price),1) over ())/lag(sum(price),1)
over () as sales_MOM_PERCENTAGE
,lag(sum(price),12) over () as sales_LY
,(sum(price)-lag(sum(price),12) over ())/lag(sum(price),12)
over () as sales_YOY_PERCENTAGE
from KYLIN_Sales s
join KYLIN_CAL_DT c
on s.part_dt=c.cal_dt
group by month_id
order by month_id

此处以Tableau为例，将同比环比的结果进行可视化分析。

首先需要使用Tableau进行ODBC连接 Kyligence Enterprise ，如果你没有创建与 Kyligence Enterprise 的 ODBC 连接，请首先参考链接中的文章进行首次ODBC的配置：https://docs.kyligence.io/books/v4.1/zh-cn/integration/driver/odbc/

Figure 7

将SQL语句以自定义SQL的形式导入Tableau：

Figure 8

进行简单的报表制作，得到同比环比的可视化分析如下：

Figure 9

5. 对不同区间的数据进行同比环比计算

在实际的分析场景中，进行同比环比计算时，分析师希望对数据进行分区分别计算同比环比，例如希望获得各产品分类下的月环比结果，这时候需要在lag函数中定义具体计算的区间即 ：

lag(sum(price),1) over (partition by g.categ_lvl3_name order by c.month_id) as sales_LM

在partition by中定义categ_lvl3_name可以实现获取前一个月的销量时以产品分类单独进行计算，定义order by month_id 基于月份进行排序，然后取前一行的销量。如上所述我们可以用以下SQL计算出各产品分类下的同比及环比：

select g.categ_lvl3_name
,c.month_id
,sum(price) as sales
,lag(sum(price),1) over (partition by g.categ_lvl3_name order by c.month_id) as sales_LM
,(sum(price)-lag(sum(price),1) over (partition by g.categ_lvl3_name order by c.month_id))/lag(sum(price),1) over (partition by g.categ_lvl3_name order by c.month_id) as sales_MOM_PERCENTAGE
from KYLIN_Sales s
join KYLIN_CAL_DT c
on s.part_dt=c.cal_dt
join KYLIN_CATEGORY_GROUPINGS g
on s.leaf_categ_id=g.leaf_categ_id
and s.LSTG_SITE_ID=g.SITE_ID
group by g.categ_lvl3_name, month_id
order by g.categ_lvl3_name,month_id

在分析页面计算可获得结果如下：

Figure 10

Figure 11

6. 使用窗口函数计算同比环比的局限性

值得一提的是，由于窗口函数lag计算同比环比时，函数只是单纯的按照用户指定的排序次序，找到前一行的值或前十二行的值，因此这种计算的准确是基于数据中包含每月的完整数据的前提下。如果某月份的数据缺失的话，就会造成向前找到上个月或一年前的值出错。

Figure 12

如在上图中的情况，lag函数只是单纯的查找到了上一行的销量数据进行显示，对产品分类“Clippings”，由于2012年3月销量数据的缺失，2014年4月环比的销量就错误的显示了2014年2月的销量数据。类推也可以预见到产品分类“Clippings”的同比数据由于2014年3月和5月的数据缺失，在向上查找第十二行时也会显示错误的同比销量数据。

7. 使用Tableau进行同比环比计算

下面我们以Tableau为例介绍如何在BI工具中直接进行同比环比计算。

建立ODBC连接并在Tableau中连接 Kyligence Enterprise 中的learn_kylin数据源，本例中使用自定义SQL作为数据源连接, 请注意连接Tableau时请使用实时连接, 读者也可以直接拉取表，在Tableau中进行建模作为数据源连接。

Figure 17

创建新的Tableau工作簿，在工作簿添加日期和销售金额,日期显示为年月维度销售金额由事实字段price 计算得出:

Figure 18

Figure 19

接下来我们可以利用Tableau自带的函数lookup获得环比的上个月的销售金额，lookup（）函数的功能就是返回与当前行偏移n行的目标行的数值,如:

Lookup(sum(price),-1)

即可返回当前行前一行的数值。

Figure 20

Figure 21

下一步我们可以利用计算好的上月销量和当月销量计算出环比百分比，(当月销量-上月销量)/上月销量*100%。

Figure 22

8. 使用Tableau工具对不同区间的数据进行同比环比计算

如本例中在表中再添加Region维度，此时我们希望销量环比仅在同一个Region范围内进行计算，而不要跨越Region进行计算，否则计算出来的环比是没有意义的，此时我们只需要检查表计算的计算范围即可，右击月环比度量->编辑表计算，选择计算依据为特定唯独->年月。计算帮助提示我们目前的计算范围仅会跨越年月进行计算，而对不同的Region表计算会重新开始，这和我们期望的是一致的。

Figure 23

Figure 24

同理我们可以实现同比计算，用lookup函数回退12个月找到去年同比月份的销量：

lookup(sum([PRICE]),-12)

Figure 25

再计算当前月份和去年同比的变化百分比: ([销量]-[去年销量])/[去年销量]。

Figure 26

至此我们就用Tableau实现了同比环比分析，由此类推也可以实现周、季度的同比环比。

Figure 27

值得一提的是Tableau的表计算是基于报表上存在的数据计算的出的，如数据在数据库中存在但是并没有展示在报表上，则相应的表计算无法实现，如下图将2012年数据从报表端筛选掉，尽管2013数据在数据库中存在，月同比仍无法正确计算。

Figure 28

9. 总结

可以看到同比环比等计算可以放在 Kyligence Enteprrise（Apache Kylin）或BI端进行计算，在查询 Kylin Cube 时再进行同比环比的计算可以避免在Cube构建时进行过多的度量计算而造成对性能的影响，同时也赋能分析师更加灵活自主的进行分析，减少分析对ETL的依赖度。