BI 前端实践 15:基于语义层的自助查询

实践目标

         了解基于DQL语义层的自助查询如何做明细、分组查询,以及用什么方式应对表间的关联。最后了解其源代码,以及与之前DQL多维分析的共性。

自助查询概览

         不同于多维分析,有时只想查询出数据/下载数据,这时润乾专门提供了一个自助查询页面,和分析页面一样,也是基于元数据树,但更细致的实现了DQL查询能力。

..

         左上是一些功能按钮,右上列出raqsoftConfig.xml配置的所有DQL类型数据源,选其中一个做自助查询。

         最左侧把所有的分组维度单独列出来,中间是元数据树,可选各级字段以及对它们的汇总。右侧是查询区域,可以更改字段别名、设置过滤条件、汇总值条件、设置分组维度。

         选好字段,完成查询设置后,点击左上的查询按钮..进行查询,结果数据量比较大时,显示部分数据,也可以通过下载按钮把全部数据下载成文件:

..

         也可以点击左上的分析按钮..,直接对结果进行多维分析:

..

 

明细数据查询

         以订单表为例,从展开的各级选出了一些字段,有销售、产品、收发货城市多方面的信息。

..

         虽然涉及多个数据库中的物理表,但DQL查询是针对单个订单表的:

         SELECT

                  T1.销售ID.姓名 销售姓名

                  ,T1.产品.含批次号产品名

                  ,T1.发货城市.区域名称 发货城市

                  ,T1.收货城市.区域名称 收货城市

                  ,T1.发货城市.父区域ID.区域名称 发货省份

                  ,T1.产品.引入日期 产品初次进货日期

                  ,T1.金额

         FROM 订单表 T1

 

         区域名称字段被选出了三次,观察来源,它们的含义是明确的,发货城市的父区域是发货省份,如果进一步的想要省份所在的地区,可以如下切换信息来源:

..

下一步设置个过滤条件:

..

         详细的条件是:

         金额 大于 100

         并且

         (

                  (发货城市 等于 上海 并且 收货城市 等于 北京)

                  或者

                  (发货城市 等于 深圳 并且 收货城市 等于 成都)

         )

 

         条件设置界面结构类似,根据缩进表示条件的组合逻辑:

..

         隐藏掉操作按钮,用简洁方式看的就更清楚些:

..

 

         相应的条件在DQL中也会增加上:

         SELECT

                  T1.销售ID.姓名 销售姓名

                  ,T1.产品.含批次号产品名

                  ,T1.发货城市.区域名称 发货城市

                  ,T1.收货城市.区域名称 收货城市

                  ,T1.发货城市.父区域ID.区域名称 发货省份

                  ,T1.产品.引入日期 产品初次进货日期

                  ,T1.金额

         FROM 订单表 T1

         WHERE

                  T1.金额>100

                  AND (

                           (T0.发货城市=101 AND T1.收货城市=102)

                           OR

                           (T0.发货城市=103 AND T1.收货城市=104)

                  )

        

         多级的字段表达式,除了选出字段时可用,也可用于条件:

         AND T1.销售ID.部门.部门经理.姓名 in (‘张林’,‘柳青’)

..

分组查询

         查询需求是按照年份、月份、产品分组,统计订单数量、金额汇总。接下来按照这个需求描述的顺序来操作。

简单尝试

         先拖入年份、月份、产品三个分组维度,这时还不构成完整的查询,数据来自哪个表还不知道。

..

 

         从订单表拖入订单ID的计数,这时三个维度的来源也自动出现了。

..

 

         现在就初步形成了一个查询,对比下界面和它自动生成的DQL,主要看ONBY子句,ON指明用什么维度分组,BY指明分组的数据来自哪里。:

         SELECT

                  T1.count(订单ID) 订单数量

                  ON , 年月, 产品

         FROM

                  订单表 T1 BY T1.订单日期#,  T1.订单日期#年月,  T1.产品ID

变换维度分组依据的数据

         上面年份、月份默认的是按照订单日期计算,而实际查询需求想要的是按照发货日期计算,这时需要把维度的来源切换下,DQL中的BY子句也会跟着变化:

         BY T1.发货日期#,  T1.发货发货#年月,  T1.产品ID

..

多汇总值及多表JOIN的分组查询

         除了统计订单数量,还要汇总订单金额:

..

         同时这个查询的结构变复杂了,注意两个汇总值要求的年份、月份不一样,订单数量以发货日期为准,而订单金额的合计以订单日期为准,这时的DQL做了两表JOIN(尽管物理表都是订单表,但查询逻辑上区分成了T1T2)

         SELECT

                  T1.count(订单ID) 订单数量

                  ,T2.sum(金额) 金额求和

                  ON ,年月 ,产品

         FROM

                  订单表 T1 BY T1.订单日期# ,T1.订单日期#年月 ,T1.产品ID

                  JOIN

                  订单表 T2 BY T2.发货日期# ,T2.发货日期#年月 ,T1.产品ID

         重点强调一下,界面上操作时,按照需求设置维度的来源就行,不用关心是不是多表JOIN。更不用理解分类信息是不是来自同一个物理表。接下来看一个来自不同物理表的多表JOIN分组。

多物理表JOIN的分组查询

         润乾安装包自带了个DQL数据源DataLogic,里面有订单的明细金额、回款单的回款金额、支付单的支付金额,分属于三个不同的物理表,按照年份汇总这三个金额。

..

         直接拖选年份维度及三种金额的汇总就完成查询了。再次强调这个界面查询过程不用理解JOIN,不用理会数据来自哪个物理表。

..

         但为了了解本质,还是要解释下隐藏的细节,尽管各表中的日期含义不一样(签单日期、回款日期、支付日期),但它们只要同属于年份这个维度,就能关联到一起查询。自动生成的DQL语句也能看到JOIN了不同的物理表:

         SELECT

                  T1.sum(明细金额)

                  ,T2.sum(金额)

                  ,T3.sum(支付金额)

                  ON

         FROM

                  订单明细 T1 BY T1.订单.签单日期#

                  JOIN 回款单 T2 BY T2.回款日期#

                  JOIN 支付单 T3 BY T3.支付日期#

 

         这些分组查询中经常用BY子句,实际DQL语法中比较智能,假如在某个表下,一个维度只有一个明确的来源,就可以省略掉,DQL查询引擎会自动找到维度来源字段。比如上面的回款单里只有一个日期字段(回款日期),那它的BY就可以省掉。

汇总前/后的两种过滤

         做分组查询时,指标的过滤条件分为汇总前、汇总后两种,理解起来也不困难,订单金额小于100的不参与汇总;汇总后,只选出单个产品月销售额大于5000的数据。..

         以下是两种过滤条件在DQL中的体现:

         SELECT

                  T1.count(订单ID) 订单数量

                  ,T1.sum(金额) 金额求和

                  ON ,年月 ,产品

         FROM

                  订单表 T1

                           WHERE T1.金额>100

                           BY T1.发货日期# ,T1.发货日期#年月 ,T1.产品ID

         HAVING T1.sum(金额)>5000

自助查询源代码与多维分析

         自助查询功能的源代码和多维分析是在一起的,涉及的JSJAVA文件基本相同,只是taglib分开实现了:

         浏览器端的Javascript源代码都在{WEB根目录}/raqsoft/guide/js/下,主要的js文件有dqlApi.jsdqlreport.jsquery.jsraqsoftApi.jswhere.jscommon.js

         JSP中的taglib技术展示页面,taglib定义在{WEB根目录}/WEB-INF/raqsoftQuery.tld,实现taglibJava类是com.raqsoft.guide.tag.QueryTag.java,其它一些主要Java类有:

         com.raqsoft.guide.web.DataSphereServlet.java,请求入口的Servlet

         com.raqsoft.guide.web.dl.ActionResultPage.java,处理大部分Servlet请求,主要功能代码在这个类里。

         com.raqsoft.guide.web.dl.DfxQuery.java,用内置集算器脚本查询/计算不同来源的数据。

         com.raqsoft.guide.web.dl.DfxData.java,缓存计算好的数据集。

 

         阅读自助查询相关的源代码后,发现它和多维分析的逻辑非常类似,都是从DQL Server获得元数据信息,用taglib制作一个拖拽互动的界面,自动生成DQL语句,从而查询数据。虽然两个界面的对元数据的组织方式、操作略有差异,但都会体现DQL语法中的自动外键关联、按维分组对齐关联这些特点。这些界面可以认为是对DQL的可视化,其它多维分析产品做的CUBE建模则可以认为是对SQL的可视化。无论怎么设计两种查询语言的可视化方案,各自的语法特征最终决定了查询过程中的思维方式。

         SQL做不到隐藏关联,所以其可视化只适用于预制CUBE,而DQL能进一步的直接用于分析、全库数据自助查询,无论谁设计一套DQL可视化都容易做到。