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 ?

[tbc92]

Je pense qu'on peut s'en sortir, avec la fonction LAG (ou LEAD, elles sont interchangeables en remplaçant ASC par DESC et vice-versa)

Je n'utilise jamais ces fonctions, mais ceci devrait marcher.

Etape 1 :
On va chercher la ligne suivante et la ligne précédente en triant sur cette colonne POS_MDM

Code :

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

1
2
3
4
5
6
select REPORT_ID, SHEET_ID,POS_MDM, VALUE, 
  LEAD(POS_MDM,1, -1) ( order by pos_mdm asc ) as MDM_SUIVANT, 
  LEAD(POS_MDM,1 , -1) ( order by pos_mdm desc ) as MDM_PRECEDENT ,
  LEAD(VALUE,1, 0) ( order by pos_mdm asc ) as VALUE_SUIVANT, 
  LEAD(VALUE,1 , 0) ( order by pos_mdm desc ) as VALUE_PRECEDENT 
from measure

Comme valeur par défaut, je mets -1, je mets une valeur 'totalement décalée' par rapport aux valeurs qu'on va rencontrer dans cette colonne, pour faire en sorte que quand on traite la 1ère ou la dernière ligne, ça se passe bien.

Etape 2 :
On regarde laquelle de ces 2 lignes donne l'écart le plus faible.

Code :

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

1
2
3
4
5
6
7
8
9
10
select REPORT_ID, SHEET_ID,POS_MDM, VALUE,
  LEAD(POS_MDM,1, -1) ( order by pos_mdm asc ) as MDM_SUIVANT, 
  LEAD(POS_MDM,1, -1) ( order by pos_mdm desc ) as MDM_PRECEDENT ,
  LEAD(VALUE,1, 0) ( order by pos_mdm asc ) as VALUE_SUIVANT, 
  LEAD(VALUE,1 , 0) ( order by pos_mdm desc ) as VALUE_PRECEDENT,
  case when abs ( LEAD(POS_MDM,1,-1) ( order by pos_mdm asc )  - POS_MDM ) <  abs ( LEAD(POS_MDM,1, -1) ( order by pos_mdm desc ) - pos_MDM ) 
        then 'SUIVANT' 
        else 'PRECEDENT' 
        end as test
from measure

Etape 3 : ok, on sait s'il faut prendre la suivante ou la précédente, on le fait.

Code :

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

1
2
3
4
5
6
7
8
9
10
select REPORT_ID, SHEET_ID,POS_MDM, VALUE,
  LEAD(POS_MDM,1, -1) ( order by pos_mdm asc ) as MDM_SUIVANT, 
  LEAD(POS_MDM,1, -1) ( order by pos_mdm desc ) as MDM_PRECEDENT ,
  LEAD(VALUE,1, 0) ( order by pos_mdm asc ) as VALUE_SUIVANT, 
  LEAD(VALUE,1 , 0) ( order by pos_mdm desc ) as VALUE_PRECEDENT,
  VALUE + case when abs ( LEAD(POS_MDM,1, -1) ( order by pos_mdm asc )  - POS_MDM ) <  abs ( LEAD(POS_MDM,1, -1) ( order by pos_mdm desc ) - pos_MDM ) 
      then LEAD(VALUE ,1, 0) ( order by pos_mdm asc )
      else LEAD(VALUE ,1, 0) ( order by pos_mdm desc ) 
      end as test
from measure

Je laisse les 4 colonnes explicatives, mais tu n'en as pas besoin.

En terme de performance, ça devrait être beaucoup beaucoup mieux.

[jordan63000]

Ok je vois top comme raisonnement, toutefois je pense que cela ne peux fonctionner avec mon cas si je comprends bien ce que tu explique. En effet je prends l'exemple suivant :



Pour la ligne 5092 j'additionnerai les valeurs des lignes 5091 et 5092, ça c'est parfait.
Par contre pour la ligne 5093 j'additionnerai les valeurs des lignes 5092 et 5093, or la ligne 5092 est une mesure PunchPosition donc je ne devrais pas en tenir compte et prendre la valeur de la ligne 5091. Pour ce faire il faudrait que la fonction lead filtre sur les lignes ayant pour mesure Iridescence. Cela est peut être possible ?

Je ne sais pas si je suis clair :/

[tbc92]

Arghh
Du coup, je ne vois pas de solution sans jointure, mais ça ne veut pas dire qu'il n'y en a pas.

[jordan63000]

Ok

Merci en tout cas !

[vanagreg]

En fait je pense qu'on peut s'en sortir avec des fonctions analytiques, mais ici le plus simple c'est d'utiliser MATCH_RECOGNIZE, disponible puisque tu es en 19c.
En gros le pattern ici c'est une ou plusieurs mesures 'PunchPosition_DevPosCDmm'. Tu as alors besoin de connaitre la valeur précédente IridesPosition_DevPosCDmm et la valeur suivante IridesPosition_DevPosCDmm.

Par exemple, tes données, triées sur sheet_id et pos_mdm (partitionné sur report_id) :

IridesPosition_DevPosCDmm
IridesPosition_DevPosCDmm
IridesPosition_DevPosCDmm
PunchPosition_DevPosCDmm -- 1er pattern trouvé ici
PunchPosition_DevPosCDmm --(2 lignes)
IridesPosition_DevPosCDmm
PunchPosition_DevPosCDmm -- 2è pattern trouvé ici (1 ligne)
IridesPosition_DevPosCDmm
PunchPosition_DevPosCDmm --
PunchPosition_DevPosCDmm --3è pattern trouvé ici (3 lignes)
PunchPosition_DevPosCDmm --
IridesPosition_DevPosCDmm
...

Donc, voici une proposition de requête que je te laisse tester:

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, 
        sheet_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 sheet_id, 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' 
                ) 
)     
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 ;

Donc, tu vois que dans la clause PATTERN, j'ai juste précisé "PunchPosition+", parce qu'on cherche une ou plusieurs occurrences consécutives de la mesure PunchPosition. Pour trouver les valeur précédente et suivante:
prev(first(pos_mdm)) -- ligne précédente la 1ère ligne matchée, donc la valeur inférieure de IridesPosition_DevPosCDmm
next(final last(pos_mdm)) -- ligne suivante la dernière ligne matchée, donc la valeur supérieure de IridesPosition_DevPosCDmm

[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.

[tbc92]

Les index ont été ajoutés récemment ? Pas sûr.
Ta table est-elle bien administrée ? Ce que je veux dire, c'est : y a-t-il un process pour mettre à jour les statistiques régulièrement. Un index, c'est bien à un instant t, mais ça se dégrade dans le temps, au fur et à mesure des insertions de nouvelles données. Il y a des commandes comme analyze table xxxx compute statistics (c'est la commande de base, il y a mieux). Si les statistiques sont dégradées, les performances aussi sont dégradées.

[jordan63000]

Oui ils ont été ajoutés récemment, j'ai aussi recalculé les stats et depuis pas d'ajout de nouvelles lignes.

[Waldar]

Je pense avoir une solution un peu plus simple qu'avec le match_recognize, mais je ne suis pas certain d'avoir bien compris le problème dans son intégralité, je m'y perds un peu entre les différentes images.

Pouvez-vous fournir un petit jeu d'essai (10-20 lignes), significatif, idéalement sous la forme d'insert into ?