第 37 期:JOIN 延伸 – 维度查询语法

简介:

有了维度定义后,我们就可以来梳理前面讲过的简化 JOIN 语法了。

先定义字段维度:

维度字段的维度为其本身;
外键字段的维度为相应外键表中关联字段的维度;
测度字段没有维度;
这是个递归定义。

然后再严格定义同维表和主子表:

同维表:两个表的主键字段维度集合对应相同,则称两个表同维;

主子表:某个表的主键字段维度集合是另一个表的主键字段维度集合的真子集,则前者称为后者的主表,后者为前者的子表;

按这个定义,容易得到这些结论:

同维表的同维表是同维表,同维表是等价关系;

主表的同维表是主表,子表的同维表是子表;子表的子表是子表;

还要定义表的广义字段:

1.本表的字段是其广义字段;
2.广义字段作为普通字段的所在表的同维表的字段是广义字段;
3.某广义字段是外键字段时,那么它对应的外键表的字段是广义字段;
4.广义字段的维函数是广义字段;
这还是个递归定义。

回顾前面的例子来理解:
SELECT * FROM employee WHERE nationality=’美国’ AND department.manager.nationality=’中国’

SELECT id,name,salary+allowance FROM employee
其中 department.manager.nationality,salary,allowance 都是表 employee 的广义字段。

有了广义字段概念后,前面所说的消除关联的语法就是很自然的事了。在 SQL 语法中允许将表的广义字段当作普通字段引用,就可以实现外键属性化和同维表等同化,再允许将子表的广义字段作为集合字段在本表运算时进行聚合运算,就实现了主子表一体化。结合前面文章中的例子很容易理解。

这种改进的语法以维度概念作为核心 ,为和 SQL 区别,我们把它称为DQL(D 是 Dimension)。

我们再来解决维度对齐中的那个小漏洞,考查前面文章中的例子:
SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount)

FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date
这个查询是想按日期分别统计合同额、回款额及发票额,但选出的字段(表达式)中并没有作为关键字段的日期,而只有一些合计数,这会得到一个让人看不懂的结果集。

这里参与 JOIN 的三个表中都有 date 字段,选任何一个放在 SELECT 中都是不合适的,因为任何一个表都可能有日期不全的情况,而且这三个表是完全对称的关系,也没有道理让其中任何一个特殊化。这种情况时,在 SQL 中大概要写成 coalesce(Contract.date,Payment.date,Invoice.date) 的形式(Oracle 语法),有点繁琐。

当我们从数据库结构中已经抽取出维度之后,就可以较方便地解决这个问题了。显然,这几个 date 都是有维度的字段,我们把这个维度命名为 DATE,那么上面语句可以写成这样:

SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount) ON DATE

FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date
增加一个 ON 子句来指明用于对齐的目标维度,这些维度会自动被选出到结果集,并处理空值的情况。

类似地,后一个例子应当写成:
SELECT Sales.COUNT(1), Contract.SUM(price) ON AREA

FROM Sales GROUP BY area FULL JOIN Contract GROUP BY customer.area
用于向维度对齐的字段还可以是广义字段。

另外,在有了维函数概念后,还可以进一步简化某些查询。

比如前面那个三表对齐的例子中,我们希望按月而不是按日期统计,当然可以写成:
SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount) ON MONTH FROM Contract GROUP BY month(date) FULL JOIN Payment GROUP BY month(date) FULL JOIN Invoice GROUP BY month(date)

其中 month 是一个维函数,以日期为参数,返回 MONTH 维度取值。

不过,维函数在数据结构设计时就已经定义好了,在明确知道对齐维度时,可以根据用来对齐的字段自动寻找一个合适的维函数来用,这样上面的句子简化成不写维函数也不会有歧义:
SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount) ON MONTH FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date

想改变统计维度的层次时,只要改写 ON 的部分即可,GROUP BY 部分可以不动。

我们知道,在多维分析时为了提高性能常常会做预先汇总,也就是根据分析中可能出现的维度组合事先把测度的统计值计算好保存起来,需要时直接引用而不必再从头遍历计算。而把所有组合情况都预先汇总是不大现实的(因为存储空间过大),一般只能选择最常用的维度组合。

这样有两个问题:1. 若干套汇总数据和一个基础数据是如何对应的?2. 怎么知道哪些组合是最常用的?

在 SQL 体系下,如果是针对没有关联运算的单表,那么这两个问题都不是很难处理。基础数据就是一个单表,汇总数据和这个表的某些维度组合对应;将历史分析过程记录下来之后,就可以统计出哪些维度组合最常用,从而指导汇总数据的建设。

但是,如果允许关联运算,多维分析过程中会拼出带 JOIN 的 SQL 来,这个问题就复杂多了。基于关系代数的 JOIN 定义,很难描述汇总数据与基础数据的对应关系,而维度组合也隐藏在 SQL 的 JOIN 语法句,很难拆出来当前的查询到底在针对那些维度进行汇总。

而使用 DQL 就简单多了。汇总数据和基础数据的对应问题,仅仅是把普通字段推广到广义字段,逻辑上看仍然是个单表。而 DQL 语句中能很清晰明了地看出每句查询是是在针对哪些维度(广义字段)汇总,这样就就很容易统计最常用的维度组合。

作者:279400248
链接:http://c.raqsoft.com.cn/article/1533881617438
来源:乾学院
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

相关文章
|
SQL 索引 数据库
使用instr 函数优化替换Like条件子句提高数据检索性能案例总结
使用instr 函数优化替换Like条件子句提高数据检索性能
1148 0
使用instr 函数优化替换Like条件子句提高数据检索性能案例总结
|
4月前
|
分布式计算 BI MaxCompute
SQL 能力问题之输出聚合的维度列的名称,如何解决
SQL 能力问题之输出聚合的维度列的名称,如何解决
|
4月前
|
SQL 数据处理
SQL 能力问题之合并两个存在交叉的日期区间,如何解决
SQL 能力问题之合并两个存在交叉的日期区间,如何解决
|
5月前
|
SQL 安全 数据挖掘
Elasticsearch如何聚合查询多个统计值,如何嵌套聚合?并相互引用,统计索引中某一个字段的空值率?语法是怎么样的?
Elasticsearch聚合查询用于复杂数据分析,包括统计空值率。示例展示了如何计算字段`my_field`非空非零文档的百分比。查询分为三步:总文档数计数、符合条件文档数计数及计算百分比。聚合概念涵盖度量、桶和管道聚合。脚本在聚合中用于动态计算。常见聚合类型如`sum`、`avg`、`date_histogram`等。组合使用可实现多值统计、嵌套聚合和空值率计算。[阅读更多](https://zhangfeidezhu.com/?p=515)
297 0
Elasticsearch如何聚合查询多个统计值,如何嵌套聚合?并相互引用,统计索引中某一个字段的空值率?语法是怎么样的?
|
6月前
|
SQL 分布式计算 算法
当两个表进行Join操作时,如果它们的数据不符合MapJoin规范,您可以尝试以下优化方案
当两个表进行Join操作时,如果它们的数据不符合MapJoin规范,您可以尝试以下优化方案
85 4
|
6月前
|
SQL C++
组合两个表(C++)
组合两个表(C++)
34 0
【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放
从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,还是那就话,别死记网上结论、在使用内外关联时,特别是简写方式时记住关联条件不要乱放!【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。
【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放
|
缓存 自然语言处理 Swift
本周推荐 | 表达式引擎的组合子实现方案
推荐语:本文清晰而详细地介绍了如何使用 Parser 组合子方案,结合 Monad 通过合理的分层、抽象和组合,在性能达标的情况下实现消息场景中函数式的表达式解析。非常具有实践意义,推荐阅读学习! ——大淘宝技术终端开发工程师 闲行
260 0
本周推荐 | 表达式引擎的组合子实现方案
|
6月前
|
关系型数据库 分布式数据库 PolarDB
使用 PolarDB 开源版 bloom filter index 实现任意字段组合条件过滤
背景PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.本文将介绍使用 PolarDB 开源版 bloom filter index 实现任...
128 0
|
存储 SQL 并行计算
使用 PolarDB 开源版 bloom filter index 实现任意字段组合条件过滤
PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力. 本文将介绍使用 PolarDB 开源版 bloom filter index 实现任意字段组合条件过滤
269 0