Извлечение первой и последней записей сгруппированных данных с помощью агрегатных функций в MySQL

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

Чтобы оптимизировать этот процесс, MySQL предлагает более эффективное решение, используя функции GROUP_CONCAT и SUBSTRING_INDEX .

Используя GROUP_CONCAT и SUBSTRING_INDEX

Используя эти функции вместе, вы можете объединить значения из группы, а затем извлечь первое и последнее значения с помощью SUBSTRING_INDEX. Вот как:

SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1) AS open
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1) AS close

Как это работает:

• GROUP_CONCAT объединяет все значения открытия или закрытия в одну строку, отсортированную по столбцу datetime.
• SUBSTRING_INDEX извлекает первое и последнее значения из объединенной строки путем ее разделения через запятую (','). Аргумент '1' указывает первое или последнее вхождение.

Пример:

Рассмотрим следующий запрос:

SELECT MIN(low_price), MAX(high_price),
SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1) AS open,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1) AS close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

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