SQL进阶——GROUP BY和PARTITION BY的意义！

本篇为大家介绍下GROUP BY和PARTITION BY的意义。

在SQL的功能中，GROUP BY和PARTITION BY非常相似——也可以说几乎一样。而且，两者都有数学的理论基础。本篇文章将以集合论和群论中的“类”这一重要概念为核心，阐明GROUP BY和PARTITION BY的意义。

在使用SQL进行各种各样的数据提取时，一个常用的操作是按照某种标准为数据分组。不仅是使用SQL的时候，在日常生活中整理或者分析数据时，我们也经常需要给数据分组。

SQL的语句中具有分组功能的是GROUP BY和PARTITION BY，它们都可以根据指定的列为表分组。区别仅仅在于，GROUP BY在分组之后会把每个分组聚合成一行数据。

例如，有下面这样一张存储了几个团队及其成员信息的表。

Teams

对这张表使用GROUP BY或者PARTITION BY，可以获取以团队为单位的信息。无论使用哪一个，都可以将原来的表Teams分割成下面几个子集，然后通过SUM函数进行聚合，或者通过RANK函数计算位次。

SELECT 
  member, 
  team, 
  age,
  RANK() OVER(PARTITION BY team ORDER BY age DESC) rn,
  DENSE_RANK() OVER(PARTITION BY team ORDER BY age DESC) dense_rn,
  ROW_NUMBER() OVER(PARTITION BY team ORDER BY age DESC) row_num
FROM Members
ORDER BY team, rn;

结果：

分割后的子集如下图所示。

一般情况下集合用圆来表示。但是，为了使“分割”（cut）操作看起来更直观，这里故意使用了直线来划分子集。

接下来我们重点关注一下划分出的子集，可以发现它们有下面这3个性质。

1．它们全都是非空集合。

2．所有子集的并集等于划分之前的集合。

3．任何两个子集之间都没有交集。

因为这些子集都是通过表中存在的“team”列的值分割出来的，所以不可能存在空集。而且，将分割后的子集全部加起来，很明显就是原来的集合。换句话说，分割之后不存在没有归属的成员。

还有，不存在同时属于两个子集（＝同时属于多个团队）的成员。一个成员一定只属于分割后的某个子集。所以我们也可以认为，GROUP BY和PARTITION BY都是用来划分团队成员的函数。

在数学中，满足以上3个性质的各子集称为“类”（partition），将原来的集合分割成若干个类的操作称为“分类”。这些都是群论等领域的术语。被分割出来的类，和“分类”中的“类”意思是相同的，很好理解。

SQL中PARTITION BY子句的名字就来自于类的概念（即partition）。虽然我们可以让GROUP BY子句也使用这个名字，但是因为它在分类之后会进行聚合操作，所以为了避免歧义而采用了不同的名字。一般来说，我们可以采取多种方式给集合分类。在SQL中也一样，如果改变GROUP BY和PARTITION BY的列，生成的分组就会随之变化。

在SQL中，GROUP BY的使用非常频繁，由此可以知道我们身边存在着很多类。例如学校中的班级和学生的出生地等。没有学生的班级是没有存在意义的，而出生地为两个省的人应该也是不存在的（出生地不详的人可能会有，但是这样的人应该属于列为NULL的类）。

扑克牌的卡片也一样。52张卡片根据花型可以分为4类，根据颜色可以分为红色和黑色两类。属于同一类的元素满足相同的标准，就像朋友一样——至少比与不同类的元素之间的关系近一些（数学上这种关系称为“等价关系”）。用一个不算很贴切的词语来说就是“物以类聚”。

在群论中，根据分类方法不同，分割出来的类有各种各样的名字。群论中有很多非常有趣的类，比如“剩余类”。正如其名，它指的是通过对整数取余分割出的类（一般来说类不一定都是数的集合，不过现在我们只考虑数的情况）。

例如，通过对3取余给自然数集合N分类后，我们会得出下面3个类。

余0的类：M1 = {0, 3, 6, 9, …}

余1的类：M2 = {1, 4, 7, 10, …}

余2的类：M2 = {2, 5, 8, 11, …}

从类的第2个性质我们知道，这3个类涵盖了全部自然数。可以用下面的公式来描述这种情况。

M1 + M2 + M3 = N

我们将这3个类称为“模3剩余类”。模指的是除数，英文是Modulo。与类相比，模的概念稍微有些抽象，不太好理解。

模在SQL中也有实现，就是取模函数MOD。虽然标准SQL中没有定义它，但是大部分数据库中都有实现（SQL Server中使用%运算符）。在SQL中一般是下面这样的用法。

--对从1到10的整数以3为模求剩余类
SELECT 
  MOD(num, 3) AS modulo,
  num
FROM Natural
ORDER BY modulo, num;

结果：

剩余类也有很多有趣的性质，可以有广泛的应用。举一个例子，求剩余类会将自然数集合分割成大小相等的一些类，所以在需要从大量数据中按照特定比例抽样的时候非常方便。例如，使用下面的查询语句可以随机地将数据减为原来的五分之一（表中没有连续编号的列时，使用ROW_NUMBER函数重新编号就可以了）。

--从原来的表中抽出（大约）五分之一行的数据
SELECT ＊
FROM SomeTbl
WHERE MOD(seq, 5) = 0;

--表中没有连续编号的列时，使用ROW_NUMBER函数就可以了
SELECT ＊
FROM (SELECT col,
             ROW_NUMBER() OVER(ORDER BY col) AS seq
     FROM SomeTbl)
WHERE MOD(seq, 5) = 0;

当然，实际上表中数据的行数未必刚好是5的倍数，所以剩余类之间的大小也不一定相等。但是，上面的查询语句肯定满足“随机地等分数据”这一随机抽样的需求。

通过这篇文章，对于GROUP BY和PARTITION BY的执行过程，以及它们的数学基础，大家是否有了更深的理解呢？总地来说就是，SQL和关系数据库中大量引入了集合论、群论中的成果。

可能大家会觉得这些内容有些抽象，好吧，确实很抽象，但是正因为抽象，才有了广泛的应用。数学理论并不是脱离实际的游戏，它其实隐藏了大量能够用于日常工作的技巧。但是如果只是等待，很难发现它们的身影。数据工程师们只有通过自身的努力，在理论和实践之间搭建起连通的桥梁，才能提高自身的数学应用能力。