BI 前端实践 8:关联查询语言──DQL

实践目标

         通过对比DQL及它翻译出的SQL,初步认识DQL,体会它怎么支持有关联(JOIN)的多维分析。

认识DQL

        观察上一节最后一个多维分析例子,前端分析时,DQL服务器上会打印出这样的日志:

..

        能看到提交到DQL服务器的查询语句是DQL,和SQL语法有区别。DQL服务器把它翻译出来成SQL后,才去原始数据库执行获得数据。

        原始数据库被DQL服务器包裹起来隐藏掉,并且更换了一种新的查询语言。这样的结构令人产生疑问:为什么多维分析不直接拼成SQLDQL比起SQL来有什么便利,有什么区别,这就是本篇所探究的内容。

        如果只是在单个表查数据,DQLSQL几乎一样,不能体现差异,重点要关注关联查询。

测试数据

         选用一套关联方式比较全面的测试数据,这个Mysql库涉及六个表,订单表(orders)是事实表,其它的是维表员工表(employee)、员工销售表(employee_sale)、部门表(department)、区域表(area)、产品表(product),维表之间也存在不少关联。

..

         这些关联关系比较常见,总结一些特点:

        1、 员工销售表是员工表的补充,它们之前是一对一的关系。

        2、 存在着多级关联,订单表员工表部门表。

        3、 区域表存在自己关联自己的情况。

        4、 员工表部门表,同时部门表员工表(找经理),这两个表互相关联。

        5、 订单表和区域表关联了两次,存在重复关联。

        6、 大部分通过单个字段关联,而产品表属于多字段组合主键(产品ID、批次ID),这样订单表产品表就通过两个字段关联了。

制作有关联关系的元数据

         新建元数据orders.lmd,直接导入这些数据库表,如果数据库中定义了主键,外键关系,也会自动导入,有时候数据库为了使用灵活,往往不定义外键关系,甚至主键。这时就要在元数据中正确的补上它们(元数据里正确设置表关系非常重要,依赖预设的这些关系才能实现自动关联的多维分析),手动设置主键:

..

         手动添加外键关系:

..

         特别还要注意多字段外键的设置:

..

         设置外键前,需要先把指向表的主键设置正确。所以操作时,先把所有表的主键设置正确,再逐一设置外键,这样不容易遗漏。

        如下图中的元数据视图,是另外一种对关系的展示方式,是一个总线结构,中间的总线是所有的主键,在做多维分析时,这些主键字段是用来做分组的,也就是常说的维度。分列在两边的表同样会标识出主键,所有字段中,不管是主键字段,还是外键字段,都会指向相关的维度。从本质上说,指向同一个维度的所有字段本来就是指代同一种实体。

..

 

执行DQL的测试工具

        针对上面制作好的元数据,在DQL设计器中可以测试执行DQL

..

    输入 DQL,选择执行查询的数据源 Mysql 库 orders,点击翻译,就能看到翻译好的 SQL 和下方的查询结果:

..

         接下来测试几个DQL示例。

DQL示例──同维表

        employee_saler表是对employee表的补充,记录每个员工作为销售的信息,如主要负责城市字段(major_city) employee_saler的主键字段设置外键到employee表主键(employee_saler.emp_id=employee.emp_id),这样两表主键指向同一个维,也就是同维表,同时查询两个表的信息时,可以from随意一个表就可以:

DQL

        select

                emp_name

                ,gender

                ,major_city

        from employee

 

翻译成的SQL:

        SELECT

                T_1.emp_name emp_name

                ,T_1.gender gender

                ,T_1_1.major_city major_city

        FROM

                employee T_1

                LEFT JOIN employee_saler T_1_1 ON T_1.emp_id=T_1_1.emp_id

 

         两个(甚至多个)同维表中的数据都是一对一的关系,元数据中会把它们自动贴合在一起,当成一个表查询:

..

DQL示例──多级关联表

        查询目标是:以订单表为起点,查询出销售姓名,销售所在部门。涉及订单表(orders),销售员工表(employee),部门表(department)

DQL

        select 

                order_id

                ,emp_id.emp_name

                ,emp_id.dept_id.dept_name

        from orders

 

翻译成的SQL:

        SELECT

                T_1_1.order_id order_id

                ,T_1_2.emp_name emp_name

                ,T_1_3.dept_name dept_name

        FROM

                orders T_1_1

                LEFT JOIN employee T_1_2 ON T_1_1.emp_id=T_1_2.emp_id

                LEFT JOIN department T_1_3 ON T_1_2.dept_id=T_1_3.dept_id

 

        这个查询的特点是发生了多级的外键关联,DQL中用T.FK1.FK2.F3的方式消除了显式的关联。

 

DQL示例──自关联表

        查询目标是:查询出订单的发货城市名称、以及所在的省份名称、地区名称。涉及订单表(orders),区域表(area)

DQL

        select

                send_city.name city,

                send_city.pid.name province,

                send_city.pid.pid.name region

        from orders

 

翻译成的SQL:

        SELECT

                T_1_2.name city

                ,T_1_3.name province

                ,T_1_4.name region

        FROM

                orders T_1_1

                LEFT JOIN area T_1_2 ON T_1_1.send_city=T_1_2.area_id

                LEFT JOIN area T_1_3 ON T_1_2.pid=T_1_3.area_id

                LEFT JOIN area T_1_4 ON T_1_3.pid=T_1_4.area_id

 

        区域表是自关联的,通过本表pid把上下两级区域关联起来,orders.send_city是城市,那orders.send_city.pid就是省份、orders.send_city.pid.pid就是地区。

 

DQL示例──互关联表

        查询目标是:查询出员工姓名及他的经理姓名。涉及销售员工表(employee),部门表(department)

DQL

        select

                emp_name,

                dept_id.manager.emp_name manager

        from employee

 

翻译成的SQL:

        SELECT

                T_1_1.emp_name emp_name

                ,T_1_3.emp_name manager

        FROM

                employee T_1_1

                LEFT JOIN department T_1_2 ON T_1_1.dept_id=T_1_2.dept_id

                LEFT JOIN employee T_1_3 ON T_1_2.manager=T_1_3.emp_id

         员工表字段dept_id外键到部门表,部门表字段manager又外键回员工表,这种互相关联的情况同样适用多级字段表达式,employee.dept_id.manager.emp_name表示:员工的部门的经理的姓名。

DQL示例──重复关联表

        查询目标是:查询出订单的发货、收货城市名称。涉及销售员工表(employee),区域表(area)

DQL

        select

                send_city.name sendCity

                ,receive_city.name receiveCity

        from orders

 

翻译成的SQL:

        SELECT

                T_1_2.name sendCity,

                T_1_3.name receiveCity

        FROM

                orders T_1_1

                LEFT JOIN area T_1_2 ON T_1_1.send_city=T_1_2.area_id

                LEFT JOIN area T_1_3 ON T_1_1.receive_city=T_1_3.area_id

 

         订单表有发货、收货两种城市,都关联到区域表,用send_city.namereceive_city.name两个字段表达式获得两个城市名称。

DQL示例──多字段关联表

         测试多字段外键的关联表,是否能像单字段外键那样直接引用:

DQL

        select

                send_city

                ,order_product.product_name

        from orders

 

翻译成的SQL:

        SELECT

                T_1_1.send_city send_city

                ,T_1_2.product_name product_name

        FROM

                orders T_1_1 LEFT JOIN product T_1_2

                ON T_1_1.product_id=T_1_2.product_id AND T_1_1.batch_id=T_1_2.batch_id

         订单表中order_product是多字段外键,仍然能用order_product.product_name的方式获得产品表的字段。

总结

         可以看出来,DQL中用同维表、多级的字段表达式隐藏了SQL中的表关联,多级的字段表达式也契合多维分析界面中的元数据树,把生成SQL这个比较难的动作封装到DQL服务器里了。基于这种结构,只引入DQL服务器,自己开发一套多维分析前端界面也不算难,正确拼出DQL就能得到数据。