mysql - 如何在 sql 中创建查询以将句子切成单词并将它们添加到新表中的频率

Question

我正在尝试做一个我不确定是否可能的查询
我有一张 table 叫 句子 其中包含 ID、句子和验证，如下图所示。



我还有一张 table 叫，字数 其中包含 ID、单词和频率。所以我希望当一个句子输入更新或删除时，该表相应地更新或每天更新，因为可能有很多句子

我的预期输出类似于下图。



任何想法都是可行的，任何人都可以帮忙。

Answer 1

如果您运行的是 MySQL 8.0，我会为此推荐一个递归公用表表达式。这个想法是迭代地遍历每条消息，沿途将其拆分为单词。然后剩下要做的就是聚合。

with recursive cte as (
    select 
        substring(concat(sent, ' '), 1, locate(' ', sent)) word,
        substring(concat(sent, ' '), locate(' ', sent) + 1) sent
    from messages
    union all
    select 
        substring(sent, 1, locate(' ', sent)) word,
        substring(sent, locate(' ', sent) + 1) sent
    from cte
    where locate(' ', sent) > 0
)
select row_number() over(order by count(*) desc, word) wid, word, count(*) freq
from cte 
group by word
order by wid

在早期版本中，您可以使用数字表模拟相同的行为。

Demo on DB Fiddle

样本数据:

sent                       | verif:------------------------- | ----:hello my name is alex      |  nullhey alin and alex I'm tom  |  nullhello alex my name is alin |  null

Results:

wid | word   | freq--: | :----- | ---:  1 | alex   |    3  2 | alin   |    2  3 | hello  |    2  4 | is     |    2  5 | my     |    2  6 | name   |    2  7 | and    |    1  8 | hey    |    1  9 | I'm    |    1 10 | tom    |    1

When it comes to maintaining the results of the query in a separate table, it is probably more complicated than you think: you need to be able to insert, delete or update the target table depending on the changes in the original table, which cannot be done in a single statement in MySQL. Also, keeping a flag up to date in the original table creates a race condition, where changes might occur while your are updating the target target table.

A simpler option would be to put the query in a view, so you get an always-up-to-date perspective on your data. For this, you can just wrap the above query in a create view statement, like:

create view words_view as < above query >;

如果性能成为问题，那么您还可以定期截断和重新填充单词表。

truncate table words;
insert into words < above query >;