MySQL: вычисление локализованного MIN MAX с подзадачей

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

Мне нужно получить MIN и MAX из предыдущих пяти вхождений в таблицу. Хотя моя таблица в настоящее время упорядочена в порядке DESC, я не могу полагаться на нее, поддерживая эту структуру, поскольку последующие обновления RECENT_DATE для определенного MY_ID изменят самые последние 5, а также обновят MY_VALUE.

Вот немного моей базовой таблицы, над которой я работаю.

MY_ID | RECENT_DATE | MY_VALUE
 392, 2013-10-06 12:00:00, 18332
 146, 2013-09-06 12:00:00, 5623
 72, 2013-09-02 12:00:00, 23242
 643, 2013-09-01 12:00:00, 15242
 492, 2013-08-06 12:00:00, 15332
 346, 2013-07-26 12:00:00, 17332
 172, 2013-07-22 12:00:00, 14001
 123, 2013-07-22 12:00:00, 13918
 243, 2013-07-11 12:00:00, 23229

В конце мне нужно, чтобы он выглядел.

MY_ID | RECENT_DATE | MY_VALUE | MAX_VALUE | MIN_VALUE
 392, 2013-10-06 12:00:00, 18332, 23242, 5623
 146, 2013-09-06 12:00:00, 5623, 23242, 5623
 72, 2013-09-02 12:00:00, 23242, 23242, 14001
 643, 2013-09-01 12:00:00, 15242, 17332, 13918
 492, 2013-08-06 12:00:00, 15332, 23229, 13918
 346, 2013-07-26 12:00:00, 17332, 23229, 13918
 172, 2013-07-22 12:00:00, 14001, 23229, 13918
 123, 2013-07-22 12:00:00, 13918, 23229, 13918
 243, 2013-07-11 12:00:00, 23229, 23229, 23229

Итак, вот таблица быстрого образца

CREATE TABLE IF NOT EXISTS dbo.baseTable
(
 MY_ID BIGINT(20) UNSIGNED, 
 RECENT_DATE DATETIME, 
 MY_VALUE BIGINT(20), 
);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (392, '2013-10-06 12:00:00', 18332);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (146, '2013-09-06 12:00:00', 5623);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (72, '2013-09-02 12:00:00', 23242);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (643, '2013-09-01 12:00:00', 15242);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (492, '2013-08-06 12:00:00', 15332);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (346, '2013-07-26 12:00:00', 17332);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (172, '2013-07-22 12:00:00', 14001);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (123, '2013-07-22 12:00:00', 13918);
INSERT INTO dbo.baseTable (MY_ID, RECENT_DATE, MY_VALUE) VALUES (243, '2013-07-11 12:00:00', 23229);

Теперь вот моя попытка забитого запроса A:

SELECT DISTINCT t1.MY_ID,
 t1.RECENT_DATE,
 t1.MY_VALUE, MAX(t2.MY_VALUE) AS MAX_MY_VALUE, MIN(t2.MY_VALUE) AS MIN_MY_VALUE
FROM dbo.baseTable t1
 INNER JOIN (
 SELECT t.MY_ID, t1.RECENT_DATE, t.MY_VALUE
 FROM dbo.baseTable t
 WHERE t.RECENT_DATE <= t1.RECENT_DATE
 LIMIT 5) AS t2 ON t1.MY_ID = t2.MY_ID
/* WHERE OMITTED */
ORDER BY t1.RECENT_DATE DESC
;
1 ответ

Этот запрос должен вернуть нужный результат:

SELECT
 ta.MY_ID, ta.MY_ID, ta.RECENT_DATE, ta.MY_VALUE,
 MAX(tb.MY_VALUE),
 MIN(tb.MY_VALUE)
FROM (
 SELECT
 t1.MY_ID,
 t1.RECENT_DATE,
 t1.MY_VALUE,
 SUBSTRING_INDEX(GROUP_CONCAT(t2.MY_ID ORDER BY t2.RECENT_DATE DESC), ',', 5) as Last_5
 FROM
 baseTable t1 INNER JOIN baseTable t2
 ON t1.recent_date >= t2.recent_date
 GROUP BY
 t1.my_id
) ta INNER JOIN baseTable tb ON FIND_IN_SET(tb.MY_ID, Last_5)
GROUP BY
 ta.MY_ID
ORDER BY
 RECENT_DATE DESC

Это не стандартный SQL и использует некоторые трюки MySQL. Пожалуйста, см. Скрипку здесь.

licensed under cc by-sa 3.0 with attribution.