两招加速时间智能分析之 Tableau 篇

本文通过窗口函数SUM() OVER(PARTITION BY... ORDER BY...) 实现MTD,QTD,YTD计算。

该函数先通过PARTITION BY...分组，分组内ORDER BY...排序，SUM() OVER在分组内实现第一行到当前行的累计。

比如，定义sql如下：

Select c1,c2,sum(c2)

over(partition by c1 order by c2) as c2_sum

--先按c1分组，组内按c2排序，同一组内由第一行到当前行累计c2

from test;

查询结果如下：

在SQL处理中，窗口函数一般都是最后一步执行，利用这一特性，可在查询时先获取cube数据，再通过窗口函数对数据二次聚合，实现MTD,QTD,YTD的加速。

2. 实现MTD，QTD， YTD

通过上文介绍的窗口函数，可定义如下sql查询各个国家的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 KYLIN_SALES

inner join KYLIN_CAL_DT

on PART_DT=CAL_DT

inner join KYLIN_ACCOUNT

on BUYER_ID=ACCOUNT_ID

inner join KYLIN_COUNTRY

on ACCOUNT_COUNTRY=COUNTRY

group by NAME,PART_DT,MONTH_BEG_DT,QTR_BEG_DT,YEAR_BEG_DT

order by NAME,PART_DT

（用户可直接复制该SQL进行体验）

查询结果如下，可导出验证，和MTD,QTD,YTD预期一致。

3. 将查询SQL在Tableau中复用

上文的查询SQL支持在BI端复用，本文以Tableau为例，接下来介绍具体的操作步骤。

为了确保Tableau与Kyligence Enterprise连通性，请参考Kyligence Enterprise用户手册，提前配置Kyligence ODBC驱动，Kyligence TDC。

a. 连接Kyligence Enterprise

b.模型界面，新建“自定义SQL”

将上文的查询SQL添加至Tableau自定义SQL

c.依据分析场景，拖拽维度度量

自定义SQL”添加完成后，可在Tableau工作表页面，拖拽对应的维度、度量、筛选器，获取各个国家的MTD、QTD、YTD购买金额

方法2：使用Tableau计算字段

Tableau 计算字段实现MTD,QTD,YTD的方式，是通过建立辅助日期参数，用户筛选该日期参数指定截止日期，再将交易日期与该截止日期比对，限制交易日期在MTD,QTD,YTD范围内，以获取目标数据，接下来介绍具体的操作步骤。

为了确保Tableau与Kyligence Enterprise连通性，请参考Kyligence Enterprise用户手册，提前配置Kyligence ODBC驱动，Kyligence TDC。

1. 模型准备

在cube页面，选择“kylin_sales_cube”，点击“更多操作”，在下拉菜单中选择“导出TDS”，即可一键同步Kyligence Enterprise 模型和计算度量至Tableau。

双击.TDS文件，Tableau Desktop查看模型，根据需要将一些维度，度量显示或隐藏。

2. 新建日期参数

新建日期参数“FilterDate”，可从现有的字段中添加，

3. 新建MTD，QTD，YTD计算字段

Tableau Desktop创建计算字段：MTD_FLAG，QTD_FLAG，YTD_FLAG，计算逻辑如下：

4. 依据分析场景，动态获取MTD,QTD,YTD

若您想要查询：截至到2012/3/22，各个国家对应的MTD购买金额，可按如下操作获取:

a.显示参数控件

右键点击“FilterDate”，选择“显示参数控件”，下拉选择参数FilterDate=’2012/3/22’

b. 添加MTD筛选器

将“MTD_FLAG”拖至页面筛选器,并筛选“Y”，再将BUYER_COUNTRY_NAME，GMV_SUM分别拖至列行，即可获取截至到2012/3/23，各个国家对应的MTD购买金额。

c. 验证查询

查看kylin后台日志，可以看出图表数据来源自kylin_sales_cube，响应时间0.177秒。

分析查询SQL， where 语句对应Tableau界面筛选器的筛选内容，限定了查询的日期范围（MTD）：2012/3/1~2012/3/23， Group by的维度度量与Tableau图表展示维度度量一致。所以，Tableau MTD逻辑均转换成对应的查询SQL，在Kyligence Enterprise中执行，Tableau直接取执行后的聚合结果。