Discussion :

[mado_stev]

Bonjour,

Je commence avec Teradata et j'ai cet exercice ci-dessous, j'ai essayé de le résoudre (en utilisant des tables volatiles) mais je suis toujours à la première partie de la partie 1: (sans Bteq)

sélectionnez les lignes de table_a satisfaisant toutes la condition:

- c6 is null

On ne garde qu'une seule ligne par tuple suivant: c1, c2, c3, Année / mois / jour de la date (on prend la ligne avec max date et si nécessaire avec max id). Les lignes sont ensuite triées sur ce tuple par ordre croissant de date.

1- Pour chaque première ligne sélectionnée du triplet c1 / c2 / c3, s'il y a une ligne dans table_b qui satisfait toutes les conditions suivantes:

Code :

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

1
2
3
4
 a.c1 = b.c1
and a.c2 = b.c2
and a.c3 = b.c3
and b.dat_fin = '2999-12-31'

Donc si le champ c5 est identique On passe à la ligne suivante du triplet (si elle existe)

sinon (le champ c5 n'est pas identique) on ferme la ligne de table_b comme suit:

b.date_fin = a.DATe - 1 jour
et loading_date = current_timestamp (0)

Et nous insérons une ligne dans table_b (règles spécifiées après, cas 1)

Sinon (il n'y a pas de ligne dans table_b qui satisfait à la condition 2)

On insère la ligne dans table_b (règles spécifiées après, cas 1).

2-Pour les n lignes suivantes du triplet c1 / c2 / c3 éventuellement existant,

si la différence entre Ln-1.date et Ln.date est strictement supérieure à 1 mois alors le champ date = Ln-1.date + 1 mois (date) pour la ligne insérée dans table_b à partir de Ln-1 (insertion case2)

et une ligne est insérée à partir de Ln (insertion du cas 1). Ensuite, nous retournons à la ligne suivante du même triplet et nous jouons à nouveau le même algorithme. Si non

s'il n'y a pas de différence sur le champ c5, on va passer à la ligne suivante pour le même triplet et on fait le même algorithme

s'il y a une différence sur c5 alors le champ b.DAT_FIN = Ln.date - 1 jour (date) pour la ligne précédemment insérée dans le tableau b pour le triplet (insertion case3) et une ligne est insérée à partir de Ln (insertion case 1) .
Ensuite, nous retournons à la ligne suivante du même triplet pour effectuer le même algorithme.

NB: lorsqu'il y a 2 lignes sur le même triplet, n-1 = 1ère ligne et n = 2ème ligne


exemple:

nous avons dans le tableau a (sans triplet ouvert dans le tableau b)

Code X :

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

1
2
3
4
5
6
7
c1  c2  c3  c5  date                 c6
--  --  --  --  -------------------  ----
 1   1   1   4  31/01/2020 00:00:01  null
 1   1   1  15  31/01/2020 00:00:02  null
 1   1   1  15  29/02/2020 00:00:01  null
 1   1   1  15  15/04/2020 00:00:01  null
 1   1   1   4  15/05/2020 00:00:01  null

nous devrions obtenir dans le tableau b

Code X :

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

1
2
3
4
5
c1  c2  c3  c5  Date        Date_fin
--  --  --  --  ----------  ----------
 1   1   1  15  31/01/2020  31/03/2020
 1   1   1  15  15/04/2020  14/05/2020
 1   1   1   4  15/05/2020  31/12/9999

insertion cas:

Code :

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

1
2
3
4
5
6
7
8
b.c1 = a.c1
b.c2 = a.c2
b.c3 = a.c3
b.date = a.date
b.DAT_FIN = 31/12/2999 (case 1)
b.DAT_FIN = Ln-1.date (date) + 1 month (case 2)
b.DAT_FIN = Ln.date (date) -1 day (case 3)
loading date = current_timestamp(0)

Merci d'avance pour vos retours

[Waldar]

L'algorithme est :

1. vraiment très mal expliqué
2. ligne à ligne, c'est à dire non-performant

Je suis embêté, j'ai une solution ensembliste mais si je vous la donne le but de l'exercice ne sera pas atteint.
Je vais donc vous laisser chercher un peu.

Allez je vous aide et je vous montre comment j'ai construit la requête.
La partie la plus complexe est bien entendu celle que j'ai supprimée.

Données

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
create multiset volatile table mvt_TableA, no log
( c1        byteint
, c2        byteint
, c3        byteint
, c5        byteint
, ts        timestamp(0)
, c6        byteint
)
primary index (c1, c2, c3)
on commit preserve rows;
 
insert into mvt_TableA (c1, c2, c3, c5, ts) values (1, 1, 1,  4, timestamp '2020-01-31 00:00:01');
insert into mvt_TableA (c1, c2, c3, c5, ts) values (1, 1, 1, 15, timestamp '2020-01-31 00:00:02');
insert into mvt_TableA (c1, c2, c3, c5, ts) values (1, 1, 1, 15, timestamp '2020-02-29 00:00:01');
insert into mvt_TableA (c1, c2, c3, c5, ts) values (1, 1, 1, 15, timestamp '2020-04-15 00:00:01');
insert into mvt_TableA (c1, c2, c3, c5, ts) values (1, 1, 1,  4, timestamp '2020-05-15 00:00:01');

Requête

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
with cte_TableA (c1, c2, c3, c5, dt) as
(
  select c1, c2, c3, c5, ts (date)
    from mvt_TableA
   where c6 is null
 qualify row_number() over(partition by c1, c2, c3, ts (date) order by ts desc) = 1
)
  ,  cte_calc_fin (c1, c2, c3, c5, c5_l, pd) as
(
select ...
)
  select c1, c2, c3, c5
       , begin(pd) as dt
       , last(pd)  as dt_fin
    from cte_calc_fin
order by dt;
 
c1  c2  c3  c5  dt          dt_fin
--  --  --  --  ----------  ----------
 1   1   1  15  2020-01-31  2020-03-31
 1   1   1  15  2020-04-15  2020-05-14
 1   1   1   4  2020-05-15  9999-12-31

[mado_stev]

Merci Waldar pour votre réponse,

Oui j'avoue que le descriptif de l'exercice est compliqué.
Pour les perfs, ce sont des tables peu volumineuses environ 50k ( n’empêche pas d'optimiser la requête).

ceci est comment j'ai commencé y a qlq jours (sans pouvoir avancer):

Code :

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

1
2
3
4
5
6
7
select a.*, rank()over(partition by a.c1,a.c2,a.c3 order by a.dt) as rang
from
(select a.*
from  a 
where a.c6 is null
qualify row_number() over(partition by a.c1,a.c2,a.c3,cast(a.dt as date format'yyyymmdd') order by a.dt desc,a.id desc) = 1
)a;

Mais le plus compliqué est comment faire de comparaison entre les lignes ensuite faire de mise à jour et des inserts.

D'après ce que j'ai compris de votre requête vous envisagez de faire annule et remplace sur la table b à chaque exécution !

Cdt

[Waldar]

D'après ce que j'ai compris de votre requête vous envisagez de faire annule et remplace sur la table b à chaque exécution !

Aucune idée, puisque vous n'avez pas fourni d'état initial de la table B.

Pour les mises à jour et les inserts, MERGE c'est très bien.

[mado_stev]

La table b au début ( la 1er exécution) est vide, par la suite y a le script qui mis à jour et/ou insert des lignes.
Seulement les lignes ouvertes (

Code :

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

dat_fin = '2999-12-31'

) de la table b sont sélectionnées.

[mado_stev]

Waldar, pouvez vous me fournir plus de détail sur votre requêtes s'il vous plait ?
j'avais essayé plusieurs fois en me basant sur votre requête mais ça a l'air compliqué pour moi, j'ai jamais manipulé les CTE, je débute sur teradata .
Merci

[Waldar]

J'ai fait comme ceci :

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
with cte_TableA (c1, c2, c3, c5, dt) as
(
  select c1, c2, c3, c5, ts (date)
    from mvt_TableA
   where c6 is null
 qualify row_number() over(partition by c1, c2, c3, ts (date) order by ts desc) = 1
)
  ,  cte_calc_fin (c1, c2, c3, c5, c5_l, pd) as
(
select normalize
       c1, c2, c3
     , c5, lead(c5, 1, 0) over(partition by c1, c2, c3 order by dt)
     , period(dt, case
                    when c5 = c5_l
                     and months_between(lead(dt, 1) over(partition by c1, c2, c3 order by dt asc), dt) > 1
                    then add_months(dt + 1, 1)
                    else lead(dt, 1, date '3000-01-01') over(partition by c1, c2, c3 order by dt asc)
                  end)
  from cte_TableA
)
  select c1, c2, c3, c5
       , begin(pd) as dt
       , last(pd)  as dt_fin
    from cte_calc_fin
order by dt;

Mais je ne pense pas que cette requête soit le but de l'exercice.

[mado_stev]

Merci bcp Waldar, je me base sur votre requête pour essayer de résoudre l'exercice.
Peut être avec une merge ( pour mettre à jour et inserer dans la table b) à la fin ça sera bon.

[SQLpro]

Inspirez vous de la norme SQL qui définit les processus d'historisation des données en rajoutant :
1) dans la table source, les colonnes date_debut et date_fin avec les informations suivantes :

• date_debut = date de la dernière mise à jour (moment d'insertion ou d'UPDATE)
• date_fin = la fin des temps en SQL soit le 31/12/9999 à 23h59m59s9999999...

2) une table d'historique recevant :
la ligne avant modification avec une valeur de date_fin = au moment de l'UPDATE
la signe supprimée avec une valeur de date_fin = au moment du DELETE
3) une vue de synthèse présentant l'ensemble des informations de ces deux tables à l'aide d'un UNION ALL

Pour cela il vous faudra créer un déclencheur sur INSERT, UPDATE et DELETE.

A me lire : https://blog.developpez.com/sqlpro/p...r-presentation

A +