Как выбрать первую и последнюю запись сгруппированной записи в запросе MySQL с агрегатными функциями?
Если я правильно понимаю ваш вопрос, вам нужно динамическое «скользящее окно», по которому можно рассчитать минимальное значение. Предполагая, что ваш индекс является индексом по умолчанию, то есть он отсортирован в порядке возрастания, вы можете попробовать следующий подход:
import pandas as pd
import numpy as np
from bisect import bisect_left
df = pd.DataFrame({'open': [1, 2, 3, 4, 5],
'high': [5, 6, 6, 5, 7],
'low': [1, 3, 2, 4, 4],
'close': [3, 5, 3, 5, 6]})
Используются те же данные выборки, что и для mommermi, но с низким значением на третий день, измененным на 2 так как третий день также должен быть включен в «скользящее окно».
df['day'] = np.where(df['close'] > df['open'], 'bull', None)
Мы вычисляем столбец day, используя векторизованную операцию numpy, которая должна быть немного быстрее.
bull_index_array = df.loc[df['day'] == 'bull'].index
Мы храним значения индекса строк (дней), которые мы пометили как быков.
def find_index(a, x):
i = bisect_left(a, x)
return a[i-1]
Бисект из базовой библиотеки позволит нам эффективно найти индекс предыдущего бычьего дня. Для этого требуется, чтобы индекс был отсортирован по умолчанию.
def min_value(x):
cur_index = x.name
prev_bull_index = find_index(bull_index_array, cur_index)
return df.loc[prev_bull_index:cur_index, 'low'].min()
Далее мы определяем функцию, которая будет создавать наше «динамическое» скользящее окно путем нарезки исходного кадра данных по предыдущему и текущему индексу.
df['min'] = df.apply(min_value, axis=1)
Наконец, мы применяем функцию min_value по строкам к кадру данных, получая это:
open high low close day min
0 1 5 1 3 bull NaN
1 2 6 3 5 bull 1.0
2 3 6 2 3 None 2.0
3 4 5 4 5 bull 2.0
4 5 7 4 6 bull 4.0
4 ответа
Вы хотите использовать 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 для получения дополнительных объяснений.
Попробуйте начать с ...:
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
Предполагая, что вам нужны идентификаторы записей с наименьшей 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 (дата) и обновляющееся при каждом обновлении столбца 'date'. Таким образом, вам не нужно пересчитывать его для каждого запроса, и вы можете индексировать его.
Новые покрывающие индексы должны выглядеть так. Порядок важен. КЛЮЧ yw_lp_id (year_week, low_price, id), КЛЮЧ 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
. Индексы покрытия очень полезны. За подробностями обращайтесь на этот .
Вот отличное решение этой конкретной проблемы: http://topwebguy.com/first-and-last-in-mysql-a-working-solution/ { {1}} Это почти так же просто, как использовать 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;