PostgreSQL全文檢索-詞頻統計

德哥發表於2018-04-18

SQL

背景

TF(Term Frequency 詞頻)/IDF(Inverse Document Frequency 逆向文字頻率)是文字分析中常見的術語。

PostgreSQL支援全文檢索，支援tsvector文字向量型別。

如何在一堆文字中，找到熱詞，或者對詞頻進行分析呢？

方法1，ts_stat

第一種方法來自PostgreSQL的內建函式，ts_stat，用於生成lexeme的統計資訊，例如我想知道某個問答知識庫中，出現最多的詞是哪個，出現在了多少篇文字中。

ts_stat介紹如下

https://www.postgresql.org/docs/devel/static/functions-textsearch.html

https://www.postgresql.org/docs/devel/static/textsearch.html

https://www.postgresql.org/docs/devel/static/textsearch-features.html

12.4.4. Gathering Document Statistics

The function ts_stat is useful for checking your configuration and for finding stop-word candidates.

ts_stat(sqlquery text, [ weights text, ]  
        OUT word text, OUT ndoc integer,  
        OUT nentry integer) returns setof record

sqlquery is a text value containing an SQL query which must return a single tsvector column. ts_stat executes the query and returns statistics about each distinct lexeme (word) contained in the tsvector data. The columns returned are

word text — the value of a lexeme
ndoc integer — number of documents (tsvectors) the word occurred in
nentry integer — total number of occurrences of the word

If weights is supplied, only occurrences having one of those weights are counted.

For example, to find the ten most frequent words in a document collection:

SELECT * FROM ts_stat(`SELECT vector FROM apod`)  
ORDER BY nentry DESC, ndoc DESC, word  
LIMIT 10;

The same, but counting only word occurrences with weight A or B:

SELECT * FROM ts_stat(`SELECT vector FROM apod`, `ab`)  
ORDER BY nentry DESC, ndoc DESC, word  
LIMIT 10;

測試

1、建立生成隨機字串的函式

create or replace function gen_rand_str(int) returns text as $$    
  select substring(md5(random()::text), 4, $1);    
$$ language sql strict stable;

2、建立生成若干個隨機詞的函式

create or replace function gen_rand_tsvector(int,int) returns tsvector as $$    
  select array_to_tsvector(array_agg(gen_rand_str($1))) from generate_series(1,$2);    
$$ language sql strict;

postgres=# select gen_rand_tsvector(4,10);  
                           gen_rand_tsvector                             
-----------------------------------------------------------------------  
 `21eb` `2c9c` `4406` `5d9c` `9ac4` `a27b` `ab13` `ba77` `e3f2` `f198`  
(1 row)

3、建立測試表，並寫入測試資料

postgres=# create table ts_test(id int, info tsvector);  
CREATE TABLE  
postgres=# insert into ts_test select generate_series(1,100000), gen_rand_tsvector(4,10);  
INSERT 0 100000

4、檢視詞頻，總共出現了多少次，在多少篇文字（多少條記錄中出現過）

postgres=# SELECT * FROM ts_stat(`SELECT info FROM ts_test`)  
ORDER BY nentry DESC, ndoc DESC, word  
LIMIT 10;  
 word | ndoc | nentry   
------+------+--------  
 e4e6 |   39 |     39  
 9596 |   36 |     36  
 a84c |   35 |     35  
 2b44 |   32 |     32  
 5146 |   32 |     32  
 92f6 |   32 |     32  
 cd56 |   32 |     32  
 fd00 |   32 |     32  
 4258 |   31 |     31  
 5f18 |   31 |     31  
(10 rows)

5、再寫入一批測試資料，檢視詞頻，總共出現了多少次，在多少篇文字（多少條記錄中出現過）

postgres=# insert into ts_test select generate_series(1,100000), gen_rand_tsvector(2,10);  
INSERT 0 100000  
postgres=# SELECT * FROM ts_stat(`SELECT info FROM ts_test`)                               
ORDER BY nentry DESC, ndoc DESC, word  
LIMIT 10;  
 word | ndoc | nentry   
------+------+--------  
 30   | 4020 |   4020  
 a7   | 4005 |   4005  
 20   | 3985 |   3985  
 c5   | 3980 |   3980  
 e6   | 3970 |   3970  
 f1   | 3965 |   3965  
 70   | 3948 |   3948  
 5e   | 3943 |   3943  
 e4   | 3937 |   3937  
 2b   | 3934 |   3934  
(10 rows)

方法2，madlib

實際上MADlib也提供了詞頻統計的訓練函式

http://madlib.apache.org/docs/latest/group__grp__text__utilities.html

Term frequency

Term frequency tf(t,d) is to the raw frequency of a word/term in a document, i.e. the number of times that word/term t occurs in document d. For this function, `word` and `term` are used interchangeably. Note: the term frequency is not normalized by the document length.

    term_frequency(input_table,  
                   doc_id_col,  
                   word_col,  
                   output_table,  
                   compute_vocab)

Arguments:

input_table

TEXT. The name of the table storing the documents. Each row is in the form <doc_id, word_vector> where doc_id is an id, unique to each document, and word_vector is a text array containing the words in the document. The word_vector should contain multiple entries of a word if the document contains multiple occurrence of that word.

id_col

TEXT. The name of the column containing the document id.

word_col

TEXT. The name of the column containing the vector of words/terms in the document. This column should of type that can be cast to TEXT[].

output_table

TEXT. The name of the table to store the term frequency output. The output table contains the following columns:

id_col: This the document id column (same as the one provided as input).
word: A word/term present in a document. This is either the original word present in word_col or an id representing the word (depending on the value of compute_vocab below).
count: The number of times this word is found in the document.

compute_vocab

BOOLEAN. (Optional, Default=FALSE) Flag to indicate if a vocabulary is to be created. If TRUE, an additional output table is created containing the vocabulary of all words, with an id assigned to each word. The table is called output_table_vocabulary (suffix added to the output_table name) and contains the following columns:

wordid: An id assignment for each word
word: The word/term

參考

《一張圖看懂MADlib能幹什麼》