数据派THU

发布清华大学数据科学相关科研动态、教学成果及线下活动

数据蒋堂 | JOIN延伸 - 维度查询语法

640?wx_fmt=png&wxfrom=5&wx_lazy=1

来源:数据蒋堂

作者:蒋步星

本文长度为1320,建议阅读3分钟

本文为你讲解JOIN延伸之维度查询语法。


640?wx_fmt=png&wxfrom=5&wx_lazy=1

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

先定义字段维度

  1. 维度字段的维度为其本身;

  2. 外键字段的维度为相应外键表中关联字段的维度;

  3. 测度字段没有维度。

这是个递归定义。

640?wx_fmt=png

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

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

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

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

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

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


640?wx_fmt=png

还要定义表的广义字段

  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的广义字段。


640?wx_fmt=png

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

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

640?wx_fmt=png

我们再来解决维度对齐中的那个小漏洞,考查前面文章中的例子:

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

用于向维度对齐的字段还可以是广义字段。

640?wx_fmt=png

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

比如前面那个三表对齐的例子中,我们希望按月而不是按日期统计,当然可以写成:

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部分可以不动。

640?wx_fmt=png

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

这样有两个问题:

1. 若干套汇总数据和一个基础数据是如何对应的?

2. 怎么知道哪些组合是最常用的?

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

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

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

专栏作者简介

640?

润乾软件创始人、首席科学家


清华大学计算机硕士,著有《非线性报表模型原理》等,1989年,中国首个国际奥林匹克数学竞赛团体冠军成员,个人金牌;2000年,创立润乾公司;2004年,首次在润乾报表中提出非线性报表模型,完美解决了中国式复杂报表制表难题,目前该模型已经成为报表行业的标准;2014年,经过7年开发,润乾软件发布不依赖关系代数模型的计算引擎——集算器,有效地提高了复杂结构化大数据计算的开发和运算效率;2015年,润乾软件被福布斯中文网站评为“2015福布斯中国非上市潜力企业100强”;2016年,荣获中国电子信息产业发展研究院评选的“2016年中国软件和信息服务业十大领军人物”;2017年, 自主创新研发新一代的数据仓库、云数据库等产品即将面世。


数据蒋堂

《数据蒋堂》的作者蒋步星,从事信息系统建设和数据处理长达20多年的时间。他丰富的工程经验与深厚的理论功底相互融合、创新思想与传统观念的相互碰撞,虚拟与现实的相互交织,产生出了一篇篇的沥血之作。此连载的内容涉及从数据呈现、采集到加工计算再到存储以及挖掘等各个方面。大可观数据世界之远景、小可看技术疑难之细节。针对数据领域一些技术难点,站在研发人员的角度从浅入深,进行全方位、360度无死角深度剖析;对于一些业内观点,站在技术人员角度阐述自己的思考和理解。蒋步星还会对大数据的发展,站在业内专家角度给予预测和推断。静下心来认真研读你会发现,《数据蒋堂》的文章,有的会让用户避免重复前人走过的弯路,有的会让攻城狮面对扎心的难题茅塞顿开,有的会为初入行业的读者提供一把开启数据世界的钥匙,有的甚至会让业内专家大跌眼镜,产生思想交锋。


往期回顾:

数据蒋堂 | JOIN延伸 - 维度概念

数据蒋堂 | JOIN提速 - 有序归并

数据蒋堂 | JOIN提速 - 外键指针的衍生

数据蒋堂 | JOIN提速 - 外键指针化

数据蒋堂 | JOIN简化 - 意义总结

数据蒋堂 | JOIN简化-消除关联

数据蒋堂 | JOIN简化 - 维度对齐

数据蒋堂 | JOIN运算剖析

数据蒋堂 | 迭代聚合语法

数据蒋堂 | 非常规聚合

数据蒋堂 | 再谈有序分组

数据蒋堂 | 有序分组

数据蒋堂 | 非等值分组

数据蒋堂 | 还原分组运算的本意

数据蒋堂 | 有序遍历语法

数据蒋堂 | 常规遍历语法

数据蒋堂 | 从SQL语法看离散性

数据蒋堂 | 从SQL语法看集合化

数据蒋堂 | SQL用作大数据计算语法好吗?

数据蒋堂 | SQL的困难源于关系代数

数据蒋堂 | SQL像英语是个善意的错误

数据蒋堂 | 开放的计算能力为数据库瘦身

数据蒋堂 | 计算封闭性导致臃肿的数据库

数据蒋堂 | 怎样看待存储过程的移植困难

数据蒋堂 | 存储过程的利之弊

数据蒋堂 | 不要对自助BI期望过高

数据蒋堂 | 报表的数据计算层

数据蒋堂 | 报表应用的三层结构

数据蒋堂 | 列式存储的另一面

数据蒋堂 | 硬盘的性能特征

数据蒋堂 | 我们需要怎样的OLAP?

数据蒋堂 | 1T数据到底有多大?

数据蒋堂 | 索引的本质是排序

数据蒋堂 | 功夫都在报表外--漫谈报表性能优化

数据蒋堂 | 非结构化数据分析是忽悠?

数据蒋堂 | 多维分析的后台性能优化手段


校对:谭佳瑶

为保证发文质量、树立口碑,数据派现设立“错别字基金”,鼓励读者积极纠错

若您在阅读文章过程中发现任何错误,请在文末留言,或到后台反馈,经小编确认后,数据派将向检举读者发8.8元红包

同一位读者指出同一篇文章多处错误,奖金不变。不同读者指出同一处错误,奖励第一位读者。

感谢一直以来您的关注和支持,希望您能够监督数据派产出更加高质的内容。

640?wx_fmt=jpeg

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

数据蒋堂 | JOIN延伸 - 维度查询语法

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭