在11行代码中实现更好的SQL

# 11行代码实现更优SQL 来源：https://prela-lang.org/tutorial/ Prela (https://prela-lang.org/) 是 UCLA REPL (https://repl.la/) 正在开发的一种新型查询语言。该语言与SQL差异显著，但其核心思想非常简单。本简短教程将指导您用Python构建一个Prela的玩具版本，以理解其核心原理。学完本教程后，您将明白以下查询的工作机制： ``` movie.where(company.s(country).eq("[us]") & keyword.eq("character-name-in-title")) .select(title & cast.s(person).s(alias).s(text)) ``` 您可能已经能猜到它的功能：该查询查找所有由美国公司制作且标题中包含角色名称的电影，并输出标题及每位演员的别名。请注意，等效的SQL查询 (https://github.com/gregrahn/join-order-benchmark/blob/master/16b.sql) 长度超过20行。 Prela的首个特殊之处在于它仅包含*二元关系*，即只有两列的表。初看这似乎非常受限，但实际上可以轻松地将多列宽表"二元化"。假设我们有一个电影表： ID title year 646 The Godfather 1972 478 Seven Samurai 1954 583 Casablanca 1942 我们可以将这个3列的表分解为3个二元关系 1 (https://prela-lang.org/tutorial/#fn1)，每个关系将行号映射到列值： ``` movie = Rel([(646, 0), (478, 1), (583, 2)]) title = Rel([(0, "The Godfather"), (1, "Seven Samurai"), (2, "Casablanca")]) year = Rel([(0, 1972), (1, 1954), (2, 1942)]) ``` **提示** 本教程使用 snip (https://remy.wang/snip/) 将代码单元格连接成类似笔记本的环境 2 (https://prela-lang.org/tutorial/#fn2)，在一个单元格中的修改会反映在后续单元格中。 上面的 `movie`、`title` 和 `year` 关系分别表示原始表的 `ID`、`title` 和 `year` 列。请注意行号在 `title` 和 `year` 中排在第一位，但在 `movie` 中排在第二位（`movie` 也没有命名为 `ID`）。原因稍后会清楚。 专注于二元关系的动机在于它们可以泛化函数。函数之所以强大，是因为它们能够*组合*，从而成为程序的构建模块。函数将每个输入映射到唯一的输出，而二元关系可以将一个输入映射到多个不同的输出。从某种意义上说，二元关系可以被视为*非确定性*函数，并且我们可以像组合函数一样组合它们。 以上内容非常抽象，让我们回到示例。为简化起见，我们将重点关注那些将每个输入恰好映射到一个输出的关系，即它们恰好都是函数。"调用"一个关系则简化为将该关系转换为字典并查找值： ``` print(dict(movie)[646], dict(title)[0], dict(year)[0]) ``` 现在我们准备介绍Prela中第一个也是最重要的操作符：关系组合。函数组合的工作原理是先应用一个函数，然后将另一个函数应用于输出。两个关系 `r` 和 `s` 的组合本身是一个关系，首先通过 `r` 将 `x` 映射为某个 `y`，然后通过 `s` 将 `y` 映射为最终的"输出"。这可以通过将 `s` 转换为字典 `d`，遍历 `r` 中的 `(x, y)` 对，最后如果 `y` 在 `d` 中则输出 `(x, d[y])` 来实现： ``` def select(r, s): d = dict(s) return [ (x, d[y]) for x, y in r if y in d ] ``` 使用我们的示例，下面的查询将 `movie` 与 `title` 组合，得到一个将每个电影ID映射到其标题的关系：3 (https://prela-lang.org/tutorial/#fn3) ``` print(movie.select(title)) ``` 尝试将 `title` 改为 `year`，看看结果如何。组合的威力在我们链式调用多个 `.select` 时真正显现出来。假设我们添加一个外键列，将每部电影映射到其制作公司，以及另一个电影公司的表： ID title year company ... ... ... ... 0 ... ... ... ... 1 ... ... ... ... 2 ID name country 0 Paramount [us] 1 Toho [jp] 2 Warner Bros. [us] 以同样方式分解会给我们带来另外四个关系： ``` company = Rel([(0, 0), (1, 1), (2, 2)]) id2row = Rel([(0, 0), (1, 1), (2, 2)]) name = Rel([(0, "Paramount"), (1, "Toho"), (2, "Warner Bros.")]) country = Rel([(0, "[us]"), (1, "[jp]"), (2, "[us]")]) ``` 然后，我们可以通过链式 `.select` 调用（此处用 `.s` 缩写）来找到电影制作公司的国家： ``` print(movie.s(company).s(id2row).s(country)) ``` 因为通过外键连接几乎总是需要"解析"一个ID到一行，Prela会自动插入该步骤，因此可以这样写 4 (https://prela-lang.org/tutorial/#fn4)，读起来就像"一部电影的公司的国家"！ ``` print(movie.s(company).s(country)) ``` 这也是教程开头代码片段最后一行 `cast.s(person).s(alias).s(text)` 发生的情况。 到目前为止，每个查询都返回了单列值。为了选择*多个*属性，我们引入了 `&` 操作符。 其中 `.select` 将 `r` 的第二列与 `s` 的第一列进行匹配，`&` 则在*两者*的第一列上连接 `r` 和 `s`，然后将它们的第二列配对： ``` def and_(r, s): d = dict(s) return [ (x, (y, d[x])) for x, y in r if x in d ] ``` 因此 `title & year` 将每个电影行同时映射到它的两个属性： 请注意结果仍然是一个二元关系，`&` 只是将值嵌套成一个元组。这意味着我们可以像处理任何其他关系一样继续组合它，这就是一个查询返回多列的方式： ``` print(movie.select(title & year)) ``` 接下来，我们需要一种方法来指定想要*哪些*行。谓词 `.eq(v)` 过滤一个关系，只保留第二列等于 `v` 的对： ``` def eq(r, v): return [ (x, y) for x, y in r if y == v ] ``` 单独使用时，`.eq` 仅缩小其应用的关系范围。下面的查询仍然将电影行映射到国家，只是不再是所有行： ``` print(company.s(country).eq("[us]")) ``` 最后，*限制*操作符 `.where` 接受类似上面的谓词并用它来过滤另一个关系。 ``` def where(r, s): d = dict(s) return [ (x, y) for x, y in r if y in d ] ``` 将我们的谓词传递给 `.where` 将其转换为对电影的过滤： ``` print(movie.where(company.s(country).eq("[us]"))) ``` 这直接从代码中读出来："公司国家为 [us] 的电影"。 查询变长了，让我们重构一下： ``` american = company.s(country).eq("[us]") print(movie.where(american)) ``` 等等，我们刚刚用普通的Python变量创建了一个CTE (https://www.postgresql.org/docs/current/queries-with.html) 吗？是的！这是可能的，因为Prela查询由操作符组成，并且每个子表达式都是一个有效的查询。 我们如何有多个条件？一个幸运的巧合是，因为 `&` 连接其参数，一旦嵌套在 `.where` 内部，它就兼作逻辑与： ``` print(movie.where(american & year.eq(1942))) ``` 只有《卡萨布兰卡》既是美国的*又*是1942年的。将所有部分组合起来，`.select` 然后为通过过滤器的电影获取我们想要查看的任何列： ``` print(movie.where(american & year.eq(1942)).select(title & year)) ``` 我们甚至可以将谓词推入 `select` 子句以获得更清晰的查询： ``` print(movie.where(american).select(title & year.eq(1942))) ``` 这就是这门语言的全部内容！Prela还支持分组和聚合，以及其他常见操作符。我们正在编写该语言的完整文档，因此目前您可以参考我们的论文 (https://arxiv.org/abs/2607.26356) 以获取更多细节。作为练习 5 (https://prela-lang.org/tutorial/#fn5)，您可以尝试定义必要的关系，使开头的代码片段能够运行。 ``` # keyword = ... # ... print(movie.where(company.s(country).eq("[us]") & keyword.eq("character-name-in-title")) .select(title & cast.s(person).s(alias).s(text))) ``` 我们玩具版Prela的独立Python程序可以在这里 (https://github.com/remysucre/prela/blob/main/tutorial/prela.py) 找到。 --- 1. 这也被称为6NF (https://en.wikipedia.org/wiki/Sixth_normal_form) 分解。如果您担心这会引入开销，请查看这篇帖子 (https://remy.wang/blog/cps.html) 了解Prela如何使用CPS (continuation-passing style) 编译消除间接层。↩︎ (https://prela-lang.org/tutorial/#fnref1) 2. 与例如Jupyter不同，snip总是从头开始执行以避免状态损坏。↩︎ (https://prela-lang.org/tutorial/#fnref2) 3. `.select` 方法语法使用了将 `Rel.select` 转发给 `select()` 的相同技巧。↩︎ (https://prela-lang.org/tutorial/#fnref3) 4. 这里我们使用行号作为公司ID来作弊。↩︎ (https://prela-lang.org/tutorial/#fnref4) 5. 解决方案*隐藏*在此页面的某个地方 ;\)↩︎ (https://prela-lang.org/tutorial/#fnref5)