Discussion :

[Emmanuel R]

Bonjour,

J'ai une question à vous poser, étant face à une table de logs un peu étrange et ayant besoin de transformer les données à l'intérieur pour arriver au résultat souhaité.

J'ai dans cette table ce jeu de donnée :

Code :

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

1
2
3
4
ITER      STAT
1         5910
2         15908
3         20001

Je voudrais arriver à ce résultat en construisant un nouveau champ dans une requête SQL :

Code :

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

1
2
3
4
ITER      STAT        STAT_REELLE
1         5910        5910
2         15908       4088
3         20001       5

La règle est :

• Si ITER=1, STAT_REELLE = STAT.
• SI ITER>1, STAT_REELLE = STAT - (STAT + STAT_REELLE de la ligne ITER -1)

En effet :

• Pour ITER=2 : 15908 - (5910 + 5910) = 4088
• Pour ITER=3 : 20001 - (15908 + 4088) = 5

Ca me fait grandement penser à des fonctions récursives, mais je ne connais pas d'équivalent sous SQL...
Voici ce à quoi je suis arrivé :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select ITER, STAT,
CASE
	WHEN ITER=1 then STAT
	
	ELSE STAT - ( (select STAT
	               from LOGS L2
		       where L2.ITER = L1.ITER - 1
		       ) 
                       + STAT_REELLE de la ligne ITER -1 )
END as STAT_REELLE
	
from LOGS L1

Et c'est là que vous entrez en jeu pour m'aider à dire ce que je pourrais mettre à la place du rouge, vu que je dois utiliser un champ qui n'est pas encore défini à ce moment.
Il est possible de passer par une table temporaire si c'est nécessaire.

En vous remerciant par avance de votre aide, je vous souhaite une excellente journée.

Emmanuel R.

[punkoff]

bonjour,


quel est votre sgbd ?

[Emmanuel R]

Bonjour, il s'agit d'alimenter une table sur Teradata à partir de données sources sur le SGBD H2. J'ai donc accès aux fonctionnalités de ces deux SGBD (mais pas en même temps).

[punkoff]

oki,

je ne connais aucun de ces deux sgbd.

Pour les requetes recursive : http://sqlpro.developpez.com/cours/s...te-recursives/


Sinon regardez du coté des fonctions analytique lead / lag

[Olivier Dufour]

Bonsoir,

Il faut effectivement faire une boucle qui va aller modifier vos lignes dans le bon ordre.

En SQL pur, on pourrait utiliser une sous requete hierarchique ...

C'est plus simple et efficace en PL/SQL (coté Exadata du coup) :

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
18
19
20
21
22
23
24
25
 
declare
sum_stats number;
begin
update logs
   set stat_reelle = stat
 where iter=1
  returning stat + stat_reelle into sum_stats
;
 
for r in (select rowid as r
               , stat
            from logs
            where iter>1
            order by iter)
loop
       update logs
          set stat_reelle = stat - sum_stats
        where rowid = r.r
        returning stat + stat_reelle into sum_stats
       ;
 
end loop;
end;
/

Je vous laisse ajouter le COMMIT.

Cdlt,
OD

[Emmanuel R]

Bonjour,

Quelques nouvelles, seulement maintenant, car j'ai mis du temps à résoudre mon problème. Avant tout je travaille sur un ETL, qui génère donc du code SQL. Donc à mon grand regret même si ça me faisait penser à des fonctions récursives je n'avais aucun moyen d'utiliser ces dernières... Désolé Olivier.

Ensuite, j'ai regardé la fonction Lag (qui existe sous Oracle). J'ai trouvé l'équivalent sur Teradata qui utilise la fonction COALESCE. J'y ai cru jusqu'au bout, mais ça n'a pas fonctionné car il est pas possible d'utiliser des "orderer analyticals functions in subqueries" (de mémoire).

Au final, vu qu'il semble y avoir un véritable bug dans l'alimentation des tables de logs que j'utilise, j'ai demandé une correction à l'éditeur. W&S...


Je rajoute une info supplémentaire pour le suivant qui passera par là : il est possible de gérer le problème d'accéder à la ligne précédente avec une table temporaire. Pour la marche à suivre, sous ETL :

• Création de la table temporaire avec autant de champs dont on a besoin dans le calcul. Il faut en plus un champ ordonné (par exemple avec NUM_ROWS)
• Alimentation de cette dernière, sans toucher aux nouveaux champs (constante possible si besoin d'init)
• On la réalimente en update. Deux sources : cette même table temporaire en deux exemplaires, mais jointe en inner join sur la clé ainsi que le champ "compteur" : NUM_ROWS = NUM_ROWS - 1 (pour récupérer la ligne précédente). On peut dès lors alimenter les nouveaux champs en faisant des calculs entre la ligne courante et la ligne précédente.

Je ne pense pas que ça passe bien avec des grosses volumétries (chargement de la même table deux fois en mémoire), mais c'est au moins une solution possible.

Bonne journée à tous.

Emmanuel R.