sql - 在文本 block 中查找单词/短语中的字符百分比

Question

希望创建一个条件来查找文本 block 中每个单词中 % 的字符。我在想 regexp_matches 可能是我需要的，但我不确定如何对其进行编码以查找每个单词/短语的出现百分比。

在过去，我在下面使用它来查明整个文本 block 中的空间，然后计算 block 长度的百分比:

(select count(*) from regexp_matches(table.blocktext, ' ', 'gi')

例如，每个文本 block 可以是 100 到 1000 个字符(一两段)。在这些字符中，我想说的是，对于每个单词，它们是否有 80% 或更多的相同字符出现(可以是字母、数字或其他任何字符)。所以我并没有告诉它这个字符，只是说出构成单词/短语 80% 或更多的任何字符。我还认为我必须添加一个长度条件，以便它避免可能很常见的较短的流行词(如“woohoo”等)。因此，长度条件也可能为 8 或更多。

我希望这在 Postgres 中是可能的，尽管到目前为止我还没有设法在我的搜索中找到任何例子。非常感谢任何帮助，非常感谢您的关注。

Answer 1

一种方法:

SELECT c, count(*) AS ct, (count(*) * 100) / length($1) AS pct
FROM   unnest(string_to_array($1, NULL)) c
WHERE  length($1) > 7  -- maybe a length condition of 8 or more
GROUP  BY 1 
HAVING count(*) > (length($1) * 80) / 100  -- 80 is your % threshold
ORDER  BY 2 DESC;

$1 是要分析的字符串。

返回占给定文本 80% 以上的字符。显然，50 及以上的百分比只能是一行。或者，如果没有足够频繁地使用字符，则什么都不用。

(count(*) * 100)/length($1) 是用整数除法舍入和计算的最快方法。如果您想要精确的结果，请改用 count(*) >= (length($1) * 80)/100.0(另请注意此处的 >=)。 (100.0 将计算结果强制为 numeric 且精确。)

您可以轻松地将其包装到函数或准备好的语句中，并将字符串和百分比作为参数传递。像这样:

CREATE OR REPLACE FUNCTION f_char_pct(_word text, _pct int)
  RETURNS boolean AS
$func$
SELECT EXISTS (
   SELECT 1
   FROM   unnest(string_to_array(_word, NULL)) c
   GROUP  BY c 
   HAVING count(*) > (length(_word) * _pct) / 100
   )
$func$  LANGUAGE sql IMMUTABLE;

调用:

“如果给定文档中有一个单词超过 7 个字符且字符百分比低于给定阈值，则返回 false。否则 true”

SELECT NOT EXISTS (
   SELECT 1
   FROM   unnest(string_to_array('1000000000000z abc 1234567890', ' ')) word
   WHERE  length(word) > 7
   AND    NOT f_char_pct(word, 80)
   );

返回 false。
对于“1000000000000z abc 2222222”返回 true，因为“abc”被忽略并且其他两个词具有 > 80% 的相同字符。
顺便说一句，对于 NULL 输入返回 false。

dbfiddle here

你也可以将整个事情包装在一个函数中......

unnest(string_to_array($1, ' ')) 在每个空格处拆分单词。更复杂的定义是可能的。您可能会使用文本搜索基础设施。考虑:

• Finding the most commonly used non-stop words in a column

---

顺便说一句，我会替换你的简单计数:

select count(*) from regexp_matches(table.blocktext, ' ', 'gi')

.. 使用这个等效但更快的方法:

SELECT length(table.blocktext) - length(replace(table.blocktext, ' ', ''))

正则表达式函数功能强大，但要付出一定的代价。聚合步骤使其成本更高。相关:

• GROUP BY or COUNT Like Field Values - UNPIVOT?