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

我正在尝试执行一个查询，但不确定是否可行 我有一张桌子叫句子其中包含IS、句子和验证，如下图所示。

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

我的预期输出如下图所示。

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

如果您运行的是 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

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

样本数据：

sent                       | verif
:------------------------- | ----:
hello my name is alex      |  null
hey alin and alex I'm tom  |  null
hello 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
  

当涉及到在单独的表中维护查询结果时，它可能比您想象的更复杂：您需要能够根据原始表中的更改来插入、删除或更新目标表，而这是不可能的在 MySQL 中用一条语句完成。此外，在原始表中保持标志为最新会产生竞争条件，在更新目标表时可能会发生更改。

一个更简单的选择是将查询放在视图中，这样您就可以获得有关数据的始终最新的视角。为此，您可以将上面的查询包装在create view声明，如：

create view words_view as < above query >;

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

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