Discussion :

[voxov]

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

[al1_24]

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

[voxov]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[al1_24]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
;

[voxov]

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

[Waldar]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[voxov]

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.

[Waldar]

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.

[voxov]

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.

[Waldar]

Ce coup-ci, rien à redire !