Discussion :

[jmclej]

Bonjour.
J'ai une table TableA qui est comme cela :

ViewDate ID prime otherfields
31/07/2023 1 10 titi
31/07/2023 1 10 titi
31/07/2023 1 10 toto
31/07/2023 2 10 tata
31/07/2023 2 10 tata
31/07/2023 2 10 tutu

Et une table TableB qui est comme cela :

ViewDate ID prime otherfields
31/08/2023 2 10 tata
31/08/2023 2 30 tata
31/08/2023 2 30 tutu
31/08/2023 3 30 tata
31/08/2023 3 30 tata
31/08/2023 3 30 tutu

Je veux calculer la variation ligne à ligne entre les 2 tables avec les règles suivantes :
Si un même ID est trouvé, alors la valeur du champs prime est égale à prime de TableB - prime de TableA
Si un ID n'est pas présent dans TableA, alors je veux quand même une ligne avec la valeur du champs prime égale à prime de TableB - 0
Si un ID n'est pas présent dans TableB, alors je veux quand même une ligne avec la valeur du champs prime égale à 0 - prime de TableA
Si un résultat de variation est égale à 0, alors je supprime la ligne en question
Je m'attends donc à avoir le résultat suivant (moins la ligne où prime = 0) :

ViewDate ID prime otherfields
31/08/2023 1 -10 titi
31/08/2023 1 -10 titi
31/08/2023 1 -10 toto
31/08/2023 2 0 tata
31/08/2023 2 20 tata
31/08/2023 2 20 tutu
31/08/2023 3 30 tata
31/08/2023 3 30 tata
31/08/2023 3 30 tutu

Ma requête actuelle, en spark SQL qui doit s'exécuter sur databricks, est la suivante :

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
create table TableA (ViewDate date, ID integer, prime integer, otherfields string);
create table TableB (ViewDate date, ID integer, prime integer, otherfields string);
create table TableVAR (ViewDate date, ID integer, prime integer, otherfields string);
 
insert into TableA select '2023-07-31', 1, 10, 'titi';
insert into TableA select '2023-07-31', 1, 10, 'titi';
insert into TableA select '2023-07-31', 1, 10, 'toto';
insert into TableA select '2023-07-31', 2, 10, 'tata';
insert into TableA select '2023-07-31', 2, 10, 'tata';
insert into TableA select '2023-07-31', 2, 10, 'tutu';
 
insert into TableB select '2023-08-31', 2, 10, 'tata';
insert into TableB select '2023-08-31', 2, 30, 'tata';
insert into TableB select '2023-08-31', 2, 30, 'tutu';
insert into TableB select '2023-08-31', 3, 30, 'tata';
insert into TableB select '2023-08-31', 3, 30, 'tata';
insert into TableB select '2023-08-31', 3, 30, 'tutu';
 
insert into TableVAR (ViewDate, ID, prime, otherfields)
select 
B.ViewDate,
COALESCE(A.ID, B.ID),
COALESCE(B.prime, 0) - COALESCE(A.prime, 0),
COALESCE(A.otherfields, B.otherfields)
from TableA A full outer join TableB B on A.ID = B.ID
where A.ViewDate ='2023-07-31' and B.ViewDate ='2023-08-31';
 
select * from TableVAR;
delete from TableVAR where prime = 0;
 
drop table TableA;
drop table TableB;
drop table TableVAR;

Le problème est que cela me retourne le résulat suivant :
ViewDate ID prime otherfields
31/08/2023 2 0 tata
31/08/2023 2 0 tata
31/08/2023 2 0 tutu
31/08/2023 2 20 tata
31/08/2023 2 20 tata
31/08/2023 2 20 tutu
31/08/2023 2 20 tata
31/08/2023 2 20 tata
31/08/2023 2 20 tutu
Quel est le problème dans ma requête et comment la corriger pour qu'elle retourne le résultat attendu s'il vous plaît ?
Je sais que je ne fais pas la jointure sur otherfields car ce ne sont pas des identifiants

[skuatamad]

1/ Les filtres sur les dates sont dans la clause WHERE ils suppriment donc les NULL, pour conserver l'intérêt de la jointure externe il faut les remonter dans la clause ON.
Il ne sera par contre pas possible d'avoir l'ID 1 associé au 31/08, il pourra être NULL ou au 31/07.

2/ Dans tableA et dans tableB il y a 3 fois l'ID 2, donc la jointure entre ces 2 tables sur uniquement l'ID génèrera 3x3=9 lignes avec les combinaisons des possibles des autres champs.

3/ Pour obtenir un résultat semblable à la description, il faut "numéroter" les lignes par id pour avoir des valeurs distinctes sur lesquels réaliser la jointure, quleque chose comme :

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
  with ta as (
select row_number() over (partition by id, ViewDate order by otherfields) as rn
     , t.*
  from tableA t
)
     , tb as (
select row_number() over (partition by id, ViewDate order by otherfields) as rn
     , t.*
  from tableB t
)     
select COALESCE( B.ViewDate, A.ViewDate)
     , COALESCE(b.ID, a.ID)
     , COALESCE(B.prime, 0) - COALESCE(A.prime, 0)
     , COALESCE(b.otherfields, a.otherfields)
  from ta A 
  full outer join tb B 
   on b.id = a.id
  and b.rn = a.rn
  and A.ViewDate ='2023-07-31' 
  and B.ViewDate ='2023-08-31'
order by coalesce(B.ViewDate, A.ViewDate), COALESCE(b.ID, a.ID);