How to find most popular word occurrences in MySQL?

Question

I have a table called results with 5 columns.

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.

Sample data:

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

Results (single words):

for    6
sale    6
cheap    2
and    2
house    2
furniture 1
cars    1
etc...

Your question is very ambiguous. Please provide sample data and desired results. — Gordon Linoff, Sep 24 '15 at 12:22
possible duplicate of [MySQL match() against() - order by relevance and column?](http://stackoverflow.com/questions/6259647/mysql-match-against-order-by-relevance-and-column) Not an exact duplicate but it answers your question — AgeDeO, Sep 24 '15 at 12:25
@GordonLinoff a list of all possible words? or a large sample data set? — User, Sep 24 '15 at 12:30
The question is pretty much clear and it also reminds me of recent vSauce upload by Michael ["The Zipf Mystery"](https://www.youtube.com/watch?v=fCn8zs912OE). — Anurag, Oct 01 '15 at 08:46

Gordon Linoff · Accepted Answer · 2015-10-04T13:14:57.737

9

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
     . . .

The SQL Fiddle (courtesy of @GrzegorzAdamKowalski) is here.

edited Oct 04 '15 at 13:14

answered Sep 24 '15 at 12:31

Gordon Linoff

1,242,037
58
646
786

Can you put this in a SQL fiddle? – User Sep 24 '15 at 13:29
1

@GrzegorzAdamKowalski . . . Thank you, thank you. I had the comparison backwards. Fixed and triple thanks for the SQL Fiddle. – Gordon Linoff Oct 04 '15 at 13:16

score 7 · Answer 2 · answered Oct 04 '15 at 14:48

You can use ExtractValue in some interesting way. See SQL fiddle here: http://sqlfiddle.com/#!9/0b0a0/45

We need only one table:

CREATE TABLE text (`title` varchar(29));

INSERT INTO text (`title`)
VALUES
    ('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')
;

Now we construct series of selects which extract whole words from text converted to XML. Each select extracts N-th word from the text.

select words.word, count(*) as `count` from
(select ExtractValue(CONCAT('<w>', REPLACE(title, ' ', '</w><w>'), '</w>'), '//w[1]') as word from `text`
union all
select ExtractValue(CONCAT('<w>', REPLACE(title, ' ', '</w><w>'), '</w>'), '//w[2]') from `text`
union all
select ExtractValue(CONCAT('<w>', REPLACE(title, ' ', '</w><w>'), '</w>'), '//w[3]') from `text`
union all
select ExtractValue(CONCAT('<w>', REPLACE(title, ' ', '</w><w>'), '</w>'), '//w[4]') from `text`
union all
select ExtractValue(CONCAT('<w>', REPLACE(title, ' ', '</w><w>'), '</w>'), '//w[5]') from `text`) as words
where length(words.word) > 0
group by words.word
order by `count` desc, words.word asc

Hotdin Gurning · Answer 3 · 2015-09-27T19:22:08.527

This would give you single words (Just if I understand what your single word means.):

select concat(val,' ',cnt) as result from(
    select (substring_index(substring_index(t.title, ' ', n.n), ' ', -1)) val,count(*) as cnt
        from result t cross join(
         select a.n + b.n * 10 + 1 n
         from 
                (select 0 as n union all select 1 union all select 2 union all select 3 
                        union all select 4 union all select 5 union all select 6 
                        union all select 7 union all select 8 union all select 9) a,
                (select 0 as n union all select 1 union all select 2 union all select 3 
                        union all select 4 union all select 5 union all select 6 
                        union all select 7 union all select 8 union all select 9) b
                order by n 
        ) n
    where n.n <= 1 + (length(t.title) - length(replace(t.title, ' ', '')))
    group by val
    order by cnt desc
) as x

Result should be looks like this :

Result
--------
for 6
sale 6
house 2
and 2
cheap 2
phones 1
iphones 1
dogs 1
furniture 1
cars 1
androids 1
cats 1

But if the single word you need like this :

result
-----------
for 6 sale 6 house 2 and 2 cheap 2 phones 1 iphones 1 dogs 1 furniture 1 cars 1 androids 1 cats 1

Just modify the query above to:

select group_concat(concat(val,' ',cnt) separator ' ') as result from( ...

@user it's count 100. So in this case, the subquery `n` would return 1-100. Take a look at http://stackoverflow.com/questions/19073500/sql-split-comma-separated-row for details explanation. — Hotdin Gurning, Sep 27 '15 at 22:09

score 0 · Answer 4 · edited May 23 '17 at 12:08

0

Update

Idea taken from https://stackoverflow.com/a/17942691/98491

This query works on my machine (MySQL 5.7), however Sqlfiddle reports an error. The basic idea is that you should either create a table with numbers from 1 to maximum word occurence (like 4) in your field or as I did, use a UNION 1 .. 4 for simplicity.

CREATE TABLE products (
  `id` int,
  `name` varchar(45)
);

INSERT INTO products
    (`id`, `name`)
VALUES
    (1, 'for sale'),
    (2, 'for me'),
    (3, 'for you'),
    (4, 'you and me')
;

SELECT name, COUNT(*) as count FROM
(
SELECT
  product.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(product.name, ' ', numbers.n), ' ', -1) name
FROM
  (
    SELECT 1 AS n
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
  ) AS numbers
  INNER JOIN products product
  ON CHAR_LENGTH(product.name)
     -CHAR_LENGTH(REPLACE(product.name, ' ', ''))>=numbers.n-1
ORDER BY
  id, n
)
AS result
GROUP BY name
ORDER BY count DESC

Result will be

for | 3
you | 2
me  | 2
and | 1
sale| 1

edited May 23 '17 at 12:08

Community

1
1

answered Sep 24 '15 at 12:25

Jürgen Steinblock

30,746
24
119
189

2

You may mention that one would need a FULLTEXT index for this, supported for MyISAM and since 5.6 also InnoDB – Kaii Sep 24 '15 at 12:28
Why is there no `substring` to split the string at each space? – User Sep 24 '15 at 12:31
@User you don't need split `match ... against` will give you an integer between zero and one which is higher for a better match. – Jürgen Steinblock Sep 24 '15 at 12:38
The result is supposed to return single words and this doesn't. Check the original question. – User Sep 24 '15 at 13:24
@User Yes after you updated your question I now understand what you want to achive. I will update my answer. – Jürgen Steinblock Sep 24 '15 at 13:56

score 0 · Answer 5 · answered Oct 03 '15 at 17:09

SQL is not well suited for this task, While possible there are limitations (the number of words for example)

a quick PHP script to do the same task may be easier to use long term (and likely quicker too)

<?php
$rows = [
    "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",
];

//rows here should be replaced by the SQL result
$wordTotals = [];
foreach ($rows as $row) {
   $words = explode(" ", $row);
    foreach ($words as $word) {
        if (isset($wordTotals[$word])) {
            $wordTotals[$word]++; 
            continue;
        }

        $wordTotals[$word] = 1;
    }
}

arsort($wordTotals);

foreach($wordTotals as $word => $count) {
    echo $word . " " . $count . PHP_EOL;
}

Output

for 6
sale 6
and 2
cheap 2
house 2
phones 1
androids 1
furniture 1
cats 1
cars 1
dogs 1
iphones 1

Python is what I've been using to gather the data, but this could work. I don't use PHP that much, so do you mind changing the code to load the rows from the DB? — User, Oct 03 '15 at 23:46

score 0 · Answer 6 · answered Oct 04 '15 at 10:18

Here is working SQL Fiddle: http://sqlfiddle.com/#!9/0b0a0/32

Let's start with two tables - one for texts and one for numbers:

CREATE TABLE text (`title` varchar(29));

INSERT INTO text
    (`title`)
VALUES
    ('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')
;

CREATE TABLE iterator (`index` int);

INSERT INTO iterator
    (`index`)
VALUES
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
    (16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
;

The second table, iterator must contains numbers from 1 to N where N higher or equal to the lenght of the longest string in text.

Then, run this query:

select
  words.word, count(*) as `count`
from 
(select
  substring(concat(' ', t.title, ' '), i.index+1, j.index-i.index) as word
from
  text as t, iterator as i, iterator as j
where
    substring(concat(' ', t.title), i.index, 1) = ' '
and substring(concat(t.title, ' '), j.index, 1) = ' '
and i.index < j.index
) AS words
where
    length(words.word) > 0
and words.word not like '% %'
group by words.word
order by `count` desc, words.word asc

There are two selects. Outer one simply groups and counts single words (words of length greater than 0 and without any spaces). Inner one extracts all strings starting from any space character and ending with any other space character, so strings aren't words (despite naming this subquery words) because they can contain other spaces than starting and ending one.

Results:

word    count
for     6
sale    6
and     2
cheap   2
house   2
androids    1
cars    1
cats    1
dogs    1
furniture   1
iphones     1
phones  1

How to find most popular word occurrences in MySQL?

6 Answers6

Linked