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中怎么處理文本數據問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。