У меня пока вышло только по набору символов, криво-косо и долго:
| Код |
CREATE TABLE `postfixes` ( -- для стемминга
`id_type` bigint UNSIGNED NOT NULL,
`word` varchar(255) NOT NULL,
/* Keys */
PRIMARY KEY (`id_type`, `word`)
) ENGINE = MyISAM;
CREATE INDEX `Index_2`
ON `postfixes`
(`word`);
CREATE INDEX `Index_3`
ON `postfixes`
(`id_type`);
-- Function: base_form
-- DROP FUNCTION IF EXISTS `base_form`;
DELIMITER |
CREATE DEFINER = 'tishaishii'@'%' FUNCTION `base_form`
(
`in_word` varchar(1024)
)
RETURNS varchar(1024) charset cp1251
BEGIN
declare done boolean default false;
declare post integer default null;
repeat
set post=null;
select
char_length(word)
from
ratiss.postfixes
where
id_type=2 and
char_length(in_word)-char_length(word)>2 and
in_word like concat('%', word)
limit
1
into
post;
if (post is not null) then
set in_word=substring(in_word from 1 for char_length(in_word)-post);
end if;
until (post is null) end repeat;
return in_word;
END|
DELIMITER ;
-- Procedure: getWords
-- DROP PROCEDURE IF EXISTS `getWords`;
DELIMITER |
CREATE DEFINER = 'tishaishii'@'%' PROCEDURE `getWords` ()
BEGIN
declare rx varchar(255) default '[à-ÿÀ-ßa-zA-Z]';
declare done bool DEFAULT false;
declare str varchar(1024) default '';
declare word varchar(1024);
declare chr char(1);
declare len integer default 0;
declare i integer default 1;
declare cur1 cursor for select `query` from history.searched
union all
select `query` from history.cache_step c where id_parent is null
limit 10;
declare continue handler for not found set done = true;
drop temporary table if exists getWordsRes;
drop temporary table if exists getWordsRes2;
create temporary table getWordsRes(
word varchar(1024),
base varchar(1024)
);
create temporary table getWordsRes2 (
base varchar(1024),
`count` bigint(20)
);
create index index_1 ON getWordsRes (base);
create index index_1 ON getWordsRes2 (base);
set done=0;
open cur1;
while (not done) do
fetch cur1 into str;
set len=char_length(str);
set i=1;
set word='';
while(i<=len)do
set chr=substring(str from i for 1);
if(chr regexp rx)then
set word=concat(word, chr);
else
if (char_length(word)>0) then
insert into getWordsRes values(
word,
base_form(word)
);
set word='';
end if;
end if;
set i=i+1;
end while;
if (char_length(word)>0) then
insert into getWordsRes values(
word,
base_form(word)
);
end if;
end while;
insert into getWordsRes2
select
base, count(*) `count`
from
getWordsRes
group by
base;
select
(select gwr.word from getWordsRes gwr where gwr.base=t.base limit 1) as word,
t.`count`
from
getWordsRes2 t
order by
t.`count` desc;
END|
DELIMITER ;
|
|