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

Я пытаюсь получить первую и последнюю запись "сгруппированной" записи.
Точнее, я делаю запрос вроде этого

SELECT MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

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

Есть ли способ (по меньшей мере, время обработки), чтобы сделать это с MySQL?

Ответ 1

Вы хотите использовать GROUP_CONCAT и 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

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

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

Ответ 2

Попробуйте это, чтобы начать с...:

Select YearWeek, Date, Min(Low_Price), Max(High_Price)
From
   (Select YEARWEEK(date) YearWeek, Date, LowPrice, High_Price
    From Symbols S
    Where Date BETWEEN(.. ..)
    GROUP BY YEARWEEK(date)) Z
Group By YearWeek, Date

Ответ 3

Вот большое конкретное решение этой конкретной проблемы: http://topwebguy.com/first-and-last-in-mysql-a-working-solution/ Это почти так же просто, как использование FIRST и LAST в MySQL.

Я буду включать код, который фактически предоставляет решение, но вы можете посмотреть весь текст:

SELECT
word ,  

(SELECT a.ip_addr FROM article a
WHERE a.word = article.word
ORDER BY a.updated  LIMIT 1) AS first_ip,

(SELECT a.ip_addr FROM article a
WHERE a.word = article.word
ORDER BY a.updated DESC LIMIT 1) AS last_ip

FROM notfound GROUP BY word;

Ответ 4

Предполагая, что вы хотите, чтобы идентификаторы записей с наименьшим low_price и самым высоким high_price вы могли добавить эти два столбца в ваш запрос,

SELECT 

(SELECT id ORDER BY low_price ASC LIMIT 1) low_price_id,
(SELECT id ORDER BY high_price DESC LIMIT 1) high_price_id,

MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

Если эффективность является проблемой, вы должны добавить столбец для "year_week", добавить некоторые индексы покрытия и разделить запрос на два.

Столбец 'year_week' - это только INT, установленный в значение YEARWEEK (дата) и обновляемый всякий раз, когда обновляется столбец "дата". Таким образом, вам не нужно пересчитывать его для каждого запроса, и вы можете его индексировать.

Новые индексы покрытия должны выглядеть так. Заказ имеет важное значение. KEY yw_lp_id (year_week, low_price, id), KEY yw_hp_id (year_week, high_price, id)

Затем вы должны использовать эти два запроса

SELECT 
(SELECT id ORDER BY low_price ASC LIMIT 1) low_price_id,
MIN(low_price), open, close
FROM symbols
WHERE year_week BETWEEN(.. ..)
GROUP BY year_week

SELECT 
(SELECT id ORDER BY high_price DESC LIMIT 1) high_price_id,
MAX(high_price), open, close
FROM symbols
WHERE year_week BETWEEN(.. ..)
GROUP BY year_week

Покрывающие индексы очень полезны. Для более подробной информации просмотрите этот.