php - 使用SQL找出出现次数最多的词的最高频率

Question

我正在为我的应用程序(由 JavaScript 和 PHP 组成)使用 MySQL Workbench。我有一个包含推文的 SQL 数据库。我想查询推文(句子)中出现频率最高的词是什么。我做过研究说要使用 count() 来查询，但我仍然无法得到我想要的。

示例数据集:

tweetsID |  Tweets                                           | DateTime
   1     | I can't wait to go to school tomorrow!            | 2014-07-18 12:00:00
   2     | My teacher saw me hanging out after school        | 2014-07-18 12:20:00
   3     | I had Pepper Lunch for my dinner                  | 2014-07-18 12:30:00
   4     | Something happened in my school omg               | 2014-07-18 12:40:00
   5     | This project is so hard!                          | 2014-07-18 12:50:00

预期输出:

Words   |frequency
  I     |2
 can't  |1
wait    |1
 to     |2
school  |3
tomorrow|1
  !     |2
 my     |3
had     |1
teacher |1
saw     |1
 me     |1
hanging |1
out     |1
after   |1
pepper  |1
lunch   |1
for     |1
dinner  |1
something|1
happened |1
in       |1
  omg    |1
 this    |1
project  |1
  is     |1
  so     |1
 hard    |1

我已在以下链接中创建示例数据:

[ http://sqlfiddle.com/#!2/3b3f2/1 ]

任何人都可以教我或任何引用资料供我指导？提前谢谢你。

Answer 1

我认为您最好的选择是使用 PHP 执行此操作。 array_count_values() 浮现在脑海中。

试试这个:

$sqlresults = array(
    "I can't wait to go to school tomorrow!",          
    "My teacher saw me hanging out after school",  
    "I had Pepper Lunch for my dinner",               
    "Something happened in my school omg",            
    "This project is so hard!"
);  

$arr = array();
foreach ($sqlresults as $str) {
    $arr = array_merge($arr, explode(' ', $str));    
}

$arr = array_count_values($arr);

print_r($arr);

See demo

---

引用资料:

• > array_count_values()
• > array_merge()
• > explode()