如何在MySQL中找到最流行的单词出现? [英] How to find most popular word occurrences in MySQL?
问题描述
我有一个名为results
的表,该表有5列.
I have a table called results
with 5 columns.
我想使用title
列查找表示以下内容的行:WHERE title like '%for sale%'
,然后在该列中列出最受欢迎的单词.一个是for
,另一个是sale
,但我想看看与此相关的其他词.
I'd like to use the title
column to find rows that are say: WHERE title like '%for sale%'
and then listing the most popular words in that column. One would be for
and another would be sale
but I want to see what other words correlate with this.
样本数据:
title
cheap cars for sale
house for sale
cats and dogs for sale
iphones and androids for sale
cheap phones for sale
house furniture for sale
结果(单字):
for 6
sale 6
cheap 2
and 2
house 2
furniture 1
cars 1
etc...
推荐答案
您可以通过一些字符串操作来提取单词.假设您有一个数字表,并且单词之间用单个空格隔开:
You can extract words with some string manipulation. Assuming you have a numbers table and that words are separated by single spaces:
select substring_index(substring_index(r.title, ' ', n.n), ' ', -1) as word,
count(*)
from results r join
numbers n
on n.n <= length(title) - length(replace(title, ' ', '')) + 1
group by word;
如果没有数字表,则可以使用子查询手动构造一个数字表:
If you don't have a numbers table, you can construct one manually using a subquery:
from results r join
(select 1 as n union all select 2 union all select 3 union all . . .
) n
. . .
SQL提琴(由@GrzegorzAdamKowalski提供)是此处.
The SQL Fiddle (courtesy of @GrzegorzAdamKowalski) is here.
这篇关于如何在MySQL中找到最流行的单词出现?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!