La sélection de la première et la dernière valeur dans un groupe
j'ai une table MySql composée de cours quotidiens (open, high, low, close et volume) que j'essaie de convertir en données hebdomadaires à la volée. Jusqu'à présent, j'ai la fonction suivante, qui travaille pour les hauts, les bas, et le volume:
SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;
je dois sélectionner la première instance de _open dans la requête ci-dessus. Ainsi, par exemple, s'il y avait un jour férié le lundi (dans une semaine particulière) et la bourse ouverte le mardi, la valeur _open devrait être choisie à partir du mardi qui est regroupé dans sa semaine. De même, la valeur de fermeture devrait être la dernière _close de cette semaine.
est-il possible de sélectionner quelque chose comme FIRST() et LAST() dans MySql de sorte que ce qui précède puisse être enveloppé dans une seule SELECT plutôt que d'utiliser des requêtes select imbriquées?
Voici la déclaration create de ma table pour avoir une idée du schéma:
delimiter $$
CREATE TABLE `mystockdata` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol_id` int(11) NOT NULL,
`_open` decimal(11,2) NOT NULL,
`_high` decimal(11,2) NOT NULL,
`_low` decimal(11,2) NOT NULL,
`_close` decimal(11,2) NOT NULL,
`_volume` bigint(20) NOT NULL,
`add_date` date NOT NULL,
PRIMARY KEY (`id`),
KEY `Symbol_Id` (`symbol_id`,`add_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$
Mise à jour: il n'y a pas de nulls, partout où il y a un jour férié/week-end, la table ne porte aucune mention pour cela date.
4 réponses
si vous utilisez MySQL 8, la solution préférable serait d'utiliser les fonctions de fenêtre FIRST_VALUE () et/ou LAST_VALUE (), qui sont maintenant disponibles. Jetez un oeil à réponse de Lukas Eder.
mais si vous utilisez une ancienne version de MySQL, ces fonctions ne sont pas
soutenu. Vous devez les simuler en utilisant une sorte de solutions de rechange.,
par exemple, vous pouvez utiliser la fonction de chaîne agrégée GROUP_CONCAT () qui crée un ensemble de tous les _open et _close valeurs de la semaine commandés par _date_open et _date desc_close et d'en extraire les premiers éléments de l'ensemble:
select
min(_low),
max(_high),
avg(_volume),
concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
substring_index(group_concat(cast(_open as CHAR) order by _date), ',', 1 ) as first_open,
substring_index(group_concat(cast(_close as CHAR) order by _date desc), ',', 1 ) as last_close
from
mystockdata
group by
myweek
order by
myweek
;
une autre solution consisterait à utiliser des sous-séries avec LIMIT 1 dans le SELECT l'article:
select
min(_low),
max(_high),
avg(_volume),
concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
(
select _open
from mystockdata m
where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
order by _date
LIMIT 1
) as first_open,
(
select _close
from mystockdata m
where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
order by _date desc
LIMIT 1
) as last_close
from
mystockdata
group by
myweek
order by
myweek
;
Veuillez noter que j'ai ajouté LPAD () chaîne de fonction myweek, pour faire le nombre de semaine toujours deux chiffres de long, sinon semaines ne sera pas être classés correctement.
soyez également prudent lorsque vous utilisez substring_index en conjonction avec group_concat(): si l'une des chaînes groupées contient une virgule, la fonction pourrait ne pas retourner le résultat attendu.
à partir de MySQL 8, Vous devriez idéalement utiliser fonctions de fenêtre pour la tâche:
WITH
t1 AS (
SELECT _low, _high, _volume, CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
),
t2 AS (
SELECT
t1.*,
FIRST_VALUE(_open) OVER (PARTITION BY myweek ORDER BY _date) AS first_open,
FIRST_VALUE(_close) OVER (PARTITION BY myweek ORDER BY _date DESC) AS last_close
FROM t1
)
SELECT MIN(_low), MAX(_high), AVG(_volume), myweek, MIN(first_open), MAX(last_close)
FROM t2
GROUP BY myweek
ORDER BY myweek;
Vous aurez probablement besoin d' COALESCE fonction pour obtenir la première valeur. Cependant, vous devez vous assurer que les jours sans données (week-ends et jours fériés) ont une valeur nulle pour _open les jours sans données.
Utilisation serait:
SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;
Pour le dernier (), je ne peux que penser à une jolie hacky solution, qui consisterait à utiliser GROUP_CONCAT puis manipulation de la chaîne pour obtenir la dernière valeur de la liste. Donc peut-être quelque chose comme ceci:
SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;
notez que vous pouvez aussi utiliser le GROUP_CONCAT approche pour le premier élément, au lieu de fusionner si vous voulais cohérente à la recherche de la requête
SELECT MIN(_low), MAX(_high), AVG(_volume), SUBSTRING_INDEX(GROUP_CONCAT(_open), ',', 1), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;
GROUP_CONCAT pour fonctionner correctement, vous devez également vous assurer que les dates sans valeurs null _open et _close champs.
en gros, ce que vous devez faire:
- group by PRODUCTID
- au sein de chaque groupe, ordre par emplacement
- sélectionnez le premier prix pour le même produit que commandé par emplacement
Mettre ensemble, vous pouvez utiliser la requête suivante:
SELECT PRODUCTID,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(LOCATION AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS LOCATION,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(PRICE AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS PRICE
FROM ProductLocation
GROUP BY PRODUCTID;
notez que MySQL n'a pas de fonctions agrégat FIRST() et LAST() pour GROUP BY mais que les fonctions FIRST() et LAST() peuvent être simulées en utilisant GROUP_CONCAT() et SUBSTRING_INDEX ().