作者
digoal
日期
2018-03-09
标签
PostgreSQL , 全文检索 , 词频统计 , ts_stat , madlib
背景
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)
性能: 在2018款macmini i5的cpu机器上, 每秒约处理400万词组.
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
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
参考
http://madlib.apache.org/docs/latest/group__grp__text__utilities.html
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
http://madlib.incubator.apache.org/
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
