使用KAP實現同比環比計算

同比環比分析是商業中常用的分析手段，傳統計算同比環比的方法是在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
,(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

在分析頁面獲得結果如下：

可以看到由於目前數據集中的數據是由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連接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_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

在分析頁面計算可獲得結果如下：

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_CAL_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) )

類似的月同比計算的公式為（當月銷量-去年當月銷量）／去年當月銷量,轉成MDX語言為:

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

增加對分母及月份不存在的語句檢查後得到月同比的計算公式為：

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表計算會重新開始，這和我們期望的是一致的。