计算每个单词的频率 [英] Count the frequency of each word

问题描述

我一直在浏览互联网，意识到MySQL并不是达到此目的的最佳方法，但无论如何我都在问.有人看到或使用过什么查询，函数或存储过程，这些查询，函数或存储过程将使一个单词在整个文本列中的出现频率.

I've been trolling the internet and realize that MySQL is not the best way to get at this but I'm asking anyway. What query, function or stored procedure has anyone seen or used that will get the frequency of a word across a text column.

    ID|comment
    ----------------------
 Ex. 1|I love this burger
     2|I hate this burger

     word   |  count
     -------|-------
     burger |  2
     I      |  2
     this   |  2
     love   |  1
     hate   |  1

推荐答案

此解决方案似乎可以完成工作(从

This solution seems to do the job (stolen almost verbatim from this page). It requires an auxiliary table, filled with sequential numbers from 1 to at least the expected number of distinct words. This is quite important to check that the auxiliary table is large enough, or results will be wrong (showing no error).

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(maintable.comment, ' ', auxiliary.id), ' ', -1) AS word,
    COUNT(*) AS frequency
FROM maintable 
JOIN auxiliary ON
    LENGTH(comment)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(comment, ' ', auxiliary.id), ' ', -1)
    <> SUBSTRING_INDEX(SUBSTRING_INDEX(comment, ' ', auxiliary.id-1), ' ', -1)
GROUP BY word
HAVING word <> ' '
ORDER BY frequency DESC;

SQL提琴

这种方法效率极低，因为它不能使用任何索引.

This approach is as inefficient as one can be, because it cannot use any index.

作为替代方案，我将使用一个统计表，该表将与触发器保持最新.也许使用上述方法初始化统计信息表.

As an alterative, I would use a statistics table that I would keep up-to-date with triggers. Perhaps initialise the stats table with the above.

这篇关于计算每个单词的频率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！