Optimisation d'un script

Version imprimable

Voir 40 message(s) de cette discussion en une page

01/05/2018, 11h13
soazig

Rebonjour,
N'y-a-t-il que moi que ça choque l'utilisation de fonction de fenêtrage Min, max et count avec un order by dans le over?
Pour un ROW_NUMBER() je le conçois mais pour les min, max et count je ne vois pas à quoi il peut servir.
Par ailleurs, j'avoue mon ignorance quand aux indications

Code:

rows between 1 preceding and 1 preceding

A+
Soazig
01/05/2018, 11h53
lotofski

Bonjour SOAZIG ;
Merci pour votre aide , je vais suivre tes conseilles et voir aprés le résultat , merci beaucoup et bon 1 mai
01/05/2018, 16h31
lotofski

Rebonjour Soazig ,
Merci beaucoup pour votre aide , mais comme je peux pas l'essayer OJR , mais j'essaye de comprendre un peu le script :
- dans chaque sélect on trouve ordre , il sert à quoi ( je veux juste apprendre je connais pas grand chose )
- et pour les index que je dois créer , est ce que ça va pas créer des anomalies vu que ptr ils les ont déja créer .
Merci encore une fois pour votre aide , je dois l'essayer demain et l'expliquer pendant la réunion du matin :aie: :aie: :aie:
01/05/2018, 18h40
soazig
Rebonjour,
Pour ordre j'ai fait ce que je te suggérai à mon premier poste.
J'ai remplacé

Code:

case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end

par Ordre.
On a une table de correspondance nommé Correspondance qui est sous forme de with et qui indique
Code:

1 2 3 4 5 Cod_EVT;Ordre 74;1000 43:1000 21;0 42;0
Donc pour ce qui est dans la table de correspondance à la place du case when il suffit de prendre la colonne Ordre, pour ce qui n'y est pas on est sur le else du case when on met COD_EVT. C'est pour cela que la jointure sur Correspondance est externe, pour prendre en compte ce qui n'est pas dans la table de correspondance.
[Edit]Comme le case when apparaissait dans un order by je l'ai appelé ordre mais si fonctionnellement ça a un nom, il faut utiliser ce nom
[/edit]
Pour les index maintenant, je ne comprends pas ptr dans

Citation:

et pour les index que je dois créer , est ce que ça va pas créer des anomalies vu que ptr ils les ont déja créer .

Si l'index existe il ne faut pas le créer, je croyais qu'il n'y avait pas d'index sur tes tables à part la clé primaire.
Si tu crées des index non uniques cela ne créera pas d'anomalie, cela peut ralentir les écritures dans les tables concernées.
[Edit]Remarque on peut créer plusieurs index par tables sans problème, et c'est très fréquent[/Edit]
Cordialement
Soazig
02/05/2018, 00h03
lotofski

je vous remercie pour tout ces détailles , merci beaucoup
02/05/2018, 11h16
lotofski

2 pièce(s) jointe(s)

Bonjour Soazig ;
Voila j'ai lancé l script mais j'ai des erreurs ,avec et sans correspondance Pièce jointe 375291 Pièce jointe 375295 ,
et j'ai essayé de garder le mm script d'avant et de mettre les 3 lignes en commentaires pour voir si il ramènent ou pas (finalement ils ramènent perce-que 2000 lignes en plus )
02/05/2018, 11h36
aieeeuuuuu

Bonjour,

Citation:

Envoyé par soazig

N'y-a-t-il que moi que ça choque l'utilisation de fonction de fenêtrage Min, max et count avec un order by dans le over?
Pour un ROW_NUMBER() je le conçois mais pour les min, max et count je ne vois pas à quoi il peut servir.

Cela permet de limiter la fenêtre aux lignes qui précédent la ligne courante, selon l'ordre spécifié.
très pratique notamment avec un SUM pour obtenir une somme cumulée croissante.
02/05/2018, 11h53
soazig

Bonjour,
Merci aieeeuuuuu, j'aurais appris quelque chose.
Pour lotofski, et ses erreurs d'execution; peut-être que Teradata ne permet pas l'usage de CTE dans un insert, peux-tu essayer juste le select?
Que donne les index, parce que c'est surtout là que tu peux gagner?
Cordialement
Soazig
02/05/2018, 12h03
Waldar

Citation:

Envoyé par soazig

Code:

rows between 1 preceding and 1 preceding

C'est une clause des fonctions de fenêtrage qui permettent de borner leur utilisation à un nombre de lignes précis, ici de 1 en arrière jusqu'à 1 en arrière, on est pile dans l'équivalence de la fonction LAG(1).
Cette fonction n'a été implémentée qu'assez récemment dans Teradata, en v15 ou 16 je ne sais plus.

À noter qu'on peut aussi utiliser RANGE à la place de ROWS ce qui permet d'avoir des bornes dynamiques & plus fonctionnelles que techniques.
C'est déjà référencé dans cet article qui date de 2004 :
https://lalystar.developpez.com/fonc...alytiques/#LII
02/05/2018, 12h08
Waldar

Citation:

Envoyé par soazig

Pour lotofski, et ses erreurs d'execution; peut-être que Teradata ne permet pas l'usage de CTE dans un insert, peux-tu essayer juste le select ?

La syntaxe with insert select est propre à SQL-Server, et c'est une aberration d'avoir un ordre DML qui apparaît en plein milieu d'un SELECT.

Citation:

Envoyé par soazig

Que donne les index, parce que c'est surtout là que tu peux gagner?

Probablement la table a grossie et les index ne seront pas utilisés :)
Teradata étant un MPP les index, hors primaires, sont très très rarement utiles.

Bonjour,
Concernant la requête que j'ai proposée, tu peux remplacer le with Correspondance par une vrai table ou par une table temporaire.
L'autre solution mais qui devient lourde,c'est de remplacer l'utilisation de correspondance par sa définition comme ceci.
Mais déjà que la requête n'est pas très lisible, ça commence à être vraiment compliqué.

Code:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
INSERT INTO STAMP_OPS_EVT_TMP
Select 
	--ETAPE TMP7: Pour chaque station qui n est pas nouvelle (voir TMP5), on reporte le numéro précédent
	NUM_RTE
	--On reporte les numéro de station qui ne changent pas.
    ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) else NUM_STA 
    end as NUM_STA
	,COD_EVT
    ,IDT_CNT_RCU
    ,DAT_EVT_UTC
    ,DAT_LOC_EVT
    ,DAT_DEP
    ,LCT_SCA
	,ESC_ORI
    ,ESC_DST
    ,NOM_PRN_ORI
    ,NOM_PRN_DST
    ,NUM_VOL_DEP
    ,NUM_VOL_ARV
    ,IDT_VOL
 
 FROM 
	(
	--ETAPE TMP6: Pour chaque route, pour chaque nouvelle station, On affecte un numéro croissant à chaque nouvelle station (voir TMP5)
	Select 
	NUM_RTE
 
		--S il s agit d une nouvelle station (voir TMP5), on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de station
		,case 
		when NUM_STA=1 then coalesce(Count(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Odre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
		else NUM_STA 
		end as 
		 NUM_STA
		,COD_EVT
		,IDT_CNT_RCU
		,DAT_EVT_UTC
		,DAT_LOC_EVT
		,DAT_DEP
		,LCT_SCA
		,ESC_ORI
		,ESC_DST
		,NOM_PRN_ORI
		,NOM_PRN_DST
		,NUM_VOL_DEP
		,NUM_VOL_ARV
		,IDT_VOL
		,Ordre
	 FROM 
		(
		-- ETAPE TMP5 Pour chaque route, on identifie les nouvelles stations avec un indicateur '1'
		Select 
			NUM_RTE
			--indicateur de changement de station pour chaque route
			,case 
				when coalesce(min(LCT_SCA) over ( PARTITION BY IDT_CNT_RCU , NUM_RTE order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), 0) <> LCT_SCA  then  1
				else null
				end  as NUM_STA
			,COD_EVT
			,IDT_CNT_RCU
			,DAT_EVT_UTC
			,DAT_LOC_EVT
			,DAT_DEP
			,LCT_SCA
			,ESC_ORI
			,ESC_DST
			,NOM_PRN_ORI
			,NOM_PRN_DST
			,NUM_VOL_DEP
			,NUM_VOL_ARV
			,IDT_VOL
			,Ordre
		FROM 
			(
			--ETAPE TMP4: Pour les lignes qui ne sont pas un début de route, on reporte le numéro précédent
				Select 
				case when NUM_RTE is null then 
											-- Si la ligne n'a pas de prédécésseur alors on mets 1 sinon on mets la valeur du prédécesseur
											Case when max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) is null
											then 1
											Else max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING)
											end
										else NUM_RTE end as NUM_RTE
				,COD_EVT
				,IDT_CNT_RCU
				,DAT_EVT_UTC
				,DAT_LOC_EVT
				,DAT_DEP
				,LCT_SCA
				,ESC_ORI
				,ESC_DST
				,NOM_PRN_ORI
				,NOM_PRN_DST
				,NUM_VOL_DEP
				,NUM_VOL_ARV
				,IDT_VOL
				,Ordre
			FROM 
				(
				--ETAPE TMP3 : On affecte un numéro croissant à chaque début de route
				SELECT distinct
					 --s il s agit d un début de route, on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de début de route
					case 
					when RTE=1 then coalesce(Count(RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC asc , case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
					else RTE 
					end as NUM_RTE			   
					,TMP2.COD_EVT
					,TMP2.IDT_CNT_RCU
					,TMP2.DAT_EVT_UTC
					,TMP2.DAT_LOC_EVT
					,TMP2.DAT_DEP
					,TMP2.LCT_SCA
					,TMP2.ESC_ORI
					,TMP2.ESC_DST
					,TMP2.NOM_PRN_ORI
					,TMP2.NOM_PRN_DST
					,TMP2.NUM_VOL_DEP
					,TMP2.NUM_VOL_ARV
					,TMP2.IDT_VOL
					,Ordre
				FROM
				   ( --ETAPE TMP2: On identifie les début de route avec des indicateur '1' 
					SELECT
						--On affecte un indicateur  pour chaque début de route du Receptacle
						--s il s agit d un premier evènement (sauf iSC) sans début identifié
						case 
						when ROW_NUMBER() over ( PARTITION BY IDT_CNT_RCU 
												order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  ) = 1 
								AND COD_EVT <>'iSC' 
								AND COD_EVT <>'6'
						then 1
						--s il s agit bien d un début identifié
						when COD_EVT in ('74','43') 
						then 1
						-- s il n y a pas de début de route mais que la ligne précédente est une fin de route, alors on crée une nouvelle route quand même
						when coalesce(min(COD_EVT) over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), '#') in ('21', '42')  
						then 1
						else null end as RTE
					   ,TMP1.*
					   FROM
 
 
						   (
							-- ETAPE TMP1: On reccupère les information de base
							SELECT 
								'RECEPTACLE' as LVL
								,COD_EVT
								,IDT_CNT_RCU
								--,DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,cast (NULL as Timestamp(0)) as NON_CORR_DAT_EVT_CNT_UTC
								,DAT_EVT_UTC
								,DAT_LOC_EVT
								,DAT_DEP
								,LCT_SCA
								,ESC_ORI
								,ESC_DST
								,NOM_PRN_ORI
								,NOM_PRN_DST
								,NUM_VOL_DEP
								,NUM_VOL_ARV
								,IDT_VOL
								,COALESCE(C.Ordre, EVT2.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT2
							left outer join 
							(
								select '74' as Code_EVT, '1000' as Ordre
								union
								select '43' as Code_EVT, '1000' as Ordre
								union
								select '21' as Code_EVT, '0' as Ordre
								union 
								select '42' as Code_EVT, '0' as Ordre
							) C on
							EVT2.COD_EVT=C.COD_EVT
							--Receptacle level
							WHERE IDT_TYP = 'R'
 
							UNION 
 
							SELECT 
								 'CONTAINER' as LVL
								 ,EVT.COD_EVT
								,CNT.IDT_CNT_RCU
								--DR_REC0 Event at the COntainer level must have the data of the max(STAMP_CNT_RCU.DAT_DEB_VLD)
								--,EVT.DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,CNT.DAT_DEB_VLD as NON_CORR_DAT_EVT_CNT_UTC
								--,Las plus grande des deux dates :(CNT.DAT_DEB_VLD  , EVT.DAT_EVT_UTC     ) as DAT_EVT_UTC
								,CAST(
										SUBSTRING (CAST ( 
												 TO_TIMESTAMP(
															GREATEST(
															COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'),
															COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000')
															),'yyyymmddhh24miss')
										AS VARCHAR(25))  FROM 1 FOR 19)  
									AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS')
								AS DAT_EVT_UTC
								,EVT.DAT_LOC_EVT
								,EVT.DAT_DEP
								,EVT.LCT_SCA
								,EVT.ESC_ORI
								,EVT.ESC_DST
								,EVT.NOM_PRN_ORI
								,EVT.NOM_PRN_DST
								,EVT.NUM_VOL_DEP
								,EVT.NUM_VOL_ARV
								,EVT.IDT_VOL
								,COALESCE(C.Ordre, EVT.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT
 
							INNER JOIN DWHFRT.STAMP_CNT_RCU CNT
									ON EVT.IDT_COD_CNT = CNT.IDT_COD_CNT
							left outer join 
							(
								select '74' as Code_EVT, '1000' as Ordre
								union
								select '43' as Code_EVT, '1000' as Ordre
								union
								select '21' as Code_EVT, '0' as Ordre
								union 
								select '42' as Code_EVT, '0' as Ordre
							) C on
							EVT.COD_EVT=C.COD_EVT
							--Container level
							WHERE EVT.IDT_TYP = 'C'
							  AND CNT.IDT_TYP = 'R'		
							) as TMP1
							--where IDT_CNT_RCU='AEDXBASESTOABTT70050001000054'
					) as TMP2
				) as TMP3
 
			) as TMP4
		) as TMP5
	) as TMP6
 --order by 4,5
;

Pour Waldar merci pour le lien sur les fonctions de fenêtrage, j'apprends plein de chose.

PS : Je ne suis pas du tout sure que ça améliorera les perf!
Cordialement
Soazig

02/05/2018, 20h07
lotofski

1 pièce(s) jointe(s)

Bonsoir , Merci soazig vu que je peux travailler de chez moi ojr , j'ai essayé le dernier script , et j'ai le message suivant Pièce jointe 375583
02/05/2018, 21h00
lotofski

2 pièce(s) jointe(s)

Et voici le résultat attendu Pièce jointe 375599 Pièce jointe 375603

Bonjour tout le monde , voici la rps de mon sup ;

Le but est de passer de requêtes imbriquées de ce type:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 insert into TABLE_FINALE select .. from (select .. from ... (select from ... from TABLE_ORIGINE) )
A plusieurs requêtes séparées les unes après les autres:
Code:

1 2 3 4 5 insert into TABLE_INTERMEDIAIRE_1 select ... from TABLE_ORIGINE;
Code:

1 2 3 4 5 insert into TABLE_INTERMEDIAIRE_2 select ... from TABLE_INTERMEDIAIRE_1;
Code:

1 2 3 4 5 insert into TABLE_INTERMEDIAIRE_3 select ... from TABLE_INTERMEDIAIRE_2;
...
Code:

1 2 3 4 5 insert into TABLE_FINALE select ... from DERNIERE_TABLE_INTERMEDIAIRE;
puis vider les TABLE_INTERMEDIAIRE.

C’est tout.

Rien à modifier dans les conditions des requêtes.

Et à la fin il faut comparer pour vérifier que les résultats obtenus par ce script sont strictement identiques aux résultats obtenus avec le script d’origine.

03/05/2018, 11h20
al1_24

Une fois que tu auras écrit tes requêtes, si tu rencontres des problèmes de syntaxe ou d'exécution, tu pourras revenir ici pour exposer ce qui ne va pas...
03/05/2018, 12h08
SQLpro

Citation:

Envoyé par soazig

Rebonjour,
N'y-a-t-il que moi que ça choque l'utilisation de fonction de fenêtrage Min, max et count avec un order by dans le over?
Pour un ROW_NUMBER() je le conçois mais pour les min, max et count je ne vois pas à quoi il peut servir.
Par ailleurs, j'avoue mon ignorance quand aux indications

Code:

rows between 1 preceding and 1 preceding

A+
Soazig

Cela permet par exemple de faire des SUM cumulées.
Par exemple avoir le CA de la boite au jour le jour.
Ainsi le 1er janvier le CA sera les ventes du 1
Le 2 janvier, ce sera le cumul des 1 et 2 janvier et ainsi de suite...

Avec rows between ... preceding. vous pouvez, par exemple, faire une fenêtre glissante Exemple : CA "glissant" sur 7 jours (une semaine.

Lisez l'article que j'ai écrit à ce sujet :
https://sqlpro.developpez.com/articl...clause-window/
Cela vous ouvrira de nouveaux horizons

A +

Voir 40 message(s) de cette discussion en une page