同环比与MTD/QTD/YTD月季年累计在查询引擎中的实现

一、实现环比/同比计算

（1）利用窗函数实现同比环比计算

计算每月的销量情况的场景中，我们可以窗口函数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 
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 sales_table 
group by month_id
order by month_id

备注：当sales_LM 为空时，sales_MOM_PERCENTAGE为空，不会补充

（2）利用窗函数对不同区间的数据实现同比环比计算

在实际的分析场景中，进行同比环比计算时，分析师希望对数据进行分区分别计算同比环比，例如希望获得各产品分类下的月环比结果，此时需要在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 
	categ_lvl3_name
	,month_id
	,sum(price) as sales
	,lag(sum(price),1) over (partition by categ_lvl3_name order by month_id) as sales_LM
	,(sum(price)-lag(sum(price),1) over (partition by categ_lvl3_name order by month_id))/lag(sum(price),1) over (partition by categ_lvl3_name order by month_id) as sales_MOM_PERCENTAGE
from sales_table_2
group by categ_lvl3_name,month_id
order by categ_lvl3_name,month_id

（3）利用窗口函数计算同比环比的局限

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

二、实现MTD/QTD/YTD计算

通过窗口函数实现MTD,QTD,YTD，如对于一个订单表order，可定义如下sql查询各Name分区下的MTD,QTD,YTD 金额。

select 
       NAME as BUYER_ACCOUNT_NAME,
       PART_DT,
       MONTH_BEG_DT,
       QTR_BEG_DT,
       YEAR_BEG_DT,
       SUM(PRICE) as GMV,
       SUM(SUM(PRICE)) over (partition by NAME,MONTH_BEG_DT order by PART_DT )  GMV_MTD,
       SUM(SUM(PRICE)) over (partition by NAME,QTR_BEG_DT order by PART_DT )  GMV_QTD,
       SUM(SUM(PRICE)) over (partition by NAME,YEAR_BEG_DT order by PART_DT )  GMV_YTD
from order

备注

这里每条记录都会有基准列MONTH_BEG_DT、QTR_BEG_DT、YEAR_BEG_DT

例如： KYLIN_SALES_3

NAME	PART_DT	MONTH_BEG_DT	QTR_BEG_DT	YEAR_BEG_DT	PRICE
hello	2019-01-01	2019-01-01	2019-01-01	2019-01-01	88.0
hello	2019-01-03	2019-01-01	2019-01-01	2019-01-01	89.0
hello	2019-01-04	2019-01-01	2019-01-01	2019-01-01	88.0

查询结果

NAME	PART_DT	MONTH_BEG_DT	QTR_BEG_DT	YEAR_BEG_DT	GMV	GMV_MTD	GMV_QTD	GMV_YTD
hello	2019-01-01	2019-01-01	2019-01-01	2019-01-01	88.0	88.0	88.0	88.0
hello	2019-01-03	2019-01-01	2019-01-01	2019-01-01	89.0	177.0	177.0	177.0
hello	2019-01-04	2019-01-01	2019-01-01	2019-01-01	88.0	265.0	265.0	265.0

三、参考

• 使用Kyligence Analytics Platform 实现同比环比计算
  
• Tableau技巧——快速计算同比和环比
• 两招加速时间智能分析之 Tableau 篇