SQL中怎么处理文本数据

SQL中怎么处理文本数据，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

导入数据

　　为了简单，我们用一个只有三行(三个文档)的文本文件(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 技术来做语义理解。

关于SQL中怎么处理文本数据问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。