LINUX.ORG.RU

Выборка данных из MySQL где значение столбца растёт или падает.

 


0

3

Привет! У меня проблема с выборкой данных из БД. Мне нужно выбрать строки, в которых значение столбца open меняется (увеличивается или уменьшается) на протяжении некоторого времени. Я представляю как реализовать решение используя вычисление направления (рост или падение) значений столбца в фоне, но хотелось бы выбирать их сразу с помощью одного запроса в БД. Структура таблицы:

CREATE TABLE `stock_price` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stock_id` INT(11) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `open` DECIMAL(24, 4) NOT NULL,
  `high` DECIMAL(24, 4) NOT NULL,
  `low` DECIMAL(24, 4) NOT NULL,
  `close` DECIMAL(24, 4) NOT NULL,
  `volume` DECIMAL(24, 4) NOT NULL,
  `adj_close` DECIMAL(24, 4) NOT NULL,
  `symbol` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`stock_id`) REFERENCES `stock` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  KEY (`date`),
  KEY (`stock_id`),
  KEY (`date`),
  UNIQUE KEY (`stock_id`, `date`),
  KEY (`symbol`),
  KEY (`open`),
  KEY (`high`),
  KEY (`low`),
  KEY (`close`),
  KEY (`volume`),
  KEY (`adj_close`)
) ENGINE = InnoDB CHARACTER SET `utf8`;

Я хочу сделать один запрос и на выходе получить список stock_id где значения open росли или падали (это конечно уже два разных запроса).


это можно сделать оконными фунциями, если оно их умеет, но на жирной выборке возможны проблемы с производительностью. типа:

select lead(open) over(order by id) - open as delta ... 

drsm ★★ ()

Здравствуйте. Если ваша таблица тяжеловесная, делать все одним запросом будет туго. Я так думаю, что если диапазоны дат при вычислении роста будут довольно большими, то без расчета темпа роста обойтись будет сложно. На ум приходит только что-то вроде расчета growth_rate, и объединение запросов UNION для каждого stock_id из нужного списка. Как получить для каждого свой growth_rate одним запросом без виртуальных таблиц представляю туго. Но тогда это уже будет несколько запросов, разделенных точкой с запятой тупо, что противоречит условию задачи:

но хотелось бы выбирать их сразу с помощью одного запроса в БД

Расчитывать growth_rate можно по стандартной формуле, вот говно-запрос сходу, тестируйте и оптимизируйте уже на своих данных (сори, проверить возможности сейчас нет).

INSERT INTO `stock_price` (`stock_id`, `date`, `open`) VALUES
(111, "2016-01-01", 321), (111, "2016-01-02", 654),
(222, "2016-01-01", 654), (222, "2016-01-02", 321);
SELECT DISTINCT(`vt`.`stock_id`) FROM (
  SELECT (
    ((SELECT MAX(`open`) FROM `stock_price` AS `sp` 
      WHERE `sp`.`date`="2016-01-02" AND `sp`.`stock_id`=111) /* present value */
      -
      (SELECT MAX(`sp`.`open`) FROM `stock_price` AS `sp` 
      WHERE `sp`.`date`="2016-01-01" AND `sp`.`stock_id`=111) /* past value */)
      /
      (SELECT MAX(`sp`.`open`) FROM `stock_price` AS `sp`
      WHERE `sp`.`date`="2016-01-01" AND `sp`.`stock_id`=111) /* past value */) 
                          > 0 AS `valid_growth_rate`, 111 AS `stock_id`
      UNION
      SELECT (((SELECT MAX(`sp`.`open`) FROM `stock_price` AS `sp` 
      WHERE `sp`.`date`="2016-01-02" AND `sp`.`stock_id`=222)
      -
      (SELECT MAX(`sp`.`open`) FROM `stock_price` AS `sp`
      WHERE `sp`.`date`="2016-01-01" AND `sp`.`stock_id`=222))
      /
      (SELECT MAX(`sp`.`open`) FROM `stock_price` AS `sp` 
      WHERE `sp`.`date`="2016-01-01" AND `sp`.`stock_id`=222)) 
                          > 0 AS `valid_growth_rate`, 222 AS `stock_id`
  ) AS `vt` WHERE `vt`.`valid_growth_rate` = 1
Сразу извиняюсь за китайский, т.к. время у меня уже утро, голова не варит, но я думаю основная идея ясна - для каждого stock_id выбираем проверку growth_rate > 0 - если нужен рост значения на временном интервале (верхний пик даты, нижний пик даты), growth_rate < 0 - если нужно падение на временном интервале, ну и 0 если нужны stock_id c не измененными данными. Date range можно делать конечно какой нужно, естественно надо гнать все это через EXPLAIN если это MySQL и оптимизировать, но, к сожалению, в такое сонное время, сходу, в голову не приходит другого решения именно одним запросом =) А если серьезно - решать такие задачи одним запросом уже само по себе маразм, что доказывает мой говнокод выше =) (хотя не исключено, что кому-то удастся решить эту задачу одним запросом && элегантно =))

znenyegvkby ()
Ответ на: комментарий от znenyegvkby

Вы правы насчёт одного запроса, бредово так решать вопрос, остановил свой выбор на фоновом вычислении направления цен и выборке уже по вычисленным данным.

n0044h ()
Ответ на: комментарий от n0044h

да весь мейнстрим: postgresql, mssql, даже firebird вроде умеет...

drsm ★★ ()

на mysql видел, как делают всякую магию с временными переменными в запросах. Не знаю, на сколько такое вообще применимо (интересно было бы комментарии знающих людей услышать), даже аналоги каких-то рекурсивных запросов делают. На твой случай я такой запрос написал, вроде работает:

select p.id from (select m.id, @prev as prev, @prev := m.weight as current from (select @prev := null) as t, model as m order by m.id) as p where current > prev;
nguseff ()
Ответ на: комментарий от nguseff

Я извиняюсь, но как ваш запрос решает задачу:

Мне нужно выбрать строки, в которых значение столбца open меняется (увеличивается или уменьшается) на протяжении некоторого времени.

И причем здесь

на mysql видел, как делают всякую магию с временными переменными в запросах.

Ну вы, в-принципе, можете засунуть результаты темпа роста во временную переменную, но как и показывает мой говно-запрос выше - КПД данного решения равен 0. Как в этой ситуации можно обойтись без вычисления темпа роста на конкретном временном интервале, вот в чем вопрос? Если решение возможно без вычисления темпа, хотелось бы его услышать. Хоть бы и на ЯП без всяких SQL запросов (представим, что у нас в памяти лежит assoc-array/map)

znenyegvkby ()
Ответ на: комментарий от znenyegvkby

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

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

вычисление направления (рост или падение) значений столбца

Я написал, что это теоретически можно сделать с использованием временных переменных в теле запроса, которые меняют значение в ходе его выполнения. Так, думаю, будет понятнее:

INSERT INTO `stock_price` (`stock_id`, `date`, `open`) VALUES
(111, "2016-01-01", 321), (111, "2016-01-02", 654),(111, "2016-01-03", 777), (111, "2016-01-04", 543),(111, "2016-01-05", 323), (111, "2016-01-06", 543),
(222, "2016-01-01", 123), (222, "2016-01-02", 321),(222, "2016-01-03", 333), (222, "2016-01-04", 555),(222, "2016-01-05", 999), (222, "2016-01-06", 1024);

select stock_id, IF(open > pv, 1, null) as growth from
(
select @pv as pv, @pv:= `sp`.`open` as open,`sp`.`stock_id` as stock_id, date
from `stock_price` as `sp`, 
(select @pv := null) as tmp
where `sp`.`stock_id` in (111,222)
and `sp`.`date` between '2016-01-01' and '2016-01-07' 
order by `sp`.`stock_id`, `sp`.`date`
) as `growth_rates`
group by stock_id
having count(growth) > 4;
nguseff ()
Ответ на: комментарий от nguseff

Спасибо за пояснение, да, теперь мне ваша позиция ясна. Но получается что такое решение опирается только на последовательный рост значения. А что получится, если график роста на временном интервале будет иметь вид американских горок?=) То есть, насколько я понимаю, при таком раскладе, оно уже не заведется:

INSERT INTO `stock_price` (`stock_id`, `date`, `open`) VALUES
(222, "2016-01-01", 123), (222, "2016-01-02", {любое значение меньше предыдущего, допустим 100}),
(222, "2016-01-03", 333), (222, "2016-01-04", 555),
(222, "2016-01-05", 999), (222, "2016-01-06", 1024);
раз работает на основе предыдущего. Здесь, как мы видим, во временном интервале 2016-01-01 - 2016-01-02 значение падает, но в общем, на интервале 2016-01-01 - 2016-01-06 оно растет. Отсюда и мой вопрос - о возможности решения без вычисления темпа роста для указанного отрезка времени. Кстати, я может быть так же не до конца понял поставленную задачу, и автор имел ввиду именно последовательный рост.

znenyegvkby ()
Ответ на: комментарий от znenyegvkby

Если так, то согласен, если у нас тренд как-то по-хитрому будет считаться, лучше выделить расчет куда-нибудь в другое место.

В рамках специальной олимпиады запощу ещё одно решение, тут мы делим интервал на две(или больше, тогда по аналогии с предыдущим решением можно доработать) части, группируя по timestamp, и смотрим растут ли максимумы.

INSERT INTO `stock_price` (`stock_id`, `date`, `open`) VALUES
(111, "2016-01-01", 321), (111, "2016-01-02", 654),(111, "2016-01-03", 777), (111, "2016-01-04", 543),(111, "2016-01-05", 323), (111, "2016-01-06", 543),
(222, "2016-01-01", 123), (222, "2016-01-02", 400),(222, "2016-01-03", 311), (222, "2016-01-04", 555),(222, "2016-01-05", 999), (222, "2016-01-06", 1024);

select `growth`.`stock_id` from
(
select @pv_id as pv_id, @pv_id:=`pm`.`stock_id` as stock_id, 
@pv as pv, @pv:=`pm`.`open` as `open`
from (select @pv_id:=0, @pv := 0) as `t`,
(
select stock_id, round((unix_timestamp(date) - unix_timestamp('2016-01-01'))/(4*60*60*24+1)) as interval_key, max(`sp`.`open`) as `open`
from `stock_price` as `sp`
where `stock_id` in (111,222) and `date` between '2016-01-01' and '2016-01-07'
group by `stock_id`, `interval_key` order by `stock_id`, `interval_key`
) as `pm`
) as `growth`
where `growth`.`pv_id` = `growth`.`stock_id` 
and `growth`.`open` > `growth`.`pv`;
nguseff ()
Ответ на: комментарий от nguseff

Спасибо за объяснение, идея ясна, по поводу

лучше выделить расчет куда-нибудь в другое место.

абсолютно согласен, только я говорил не о конкретной реализации, а всего лишь о алгоритме решения. Я сразу подумал о темпе роста, единственно, в связи с возможностью расчета среднего темпа на равных интервалах времени для гибкости решения. Потому и засунул его в запрос, для примера, в первом ответе. То есть, имея средний темп роста, мы имеем что-то аналогичное score для результатов поиска, что позволит нам фильтровать результаты, отталкиваясь от допустимых порогов. Пример: Допустим, у нас есть те же начальные данные

INSERT INTO `stock_price` (`stock_id`, `date`, `open`) VALUES
(222, "2016-01-01", 123), (222, "2016-01-02", 400),
(222, "2016-01-03", 311), (222, "2016-01-04", 555),
(222, "2016-01-05", 999), (222, "2016-01-06", 1024);
Мы расчитываем средний темп роста для временного интервала в 1 день, и тут же, в запросе, устанавливаем максимальный порог, допустим результаты со средним темпом роста меньше 5%, нас не интересуют. То есть это решение, единственно, позволяет избегать простой проверки значения на >/</=. Опять же ваш вариант полностью решает поставленную автором задачу, насколько я вообще ее понял=)

znenyegvkby ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.