programming:1c-bitrix:search-stat
Статистика поисковых запросов
Поисковый запрос
SELECT url, count(url) as url_count, date_hit, urldecode(SUBSTRING_INDEX( `url` , '=', -1 )) AS search_query FROM ( SELECT * FROM a_stat_history WHERE DATE_HIT > '2011-01-01' LIMIT 100000 ) as t1 WHERE (url LIKE 'http://info/search/?q=%' OR url LIKE 'http://info/doc/?search_text=%') GROUP BY search_query ORDER BY url_count DESC
Декодирование URL
CREATE DEFINER=`hasanov`@`%` FUNCTION `urldecode`(s VARCHAR(4096)) RETURNS varchar(4096) CHARSET cp1251 DETERMINISTIC BEGIN DECLARE c VARCHAR(4096) DEFAULT ''; DECLARE pointer INT DEFAULT 1; DECLARE h CHAR(2); DECLARE h1 CHAR(1); DECLARE h2 CHAR(1); DECLARE s2 VARCHAR(4096) DEFAULT ''; IF ISNULL(s) THEN RETURN NULL; ELSE SET s2 = ''; WHILE pointer <= LENGTH(s) DO SET c = MID(s,pointer,1); IF c = '+' THEN SET c = ' '; ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN SET h1 = LOWER(MID(s,pointer+1,1)); SET h2 = LOWER(MID(s,pointer+2,1)); IF (h1 BETWEEN '0' AND '9' OR h1 BETWEEN 'a' AND 'f') AND (h2 BETWEEN '0' AND '9' OR h2 BETWEEN 'a' AND 'f') THEN SET h = CONCAT(h1,h2); SET pointer = pointer + 2; SET c = CHAR(CONV(h,16,10)); END IF; END IF; SET s2 = CONCAT(s2,c); SET pointer = pointer + 1; END while; END IF; RETURN s2; END
Кодирование URL
CREATE DEFINER=`hasanov`@`%` FUNCTION `urlencode`(s VARCHAR(4096)) RETURNS varchar(4096) CHARSET utf8 DETERMINISTIC BEGIN DECLARE c VARCHAR(4096) DEFAULT ''; DECLARE pointer INT DEFAULT 1; DECLARE s2 VARCHAR(4096) DEFAULT ''; IF ISNULL(s) THEN RETURN NULL; ELSE SET s2 = ''; WHILE pointer <= length(s) DO SET c = MID(s,pointer,1); IF c = ' ' THEN SET c = '+'; ELSEIF NOT (ASCII(c) BETWEEN 48 AND 57 OR ASCII(c) BETWEEN 65 AND 90 OR ASCII(c) BETWEEN 97 AND 122) THEN SET c = concat("%",LPAD(CONV(ASCII(c),10,16),2,0)); END IF; SET s2 = CONCAT(s2,c); SET pointer = pointer + 1; END while; END IF; RETURN s2; END
programming/1c-bitrix/search-stat.txt · Последнее изменение: 2017/03/27 11:26 — artur