Requête sql challenge 2

Version imprimable

04/10/2013, 18h02
voxov

Requête sql challenge 2
Bonjour,

Un autre cas "challenging" : je dispose d'une table dont je dois mettre à jour 2 colonnes en fonction d'une autre ligne qui se trouve dans la même table.

Exemple :
```
id	Jour	         Montant  JourPrec     Variation
1	1/06/2013	 1000		
2	2/06/2013	 1100	     1/06/2013	100
3	3/06/2013	 1200	     2/06/2013	100
4	5/06/2013	 1300	     4/06/2013	-200
5	10/06/2013	 1400	     9/06/2013	-200
6	4/06/2013	 1500	     3/06/2013	300
7	9/06/2013	 1600	     5/06/2013	300
8	12/06/2013	 1700	    10/06/2013	300
9	13/06/2013	 1800	    12/06/2013	100
10	14/06/2013	 1900	    13/06/2013	100
11	20/06/2013	 2000	    19/06/2013	-100
12	19/06/2013	 2100	    18/06/2013	-100
13	18/06/2013	 2200	    14/06/2013	300
14	30/06/2013	 2300	    20/06/2013	300
```
Les colonnes à mettre à jour sont :
JourPrec (jour précédent) = valeur de la colonne Jour du jour précédent
Variation = différence entre le Montant de la ligne et celui de la ligne du jour précédent.

Contraintes :
- il y a des "trous" dans la séquence des jours
- dans la table, les lignes ne sont pas toujours dans l'ordre chronologique

Merci d'avance pour vos solutions/commentaires.

Voxov
04/10/2013, 18h47
al1_24

Quel SGBD utilises-tu ?
Qu'as-tu déjà essayé comme requêtes ?

Citation:

Envoyé par al1_24

Quel SGBD utilises-tu ?
Qu'as-tu déjà essayé comme requêtes ?

1. J'utilise Access, mais la solution doit trourner sur PosgreSQL et sur SQL server.

2. Une requête qui fonctionne, mais ...
Code:

1 2 3 SELECT VentesDuJour.id, VentesDuJour.Jour, VentesDuJour.Montant AS MontantDuJour, (SELECT TOP 1 VentesDeVeille.Jour FROM Ventes VentesDeVeille WHERE VentesDeVeille.Jour<VentesDuJour.Jour ORDER BY VentesDeVeille.Jour DESC) AS VeilleDeJour, (SELECT TOP 1 VentesDeVeille.Montant FROM Ventes VentesDeVeille WHERE VentesDeVeille.Jour<VentesDuJour.Jour ORDER BY VentesDeVeille.Jour DESC) AS MontantDeVeille, (MontantDuJour-MontantDeVeille) AS Variation FROM Ventes AS VentesDuJour ORDER BY VentesDuJour.Jour;
... mais :
- elle ne fait pas la mise à jour
- elle contient 2 sous-requêtes dans le SELECT (une par colonne à restituer); n'y a-t-il pas une solution plus élégante ?

Voxov

Une autre manière d'écrire ta requête de sélection :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  VentesDuJour.id
    ,   VentesDuJour.Jour
    ,   VentesDuJour.Montant    AS MontantDuJour
    ,   VentesDeVeille.Jour     AS VeilleDeJour
    ,   VentesDeVeille.Montant  AS MontantDeVeille
    ,   (VentesDuJour.Montant - VentesDeVeille.Montant) AS Variation
FROM    Ventes  AS VentesDuJour
    CROSS JOIN
        Ventes  AS VentesDeVeille
        WHERE   EXISTS
                (   SELECT  NULL
                    FROM    Ventes  AS CalcVeille
                    WHERE   CalcVeille.Jour < VentesDuJour.Jour
                    HAVING  MAX(CalcVeille.Jour) = VentesDeVeille.Jour
                )
ORDER BY VentesDuJour.Jour
;

07/10/2013, 13h14
voxov

Citation:

Envoyé par al1_24

Une autre manière d'écrire ta requête de sélection :

Astucieux, mais ne fonctionne pas en Access comme ça (message "Erreur de syntaxe dans la clause FROM").
Par contre, pas de problème si on remplace "CROSS JOIN" par ",".
Merci.

voxov

Citation:

Envoyé par voxov

1. J'utilise Access, mais la solution doit trourner sur PosgreSQL et sur SQL server.

Comme sur votre autre sujet, Access c'est un peu le parent pauvre des SGBD avec des fonctionnalités très limitées.
La requête suivante fonctionne sur SQL-Server (2012+), PostGreSQL 8.x+ et Oracle (8i+) :
Code:

1 2 3 4 5 select id, Jour, Montant , lag(Jour) over(order by Jour asc) as JourPrec , Montant - lag(Montant) over(order by Jour asc) as Variation from Ventes order by id asc;
C'est une requête compatible avec la norme SQL:2003.

08/10/2013, 19h02
voxov

Citation:

Envoyé par Waldar

C'est une requête compatible avec la norme SQL:2003.

Oui, Lag() (et Lead()) sont exactement ce qui manquait à SQL pour faire ce genre de requête simplement. Et elles ne sont toujours pas supportées dans Access (2010).
Merci.
08/10/2013, 19h16
Waldar

Citation:

Envoyé par voxov

Oui, Lag() (et Lead()) sont exactement ce qui manquait à SQL pour faire ce genre de requête simplement. Et elles ne sont toujours pas supportées dans Access (2010).

Je pinaille un peu mais dans votre prose, vous ne pouvez pas écrire "sont exactement ce qui manquait à SQL".

Elles manquent à Access oui - et c'est regrettable - mais pas au langage SQL car elles sont décrites et implémentées depuis déjà dix ans : mais chaque éditeur avance à son rythme.
09/10/2013, 14h40
voxov

Citation:

Envoyé par Waldar

Je pinaille un peu mais dans votre prose, vous ne pouvez pas écrire "sont exactement ce qui manquait à SQL".

Elles manquent à Access oui - et c'est regrettable - mais pas au langage SQL car elles sont décrites et implémentées depuis déjà dix ans : mais chaque éditeur avance à son rythme.

Ce que je voulais dire, en d'autres termes, c'est que sans des fonctions comme Lead() et Lag(), des opérations de calcul assez simples sont relativement compliquées à réaliser (ex. taux d'accroissement, désaisonnalisation, moyenne mobile, lissage exponentiel, ...).

Dans SQL, elles manquaient jusqu'en 2003.
Dans Access, elles manquent toujours.
09/10/2013, 14h41
Waldar

Ce coup-ci, rien à redire !