Postgres和词云

Question

我想知道是否有可能创建一个Postgres函数来扫描一些表行并创建一个包含WORD和AMOUNT(频率)的表格？我的目标是使用这个表格来创建一个词云。

- Britto

---

你尝试过什么？你能提供在任何文本中查找“WORD”的规则吗？你能提供样本数据和期望输出吗？ - vyegorov

请查看此链接：http://videos.web-03.net/diagramacao/eduardo/mrbool/builtagcloud/figura04.png - Britto

那么，如何从上面的图片中指定WORDS，比如Drupal和/或Open Source？问题需要更精确的输入。 - vyegorov

有一张行表，其中包含文本和表达式，数据类型为TEXT。我需要一个Postgres函数来将行文本分解成单词，计算它们的数量，并生成一个包含这些信息的表格。 - Britto

Answer 1

有一种简单的方法，但它可能会很慢（取决于您的表格大小）。您可以将文本拆分为数组：

SELECT string_to_array(lower(words), ' ') FROM table;

使用这些数组，可以使用 unnest 对它们进行聚合：

WITH words AS (
    SELECT unnest(string_to_array(lower(words), ' ')) AS word
    FROM table
)
SELECT word, count(*) FROM words
GROUP BY word;

这是一种简单的方法，但存在一些问题，例如它只通过空格而不是标点符号来分割单词。

另一个可能更好的选择是使用PostgreSQL全文搜索。

- MatheusOl

---

干得好！你的想法似乎很有效。但现在我只有一个问题。我正在使用MS-Access作为前端，需要扫描的文本字段包含HTML代码，例如<div> </div>，需要被丢弃。你有什么想法吗？ - Britto

我有一个用于去除HTML代码的函数，链接在这里：http://www.siafoo.net/snippet/148。使用它，你的解决方案几乎完美。 - Britto

你可以简单地使用正则表达式来删除HTML标签：regexp_replace(words, '<[^>]*>', '', 'g')。 - MatheusOl

完美！但是，我该怎么做才能去除HTML表达式，比如&quot？ - Britto

Answer 2

虽然来晚了，但我也需要这个功能并想要使用全文检索。
这个功能方便地去掉了HTML标签。

所以基本上你需要将文本转换为 tsvector 然后使用 ts_stat：

select word, nentry 
from ts_stat($q$ 
    select to_tsvector('simple', '<div id="main">a b c <b>b c</b></div>') 
$q$)
order by nentry desc

结果：

|word|nentry|
|----|------|
|c   |2     |
|b   |2     |
|a   |1     |

但这种方法不太适合规模扩大，所以这是我最终采用的方案：

设置：

-- table with a gist index on the tsvector column
create table wordcloud_data (
    html text not null,
    tsv tsvector not null
);
create index on wordcloud_data using gist (tsv);

-- trigger to update the tsvector column
create trigger wordcloud_data_tsvupdate 
    before insert or update on wordcloud_data 
    for each row execute function tsvector_update_trigger(tsv, 'pg_catalog.simple', html);

-- a view for the wordcloud
create view wordcloud as select word, nentry from ts_stat('select tsv from wordcloud_data') order by nentry desc;

使用方法：

-- insert some data
insert into wordcloud_data (html) values 
    ('<div id="id1">aaa</div> <b>bbb</b> <i attribute="ignored">ccc</i>'), 
    ('<div class="class1"><span>bbb</span> <strong>ccc</strong> <pre>ddd</pre></div>');

之后，您的wordcloud视图应该像这样：

|word|nentry|
|----|------|
|ccc |2     |
|bbb |2     |
|ddd |1     |
|aaa |1     |

额外功能：
将 simple 替换为例如 english，PostgreSQL 将为您去除停用词并进行词干处理。

- Florian Fida