Инструменты пользователя

Инструменты сайта


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

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki