LAG si NULL récursif

Version imprimable

29/11/2013, 12h22
PaliPalo_

LAG si NULL récursif
Bonjour

Je suis sous SQL Server Express 2012.

Mon problème est le suivant:

Considérons que j'ai une table Historique( IDGroupe uniqueidentifier, Date datetime, Montant money ).

Pour une raison particulière, certains Montant ont une valeur NULL. Par exemple :
Code:

1 2 3 4 5 1 1/1/2012 12556 2 1/2/2012 -null- 3 1/3/2012 -null- 4 1/5/2012 5478 5 1/6/2012 -null-
Je voudrais que les valeurs NULL prennent la valeur du premier non NULL précédent. A partir de la table précédente, j'aimerais obtenir le résultat suivant :
Code:

1 2 3 4 5 1 1/1/2012 12556 2 1/2/2012 12556 3 1/3/2012 12556 4 1/5/2012 5478 5 1/6/2012 5478
Au début j'ai pensé utiliser LAG de la sorte :
Code:

1 2 3 4 5 SELECT ID, Date, COALESCE( Montant, LAG(Montant) OVER (ORDER BY IDGroupe, Date) ) Montant FROM Historique
Cependant, je me retrouve avec le résultat suivant :
Code:

1 2 3 4 5 1 1/1/2012 12556 2 1/2/2012 12556 3 1/3/2012 -null- 4 1/5/2012 5478 5 1/6/2012 5478
J'ai l'impression que LAG intervient après que les données furent collectées. Je veux dire qu'il me semble que pour la première ligne, vu que Montant n'est pas égal à NULL, celui-ci est bien repris. Que le second Montant étant, lui, égal à NULL prend la valeur de l'enregistrement précédent suivant la clause ORDER BY. Mais que le troisième, ayant aussi une valeur NULL, devrait se voir attribuer la valeur de l'enregistrement précédent suivant la clause ORDER BY. Or, cet enregistrement précédent semble toujours contenir NULL au lieu de la valeur précédemment attribuée (c'est-à-dire la valeur de la première ligne).

Quelqu'un pourrait m'expliquer où je me trompe ?

D'avance merci
29/11/2013, 15h10
SQLpro

C'est normal que vous ayez un NULL, puisque LAG considère la ligne d'avant...
Pour faire cela utilisez une sous requête corrélée.

A +

La question n'est pas idiote puisque la fonction LAG existe chez la concurrence avec l'option IGNORE NULLS, qui n'est pas implémentée chez SQL-Server.

Essayez comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
With Historique (IDGroupe, DateHisto, Montant) as
(
select 1, '2012-01-01', 12556 union all
select 2, '2012-01-02', null  union all
select 3, '2012-01-03', null  union all
select 4, '2012-01-05', 5478  union all
select 5, '2012-01-06', null
)
  ,  SR (IDGroupe, DateHisto, Montant, Part) as
(
select IDGroupe, DateHisto, Montant
     , sum(case when Montant is null then 0 else 1 end) over(order by IDGroupe asc)
  from Historique 
)
select IDGroupe, DateHisto
     , max(Montant) over(partition by part)
  from SR
 order by 1;

29/11/2013, 22h57
PaliPalo_

[RESOLU] LAG si NULL récursif
Citation:

Envoyé par SQLpro

C'est normal que vous ayez un NULL, puisque LAG considère la ligne d'avant...
Pour faire cela utilisez une sous requête corrélée.

A +

Ce n'est pas aussi normal que vous le dites. En tout cas dans mon esprit. La question théorique sous-jacente était : est-ce que le LAG se base sur la valeur du ROW précédent avant ou après que le LAG du ROW précédent ait été effectué.

En d'autres mots, est-ce que la fonction analytique LAG se base sur une table virtuelle dont les valeurs calculées sont modifiées en séquence ou sur une table fictive dont les valeurs calculées ne sont pas modifiées.

Démonstration avec
Code:

1 2 3 4 5 SELECT ID, Date, COALESCE( Montant, LAG(Montant) OVER (ORDER BY IDGroupe, Date) ) Montant_C FROM Historique
1) Les lignes nécessaires sont collectées dans une table fictive (peut-être uniquement les références d'index pour optimisation)
2) De la présence du LAG, le code de traitement analyse et le champ calculé Montant_C
A) La ligne 1 est analysée
i) Montant n'est pas NULL, ce sera sa valeur qui résultera dans Montant_C
B) La ligne 2 est analysée
i) Montant est NULL, la procédure "parcourt dans un ordre précis" afin de trouver la ligne précédente à l'actuelle dans cet ordre. Ici, la valeur de Montant à la ligne 1.
C) La ligne 3 est analysée
i) Montant est NULL, la procédure "parcourt dans un ordre précis" afin de trouver la ligne précédente à l'actuelle dans cet ordre. Ici, arrive ma question est la valeur Montant_C ou la valeur Montant de la ligne 2 qui sera utilisé. Autrement dit, est-ce la valeur NULL ou 12556 qui sera pris.

Je crois que je réponds à ma question dans ma post-réflexion suivante: "L'ordre de traitement des lignes n'est pas garanti en séquence sur l'ordre logique qu'on attend. Il est dès lors impossible de garantir que la ligne x-1 soit absolument traitée avant la ligne x. Donc, il se peut que la ligne x conserve une valeur NULL tout autant quelle peut avoir une nouvelle valeur calculé. Si l'état n'est pas garanti, il ne peut être fiable et doit être interdit."

Waldar, je crois qu'il y a une mésentente sur le terme IDGroupe - que j'ai mal formulé, bien entendu ;) Cependant, je vais regarder à votre solution pour en extraire l'essence. Cela pourrait me fournir une méthode de pensée inédite dans mon univers :)

Par ailleurs, jouant avec des trimestres, j'ai finalement opté pour une suite de 4 LAG en cherchant le premier des 4 montants précédents qui ne soit pas NULL. Il est garanti qu'un trimestre ait un montant. Or, je garde votre réponse sous le bras, si ça peut me permettre de faire gagner quelques précieuses secondes à mon directeur.

En tout cas merci à vous deux pour avoir fait attention à mon post et d'avoir pris du temps à me répondre.