用 SQL 而不是 Python 处理文本数据

作者： 王益（蚂蚁金服研究员）

知乎专栏： SQLFlow

本文的英语版本在：

https://medium.com/@yi.wang.2005/nlp-in-sql-word-vectors-82dffc908423

很多朋友们以为 SQL 是用来处理结构化数据的；而文本是典型的非结构化数据（unstructured data）。其实 SQL 可以处理文本，而且比 Python 更方便 。

这篇文章简要介绍如何用 MySQL 来分词，构造词表，词向量，和计算文本的相似度。本文中的例子，用 TiDB 应该也可以跑通。微调之后，Apache Hive 和阿里巴巴 MaxCompute 应该也是可以的。

导入数据

为了简单，我们用一个只有三行（三个文档）的文本文件（a.txt）作为原始数据。MySQL 只支持从特定的目录导入文件中的数据。可以用如下 SQL 语句查询这个目录：

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

把 a.txt 拷贝到这个目录（/var/lib/mysql-files/）之后，可以用如下语句导入创建一张表，并且导入数据。因为表里的 id 是自动生成的，所以导入过程会给每一行（每一个文档）分配一个文档 id。

CREATE DATABASE IF NOT EXISTS play;USE play;
DROP TABLE IF EXISTS docs;
CREATE TABLE IF NOT EXISTS docs (
    id INT NOT NULL AUTO_INCREMENT,
    doc TEXT,
    PRIMARY KEY (id));
LOAD DATA INFILE “/var/lib/mysql-files/a.txt”
    INTO TABLE docs (doc);

现在我们可以检查一下结果

mysql> SELECT * FROM docs;
+----+------------------------+
| id | doc                    |
+----+------------------------+
|  1 | fresh carnation flower |
|  2 | mother day             |
|  3 | mother teresa          |
+----+------------------------+

分词

有一些数据库系统，比如阿里云上的 MaxCompute 提供分词用的 UDF，是一个特色。本文假设没有这样的功能。仅仅按照空格来分词，SQL 也是可以通过 inner join 做到的。

因为分词是把一个字符串变成多条记录。具体的说，要取出字符串中第一个、第二个、第三个。。。子串。所以我们需要一个自然数序列。我们可以通过上面例子里自动产生文档 ID 的机制，生成这个序列。下面的语句创建一个表 incr，其中只有一列，是自动产生的自然数序列。

DROP TABLE IF EXISTS incr;
DROP PROCEDURE IF EXISTS generate_sequence;

CREATE TABLE IF NOT EXISTS incr (
n INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (n));

DELIMITER //
CREATE PROCEDURE generate_sequence()
BEGIN
    DECLARE i int DEFAULT 0;
    WHILE i < 5 DO
        INSERT INTO incr () VALUES ();
        SET i = i + 1;
    END WHILE;
END
//
DELIMITER ;

CALL generate_sequence;

上面语句创建了 SQL 子程序（procedure），其中的循环往 incr 表里增加了 5 条记录，从而产生了一个 1 到 5 的自然数序列。我们可以修改其中的 5 为其他任何数值，来创建更长或者更短的序列。

mysql> select * from incr;
+----+
| n |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

利用这个序列，我们可以把每个字符串分割成最多 5 个（或者更多）的子串。

CREATE TABLE doc_words
SELECT
docs.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(docs.doc, ' ', incr.n), ' ', -1) word
FROM
incr INNER JOIN docs
ON CHAR_LENGTH(docs.doc)
-CHAR_LENGTH(REPLACE(docs.doc, ' ', ''))>=incr.n-1
ORDER BY
id, n;

上面语句里的 join 操作把每条记录（字符串，或者叫文档）复制了 5 份；而 SELECT 操作选取每个复制中的第 i 个子串（word）；CREATE TABLE 把结果写入一张新的表 doc_words，其内容如下。

mysql> select * from doc_words;
+----+-----------+
| id | word      |
+----+-----------+
|  1 | fresh     |
|  1 | carnation |
|  1 | flower    |
|  2 | mother    |
|  2 | day       |
|  3 | mother    |
|  3 | teresa    |
+----+-----------+

停用词

很多时候，我们回想剔除分词结果中的停用词（stopwords）。假设我们有一个停用词表 —— 下文中用 (SELECT 'fresh'）替代 —— 假设这个词表里只有一个单词了，下面语句剔除掉 doc_words 表中的停用词。

mysql> SELECT * FROM doc_words WHERE word NOT IN (SELECT 'fresh');
+----+-----------+
| id | word      |
+----+-----------+
|  1 | carnation |
|  1 | flower    |
|  2 | mother    |
|  2 | day       |
|  3 | mother    |
|  3 | teresa    |
+----+-----------+

词向量

仅仅分词还不足以计算文档距离，还需要统计每个文档里，每个词出现的次数 —— 也就是词向量。下面的 SQL 语句可以很方便地做这件事。

CREATE TABLE doc_word_count
SELECT id, word, count(word) as count
FROM doc_words GROUP BY id, word;

我们看看结果。

mysql> SELECT * FROM doc_word_count;
+----+-----------+-------+
| id | word      | count |
+----+-----------+-------+
|  1 | carnation |     1 |
|  1 | flower    |     1 |
|  1 | fresh     |     1 |
|  2 | day       |     1 |
|  2 | mother    |     1 |
|  3 | mother    |     1 |
|  3 | teresa    |     1 |
+----+-----------+-------+

归一化词向量

通过归一化词向量，我们可以得到一个文档的词分布（word distribution）；这是计算文档相似度的输入。为了归一，需要能统计文档的长度，这可以通过 GROUP BY id 来实现。

mysql> SELECT id, sum(count) as len FROM doc_word_count GROUP BY id;
+----+------+
| id | len  |
+----+------+
|  1 |    3 |
|  2 |    2 |
|  3 |    2 |
+----+------+

基于上述方法，下面的 SQL 语句从 doc_words 表推导出 doc_word_dist 表，表示词分布。

CREATE TABLE doc_word_dist
SELECT doc_word_count.id, word, count/len AS prob
FROM doc_word_count,
(SELECT id, sum(count) as len FROM doc_word_count GROUP BY id) s
WHERE doc_word_count.id = s.id;

我们检查一下结果。

mysql> SELECT * FROM doc_word_dist;
+----+-----------+--------+
| id | word      | prob   |
+----+-----------+--------+
|  1 | carnation | 0.3333 |
|  1 | flower    | 0.3333 |
|  1 | fresh     | 0.3333 |
|  2 | day       | 0.5000 |
|  2 | mother    | 0.5000 |
|  3 | mother    | 0.5000 |
|  3 | teresa    | 0.5000 |
+----+-----------+--------+

文档相似度

有了归一化的词向量，下面语句计算文档之间的两两相似度（pairwise similarity）。我们用的是 dot product similarity。

SELECT x.id, y.id, sum(x.prob*y.prob)
FROM doc_word_dist x, doc_word_dist y
WHERE x.id > y.id AND x.word = y.word
GROUP BY x.id, y.id;

在这个非常简单的例子里，第二个和第三个文档里共同出现了一个单词“mother”。而其他任何文档对（pairs）都没有共用的词，所以结果只有一行。

+----+----+--------------------+
| id | id | sum(x.prob*y.prob) |
+----+----+--------------------+
|  3 |  2 |         0.25000000 |
+----+----+--------------------+

AI + SQL

从这个例子我们可以看到。虽然文档 2 和 3 在词向量空间有一定相似度，但是其实一个是关于特蕾莎修女，一个是关于母亲节 —— 英语里 mother 有修女和母亲两个意思 —— 这结果不合理。反而是 文档 1 “康乃馨” 是母亲节必备的礼物，应该存在一定的相似度。

不管我们用 SQL 还是 Python 来做文本分析，我们都希望借助 AI 的力量深刻理解文本，而不是仅仅在字面上做聚类等分析。接下来的文章，我们会更新如何利用 SQLFlow 扩展 SQL，引入 latent topic modeling 技术来做语义理解。

相关数据

上文中为了演示方便，用了很小的数据集（只有三个文档）。对于 程序员 来说，一个更有意义的数据集是 GitHub 上一些著名开源项目里的 code review comments —— 我们可以看看各个项目里的 code review 都在说些什么内容；大牛是怎么表达意见的。

为此，我们写了一个调用 GitHub API 的 crawler：

https://github.com/wangkuiyi/code-review-what/blob/master/crawl/crawl.go github.com

这个 crawler 的注释里有用法。我们试着爬下来了 TensorFlow 和 PyTorch（当然还有我们自己的项目 SQLFlow）的 code review comments。其中 PyTorch 的最多，有 20MB 左右。欢迎大家用上述方法，试试处理这样的数据。我们也很期待听到大家关于用 SQL 处理文本数据的反馈。

百度网盘链接：

https://pan.baidu.com/s/1qC9qXUya5Gvrqn3W3SaB1g 提取码: 7mta

本 文链接，点击"阅读原文"直达：

https://zhuanlan.zhihu.com/p/105828944

关于AINLP

AINLP 是一个有趣有AI的自然语言处理社区，专注于 AI、NLP、机器学习、深度学习、推荐算法等相关技术的分享，主题包括文本摘要、智能问答、聊天机器人、机器翻译、自动生成、知识图谱、预训练模型、推荐系统、计算广告、招聘信息、求职经验分享等，欢迎关注！加技术交流群请添加AINLP君微信(id：AINLP2)，备注工作/研究方向+加群目的。