Вычислить среднее среднее квартили в SQL

Я хочу написать хранимую процедуру в SQL (MySQL), чтобы вычислить среднее значение второго и третьего квартилей.

Другими словами, у меня есть записи для измерений, сколько времени требуется для загрузки URL-адреса. Записи - это (id, url, time), и они содержат много измерений для каждого URL-адреса. То, что я пытаюсь сделать, это для каждого URL-адреса удалить самый низкий и верхний 25% (т.е. Нижний и верхний квартили) и вычислить среднее значение оставшихся 25% -75% времени загрузки. И сохраните это в другой таблице.

Я видел несколько примеров для этого для MS SQL и, казалось, был относительно простым. Но я должен использовать MySQL, где:

  • Предложение LIMIT не поддерживает проценты (нет аналога для выбора 25%)
  • Предложение LIMIT не поддерживает его аргументы как переменные (только константы)
  • функции не поддерживают динамический SQL (например, PREPARE и EXECUTE)

И я добрался до места:

create procedure G(
  IN val VARCHAR(10)
)
Begin
  select @cnt:=count(*) from test where a=val;
  select  @of:= @cnt /4;
  SELECT @len:= @cnt/2; 
  Prepare stmt from 'select * from test where a="a" LIMIT ?,?';
  execute stmt using @of, @len;
END;

Я могу написать его на PHP, но думаю, что в SQL он будет иметь гораздо лучшую общую производительность. Я буду очень благодарен за помощь.

Ответы

Ответ 1

Посмотрите на ответ и комментарий @Richard aka cyberkiwi в этот вопрос:

Select *
from
(
    SELECT tbl.*, @counter := @counter +1 counter
    FROM (select @counter:=0) initvar, tbl
    ORDER BY ordcolumn
) X
where counter >= (25/100 * @counter) and counter <= (75/100 * @counter);
ORDER BY ordcolumn

Ответ 2

Вы можете создать значения квартилей, используя IF, чтобы установить их в ноль, если в неправильном квартиле:

Предположим, что таблица необработанных данных создана

DROP TABLE IF EXISTS `rawdata`;
CREATE TABLE `rawdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(250) NOT NULL DEFAULT '',
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

(и, конечно же, заселен).

Предположим также, что данные таблицы квартили создаются с помощью

DROP TABLE IF EXISTS `quartiles`;
CREATE TABLE `quartiles` (
  `url` varchar(250) NOT NULL,
  `Q1` float DEFAULT '0',
  `Q2` float DEFAULT '0',
  `Q3` float DEFAULT '0',
  `Q4` float DEFAULT '0',
  PRIMARY KEY (`url`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

(и осталось пустым).

Тогда процедура заполнения квартилей из rawdata будет выглядеть как

DELIMITER ;;

CREATE PROCEDURE `ComputeQuartiles`()
    READS SQL DATA
BEGIN
    DECLARE numrows int DEFAULT 0;
    DECLARE qrows int DEFAULT 0;
    DECLARE rownum int DEFAULT 0;
    DECLARE done int DEFAULT 0;
    DECLARE currenturl VARCHAR(250) CHARACTER SET utf8;
    DECLARE Q1,Q2,Q3,Q4 float DEFAULT 0.0;
    DECLARE allurls CURSOR FOR SELECT DISTINCT url FROM rawdata;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET currenturl='';

    OPEN allurls;
    FETCH allurls INTO currenturl;
    WHILE currenturl<>'' DO
        SELECT COUNT(*) INTO numrows FROM rawdata WHERE url=currenturl;
        SET qrows=FLOOR(numrows/4);
        if qrows>0 THEN
            -- Only session parameters can be recalculated inside a query,
            -- so @rownum:[email protected]+1 will work, but rownum:=rownum+1 will not.
            SET @rownum=0;
            SELECT
                SUM(IFNULL(QA,0))/qrows, 
                SUM(IFNULL(QB,0))/qrows, 
                SUM(IFNULL(QC,0))/qrows, 
                SUM(IFNULL(QD,0))/qrows
            FROM (
                SELECT 
                    if(@rownum<qrows,time,0) AS QA,
                    if(@rownum>=qrows AND @rownum<2*qrows,time,0) AS QB,
                    -- the middle 0-3 rows are left out 
                    if(@rownum>=(numrows-2*qrows) AND @rownum<(numrows-qrows),time,0) AS QC,
                    if(@rownum>=(numrows-qrows),time,0) AS QD,
                    @rownum:[email protected]+1 AS dummy
                FROM rawdata
                WHERE url=currenturl ORDER BY time
            ) AS baseview
            INTO Q1,Q2,Q3,Q4
            ;
            REPLACE INTO quartiles values (currenturl,Q1,Q2,Q3,Q4);
        END IF;

        FETCH allurls INTO currenturl;
    END WHILE;
    CLOSE allurls;

END ;;

DELIMITER ;

Основные моменты:

  • Используйте курсор для циклирования URL-адресов (или адаптируйте образец, чтобы принять URL-адрес в качестве параметра)
  • Для каждого URL найдите общее количество строк
  • Сделайте некоторые тривиальные математические данные, чтобы опустить средние строки, если (rowcount % 4) != 0
  • выберите все необработанные строки для URL-адреса, присвоив значение time одному из QA-QD, в зависимости от номера строки, присвоив другому Qx значение 0
  • Используйте этот запрос в качестве подзапроса для другого, который суммирует и нормализует значения
  • Используйте результаты этого суперзапроса для обновления таблицы квартилей.

Я протестировал это с 18432 необработанными строками, url=concat('http://.../',floor(rand()*10)), time=round(rand()*10000) на машине 8x1,9 ГГц, и он закончил последовательно в 0.50-0.54 сек

Ответ 3

как насчет этого?

prepare stmt from select concat('select * from test where a="a" LIMIT ',@of,@len);
execute stmt;

Ответ 4

Взгляните на этот отличный пример вычисления процентилей с MySQL. Я использовал это с большим успехом на некоторых довольно больших наборах данных.

http://planet.mysql.com/entry/?id=13588

Обратите внимание на раздел, относящийся к group_concat_max_len - это действительно важно. Установка этого значения в максимально допустимое значение, которое является вашим параметром для максимального размера пакета, гарантирует, что если строка, которую он создает, станет слишком большой, вы получите правильную ошибку, а не только предупреждение об усеченном поле.

SET @@group_concat_max_len := @@max_allowed_packet;

Я бы использовал эту функцию для вычисления 25-го и 75-го процентилей (что можно сделать в одном запросе), а затем вычислить средние значения ваших оставшихся данных, выполнив второй запрос к данным.

<?php
$lowVal = /* result of query getting the 25%ile value */;
$highVal = /* result of query getting the 75%ile value */;

$strSQL = "SELECT AVG(`field`) AS myAvg 
             FROM `table` 
             WHERE { your_existing_criteria_goes_here }
                AND `filter_field` BETWEEN '{$lowVal}' AND '{$highVal}';"
/* Run the query and extract your data */
?>

Надеюсь, что все имеет смысл и поможет с вашей проблемой:)

Ответ 5

Почему бы вам просто не использовать один запрос таким образом:

select url, avg(time)
from mytable A
where time >
       (select min(B.time) + ((max(B.time)-min(B.time))/100*25)
          from mytable B where B.url = A.url)
and time <
       (select max(B.time) - ((max(B.time)-min(B.time))/100*25)
          from mytable B where B.url = A.url)
group by url;