Выбор первого и последнего значений в группе

У меня есть таблица MySql, состоящая из ежедневных котировок акций (open, high, low, close и volume), которые я пытаюсь преобразовать в еженедельные данные "на лету". До сих пор у меня есть следующая функция, которая работает для максимумов, минимумов и объема:

SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

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

Можно ли выбрать что-то вроде FIRST() и LAST() в MySql, чтобы вышеупомянутое могло быть обернуто внутри одного SELECT, а не с помощью вложенных запросов выбора?

Здесь моя таблица создает инструкцию, чтобы получить представление о схеме:

delimiter $$
CREATE TABLE `mystockdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol_id` int(11) NOT NULL,
  `_open` decimal(11,2) NOT NULL,
  `_high` decimal(11,2) NOT NULL,
  `_low` decimal(11,2) NOT NULL,
  `_close` decimal(11,2) NOT NULL,
  `_volume` bigint(20) NOT NULL,
  `add_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Symbol_Id` (`symbol_id`,`add_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$

Обновление: нет нулей, где есть праздник/выходные дни, таблица не несет никакой записи за эту дату.

Ответы

Ответ 1

MySql не имеет агрегированной функции First() или Last(). Но вы можете имитировать их с помощью GROUP_CONCAT, который создает набор всех значений _open и _close недели, упорядоченной по _date для _open и _date desc для _close, и извлечение первого элемент набора:

SELECT
  MIN(_low),
  MAX(_high),
  AVG(_volume),
  CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek,
  SUBSTRING_INDEX(GROUP_CONCAT(CAST(_open AS CHAR) ORDER BY _date), ',', 1 ) as first_open,
  SUBSTRING_INDEX(GROUP_CONCAT(CAST(_close AS CHAR) ORDER BY _date DESC), ',', 1 ) as last_close
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Другим решением будет такое, которое использует подзапросы с LIMIT 1 в предложении SELECT:

SELECT
  MIN(_low),
  MAX(_high),
  AVG(_volume),
  CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek,
  (select _open from mystockdata m where WEEK(m._date)=WEEK(mystockdata._date) order by _date LIMIT 1) as first_open,
  (select _close from mystockdata m where WEEK(m._date)=WEEK(mystockdata._date) order by _date desc LIMIT 1) as last_close
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Ответ 2

Вам понадобится COALESCE функция, чтобы получить первое значение. Тем не менее, вы должны убедиться, что дни без данных (выходные и праздничные дни) имеют нулевое значение для _open в эти дни без данных.

Использование:

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Для значения last() я могу только подумать о довольно хакерском решении, которое должно было бы использовать GROUP_CONCAT, а затем строковое манипулирование, чтобы получить последнее значение из списка. Возможно, что-то вроде этого:

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Заметьте, что вы могли бы также использовать подход GROUP_CONCAT для первого элемента вместо объединения, если вам нужен последовательный поисковый запрос

SELECT MIN(_low), MAX(_high), AVG(_volume), SUBSTRING_INDEX(GROUP_CONCAT(_open), ',', 1), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Для правильной работы GROUP_CONCAT вам также необходимо убедиться, что даты без значений имеют нулевое значение в полях _open и _close.

Ответ 3

В принципе, что вам нужно сделать: 1. группа по ПРОИЗВОДСТВУ 2. в каждой группе, приказ LOCATION 3. выберите FIRST цену за тот же продукт, что и заказ LOCATION Объединяя их, вы можете использовать следующий запрос:

SELECT PRODUCTID, 
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(LOCATION AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS LOCATION,
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(PRICE AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS PRICE
FROM ProductLocation
GROUP BY PRODUCTID;

Обратите внимание, что MySQL не имеет агрегатных функций FIRST() и LAST() для GROUP BY, но такие FIRST() AND LAST() можно моделировать с помощью функций GROUP_CONCAT() и SUBSTRING_INDEX().