Comment récupérer le premier et le dernier enregistrement d'un enregistrement groupé dans une requête MySQL avec des fonctions agrégées?
j'essaie de récupérer le premier et le dernier enregistrement d'un enregistrement "groupé".
Plus précisément, je fais une requête comme celle-ci
SELECT MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)
mais j'aimerais obtenir le premier et le dernier enregistrement du groupe. Il pourrait en fait en faisant des tonnes de demandes mais j'ai une très grande table.
y a-t-il un moyen (si possible un faible temps de traitement) de faire cela avec MySQL?
4 réponses
vous voulez utiliser GROUP_CONCAT et 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
cela évite les requêtes sub coûteuses et je le trouve généralement plus efficace pour ce problème particulier.
consultez les pages de manuel des deux fonctions pour comprendre leurs arguments, ou visitez cet article qui comprend un exemple de comment faire conversion des délais en MySQL pour plus d'explications.
essayez ceci pour commencer... :
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
Voici une grande solution spécifique à ce problème spécifique: http://topwebguy.com/first-and-last-in-mysql-a-working-solution/ C'est presque aussi simple que D'utiliser le premier et le dernier dans MySQL.
je vais inclure le code qui fournit en fait la solution, mais vous pouvez regarder upi de l'ensemble du texte:
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;
en supposant que vous voulez les ID des enregistrements avec le plus bas prix et le plus haut prix, vous pouvez ajouter ces deux colonnes à votre requête,
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)
si l'efficacité est un problème, vous devez ajouter une colonne pour 'year_week', ajouter quelques index de couverture, et diviser la requête en deux.
la colonne' year_week 'est juste un INT défini à la valeur de YEARWEEK(date) et mis à jour chaque fois que la colonne' date ' est mise à jour. De cette façon, vous n'avez pas à recalculer pour chaque requête et vous pouvez l'index.
les nouveaux index de couverture devraient ressembler à ceci. L'ordre est important. KEY yw_lp_id (year_week, low_price, id), KEY yw_hp_id (year_week, high_price, id)
vous devriez alors utiliser ces deux requêtes
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
et
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
les index de couverture sont assez utiles. Vérifier pour plus de détails.