第 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条件子句提高数据检索性能
1143 0
使用instr 函数优化替换Like条件子句提高数据检索性能案例总结
|
4月前
|
分布式计算 BI MaxCompute
SQL 能力问题之输出聚合的维度列的名称,如何解决
SQL 能力问题之输出聚合的维度列的名称,如何解决
|
5月前
|
SQL 安全 数据挖掘
Elasticsearch如何聚合查询多个统计值,如何嵌套聚合?并相互引用,统计索引中某一个字段的空值率?语法是怎么样的?
Elasticsearch聚合查询用于复杂数据分析,包括统计空值率。示例展示了如何计算字段`my_field`非空非零文档的百分比。查询分为三步:总文档数计数、符合条件文档数计数及计算百分比。聚合概念涵盖度量、桶和管道聚合。脚本在聚合中用于动态计算。常见聚合类型如`sum`、`avg`、`date_histogram`等。组合使用可实现多值统计、嵌套聚合和空值率计算。[阅读更多](https://zhangfeidezhu.com/?p=515)
295 0
Elasticsearch如何聚合查询多个统计值,如何嵌套聚合?并相互引用,统计索引中某一个字段的空值率?语法是怎么样的?
|
5月前
|
分布式计算 DataWorks 大数据
MaxCompute产品使用合集之查询优化中对使用concat函数拼接起来的分区字段进行查询时,能否进行分区剪枝
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
5月前
|
分布式计算 关系型数据库 数据挖掘
实时数仓 Hologres产品使用合集之如果采用组合主键,比如id + 时间时间(字符串),做为组合主键后是否会导致数据倾斜呢
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
|
6月前
|
SQL 分布式计算 算法
当两个表进行Join操作时,如果它们的数据不符合MapJoin规范,您可以尝试以下优化方案
当两个表进行Join操作时,如果它们的数据不符合MapJoin规范,您可以尝试以下优化方案
82 4
|
6月前
|
SQL C++
组合两个表(C++)
组合两个表(C++)
34 0
|
SQL 关系型数据库 MySQL
sql处理重复的列,更好理清分组和分区
sql处理重复的列,更好理清分组和分区
85 0
|
缓存 自然语言处理 数据挖掘
白话Elasticsearch50-深入聚合数据分析之基于doc values正排索引的聚合内部原理
白话Elasticsearch50-深入聚合数据分析之基于doc values正排索引的聚合内部原理
108 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效率,记住内外关联条件不要乱放