超维报表核心模型 | 润乾 -九游会登陆

维查询语言dql介绍

超维报表内置的面向程序员的维查询语言（dimensional query language），简称dql。dql可以在元数据的基础上提供无关联的简单查询接口。

在从关系数据库中获取数据时，最通用的方法就是使用 sql语句。在需要处理多表关联的业务时，使用sql就会遇到比较大的麻烦，难以书写，更难以理解。针对这种需要，超维报表提供了更简单的无关联维查询语法dql来取代复杂的sql。

dql语法包括：一般查询、分组汇总、连接、维过滤等。

包含外键属性化的一般查询语句：

select 客户 id.客户名称,回款日期,金额

from 回款单

where 客户 id.城市编码=30101

分组汇总语句：

select 客户 id,

回款单.count(*) 回款笔数

only on 客户.客户 id

from 回款单

by 客户 id

连接语句：

select 销售.责任地区编码推广宣传地区,

雇员.count(籍贯城市编码#地区) 本地员工数

from 销售

by 责任地区编码

join雇员

by 籍贯城市编码#地区

外键属性化

在dql中，外键指向记录可看作本表子属性引用。外键属性化可以使多表关联查询转化成单表查询。并且允许多层和递归引用。例如下图：

bdimension_coremodel_1

如果需要查询女经理的男员工，sql语法需要将员工表与部门表连接后再与员工表连接,或者采用更复杂的多重子查询。具体写作：

select a.* from 员工 a,部门 b,员工 c

where a.部门=b.编号 and b.经理=c.编号

and a.性别=’男’ and c.性别=’女’

dql语法可以直接将外键指向记录作为本表子属性，具体写作：

select * from 员工

where 性别=’男’ and 部门.经理.性别=’女’

可以看出，超维报表负责将简单的无关联dql转换成有复杂关联的sql，使得查询变得简单。

同维表等同化

在物理数据库的设计中，基于性能、存储空间等的考虑，往往将同一个对象的多种属性，分别存在不同的物理表中，如：客户表和vip客户表。

bdimension_coremodel_2

sql语法需要将两个同维表先连接：

select a.客户id,a.客户名称,b.vip级别

from 客户 a left join vip客户 b on a.客户id=b.客户id

在超维报表中，这种同维表（主键相同的表）可视为一个表，访问其中任何一个均可直接引用其它表的字段。横向分表对上层透明，根据引用字段即时决定连接表，比事先预定义的视图效率更高。

因此，dql语法可以只从一个表引用其它同维表字段:

select 客户id,客户名称,vip级别 from 客户

这里连接的是vip客户表:

select * from 订单 where 客户id.vip级别=’钻石’

按维对齐连接

多表连接时针对每表独立设定汇总维度，无须关心表间连接关系，降低问题难度。

按维对齐连接例一：

如果需要统计部门员工平均年龄，sql语法需要写清两表之间的关联关系：

select 部门.名称,avg(员工.年龄) from 部门,员工

where 员工.部门=部门.编号

group by 部门.名称

dql语法只要各自向目标汇总维度对齐，无须书写表间关系条件，主键作为汇总维度可省略：

select 部门.名称,员工.avg(年龄)

from 员工 by 部门

按维对齐连接例二：

对于订单表和回款单表，如果要按日期统计订单金额和回款额，可以以目标结果集为导向，分别设定目标维度和汇总项及其关联项，符合自然思维：

sql语法对于这种采用非主键属性字段连接的关系，需要用子查询先分组再连接：

select t1.日期,t1.金额,t2.金额 from

(select 签单日期日期,sum(订单金额) 金额 from 订单 group by 签单日期) t1,

(select 回款日期日期,sum(金额) 金额 from 回款单 group by 日期) t2

where t1.日期 = t2.日期

习惯的先连接再分组写法，虽然简单些，但结果是错误的：

select 订单.签单日期,sum(订单.订单金额),sum(回款单.金额)

from 订单 join 回款单 on 订单.签单日期=回款单.回款日期

group by 订单.签单日期

dql语法只要各自向目标汇总维度对齐即可，不用关心连接和分组的次序，理解书写简单且不易犯错：

select 订单.sum(订单金额) 订单金额,回款单.sum(金额) 回款金额 on 日期

from 订单 by 签单日期 join 回款单 by 回款日期

按维对齐连接例三：

按地区统计销售员数据与合同额，可以按属性化的外键对齐。

sql语法对于这种嵌套多层的情况，理解和书写都非常困难：

select t1.地区,t1.数量,t2.金额 from

(select 地区,count(1) 数量 from 销售员 group by 地区）t1, (select 客户.地区地区,sum(合同.金额) 金额 from 客户,合同

where 客户.编号=合同.客户 group by 客户.地区 ) t2

where t1.地区 = t2.地区

dql语法将属性化外键当作普通字段一样处理即可：

select 销售员.count(1),合同.sum(金额) on 地区

from 销售员 by 地区 join 合同 by 客户.地区

透明化层向分表

超维报表元数据层可以智能地把针对基础表的查询转换成针对汇总表的查询，例如：汇总表如下：

bdimension_coremodel_4

按照月份汇总查询的时候，dql写做：

select sum(单价*数量) on 月,供应商

from 入库单 by 入库日期#月,供应商

因为入库单汇总表记录数比入库单表少，因此超维报表会转换成针对入库单汇总的sql语句：

select 月,供应商,入库金额 from 入库单汇总

透明化纵向分表

在数据库中，有时数据会分开存储在多个数据结构完全相同的表中，如分年度存储的销售订单数据、分部门存储的员工资料、分地区存储的客户信息等等。在数据查询与分析时，往往需要将这些表中的数据联合起来，比如查询历年销售数据、查询所有部门的员工资料或者查询全部客户信息等。按照月份分表的订单数据如下图：

bdimension_coremodel_5

超维报表可以根据dql中查询数据的范围确定具体访问的分表，例如对于按照订单id分表的订单表，dql写做：

select * from 订单总表 where 订单id=10555

超维报表确定订单id在订单2012表中，因此将dql转换成sql：

select t_1.订单id 订单id from (select * from 订单2012) t_1 where t_1.订单id=10555

dql与sql对比理解

dql查询语言比sql更简单易学，能够以更加简单的写法实现更加复杂的查询。

以下为dql和sql的对比实例：

1.单表统计（按时间和分公司统计某个事实表），sql与dql理解和书写基本相同，但dql用主键分组时可以省略不写，更加简单。

select sum(buss_zwsc),avg(fee_rate), time_id,company_id from fact_package
group by time_id,company_id

select sum(buss_zwsc),avg(fee_rate) on dim_time,dim_company
from fact_package

2.外键关联（按网络类型分类统计），dql无须使用连接，可当作单表处理。

select dim_net.net_kind,sum(fact_net.buss_xqsl)
from fact_net,dim_net
where fact_net.net_id=dim_net.net_id
and dim_net.net_rate>1
group by dim_net.net_kind

select net_id.net_kind,sum(buss_xqsl)
from fact_net
where net_id.net_rate>1
by net_id.net_kind

3.多表汇总（按时间和分公司统计多个事实表），dql从单表到多表时时写法基本一致，只要把各个表依次写上即可；而sql需要用嵌套的带分组子查询再连接。

select a.time_id,a.company_id,a.buss_zwsc,
b.revenue,c.buss_rdxqsl
from (select time_id,company_id,
sum(buss_zwsc) as buss_zwsc
from fact_package
group by time_id,company_id) a
join (select time_id,company_id,
sum(revenue) as revenue
from fact_money_wide
group by time_id,company_id) b
on a.time_id=b.time_id
and a.company_id=b.company_id
join (select time_id,company_id,
sum(buss_rdxqsl) as buss_rdxqsl
from fact_net
group by time_id,company_id) c
on a.time_id=c.time_id
and a.company_id=c.company_id

select fact_package.sum(buss_zwsc),
	fact_money_wide.sum(revenue),
		fact_net.sum(buss_rdxqsl)
on dim_time,dim_company
from fact_package
full join fact_money_wide
full join fact_net

4.表间用full join时（任何一组时间和分公司出现都有效），dql只要简单改写join修饰，但sql需要多处添加coalesce函数，代码零乱繁琐，出错概率加大。

select coalesce(a.time_id,b.time_id,c.time_id),
coalesce(a.company_id,b.company_id,
c.company_id),
a.buss_zwsc,
b.revenue,c.buss_rdxqsl
from (select time_id,company_id,
sum(buss_zwsc) as buss_zwsc
from fact_package
group by time_id,company_id) a
full join (select time_id,company_id,
sum(revenue) as revenue
from fact_money_wide
group by time_id,company_id) b
on a.time_id=b.time_id
and a.company_id=b.company_id
full join (select time_id,company_id,
sum(buss_rdxqsl) as buss_rdxqsl
from fact_net
group by time_id,company_id) c
on coalesce(a.time_id,b.time_id)=c.time_id
and coalesce(a.company_id,b.company_id)=c.company_id

select fact_package.sum(buss_zwsc),
	fact_money_wide.sum(revenue),
		fact_net.sum(buss_rdxqsl)
on dim_time,dim_company
from fact_package
full join fact_money_wide
full join fact_net

5.再带有维条件时（指定分公司的统计），dql只需要在维度上一次性加写条件即可，但sql需要在每个子查询上加写，否则涉及数据量过多导致性能严重下降。

select coalesce(a.time_id,b.time_id,c.time_id),
coalesce(a.company_id,b.company_id,
c.company_id),
a.buss_zwsc,
b.revenue,c.buss_rdxqsl
from (select time_id,company_id,
sum(buss_zwsc) as buss_zwsc
from fact_package
group by time_id,company_id
where company_id=’2710001’ ) a
full join (select time_id,company_id,
sum(revenue) as revenue
from fact_money_wide
group by time_id,company_id
where company_id=’2710001’) b
on a.time_id=b.time_id
and a.company_id=b.company_id
full join (select time_id,company_id,
sum(buss_rdxqsl) as buss_rdxqsl
from fact_net
group by time_id,company_id
where company_id=’2710001’ ) c
on coalesce(a.time_id,b.time_id)=c.time_id
and coalesce(a.company_id,b.company_id)=c.company_id

select fact_package.sum(buss_zwsc),
	fact_money_wide.sum(revenue),
		fact_net.sum(buss_rdxqsl)
on dim_time,dim_company
where dim_company==’2710001’
from fact_package
full join fact_money_wide
full join fact_net