SQL查询以获取表中的单词数 [英] SQL Query to get count of words in table
问题描述
我有一个具有这样的架构的表
I have a table which have schema like this
id name
1 jack
2 jack of eden
3 eden of uk
4 m of s
我想执行一个查询,该查询可以给我这样的单词
I want to execute a query which gives me count of words like this
count word
2 jack
2 eden
3 of
这意味着杰克去过这里2次,伊甸园2次,其中曾3次.
this means jack has been here 2 times, eden 2 times and of has been 3 times.
希望您也有问题,也可以尝试,但没有得到正确的查询或解决方法
Hope you got the question, m trying too but not getting the right query or approach to it
thnx
推荐答案
假设您的表名为temp
(可能不是-将其更改为表的正确名称)
Assuming your table is named temp
(probably not - change it to the right name of your table)
我使用子查询来查找表中的所有单词:
I used a subquery for finding all the words in your table:
select distinct regexp_substr(t.name, '[^ ]+',1,level) word , t.name, t.id
from temp t
connect by level <= regexp_count(t.name, ' ') + 1
此查询从所有记录中拆分所有单词.我将其别名为words
.
然后,我将它与您的表(在查询中称为temp)连接起来,并计算每条记录中出现的次数.
this query splits all the words from all records. I aliased it words
.
Then I joined it with your table (in the query it's called temp) and counted the number of occurences in every record.
select words.word, count(regexp_count(tt.name, words.word))
from(
select distinct regexp_substr(t.name, '[^ ]+',1,level) word , t.name, t.id
from temp t
connect by level <= regexp_count(t.name, ' ') + 1) words, temp tt
where words.id= tt.id
group by words.word
您还可以添加:
having count(regexp_count(tt.name, words.word)) > 1
更新:为了获得更好的性能,我们可以将内部子查询替换为流水线函数的结果:
首先,创建一个架构类型及其表:
update: for better performance we can replace the inner subquery with the results of a pipelined function:
first, create a schema type and a table of it:
create or replace type t is object(word varchar2(100), pk number);
/
create or replace type t_tab as table of t;
/
然后创建函数:
create or replace function split_string(del in varchar2) return t_tab
pipelined is
word varchar2(4000);
str_t varchar2(4000) ;
v_del_i number;
iid number;
cursor c is
select * from temp; -- change to your table
begin
for r in c loop
str_t := r.name;
iid := r.id;
while str_t is not null loop
v_del_i := instr(str_t, del, 1, 1);
if v_del_i = 0 then
word := str_t;
str_t := '';
else
word := substr(str_t, 1, v_del_i - 1);
str_t := substr(str_t, v_del_i + 1);
end if;
pipe row(t(word, iid));
end loop;
end loop;
return;
end split_string;
现在查询应如下所示:
select words.word, count(regexp_count(tt.name, words.word))
from(
select word, pk as id from table(split_string(' '))) words, temp tt
where words.id= tt.id
group by words.word
这篇关于SQL查询以获取表中的单词数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!