跳至正文

从SQL EXISTS谓词理解集合和运算的“阶”

标签:

2022-11-16 08:39 发表于微信公众号,前文:【格物篇52】SQL的“层”order与问题的级别LOD


01—SQL的“一阶谓词”

昨天介绍了《SQL进阶教程》“SQL中的层级”,心中满是“与我心有戚戚焉”的喜乐,这一段的基础,则是现代逻辑学体系中的谓词逻辑(predicate logic)。谓词逻辑和集合论,是支撑SQL和关系数据库的理论基础。当然,目前SQL和关系数据库只支持到“一阶谓词逻辑”,一阶之前还有0阶,因此是两种。通俗的说,1阶对应表(table),0阶对应行(row)——一阶的表正是0阶的行的集合。

谓词逻辑的出现具有划时代的意义,原因就在于为命题分析提供了函数式的方法。表常常被认为是行的集合,但从谓词逻辑的观点看,也可以认为是命题的集合(=陈述句的集合)。 C.J.Date曾经这样调侃过:数据库这种叫法有点名不副实,它存储的与其说是数据,还不如说是命题。

MICK 《SQL进阶教程》P131

我们每个人都接受通俗的理解,数据表是行的集合(table is a set of records);而对于“数据表是命题的集合”就难以承受。也可以从业务的角度理解这个问题。数据表的首要目的是记录业务过程(operational record- keeping),每一笔业务过程都是一个完整的陈述句命题,比如销售明细表的每一行代表“谁、在何时何地、给谁、提供了何种产品,以多少的单价、数量成交”。这个陈述句最终被记录下来,变成了规范化的数据表样式,如下所示:

当然,我们可以把每一行的陈述句(命题)进一步展开多个更细的命题,于是就有了“产品A单价为20元”“订单001下产品A销售5件”这样的更短的陈述句。

常见的筛选,比如“产品名称 = ‘华为Mate50’ ”,就是谓词函数和参数的结合,最终返回TURE为真的命题,即返回我们想要的行record。

最常见的谓词是“=,>,< , <>”等比较谓词,以及BETWEEN、LIKE、IS NULL等。它们完成常见的判断,返回的是TRUE真值(就像Tableau Prep中的筛选值,每一个判断都只保留TRUE的部分,也正因为此,我们说“筛选即计算”)。

这种输入值为一行的谓词叫做“一阶谓词”。 

02—SQL的二阶谓词EXISTS

这里开始介绍关键:EXISTS。和“相等、大于”等一阶谓词以“一行数据”为参数不同,EXISTS谓词可以接受多行为参数,比如在“产品档案表”中筛选在售(即在“销售明细表”有对应)的产品ID,如下:

SELECT P.产品ID , P.产品名称FROM 产品档案表 as PWHERE EXISTS (SELECT * FROM 产品销售表 as X WHERE X.产品ID = P.产品ID ) 

谓词是特殊的函数,EXISTS后面的部分就是函数引用的参数。EXISTS的嵌套查询中,SELECT子句支持如下三种写法:

  • 通配符 SELECT *
  • 常量:SELECT “这里的内容任意”
  • 列名:SELECT 产品ID

正因为EXISTS支持引用数据表(集合)为参数,因此称之为“二阶谓词”。MICK介绍说,阶order是用来区分集合或者谓词的阶数的概念。

  • 一阶谓词 = 输入值为一行数据的谓词
  • 二阶谓词 = 输入值为行数据的集合
  • 三阶谓词 = 输入值为“集合的集合”的谓词
  • 四阶谓词 = 输入值为“集合的集合的集合”的谓词
  • ……

当然,SQL和关系数据库目前只支持二阶的情况。和一阶谓词相比,EXISTS被称之为高阶函数

讲到这里,我自己都突然明白“高阶”的真正含义。我在图书中所讲的“结构化分析”,改为“高阶分析”似乎更加准确了。不管是一阶谓词,还是二阶谓词,它们都是函数,返回值都是布尔判断。SQL的特别之处是,它采用了三值逻辑,对应TRUE,FALSE和unknown。

03—SQL的全称量化和存在量化

1.8小节“EXISTS谓词的用法”的真正难点在于这里的“全称量化”和“存在量化”。喜乐君看了好几遍,才算部分理解了作者的意义。先看两种类型的命题:

  • “所有的x都满足条件P”
  • “存在(至少一个)满足条件P的x”

前者的关键词是“所有ALL”,因此称之为“全称量化”;后者的关键词是“存在EXISTS”,因此称之为“存在量化”,常用颠倒的A和E代表。SQL中的EXISTS谓词逻辑实现了谓词逻辑中的“存在量词”,遗憾的是,并没有一个与之相对的函数完成“全称量词”。幸运的是,全称量词和存在量词二者可以相互推导,这就是德·摩根定律。

奥古斯都·德·摩根首先发现了在命题逻辑中存在着下面这些关系:

非(P 且 Q)=(非 P)或(非 Q)

非(P 或 Q)=(非 P)且(非 Q)

百度百科

 于是接下来,与EXISTS相对的NOT EXISTS开始大放异彩。

比如,在客户迁徙分析中,喜乐君曾经使用Prep Builder2021的新功能“new row”完成 “各订单年月,没有消费的客户”,当然也可以使用多次JOIN完成,它们不好理解,这里可以用EXISTS函数实现。

改用上面的逻辑,可以这样表达“存在某个订单年月没有消费的客户ID”,这个是“存在量化”,他可以转化为如下的“全称量化”:“并非对所有订单年月,每个客户都有消费。”(这个对应喜乐君还要考虑一下)

这样,我们可以先完成“每个订单年月*每个客户”的全集,然后从中排除有消费的客户。

首先,我们可以使用客户信息表 和 日期表构建“客户*年月”的完整交叉表。

这里面有当月消费的客户ID,也有没有消费的客户ID。如下:

SELECT DISTINCT C.客户ID, S.订单年月FROM 客户信息表 as CCROSS JOIN 消费明细表 as S

当然,这里的客户信息表可以从消费明细表中提取而来,如果订单日期是yyyymmdd,也可以通过计算转化为“订单年月”,这里先忽略这些更基础的内容。 接下来的关键是,从上述的数据表中排除“当月有消费的客户”。

SELECT DISTINCT C.客户ID, S.订单年月FROM 客户信息表 as CCROSS JOIN 消费明细表 as SWHERE NOT EXISTS (SELECT * FROM 消费明细表 S2 WHERE S2.客户ID =C.客户ID AND S2.订单年月 = S.订单年月 )

这就是使用了排除法完成了上述的问题。SQL中提供了另一个更简单的方法——集合论的EXCEPT,如下所示:

SELECT DISTINCT C.客户ID, S.订单年月FROM 客户信息表 as CCROSS JOIN 消费明细表 as SEXCEPT(SELECT S2.客户ID,S2.订单年月FROM 消费明细表 S2 )

可见,NOT EXISTS具备了差集计算的功能。当然,书中还介绍了很多其他的案例,非常值得学习,不愧是进阶之书。

注:关于全称量化和部分量化的差异,我自己还需要多琢磨一下,有兴趣的读者,不妨使用SQL或Tableau Prep完成一下下面的两个题目:”每个订单年度*每个客户ID的交叉连接““在每个订单年月,没有来消费的客户ID是谁”

 2022-11-16 08:39 发表于上海