1
votes

Premier enregistrement combiné avec GROUP BY

Disons que j'ai une table "valeurs" qui contient les champs id (entier) nom (varchar) valeur (flottant) horodatage (int)

Maintenant, je veux calculer la valeur la plus basse et la première valeur la plus élevée (basée sur l'horodatage) pour chaque nom sur l'ensemble de la table des valeurs.

Est-ce possible d'atteindre dans une seule requête performante ? Je suis tombé sur la fonction 'first_value', mais celle-ci ne semble pas fonctionner. J'ai essayé la requête suivante, en utilisant des jointures, mais aussi sans succès.

name    open    low     high
USD     3       3       7
EUR     5       2       8
GBP     4       4       8

N'y a-t-il pas une sorte de fonction qui rendrait quelque chose de similaire possible?

id  name    value   timestamp
1   USD     3       16540
2   EUR     5       16540
3   GBP     4       16540
4   EUR     2       16600
5   USD     4       16600
6   GBP     5       16600
7   USD     6       16660
8   EUR     7       16660
9   GBP     6       16660
10  USD     5       16720
11  EUR     5       16720
12  GBP     7       16720
13  EUR     8       16780
14  USD     7       16780
15  GBP     8       16780

Exemple de données

SELECT 
    name, 
    FIRST_VALUE(value) as open, 
    MIN(value) as low, 
    MAX(value) as high
FROM values 
GROUP BY name 
ORDER BY timestamp ASC;

Exemple de sortie

SELECT 
    a.name, 
    b.value as open, 
    MIN(a.value) as low, 
    MAX(a.value) as high
FROM values a 
LEFT JOIN values b 
    ON a.name = b.name AND b.id = MIN(a.id) 
GROUP BY a.name;

J'utilise la version du client MySQL: 5.6.39 Une égalité ne devrait pas être possible, si c'est le cas, je me fiche de la valeur choisie.

mysql window-functions max min greatest-n-per-group

5 commentaires

Quelle version de MySQL utilisez-vous?

Ce que vous cherchez à faire n'est pas très clair. Pourriez-vous fournir des exemples de données et des résultats attendus pour clarifier votre question?

Définissez «premier» même lorsque vous utilisez ORDER BY, le tri n'est pas corrigé dans les cas où les valeurs sont égales. De plus, cet ORDER BY n'est pas valide avec les règles ANSI / ISO SQL GROUP BY .. comme @GMB a suggéré que nous devions voir des exemples de données et résultats attendus. Voir Pourquoi devrais-je fournir un exemple minimal reproductible pour une requête SQL très simple?

avez-vous essayé la commande UNION?

J'ai édité l'article avec des exemples de données

3 Réponses :

1
votes

Si vous utilisez MySQL 8.0, cela peut être assez facilement résolu avec les fonctions de fenêtre:

select 
    t.name,
    t.value open,
    (select min(value) from mytable t1 where t1.name = t.name) low,
    (select max(value) from mytable t1 where t1.name = t.name) high
from 
    mytable t
where timestamp = (
    select min(t1.timestamp) from mytable t1 where t1.name = t.name
)

Démo sur DB Fiddle :

select 
    t.name,
    t.value open,
    t0.low,
    t0.high
from 
    mytable t
    inner join (
        select name, min(value) low, max(value) high from mytable group by name
    ) t0 on t0.name = t.name
where t.timestamp = (
    select min(t1.timestamp) from mytable t1 where t1.name = t.name
);

Dans les versions antérieures, vous pourrait:

utiliser une sous-requête corrélée pour filtrer sur le premier enregistrement de chaque nom
joindre la table avec une requête agrégée qui calcule le minimum et le maximum de chaque nom

Query:

| name | open | low | high |
| ---- | ---- | --- | ---- |
| EUR  | 5    | 2   | 8    |
| GBP  | 4    | 4   | 8    |
| USD  | 3    | 3   | 7    |

Démo sur MySQL 5.6 DB Fiddle : mêmes résultats que ci-dessus

Ceci pourrait également être réalisé en utilisant des sous-requêtes en ligne (qui peuvent en fait mieux fonctionner): / p>

select name, value open, low, high
from (
    select
        name,
        value,
        min(value) over(partition by name) low,
        max(value) over(partition by name) high,
        row_number() over(partition by name order by timestamp) rn
    from mytable
) x
where rn = 1

Démo sur MySQL 5.6 DB Fiddle

4 commentaires

J'utilise la version 5.6.39. Je devrais peut-être envisager de simplement mettre à jour mon mysql.

@GillesLesire: oui, vous devriez envisager une mise à jour: MySQL 5.6 est sorti en 2013 et est hors support depuis 2018 ... Quoi qu'il en soit, j'ai mis à jour ma réponse avec des solutions pour cette ancienne version.

@GillesLesire: ma réponse (mise à jour) répond-elle correctement à votre question?

Oui, mais j'ai trouvé une solution plus performante pour le moment. Je cherche actuellement à mettre à niveau ma version mysql vers la version 8 et voir si votre solution offre de meilleurs résultats.

0
votes

dans une seule requête performante

Faites-le de manière logique et laissez le SGBD se soucier des performances. Si ce n'est pas assez rapide, vérifiez vos index.

La valeur associée au premier horodatage nécessite une jointure. Vous pouvez trouver le premier horodatage assez facilement. Obtenir une valeur à partir d'une ligne associée à une ligne donnée: c'est à cela que servent les jointures.

Donc, nous avons:
SELECT 
    name, 
    value as open, 
    v1.low
    v1.high
FROM values as v join (
    select name, 
    min(timestamp) as timestamp, 
    min(value) as low, 
    max(value) as high
    FROM values
    GROUP BY name 
) as v1
on v.name = v1.name and v.timestamp = v1.timestamp

2 commentaires

Je suppose que "vi" est une faute de frappe et est censé être "v1"?

De plus, la sous-requête avait besoin d'une instruction FROM pour qu'elle fonctionne.

0
votes

Cette solution semble offrir les meilleures performances.

SELECT 
name, 
CAST(SUBSTRING_INDEX(GROUP_CONCAT(CAST(value AS CHAR) ORDER BY TIMESTAMP ASC), ',', 1) AS DECIMAL(10, 6)) AS open, 
MIN(value) AS low, 
MAX(value) AS high
FROM mytable
GROUP BY name 
ORDER BY name ASC

0 commentaires