Discussion :

[jordan63000]

Bonjour,

Environnement : oracle 19c

Problématique : Via une requête sql, le but est d'additionner deux valeurs d'une même table sur deux lignes (A et B) différentes, sachant que pour trouver la ligne B à additionner il faut se baser sur une colonne et prendre la valeur la plus proche de la ligne A.

En image ça donne ça :



Si je décris l'exemple pour la feuille 5093 (en rouge) : Le but est d'additionner la valeur de la mesure PunchPosition_DevPosCDmm (tableau bleu) soit 0.543 à l'une des valeurs de cette même feuille pour la mesure IridesPosition_DevPosCDmm (tableau rose). Pour trouver la ligne il faut prendre la ligne IridesPosition_DevPosCDmm qui à le champ POS_MDM le plus proche de celui de la ligne PunchPosition_DevPosCDmm.

Auriez vous une idée ?

Merci d'avance pour vos retours

[vanagreg]

Bonjour,

Tu peux utiliser la fonction row_number pour trouver la ligne la plus proche:

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
with diff as (   
select p.num_feuille,
       p.pos_mdm, 
       p.value, 
       i.pos_mdm pos_mdm_i, 
       i.value value_i, 
       p.value + i.value somme,
       row_number() over (partition by p.num_feuille order by abs(p.pos_mdm - i.pos_mdm)) rn
from PunchPosition p
join IridesPosition i 
on (p.num_feuille = i.num_feuille)
)
select num_feuille,
       pos_mdm,
       pos_mdm_i,
       somme
from diff
where rn = 1;

[tbc92]

Tu as la fonction min ( ...) keep ( dense_rank first order by ... ) ou max ( ...) keep ( dense_rank first order by ... )

table1
col01 : les valeurs 5092, 5093 etc
col02 : le montant qui nous intéresse

Code :

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

1
2
3
4
5
6
7
select a.col01, 
   a.col02, 
   max ( b.col02 ) keep ( dense_rank first order by abs ( a.col01-b.col01)  )   as col02b , 
   a.col02 + max ( b.col02 ) keep ( dense_rank first order by abs ( a.col01-b.col01)  )  as somme
from table1 a , table1 b
where b.col01 <> a.col01
and a.col01=5093

cette requête va aller chercher la ligne la plus proche de 5093, récupérer le montant correspondant, et l'additionner au montant de départ.
Si il y a plusieurs lignes qui ont comme code 5092 ou 5094, ici, je prends la ligne avec le plus gros montant.

En gros, KEEP ... = Parmi les lignes qui sont premières ex-aequo selon un certain critère, prendre le plus gros montant (MAX) ou le plus petit (MIN)

[jordan63000]

Bonjour,

Merci pour vos réponses je regarde ca demain et je vous tiens au courant si je m'en suis sorti avec les pistes que vous avez émises.

Bonne soirée

[jordan63000]

Re bonjour,

Bilan :

La solution n°1 de vanagreg fonctionne nickel j'ai adapté à ma situation et fait une jointure avec un BETWEEN sinon le produit cartésien était monstrueux. Ca donne ça :

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
26
27
28
29
30
31
32
33
34
35
with diff as (   
select 
	p.REPORT_ID,	
	p.SHEET_ID,
	p.POS_MDM AS POSITION_PUNCH, 
	p.VALUE AS VALEUR_PUNCH, 
	i.POS_MDM AS POSITION_IRIDESCECNCE, 
	i.VALUE VALEUR_IRIDESCENCE, 
	p.VALUE + i.VALUE AS VALEUR_COURBE,
	row_number() over (partition by p.REPORT_ID, p.SHEET_ID order by abs(p.POS_MDM - i.POS_MDM)) rn
FROM (
		SELECT 	se.SHEET_ID, m.*	
		FROM MEASURE m 	
			LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
		WHERE m.REPORT_ID = 481	
			AND MEASURE_CODE in('PunchPosition_DevPosCDmm')	
			AND TRACK = 3
		) p
LEFT JOIN (
			SELECT se.SHEET_ID, m.*			
			FROM MEASURE m 		
				LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
			WHERE m.REPORT_ID = 481			
				AND MEASURE_CODE = 'IridesPosition_DevPosCDmm'					
				AND TRACK = 3
			) i on (p.report_id = i.report_id AND i.POS_MDM BETWEEN p.POS_MDM-10 AND p.POS_MDM+10)
)
select 
	REPORT_ID,
	SHEET_ID,
    POSITION_PUNCH,
    POSITION_IRIDESCECNCE,
    VALEUR_COURBE
from diff
where rn = 1;

La solution n°2 tbc92, fonctionne également. Je ne pense pas avoir complétement saisie la fonction min (valeur) keep (dense_rank first ...) mais les résultats sont ok. Voici le code

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
26
27
select 
	p.REPORT_ID, 
	p.SHEET_ID,  
	p.POS_MDM AS POSITION_PUNCH,
	min (i.POS_MDM) keep ( dense_rank first order by abs (p.POS_MDM-i.POS_MDM)) as POSITION_IRIDESCECNCE, 	
	p.VALUE + min (i.VALUE) keep ( dense_rank first order by abs (p.POS_MDM-i.POS_MDM)) as VALEUR_COURBE 
FROM (
		SELECT 	se.SHEET_ID, m.*	
		FROM MEASURE m 	
			LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
		WHERE m.REPORT_ID = 481	
			AND MEASURE_CODE in('PunchPosition_DevPosCDmm')	
			AND TRACK = 3
		) p
LEFT JOIN (
			SELECT se.SHEET_ID, m.*			
			FROM MEASURE m 		
				LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
			WHERE m.REPORT_ID = 481			
				AND MEASURE_CODE = 'IridesPosition_DevPosCDmm'					
				AND TRACK = 3
			) i on (p.report_id = i.report_id AND i.POS_MDM BETWEEN p.POS_MDM-10 AND p.POS_MDM+10)		
GROUP BY 
	p.REPORT_ID, 
	p.SHEET_ID,  
	p.POS_MDM, 
	p.VALUE

Il est intéressant de noter que la seconde solution est plus rapide. Temps d'exécution est de 38sec pour la solution n°1 contre 18sec pour la solution n°2.

Merci pour la rapidité de vos retours
Belle journée

[vanagreg]

Merci du retour. Je viens de capter que les lignes étaient dans la même table, j'avais mal lu au départ. Je pense que tu n'as donc pas besoin de faire un LEFT JOIN sur cette même table, tu devrais pouvoir t'en sortir avec des fonctions analytiques.

[jordan63000]

Oui c'est parce que j'ai mis deux tableaux ça induit en erreur !

Ah ok, toutefois je maitrise assez mal ces fonctions et malgré ce que je lis je ne comprends pas bien comme serait il possible d'enlever le left join comme les données sont dans une même tables et dans les mêmes champs, tu aurais une idée de ce que serait la requête sans left ?

[jordan63000]

Bonjour
Désolé pour le délais je ne pourrais tester cela que lundi !
Je vous tiens au courant
Bon week-end

[jordan63000]

Bonjour,

J'espère que le long weekend fut agréable !

Alors j'ai testé, j'avoue que j'ai du mal à visualiser comment cette requête "gère les données". J'ai légèrement revue la requête car le Sheet_ID et pas dans la table mesure mais la table Sheet_Event :

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
26
27
28
29
30
31
32
33
34
35
with match_recognized_data as 
(select report_id,  
        measure_code, 
        pos_mdm, 
        current_value,  
        prev_mdm,  
        next_mdm,  
        prev_value,  
        next_value,  
        --class, 
        --mn, 
        diff_prev, 
        diff_next 
 from measure m  
 match_recognize( 
                 partition by report_id 
                 order by pos_mdm 
                 measures value as current_value,            
                          prev(first(pos_mdm)) prev_mdm, 
                          next(final last(pos_mdm)) next_mdm, 
                          nvl(prev(first(value)), 0) prev_value, 
                          nvl(next(final last(value)), 0) next_value, 
                          pos_mdm - prev(first(pos_mdm)) diff_prev,
                          next(final last(pos_mdm)) - pos_mdm diff_next,
                          classifier() AS class, 
                          match_number() AS mn 
                 all rows per match 
  				 pattern(PunchPosition+) 
                 define PunchPosition as measure_code = 'PunchPosition_DevPosCDmm' 
                ) 
WHERE report_id = 481
)     
select d.*,  
       current_value + case when nvl(diff_prev, diff_next) < nvl(diff_next, diff_prev) then prev_value else next_value end somme 
from match_recognized_data d ;

Toutefois cette requête n'aboutit pas après plus de trois minutes d'attente, je pense donc rester sur les anciennes solutions

[vanagreg]

Etonnant, on ne fait pourtant qu'une seule lecture. Il y a combien lignes en tout dans la table? Combien de lignes à ramener dans la requête? Y-a-t-il des index sur les colonnes dans la clause where?

[jordan63000]

Alors c'est une table avec pour le moment 400 millions de lignes mais il y en aura beaucoup plus à l'avenir... j'ai d'ailleurs peur des performances lorsqu'il y en aura plus :/

La requête ramène et ramènera toujours environ 30k lignes peut importe le nombre de lignes dans la table car ce sont les données d'une bobine et il y a maximum 30k feuilles par bobine.

En terme d'index, j'ai en effet ajouté pour améliorer les performances de requêtage dont celui sur REPORT_ID qui est dans la clause WHERE. J'ai constaté des différence des performance en changeant seulement la VALEUR de la clause WHERE, je dis bien la valeur. Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM LINEA.MEASURE m 
LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
WHERE MEASURE_CODE = 'PunchPosition_DevPosMDmm'
 
SELECT *
FROM LINEA.MEASURE m 
LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
WHERE MEASURE_CODE = 'WatermarkPosition_DevPosMDmm'

Requête 1 : 10sec
Requête 2 : 1m 48sec

Je ne vois pour seule raison le fait que il y a beaucoup plus de ligne sur WatermarkPosition_DevPosMDmm (4 millions) que sur PunchPosition_DevPosMDmm (260k). Toutefois je trouve ca étrange que le nombre de lignes impacte autant les perf sachant qu'il y a des index justement.

NB : Pour info j'ai laissé tourner la requête et elle a mis 5 minutes pour sortir les données.

[vanagreg]

ok, tant mieux si l'ajout d'index a pu améliorer les perfs. Sur le match_recognize je vais pas insister, c'est juste surprenant qu'elle prenne autant de temps pour à peine 30k lignes à ramener.