环比怎么算计算公式,环比怎么算计算公式excel两个表格中

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

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

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

1. 准备工作

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

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

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

2. 使用KAP计算环比

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

在本例中我们首先计算每月的销量情况，随后我们可以利用KAP提供的窗口函数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我们就可以实现月环比分析：

结果如下：

3. 使用KAP计算同比

类似的我们可以用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,(s中um(price)-l怎么ag(sum(price),12) over ())/lag(sum(price),12) over ()as sales_YOY_PERCENTAGEfrom KYLIN_Sales sjoin KYLIN_CAL_DT con s.part_dt=c.cal_dtgroup by month_idorder by month_id

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

可以看到由于目前数据集中的数据是由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_PERCENTAGEfrom KYLIN_Sales sjoin KYLIN_CAL_DT con s.part_dt=c.cal_dtgroup by month_idorder by month_id

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

首先需要使用Tableau进行ODBC连接KAP，如果你没有创建与KAP的ODBC连接，请首先参考链接中的文章进行首次ODBC的配置：https://kyligence.gitbooks.io/kap-manual/content/zh-cn/driver/odbc.cn.html

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

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

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_PERCENTAGEfrom KYLIN_Sales sjoin KYLIN_CAL_DT con s.part_dt=c.cal_dtjoin KYLIN_CATEGORY_GROUPINGS gon s.leaf_categ_id=g.leaf_categ_idand s.LSTG_SITE_ID=g.SITE_IDgroup by g.categ_lvl3_name, month_idorder by g.categ_lvl3_name,month_id

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

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

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

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

如果不通过查询KAP的方式计算同比环比，绝大部分主流BI工具也支持计算同比环比，下面我们以KAP自带的可视化工具KyAnalyzer为例实现同比环比计算。

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

在KyAnalyzer中我们可以通过定义Calculated Member来计算同比及环比, 仍旧以learn_kylin样例数据集上的Cube为例进行计算。

在指标中点击添加以创建calculated Member, KyAnalyzer遵循MDX语法,根据月环比的计算公式（当月销量-上月销量）／上月销量,转成MDX语言为:

[Measures].[KYLIN_SALES.PRICE_SUM]/ ([KYLIN_CAL_DT].[MONTH_ID].currentmember.prevmember,[Measures].[KYLIN_SALES.PRICE_SUM]) - 1)

增加对分母及月份不存在的语句检查后得到月环比的计算公式为：

IIF ( ISEMPTY(([KYLIN_CAL_DT].[MONTH_ID].currentmember.prevmember,[Measures].[KYLIN_SALES.PRICE_SUM])) OR ISEMPTY([Measures].[KYLIN_SALES.PRICE_SUM]) OR([KYLIN_CA计算L_DT].[MONTH_ID].currentmember.prevmember, [Measures].[KYLIN_SALES.PRICE_SUM]) = 0 OR[Measures].[KYLIN_SALES.PRICE_SUM] = 0, null,([Measures].[KYLIN_SALES.PRICE_SUM] / ([KYLIN_CAL_DT].[MONTH_ID].currentmember.prevmember,[Measures].[KYLIN_SALES.PRICE_SUM]) - 1) )

类似的月同比计算的公式为（当月销量-去年当月销量）／去年当月销量,转成MD环比X语言为:

(PARALLELPERIOD([KYLIN_CAL_DT].[YEAR_ID].currentmember, 12, [KYLIN_CAL_DT].[MONTH_ID].currentmember), [Measures].[KYLIN_SALES.PRICE_SUM])- excel1)

增加对分母及月份不存在的语句检查后得到月同比的计算公式为：

IIF ( ISEMPTY((PARALLELPERIOD([KYLIN_CAL_DT].[YEAR_ID].currentmember, 12,[KYLIN_CAL_DT].[MONTH_ID].currentmember), [Measures].[KYLIN_SALES.PRICE_SUM]))OR ISEMPTY([Measures].[KYLIN_SALES.PRICE_SUM]) OR ((PARALLELPERIOD([KYLIN_CAL_DT].[YEAR_ID].currentmember, 12,[KYLIN_CAL_DT].[MONTH_ID].currentmember), [Measures].[KYLIN_SALES.PRICE_SUM]))= 0 OR[Measures].[KYLIN_SALES.PRICE_SUM]= 0, null, ([KYLIN_SALES.PRICE_SUM] /(PARALLELPERIOD([KYLIN_CAL_DT].[YEAR_ID].currentmember, 12, [KYLIN_CAL_DT].[MONTH_ID].currentmember),两个 [Measures].[KYLIN_SALES.PRICE_SUM])- 1) )

将Cube中定义的sum（Price）的度量及在KyAnalyzer中定义的同比环比的Calculated Member添加到查询页面的指标中，即开始使用分析同比环比的数据了。

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

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

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

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

sum(Price)

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

Lookup(sum(price),-1)

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

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

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

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