SQL 学习笔记——GROUP BY，JOIN，窗口函数

上次去认真学习 SQL 还是 20 年的 12 月，时间才过去 15 个月，但我却觉得恍若隔世。

最近看书《深入理解 Hadoop》，它对 MapReduce 使用 SQL 语言进行描述，在学习过程中我发现我对之前还不了解的 GROUP BY，聚集函数等概念有了一些更深刻的理解，正巧 Hive，Spark SQL 等都需要一定的 SQL 技术，趁此机会进行进一步的学习，把 GROUP BY，JOIN 等子句好好拿下来。

为了符合将来工作的需求，使用 Hive 进行描述（但好像其实没有涉及到 Hive 独有的操作）。

---

着实想不到，MapReduce，函数式编程，SQL，感觉三者之间联系颇多。

SQL 各子句执行顺序

在开始学习之前，先 peek 一下 SQL 的各种子句的执行顺序——

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. WINDOW
7. ORDER BY

FROM 子句首先执行，它可能是一个表，视图，或其它的 SELECT 语句，它会根据结果创建一个临时的虚拟表；然后是 WHERE 子句，对虚拟表中每一行进行筛选操作，获得一个筛选后的新的虚拟表；然后是 GROUP BY，按照特定字段/列簇进行分组；然后是 HAVING 子句，对各个分组进行筛选操作，得到筛选后的分组表；SELECT 子句，包含映射和聚集操作，根据查询的列表提取和构造指定列；然后是 SELECT 中的窗口 WINDOW 函数，对 SELECT 的结果进行 OLAP 操作，并将结果作为新的列添加到原 SELECT 结果中；最后是 ORDER BY，对 SELECT 的结果进行排序。

有两个有趣的地方——WHERE 在 SELECT 之前执行，因此无法利用到 SELECT 中的别名；WHERE 在分组之前执行，用于筛选原表中的数据，而 HAVING 在分组之后执行，用于筛选分组后（但未聚集，这时候的每一条记录，或每一个分组形如(KEY, [COL1, COL2, ...])）的数据，因此 WHERE 中无法使用聚集函数。

但其实更有趣的是，这顺序和 MapReduce 的顺序非常类似，Hive 的编写者是否也是因为注意到这一点才创造了 Hive？

GROUP BY

GROUP BY 子句执行分组操作——将记录映射成标识符（通常是特定字段），然后将标识符相同的记录划分为一组，最终得到一个[KEY, Collection[Entity]的集合。

比如，这里有一个 Student 表，它的数据为——

CREATE TABLE student_t
(
    id        INT,
    class_id  INT,
    stud_name STRING,
    age       INT,
    character STRING
);

INSERT INTO student_t
VALUES (0, 101, "kikuchi makoto", 15, "IDOL"),
       (1, 101, "haru urara", 14, "UMA_MUSUME"),
       (2, 101, "seiun sky", 16, "UMA_MUSUME"),
       (3, 102, "tokai teio", 15, "UMA_MUSUME"),
       (4, 102, "takatsuki yayoi", 16, "IDOL"),
       (5, 103, "silence suzuka", 16, "UMA_MUSUME"),
       (6, 104, "amami haruka", 17,"IDOL"),
       (7, 104, "kisaragi chihaya", 16,"IDOL");

SELECT * FROM student_t;
id class_id   name                age character
--------------------------------------------------
0  101       "kikuchi makoto"     16  "IDOL"
1  101       "haru urara"         14  "UMA_MUSUME"
2  101       "seiun sky"          16  "UMA_MUSUME"
3  102       "tokai teio"         15  "UMA_MUSUME"
4  102       "takatsuki yayoi"    16  "IDOL"
5  103       "silence suzuka"     16  "UMA_MUSUME"
6  104       "amami haruka"       17  "IDOL"
7  104       "kisaragi chihaya"   16  "IDOL"

按班级 ID 分组后，相当于是得到了一个这样的临时表，由于 SQL 的列必须是原子的，因此这个表是虚拟的——

SELECT * FROM student_t GROUP BY class_id; -- 无法编译！

key(class_id) value(id class_id)  name                age character
----------------------------------------------------------------------
101                 0  101       "kikuchi makoto"     15  "IDOL"
                    1  101       "haru urara"         14  "UMA_MUSUME"
                    2  101       "seiun sky"          16  "UMA_MUSUME"
----------------------------------------------------------------------
102                 3  102       "tokai teio"         15  "UMA_MUSUME"
                    4  102       "takatsuki yayoi"    16  "IDOL"
----------------------------------------------------------------------
103                 5  103       "silence suzuka"     16  "UMA_MUSUME"
----------------------------------------------------------------------
104                 6  104       "amami haruka"       17  "IDOL"
                    7  104       "kisaragi chihaya"   16  "IDOL"

这样的临时表是不合法的，我们必须要通过某种方式将 value 从集合变成原子的值，这种方式就是聚集函数（Aggregation Function），聚集函数将分组中的某一列作为参数，并得到一个原子的值。常见的聚集函数有 COUNT，AVG，MIN，MAX 等。

COUNT 是个特例——它可以选择接受整个分组而非某一列作为参数，语法为 COUNT(*)或COUNT（任意非 NULL 常值），这样定义的原因是 COUNT 按特定列统计时会忽略 NULL，这在需要统计所有行数时行为不符合需要。

一个例子

比如，当我们试图获取每个班级的平均年龄的时候，会使用 AVG 聚集函数，使用 age 列作为参数，它会将每个分组的 age 列变成一行并作为参数（列转行？），并得到其平均值，比如对班级 101，会得到AVG(15,14,16)，结果为——

SELECT class_id, AVG(age) AS average_age FROM student_t GROUP BY class_id;
class_id    average_age
--------------------------
101         AVG(
              15,
              14,
              16)   = 15
--------------------------
102         AVG(
              15,
              16)   = 15.5
--------------------------
103         AVG(16) = 16
--------------------------
104         AVG(
              17,
              16)   = 16.5

也可以在多个列上进行聚集操作，比如这里获取了每个班级的最大年龄，最小年龄，每个列会分别进行聚集。

SELECT 
  class_id, 
  MAX(age) AS maximum_age,
  MIN(age) AS minimum_age
FROM student_t GROUP BY class_id;

class_id    maximum_age      minimum_age
-----------------------------------------
101         MAX(             MIN(
              15,              15,
              14,              14,
              16)   = 16       16)   = 14
-----------------------------------------
102         MAX(             MIN(
              15,              15,
              16)   = 16       16)   = 15
-----------------------------------------
103         MAX(16) = 16     MIN(16) = 16
-----------------------------------------
104         MAX(             MIN(
              17,              17,
              16)   = 17       16)   = 16

聚集函数中也可以使用表达式，比如我们想要统计每个班级中 IDOL 的数量，筛选 IDOL 的操作既可以使用 WHERE 进行，也可以在聚集函数中进行——我们可以使用count(if(character = "IDOL", 1, NULL))，这借用了 count 函数如果接受到 NULL 则跳过这一特性——

SELECT class_id, COUNT(IF(character = "IDOL", 1, NULL)) as idol_num
FROM student_t GROUP BY class_id;

class_id     idol_num
------------------------------------------------------
101          COUNT(
               if("IDOL" = "IDOL", 1, NULL),
               if("UMA_MUSUME" = "IDOL", 1, NULL),
               if("UMA_MUSUME" = "IDOL", 1, NULL)) = 1
------------------------------------------------------
102          COUNT(
               if("UMA_MUSUME" = "IDOL", 1, NULL),
               if("IDOL" = "IDOL", 1, NULL))       = 1
------------------------------------------------------
103          COUNT(
               if("UMA_MUSUME" = "IDOL", 1, NULL)) = 0
------------------------------------------------------
104          COUNT(
               if("IDOL" = "IDOL", 1, NULL),
               if("IDOL" = "IDOL", 1, NULL))       = 2

将聚集函数看作对一整个列进行的操作，我认为是一个很合适的理解的方法。同时，从函数式编程的角度，可以把聚集函数内的表达式当作一个函数字面量（它是 lazy 的），使用 Scala 来表述的话，就类似count(if (_.character == "IDOL") 1 else null)，其中_为每一行的记录。

需注意，SELECT 选择的值必须是原子的，因此能选择的字段只能为分组的 key，聚集函数操作后的列；以及常数；后面提到的 HAVING 子句也有同样的限制。

顺便，没有 GROUP BY 的时候，也可以执行聚集函数，此时整个表会被当成一个分组对待，没有 KEY。

GROUP BY 在某些时候行为同 SELECT DISTINCT 一致，性能似乎也不相伯仲（Hive 是这样吗？我不确定），因此究竟使用何种方法还是看语义，使用 GROUP BY 来做去重，语义是不太明显的。

另外，某些看上去非常简单的操作可能是一条 SELECT 语句无法完成的，比如获取年龄最小的 IDOL 的记录，需要一个嵌套的 SELECT 操作，其中内层使用 GROUP BY，获取最小的 IDOL 的年龄的值，外层使用相等比较符来找到年龄同该最小年龄相等的记录——

SELECT * FROM student_t
WHERE character = "IDOL" AND 
age = (SELECT MIN(age) FROM student_t WHERE character = "IDOL");

WHERE 和 HAVING

在使用 GROUP BY 的语句中，WHERE 和 HAVING 可以提供筛选操作，其中 WHERE 在分组前进行筛选，HAVING 在分组后进行筛选。或者说，WHERE 指定行的条件，HAVING 指定组的条件。

GROUP BY 中的 WHERE

考虑我们想筛选每个 class 中所有 IDOL 的平均年龄，我们就需要首先通过 WHERE 子句筛选出所有 IDOL，再进行分组——

SELECT class_id, AVG(age) as average_age FROM student_t
WHERE character = "IDOL"
GROUP BY class_id;

class_id    average_age
--------------------------
101         AVG(15)   = 15
--------------------------
102         AVG(16)   = 16
--------------------------
104         AVG(
              17,
              16)   = 16.5

HAVING

HAGVING 子句用在对分组后对数据进行筛选中，比如对上面的例子，我们进一步想要获取平均年龄大于 15 的班级，这时候就需要使用 HAVING 子句——

SELECT class_id, AVG(age) as average_age FROM student_t
WHERE character = "IDOL"
GROUP BY class_id
HAVING AVG(age) > 15 AND 16 in age;
+--------+-----------+
|class_id|average_age|
+--------+-----------+
|102     |16         |
|104     |17         |
+--------+-----------+
-- 懒得画表了，直接出结果

由于 HAVING 在 GROUP BY 后执行，它可以执行聚集函数。

DISTINCT

在使用聚集函数时，能通过 DISTINCT 关键字能筛选出列的重复的字段，比如COUNT(DISTINCT age)，能统计出不同年龄的数量，如15,15,17,16,17，筛去重复值后得到15,17,16，结果为 3。

关联子查询——引用外部查询的变量

上面展示了获取所有班级中年龄最小的学生的 SQL 代码——

SELECT * FROM student_t
WHERE age = (SELECT MIN(age) FROM student_t);
+--+--------+----------+---+----------+
|id|class_id|stud_name |age|character |
+--+--------+----------+---+----------+
|1 |101     |haru urara|14 |UMA_MUSUME|
+--+--------+----------+---+----------+

这里使用了所谓子查询的模式——(SELECT MIN(age) FROM student_t)是一个嵌套的 SELECT 语句，它用于获得最小的年龄数；同时，由于不使用聚集函数时，整个表会被当成一个分组对待，所以这个 SELECT 的输出是一行一列的，一行一列的表可以当作标量看待，因此可以和 age 做比较。

那么如果我们想要获取每一个班级中年龄最小的学生呢？可以意识到，对每一个学生，她都需要与当前班级的最小年龄做比较，因此，在子查询中，需要利用到当前（的 WHERE 子句）所处理的行的班级信息，代码如下——

SELECT * FROM student_t AS s1
WHERE age = (SELECT MIN(age) FROM student_t AS s2 WHERE s1.class_id = s2.class_id);
+--+--------+----------------+---+----------+
|id|class_id|stud_name       |age|character |
+--+--------+----------------+---+----------+
|1 |101     |haru urara      |14 |UMA_MUSUME|
|3 |102     |tokai teio      |15 |UMA_MUSUME|
|5 |103     |silence suzuka  |16 |UMA_MUSUME|
|6 |104     |amami haruka    |17 |IDOL      |
|7 |104     |kisaragi chihaya|17 |IDOL      |
+--+--------+----------------+---+----------+

这里由于两个 SELECT 查询都为同一个表，因此需要给定不同别名以进行区分。可以发现，内部的子查询引用了外部的行的一个字段，进行了这种引用的查询称作关联子查询，它的行为非常类似闭包。

需要注意的是，普通的子查询是一次执行即可的，而对于关联子查询，如果它引用的外界的变量发生改变，它就必须需要重新计算（因为它已经不够“纯”了），因此可能性能会较差一些。

之前看日本人的《SQL 基础教程》，介绍到关联子查询时说，利用被划分的组进行比较的子查询就是关联子查询，这种说法有些狭隘，没有覆盖到所有情况。但此书确实值得一看。

JOIN

主要参考 这篇文章。这里为了一致性，只使用tb1 [LEFT|RIGHT] JOIN tb2 ON expr的语法。

JOIN 操作即将两张或更多张表进行连接，从而构造一个新的表的操作。JOIN 操作的本质就是将两张表做笛卡尔积，并进行一定的筛选操作。

最基础的 JOIN 操作为CROSS JOIN，即单纯将两张表做笛卡尔积操作，得到新的表。

比如，考虑下面两张表，它们的 id 字段是同义的。

CREATE TABLE tb1(data STRING, id INT);
CREATE TABLE tb2(id INT, data STRING);
INSERT INTO tb1 VALUES
("amami", 0),
("kisaragi", 1),
("tokai", 2),
("haru", 3);
INSERT INTO tb2 VALUES
(2, "teio"),
(3, "urara"),
(4, "sky"); -- 我 INSERT 类型写错了它怎么不报错啊……太宽松了吧

两表字段的对应关系可以这样表示——

data         id   data
-------------------------
   "amami"   0
"kisaragi"   1
   "tokai"   2    "teio"
    "haru"   3    "urara"
             4    "sky"

需注意的是，这种对应关系可能不是唯一的，表 A 的某条记录，它可能可以和表 B 的多条记录对应，Vise Versa。

CROSS JOIN

CROSS JOIN的语法非常简单，直接在 FROM 中的表名位置协商tb1 JOIN tb2即可——

SELECT * FROM tb1 JOIN tb2;
+--------+--+--+-----+
|data    |id|id|data |
+--------+--+--+-----+
|amami   |0 |2 |teio |
|amami   |0 |3 |urara|
|amami   |0 |4 |sky  |
|kisaragi|1 |2 |teio |
|kisaragi|1 |3 |urara|
|kisaragi|1 |4 |sky  |
|tokai   |2 |2 |teio |
|tokai   |2 |3 |urara|
|tokai   |2 |4 |sky  |
|haru    |3 |2 |teio |
+--------+--+--+-----+

可以看到结果符合预期，为 tb1 和 tb2 的笛卡尔积，长度为 4x3=12。

但是，笛卡尔积通常是没有实践意义的，通常找不到直接使用笛卡尔积的场景，但笛卡尔积是其它连接操作的基础，都是在笛卡尔积上进行一定的筛选的结果。

INNER JOIN（内连接）

内连接或许是最符合直觉的连接操作了，它在笛卡尔积的基础上使用一个表达式进行筛选，最常用的表达式是让两张表中相同字段判等，比如在这里，就是tb1.id = tb2.id，SQL 语句见下——

SELECT tb1.data, tb1.id, tb2.data FROM 
tb1 JOIN tb2 ON tb1.id = tb2.id;
+-----+--+-----+
|data |id|data |
+-----+--+-----+
|tokai|2 |teio |
|haru |3 |urara|
+-----+--+-----+

可以看到，结果中只有匹配该条件的记录。

内连接得到的结果表的记录数量小于表一和表二。

OUTER JOIN（外连接）

然后是外连接，外连接分为左外连接（LEFT [OUTER] JOIN）和右外连接（RIGHT [OUTER] JOIN），其中左外连接在内连接的基础上，对左边的表（称为主表），如果其某条记录在右边的表中没有匹配的，则仍旧将该记录作为结果，其中对应右表的字段设定为 NULL；右外连接则是以右边的表作为主表进行上面的操作。

下面展示了左外连接和右外连接的结果。

SELECT tb1.data, tb1.id, tb2.data FROM
tb1 LEFT JOIN tb2 ON tb1.id = tb2.id;
+--------+--+-----+
|data    |id|data |
+--------+--+-----+
|amami   |0 |NULL |
|kisaragi|1 |NULL |
|tokai   |2 |teio |
|haru    |3 |urara|
+--------+--+-----+

SELECT tb1.data, tb1.id, tb2.data FROM
tb1 RIGHT JOIN tb2 ON tb1.id = tb2.id;
+-----+----+-----+
|data |id  |data |
+-----+----+-----+
|tokai|2   |teio |
|haru |3   |urara|
|NULL |NULL|sky  |
+-----+----+-----+

外连接通常在我们需要保留主表的信息的时候，比如对于一个作者表和一个书籍表，我们想表达每个作者编写的数据，这时候如果某个作者没有书籍，我们如果使用内连接的话就会把他漏掉了。

外连接时，如果用于进行连接的字段是唯一的，则结果表的大小等于主表的大小。

总结

用一张图展示内连接，左外连接和右外连接所执行的操作，可用于形象理解。

这里总共有3个部分，考虑使用其中1个，2个，3个，则总共能得到 C3,1 + C3,2 + C3,3，总共七种连接方式。

JOIN 操作在 FROM 子句执行时执行，因此它是最先执行的，之后的 WHERE，GROUP BY 等操作全都是在 JOIN 后的临时表中执行的。

还有一些更复杂的连接操作（一切不使用=进行连接的操作都挺复杂的），现在先不研究。

WINDOW（窗口）函数

WINDOW 函数是近十年才加入 SQL 的，OLAP 方向的事物，它的名字中的 WINDOW，指的是“滑动窗口”那种窗口，即范围，窗口函数就其行为来说，可以认为是按特定顺序对特定范围的记录进行迭代，且每次迭代都会产生一个值并插入到查询结果中的函数，它的行为类似** Python 或 js 中的 yield**，维护有自己的状态，或者说函数式编程中的 scan 函数。

下面的代码使用了 ROW_NUMBER 窗口函数，该函数就像一个计数器，迭代每一行都+1。

-- 不添加主键实在不是一个好主意，主键应当是每一个表都有的，不然不符合2NF了
CREATE TABLE tb(data INT);
INSERT INTO tb VALUES (100),(101),(101),(102),(102),(102),(103);

SELECT data, ROW_NUMBER() OVER (ORDER BY data) FROM tb;
+----+------+
|data|number|
+----+------+
|100 |1     |
|101 |2     |
|101 |3     |
|102 |4     |
|102 |5     |
|102 |6     |
|103 |7     |
+----+------+

相似用途的窗口函数还有 RANK 和 DENSE_RANK，它们的语义一看结果便知。

SELECT data,
       RANK() OVER (ORDER BY data) AS ranking,
       DENSE_RANK() OVER (ORDER BY data) AS dense_ranking,
       ROW_NUMBER() OVER (ORDER BY data) AS row_num
FROM tb;
+----+-------+-------------+-------+
|data|ranking|dense_ranking|row_num|
+----+-------+-------------+-------+
|100 |1      |1            |1      |
|101 |2      |2            |2      |
|101 |2      |2            |3      |
|102 |4      |3            |4      |
|102 |4      |3            |5      |
|102 |4      |3            |6      |
|103 |7      |4            |7      |
+----+-------+-------------+-------+

窗口函数的语法形如fn() OVER ([PARTITION BY field] ORDER BY field)，其中 PARTITON BY 子句用于给定分组（即确定记录的范围，它会对每一个组都分别执行该窗口函数；如果不给定范围，则默认整个表作为范围）；ORDER BY 子句用于给定调用顺序，顺序是必须给定的，这是容易理解的——对于这种有状态的函数，如果调用顺序不确定，则结果也不确定了。

窗口函数有两个重要特性——

1. 窗口函数对记录的集合起作用，这是说它对整个表或者每个分组进行操作；这一点很类似聚集函数，但聚集函数处理完一整个集合生成的是一个值，而窗口函数生成一列值。
2. 窗口函数在 SELECT 后执行，因此它只能对 SELECT 的结果进行操作。

聚集函数也可以用作窗口函数，此时它的行为类似函数式编程中的 scan 函数——在 reduce 时保留之前的结果，比如下面使用了 SUM 作为窗口函数；使用聚集函数作为窗口函数时，需要将要聚集的字段置于函数的括号中——

SELECT 
  data, 
  ROW_NUMBER() OVER (ORDER BY DATA) AS id, 
  SUM(data) OVER (ORDER BY id) 
FROM tb;
+----+--+------------+
|data|id|sum_window_1|
+----+--+------------+
|100 |1 |100         |
|101 |3 |201         |
|101 |2 |302         |
|102 |6 |404         |
|102 |5 |506         |
|102 |4 |608         |
|103 |7 |711         |
+----+--+------------+

这里特意创建了一列 ID 让 SUM 用以排序，这是因为 SUM 对单行数据的结果非常奇怪，怀疑这是某种优化机制导致的。

学到后面的 ROW 的语法后，感觉这里的抽象不太合适。

下面使用 Scala 中的 scan 函数实现 ROW_NUMBER，DENSE_RANK，RANK 函数，尽量按照函数式编程的风格。如果并非产生该列，而是产生原纪录连带该列时，我觉得使用 reduce 更为适合（想想当时是如何使用 reduce 实现 map 的），但这时候的代码恐怕会太过晦涩。

def row_number[A] (entities : List[A]) : List[Int] =
  entities.scanLeft(0){(acc, _) => acc + 1}.tail

def dense_rank[A] (entities : List[A]) : List[Int] = {
  case class Vars(last : Option[A] = None, rank : Int = 0)
  entities.scanLeft(Vars()){ (acc, entity) =>
    val Vars(last, rank) = acc
    last match {
      case None => Vars(Some(entity), rank + 1)
      case Some(lastEntity) if (lastEntity == entity) => Vars(Some(entity), rank)
      case _ => Vars(Some(entity), rank + 1)
    }
  }.map(_.rank).tail
}

def rank[A] (entities : List[A]) : List[Int] = {
  case class Vars(last : Option[A] = None, counter : Int = 0, rank : Int = 0)
  entities.scanLeft(Vars()){(acc, entity) =>
    val Vars(last, counter, rank) = acc
    last match {
      // 对第一个元素
      case None => Vars(Some(entity), counter + 1, rank + 1)
      // 如果当前元素和上一个元素相等
      case Some(lastEntity) if (lastEntity == entity) =>
        Vars(Some(entity), counter + 1, rank)
      case _ =>
        Vars(Some(entity), 1, rank + counter)
    }
  }.map(_.rank).tail
}

窗口函数（或许限于聚集函数的窗口函数）的行为其实就是，对每一组的每一行数据，将窗口的开始（默认为该组的开始）到窗口的结束（默认为该行）的记录作为参数传递给窗口函数并获得当前值。这里使用了“窗口的开始”，“窗口的结束”来描述，是因为窗口的范围是可以让用户通过 ROWS 子句自己指定的，比如一个比较常见的需求是所谓的“移动平均”——对每条记录，它及其周围记录的平均值。

---

下一步是继续去学习 Hive，同时期待学习更多 SQL 编写的模式，从而使能够去编写和阅读复杂的查询操作。