跳至正文

【格物篇52】SQL的“层”order与问题的级别LOD

标签:

本文起初发布于微信公众号,特此转移到博客,并二次整理。

01—SQL中的“阶”(order)

近期阅读MICK的《SQK进阶教程》,看到最后一小节“SQL中的层级”(2.10),内心视为一震,我是没有料到这个话题的。

作者从一个众所周知的命题开始,解释了它背后的原理。

“在SQL中使用GROUP BY聚合之后,不能引用原表中除聚合键之外的列?”MICK

这里用superstore超市数据举例,可以使用Excel透视表计算“不同类别的销售额总和”,或者使用SQL的聚合查询完成。

在这个简单的问题中,“类别”是分类,“销售额总和”是答案(注意,不是“销售额”是答案,而是聚合是答案)。使用如下的SQL轻松完成:

SELECT 类别,SUM('销售额')FROM superstoreGROUP BY 类别

如果把它改为如下的查询,程序就会出错,它违反了标准SQL的规定。

SELECT 类别,SUM('销售额') ,子类别FROM superstore GROUP BY 类别

标准SQL规定,对表进行聚合查询只能在SELECT 子句中出现3种内容:

  • 通过GROUP BY 子句指定的聚合键
  • 聚合函数SUM,AVG等
  • 常量

在这个问题中,明细中存储的“销售额”仅仅在最为详细的级别,即“订单ID*产品ID”上才有业务意义,也就是,明细中“销售额”是每一笔交易(deal)的属性,而非每个“类别”的属性。所以,“各个类别的销售额总和”中的“销售额总和”, 必须是统计性质的属性——使用SUM、AVG等统计函数获得一个值。从这个角度可以理解,专业数据库领域把字段(field)或者列(column)称之为“属性”(attribute),字段、列都是通俗性的用词。(转印MICK)。

02—聚合是分析的本质,阶的变化

从上面的案例,我们可以明确的区分如下的两个数据:

  • 每个订单ID、每个产品ID的销售额
  • 每个类别的销售额总和

后者是可以从前者中聚合而来的(类别可以视为“产品ID”的属性,就像“性别”是人的属性)。为了更专业地区分二者,可以把SQL查询的明细表称之为 0阶,而把GROUP BY聚合后的结果表称之为1阶

这里的“阶”order是谓词逻辑的概念,它用于区分层次,区分集合论中的元素和集合。在这个案例中,如果把超市数据视为一个完整的集合,那么“订单ID*产品ID”对应的明细行,就是元素(element),而 “每个类别的销售额总和”则是把全集区分为三个“子集”后的抽象概括。 

我喜欢用如下的可视化来表达:

注意,数据表是“集合”,大家可以先把每一行视为一个完整的元素element,对应一笔业务过程,所有的行构成全集。这里的关键是,要理解明细表和“问题”对应两个不同的集合,而且具有不同的层级(order)。在书中,MICK如下解释:

使用GROUP BY聚合之后,SQL的操作对象便由0阶的‘‘行”变成了1阶的“行的集合”。此时,行的属性便不能使用了。可见,SQL的世界其实是层次分明的等级社会

MICK

SQL是层次分明的等级社会,人类社会何尝不是,不同人问出来的问题构成的分析世界,又何尝不是呢。

03—分析的层次=集合的阶

这样的理解,进一步坚定了喜乐君从详细级别的角度看待问题及其关系的信心。在喜乐君《数据可视化分析》《业务可视化分析》书中,使用了“详细级别”代表问题的层次——如同这里的阶order,也可以省略为层次level。一个核心的逻辑图如下:

我在书中反复提及的两句话是“聚合是分析的本质,详细级别是聚合的依据”

聚合的过程是明细表到聚合表的抽象过程,是order提升的过程,比如这里的0阶到1阶。如何定义阶order的变化?我们必须为此设置标记,那就是GROUP BY后面的字段。

基于这样的知识,进一步理解EXISTS 和IN的区别,理解标量子查询和非标量子查询的差别,就变得容易了。且听下回分解。

喜乐君Nov 15, 2022晨