Compter un nombre de modifications par jour [Résolu]

[Manu300886]

Salut à tous

je rencontre des difficultés pour exploiter les données d'une table à évolution lente de mon entrepôt de données.

la table se présente en gros de la façon suivante :
( ID , Date debut , Date fin , current_record , mon_champ )

Il y a autant de couples (ID + Date debut ) que de valeurs différentes du champ "Mon_champ"

ex:
ID--Date debut--Date fin--current_record--mon_champ
1--3/06/11--4/06/11--0--toto
1--4/06/11--5/06/11--1--tata
2--4/06/11--5/06/11--0--titi
2--5/06/11--a définir--1--tritri
1--5/06/11--a définir--1--tata

J'aimerais avoir ce résultat :

Date--Nbre de modif
3/06/11--0
4/06/11--1
5/06/11--2

Je ne vous propose pas mon début de travail car vous perdriez du temps à analyser ma quadruple imbrication de requête qui ne mène à rien.

Auriez vous un début de piste ? (PL/SQL, table temporaire, une requête magique...??)

Merci d'avance

[Waldar]

Si j'ai bien compris, la date retenue pour le nombre de modifications est la date de fin, et ce qui vous gène est de récupérer le 0 pour le 3 juin ?

Il vous faut une table calendrier dans votre modèle, il suffit ensuite d'une simple jointure externe pour faire le comptage.

[keskidi]

Bonjour,
je suis désolé, mais je ne comprend pas à quoi correspond le Nbre de modif que tu souhaites afficher.
Est-ce le nombre d'ID qui ont un enregistrement inférieur à la valeur actuelle ?
3/06/11-->0 = 0(1 - pas avant)
4/06/11-->1 = 1(1 au 3/06/11)+0(2 - pas avant)
5/06/11-->2 = 1(1 au 4/06/11)+1(2 au 4/06/11)
en comptabilisant que au 5/06/11 - on ne s'occupe pas de toutes les valeurs différentes, à savoir, on aura pas de valeur 2 pour l'ID 1 (4/06/11 + 3/06/11).

[aieeeuuuuu]

Bonjour,

Vous n'avez pas indiqué ce que vous vouliez (comment obtenez vous ce resultat ?)

mais cette requete devrait vous donner le résultat attendu à partir de l'exemple fourni

Code SQL :

1
2
3
4
5
6
7
 
SELECT
    DateDebut,
    SUM(current_record) AS NbModif
FROM 
    MaTable
GROUP BY DateDebut

[tfc3146]

Bonsoir,

Vous n'êtes pas très clair. Vous nous indiquez les données qui doivent être restituées, mais vous n'expliquez pas ce que vous voulez obtenir.

[Manu300886]

c'est vrai que je me suis mal exprimés, je recommence en m'appliquant et je vous montre pourquoi la requête de aieeeuuuu répond à mon besoin uniquement en apparence.

A la base il y une base de données source dans laquelle les utilisateurs peuvent faire de l'insertion et de la modification sur le champ "Mon_Champ".

On a mis en place un entrepôt de données alimenté par des flux ETL.
Comme on aimerai analyser les évolutions du champ "Mon_Champ" on a fait la table suivante :
(SRG_KEY , ID , Date debut , Date fin , current_record , mon_champ )

avec :

• SRG_KEY : une séquence oracle qui sert d'identifiant
• ID : l'identifiant de ma base source
• Date début : la date d'insertion dans mon entrepôt
• Date fin : date à laquelle on a enregistré une version plus récente de cette enregistrement.
• current_record : vaut 1 ou 0. 1 est la valeur la plus récente. Si Mon_Champ change, on passe 1 à 0 et on insère une nouvelle ligne dont le current_record vaudra 1.
• Mon_Champ : permet de voir toute les valeurs prise par le champ Mon_Champ dans ma base source.

exemple :
ds ma base source le 4/06/11 : (ID,Mon_Champ) = (1,toto)

Mon ETL entre en action et genere l'enregistrement suivant :
1 , 1 , 4/06/11 , vide , 1 , toto

Le lendemain (5/06/11), évolution en base source :
(ID,Mon_Champ) = (1,tata)

Mon ETL entre en action et fait 1 UPDATE + 1 INSERT :
1 , 1 , 4/06/11 , 5/06/11, 0, toto
2 , 1 , 5/06/11 , vide , 1 , tata

Ce que j'aimerai avoir, c'est le nombre de champ ayant été modifié au moins une fois le tout groupé par jour.

La requête de aieeeuuu :

Code :

1
2
3
4
5
6
SELECT
    DateDebut,
    SUM(current_record) AS NbModif
FROM 
    MaTable
GROUP BY DateDebut

me donne le nombre d'insertion par jour dans ma table (cad que j'ai mon résultat souhaité + les enregistrements n'ayant jamais été modifié)

Voila, j'ai essayé d'être le plus clair possible mais je vous ré explique à volonté, merci énormément pour votre aide

[Manu300886]

Ca y est je tiens ma requete :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
SELECT
    Date_Debut,
    SUM(current_record) AS NbModif
FROM 
    CHG_TABLE_PLANIFICATION
WHERE num_chg IN
(SELECT num_chg FROM
(
SELECT num_chg,count(*) FROM CHG_TABLE_PLANIFICATION GROUP BY num_chg HAVING count(*) > 1
))
GROUP BY Date_Debut
ORDER BY Date_Debut

Merci beaucoup

[Manu300886]

donc déjà merci aieeeuuuu pour ton code que j'ai juste du modifier un peu.

Le truc c'est que je vous ai menti sur la structure de ma table. En fait elle est plutot comme ça :
(SRG_KEY , ID , Date debut , Date fin , current_record , mon_champ1,mon_champ2,.... )

Si il y a une modification en base source sur un des champ de type "Mon_Champ" alors on insère une nouvelle ligne dans l'entrepôt.

La requête que j'ai posté précédemment compte en fait le nombre de modif qu'il y a eu sur les champ de type "Mon_Champ" par jour.

J'aimerai être plus précis et pouvoir calculer par jour le nombre de modif faite sur mon_champ1, celle sur mon_champ2 etc...

Une idée ?

[CinePhil]

Pour commencer, les champs, c'est à la campagne ou dans les formulaires ! Pas dans les tables de SGBD qui ne sont composées que de colonnes et de lignes !

Partons du cas simple :

1 , 1 , 4/06/11 , 5/06/11, 0, toto
2 , 1 , 5/06/11 , vide , 1 , tata

Mais :

Le truc c'est que je vous ai menti sur la structure de ma table. En fait elle est plutot comme ça :
(SRG_KEY , ID , Date debut , Date fin , current_record , mon_champ1,mon_champ2,.... )

Donc en fait tu peux avoir ceci :
1 , 1 , 4/06/11 , 5/06/11, 0, toto, titi
2 , 1 , 5/06/11 , NULL , 1 , toto, tata
=> col_1 n'a pas changé mais col_2 a changé.

Et pourquoi pas dans la même journée ceci :
2 , 1 , 5/06/11 , 5/6/11 , 0 , tutu, tata
3, 1, 5/6/11, NULL, 1, abc, tata
=> cette fois, c'est col_1 qui a changé et pas col2

C'est comme ça que ça fonctionne ?

Alors pas le temps de développer mais il faudra que tu comptes les modifications dans des requêtes séparées.

Il faut que tu fasses une autojointure pour trouver, pour une colonne (par exemple col_1), quelle est la ligne suivante qui a modifié cette colonne, c'est à dire SRG_KEY ligne suivante > SRG_KEY ligne considérée et valeur de col_1 ligne suivante <> valeur de col_1 ligne considérée.
Voilà la piste à explorer.

[Manu300886]

Moi à l'école on m'a dit : une bdd contient des enregistrements eux même composés de champs. et na !

Merci beaucoup en tout cas pour ta piste.
Dés que j'aurai du temps, je me pencherai dessus.

Pour l'instant j'ai contourné le probléme.

J'ai 3 tables qui fonctionnent comme précédemment avec chacune col_1, col_2, col_3
C'est pas très beau comme solution mais ça marche bien.

Je crois qu'en info décisionnel, les dimensions lentes sont trés répandus et il doit donc y avoir beaucoup de gens qui ont rencontré ce problème avant moi.

Je devrai peut être me tourner vers des forums qui concernent l info decisionnelle pour voir...

Je vous tient au courant et merci encore

Manu

[SQLpro]

Orientez vous vers le Change Data Capture... Il y a des outils pour cela, comme CDC ou Change Tracking pour SQL Server par exemple.

L'avantage est que c'est asynchrone bien que au fil de l'eau. Cela ne dérange donc nullement les transactions, car le CDC est alimenté par lecture du journal de transaction et non pas par des triggers horriblement couteux et pénalisant pour les transactions.

A +

[CinePhil]

Envoyé par Manu300886

Moi à l'école on m'a dit : une bdd contient des enregistrements eux même composés de champs. et na !

Quelle est la commande SQL pour ajouter ce que toi et ton école appellez "un champ" ?

Code :

ADD COLUMN

Et dans un trigger, qu'écrit-on ?

Code :

FOR EACH ROW

Et paf !
Tu diras à tes profs qu'ils ont besoin d'une révision en SQL !
Il peuvent venir sur Developpez.com s'ils n'ont pas les moyens de se payer un stage,
Notamment sur le site de SQLPro.

[SQLpro]

C'est la grande misère de l'éducation nationale.. Un prof y rentre à 25 ans, et y reste jusqu'à 60 tellement les places sont bonne. Regardez Luc ferry... Pourquoi s'emmerderait-il à faire ses 192 heures de cours annuelle alors qu'il est payé 7000 euros pas mois à rien foutre... Il est en disponibilité !!!
Aucune remise en question sur les cours...
On trouve encore nombre de cours en ligne sur le web de prof que je ne citerais pas et qui en sont encore aux jointures dans le WHERE !

A +

[Manu300886]

perso, je suis en cours d'obtention d'un bac + 2 en info décisionnelle.

Ma prof d'info, m'a appris qu'une BDD avait des enregistrements et des champs
(ce qui n'est peut etre pas correcte mais quelle importance ?)

Elle m'a apprit à faire des jointures avec la clause where et aujourd'hui je ne sais meme pas faire de jointure avec la clause JOIN (c'est bien ça?). Mais bon maintenant que j'ai des bases, si je me penche sur le problème ça devrait passer et au pire avec votre aide ça passera

Ce que l'on demande surtout à un prof, c'est d'être pédagogue avant d'être à la pointe.

Pour cette histoire de CDC, la situation est la suivante :

Ma base de données source est sur un SGBD SqlServer
Ma base de données cible est sur Oracle

J'utilise un logiciel d'intégration de données qui s'appelle Oracle Data Integrator. Après une brève recherche , je me suis rendu compte qu'il gère le CDC.

Il faut donc que j'aille voir mon DBA SqlServer pour savoir si le SGBD gère le CDC ?
Mais concretement, ça va me donner quoi comme rendu le CDC ? Ce que j'aime avec la dimension lente que j'ai c'est que je dispose de toute l'info. Par contre elle est trop difficile à exploiter, pour moi en tout cas.

[Waldar]

Une dimension lente, par définition, ça ne varie pas beaucoup (voire pas du tout). Donc au final, ce n'est pas vraiment un problème une fois l'initialisation passée.

Par contre, vous commettez une énorme erreur ici :

ce qui n'est peut etre pas correcte mais quelle importance ?

Qu'on vous ait mal appris, c'est une chose (courante).
Mais maintenant que vous savez que c'est faux, vous devez vous corriger.

[CinePhil]

C'est bien ce que dit SQLPro et que je dénonce aussi : grande misère de l'éducation nationale (mérite même pas de majuscule à ce nom ! )

Ta prof t'apprend des choses fausses et obsolètes ! Tu trouve ça normal ?

Nous sommes quelques-uns sur ces forums consacrés aux bases de données à se battre pour faire abandonner ces mauvais enseignements qui font prendre de mauvaises habitudes.

Quand je me souviens de la rigueur de langage que nous imposaient les profs de maths dès le collège (si et seulement si, il existe, il faut et il suffit...) - j'espère que c'est toujours le cas - et que je vois le laxisme de l'enseignement des bases de données alors que ça devrait être tout aussi rigoureux, je me dis que notre école est vraiment mauvaise !

Il est idiot de penser que l'apprentissage des jointures selon l'ancienne syntaxe est plus facile. C'est source d'erreurs et il faudra de toutes façons passer au JOIN avec les jointures externes alors autant le faire bien dès le début. C'est un peu comme si on enseignait la multiplication aux élèves de primaire avec une succession d'additions ! Ridicule !

[Manu300886]

ok, je comprend bien ce que vous me dites et de toutes façon moi je ne demande qu'à apprendre.

L'apprentissage des LEFT JOIN etc, il faut que je me penche la dessus trés bientôt.
Je vais aussi me corriger sur l'histoire des ligne/colonne champ/enregistrement.

Je pense que c'est fait dans un but pedagogique, la donnée du formulaire alimentant la base de données c'est plus parlant.

Je ne peux pas vous laisser dire que ma prof est mauvaise car elle fait au moins partie du top 10 des profs que j'ai eu toutes matière confondus. D'ailleurs à part ces 2 erreurs qui sont surement volontaires de sa part, elle a pas du en faire beaucoup d'autre.

Pour en revenir à mon problème, j'ai mis en place une dimension a évolution lente (c'est son nom dans ODI) mais elle porte mal son nom car si ma colonne change 10 fois dans la journée, je vais avoir 10 lignes de plus dans mon entrepôt. Car j'ai automatisé l'extraction à 1 fois par heure 24h/24h

C'est angoissant, j'ai réussi à capter une information précieuse et je n'arrive pas à l'exploiter

[CinePhil]

Envoyé par Manu300886

Je pense que c'est fait dans un but pedagogique,

En l'occurrence : mauvaise pédagogie !

la donnée du formulaire alimentant la base de données c'est plus parlant.

Et dire que le champ d'un formulaire sera enregistré dans une colonne d'une ligne d'une table, c'est si compliqué à comprendre ?

Je ne peux pas vous laisser dire que ma prof est mauvaise

Où ai-je dis qu'elle est mauvaise ?
J'ai seulement dit qu'elle enseignait des choses fausses et obsolètes depuis près de 20 ans. Mais peut-être que le reste de son enseignement est bon.

Pour en revenir à mon problème, j'ai mis en place une dimension a évolution lente (c'est son nom dans ODI) mais elle porte mal son nom car si ma colonne change 10 fois dans la journée, je vais avoir 10 lignes de plus dans mon entrepôt. Car j'ai automatisé l'extraction à 1 fois par heure 24h/24h

Je ne suis pas spécialiste en BI mais as-tu exploré la piste que je t'avais donnée dans une de mes précédentes réponses ?

Envoyé par CinéPhil

Donc en fait tu peux avoir ceci :
1 , 1 , 4/06/11 , 5/06/11, 0, toto, titi
2 , 1 , 5/06/11 , NULL , 1 , toto, tata
=> col_1 n'a pas changé mais col_2 a changé.

Et pourquoi pas dans la même journée ceci :
2 , 1 , 5/06/11 , 5/6/11 , 0 , tutu, tata
3, 1, 5/6/11, NULL, 1, abc, tata
=> cette fois, c'est col_1 qui a changé et pas col2

C'est comme ça que ça fonctionne ?

Alors pas le temps de développer mais il faudra que tu comptes les modifications dans des requêtes séparées.

Il faut que tu fasses une autojointure pour trouver, pour une colonne (par exemple col_1), quelle est la ligne suivante qui a modifié cette colonne, c'est à dire SRG_KEY ligne suivante > SRG_KEY ligne considérée et valeur de col_1 ligne suivante <> valeur de col_1 ligne considérée.
Voilà la piste à explorer.

[Manu300886]

Non je ne l'ai pas encore approfondi.

Helas, je m'en vais à l'école pour 3 semaines, je vais demander à ma prof les raisons pour lesquels elles commet ces erreurs. Je pense que c'est volontaires de sa part mais maintenant je veux savoir.

Dans trois semaines, je reprendrai mon travail sur le suivi de ces fameux champs et je vous tient au courant de toute facon. J'ai plusieurs fois entendu parlé de Change Data Capture sans trop comprendre. J'irai explorer cette piste la aussi.


Merci en tout cas pour votre aide, j'ai appris plein de truc ce matin

By

[CinePhil]

Je développe ma piste...

Soit la structure de table et les lignes suivantes :
SRG_KEY Croissant ID date_debut date_fin current_record colonne_1 colonne_2
1 1 2011-06-04 2011-06-05 0 toto titi
2 2 2011-06-04 NULL 1 abc zyx
3 1 2011-06-05 2011-06-05 0 tutu titi
4 1 2011-06-05 2011-06-06 0 tutu tata
5 1 2011-06-06 NULL 1 tutu tonton

=> Seul l'ID 1 a changé et entre les lignes 1 et 3 c'est colonne 1 qui a changé, entre les lignes 3 et 4 et entre les lignes 4 et 5, c'est colonne 2 qui a changé. Colonne_1 a changé 1 fois et Colonne_2 a changé 2 fois.

1) Lignes successives pour l'ID 1 (basé sur SRG_KEY auto-incrémenté)
Pour un ID donné, la ligne suivante d'une ligne est celle qui a le plus petit SRG_KEY

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT p1.SRG_KEY AS SRGKEY, 
	p2.SRG_KEY AS SRGKEY_suivant
FROM CHG_TABLE_PLANIFICATION p1
INNER JOIN CHG_TABLE_PLANIFICATION p2 
	ON p2.ID = p1.ID
	AND p2.SRG_KEY = 
	(
		SELECT MIN(SRG_KEY)
		FROM CHG_TABLE_PLANIFICATION p3
		WHERE p3.SRG_KEY > p1.SRG_KEY
			AND p3.ID = p1.ID
	)
WHERE p1.ID = 1

=> Résultat :
SRGKEY SRGKEY_suivant
1 3
3 4
4 5

2) Quelle(s) colonne(s) a (ont) changé entre deux lignes successives du même ID ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT p1.SRG_KEY AS SRGKEY, 
	p2.SRG_KEY AS SRGKEY_suivant,
	CASE 
		WHEN p2.colonne_1 <> p1.colonne_1 THEN 1
		ELSE 0
	END AS changement_de_col_1,
	CASE 
		WHEN p2.colonne_2 <> p1.colonne_2 THEN 1
		ELSE 0
	END AS changement_de_col_2	
FROM CHG_TABLE_PLANIFICATION p1
INNER JOIN CHG_TABLE_PLANIFICATION p2 
	ON p2.ID = p1.ID
	AND p2.SRG_KEY = 
	(
		SELECT MIN(SRG_KEY)
		FROM CHG_TABLE_PLANIFICATION p3
		WHERE p3.SRG_KEY > p1.SRG_KEY
			AND p3.ID = p1.ID
	)
WHERE p1.ID = 1

=> Résultat :
SRGKEY SRGKEY_suivant changement_de_col_1 changement_de_col_2
1 3 1 0
3 4 0 1
4 5 0 1

3) Comptage des changements par date de changement (date de fin)
Il suffit d'additionner les 1 de la requête précédente.

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
SELECT p1.date_fin,
	SUM(
		CASE 
			WHEN p2.colonne_1 <> p1.colonne_1 THEN 1
			ELSE 0
		END
	) AS nb_changement_de_col_1,
	SUM(
		CASE 
			WHEN p2.colonne_2 <> p1.colonne_2 THEN 1
			ELSE 0
		END
	) AS nb_changement_de_col_2	
FROM CHG_TABLE_PLANIFICATION p1
INNER JOIN CHG_TABLE_PLANIFICATION p2 
	ON p2.ID = p1.ID
	AND p2.SRG_KEY = 
	(
		SELECT MIN(SRG_KEY)
		FROM CHG_TABLE_PLANIFICATION p3
		WHERE p3.SRG_KEY > p1.SRG_KEY
			AND p3.ID = p1.ID
	)
WHERE p1.ID = 1
GROUP BY p1.date_fin

=> Résultat :
date_fin nb_changement_de_col_1 nb_changement_de_col_2
2011-06-05 1 1
2011-06-06 0 1