Mise en oeuvre de fonctions analytiques : calcul d'indicateur sur les trois derniers mois

**cisse3** · 02/08/2014, 12h05

Bonjour chers internautes,
Devant sortir les indicateurs web(tels que le nombre d'entreprise connectées les trois derniers) pour un projet sur lequel je travail, je rencontre quelques difficultés avec la fonction analytique que je tente de mettre en œuvre.
Brièvement je rappelle la structure de la table sur laquelle je travaille :Appelons-la table_entreprise contenant les champs suivants : ID_ENTREPRISE,TYPE_CONVENTION,DATE_DERNIÈRE_CONNEXION où ID_ENTREPRISE désigne la clé primaire de la table.
Pour déterminer le nombre d'entreprise s'étant connectées les trois derniers mois par type de convention j'ai utilisé la requête suivante:
Code :
Sélectionner tout - Visualiser dans une fenêtre à part

Code :

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

SELECT   covention_type, date_derniere_connexion, sum(nb_connecte) over partition BY convention_type, date_derniere_connexion order  by date_derniere_connexion rows 2 preceding)

Cette requête ne me donne pas les résultats escomptés(voir capture jointe pour le mois de janvier considéré comme étant le mois à partir duquel on cherche à déterminer l'indicateur).
En effet je constate que le cumul n'est effectué correctement que pour les mêmes conventions pour janvier(résultat KO) alors qu'il devrait l’être pour tous(résultat OK).
Auriez-vous des idées/pistes/suggestions

Merci d'avance

**Flodelarab** · 02/08/2014, 13h23

Bonjour,

tu voudrais que la date de dernière connexion de c soit 201401 alors que c'est 201312 ? Étrange.

Voudras tu le MAX(date_derniere_connexion) ?

**cisse3** · 02/08/2014, 19h49

Bonjour Flodelarab,
Merci pour ta réponse.
Ce n'est pas étrange d'avoir pour c 201401, justement toute la complexité se trouve à ce niveau.
En effet je voudrais pour 201401 la somme totale par type de convention des entreprises qui se connectées en 201311,201312 et 201401
Si 2012 apparait pour c cela signifie qu'il n'a pas été compté pour les entreprises s'étant connectées les trois derniers mois lorsqu’on était en 201401.
201401 paraît être le max des trois dates mais ce n'est pas le max que je voudrais

**Flodelarab** · 02/08/2014, 22h09

Est-ce que la requête suivante t'aide?

Code :

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

SELECT   covention_type, date_derniere_connexion, sum(COALESCE(nb_connecte,0)) over partition BY convention_type, date_derniere_connexion ORDER  BY date_derniere_connexion rows 2 preceding)

201401 paraît être le max des trois dates mais ce n'est pas le max que je voudrais

Alors d'où vient le 201401 en face du c puisque ce n'est pas le max que tu veux et que personne ne s'est connecté depuis 201312 ?
Aurais-tu interverti OK et KO?

**cisse3** · 03/08/2014, 01h56

Non la requête suivante m'aide pas :

Code :

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

 SELECT   covention_type, date_derniere_connexion, sum(COALESCE(nb_connecte,0)) over partition BY convention_type, date_derniere_connexion ORDER  BY date_derniere_connexion rows 2 preceding)

Alors d'où vient le 201401 en face du c puisque ce n'est pas le max que tu veux et que personne ne s'est connecté depuis 201312 ?
Aurais-tu interverti OK et KO?

201401 en face de c vient du fait qu'on cherche à déterminer la somme des entreprises connectées les trois derniers mois et que 201312 avec convention c satisfait bien la condition.
Tu remarqueras que la somme des entreprises s'étant connecté les 3 derniers mois(à partir de janvier) est 40 mais c'est lors de la répartition par type de convention que j'ai des souci.
En effet je dois faire la somme par type de convention.Si une convention me manque en janvier(cas de la convention c par exemple), je dois pouvoir mettre quand même "2 2014 c" ;seule mon mois de référence(ici janvier) doit apparaître
"KO" et "OK" n'ont pas été inversés
N'hésite pas si t'as des questions, je pourrais fournir un exemple plus complet

**Flodelarab** · 03/08/2014, 08h17

Si tu ne veux pas de la date de dernière connexion, il ne faut pas demander la date de dernière connexion (ni son max).
Si tu veux la date référence de ton étude, il faut demander la date référence de ton étude...
On frôle Lapalisse.

Si tu as perdu le format de date, c'est dommage car tu aurais pu demander:

Code :

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

SELECT covention_type, date_reference_etude, sum(COALESCE(nb_connecte,0)) ... WHERE date_derniere_connexion BETWEEN (date_reference_etude - INTERVAL 3 MONTH) and date_reference_etude ...

**cisse3** · 03/08/2014, 22h52

Bonjour,
J'ai l'impression qu'on tourne en rond par ma faute, le besoin n'étant pas suffisamment bien exprimé.
Si tu me le permets, je vais reprendre le besoin initial et ne pas donner seulement les bouts d'informations.
En fait, je dois concevoir un tableau croisé sur l'année N, N-1(tous les mois devant figurer) contenant certains indicateurs(agrégés au

mois) dont le nbre d'entreprises connectées les trois derniers mois.
Mes tables sources : F_ENTREPRISE, D_CALENDIRER dont le script de création est :

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
 : F_ENTREPRISE
CREATE TABLE F_ENTREPRISE
(
    NUMERO_ENTREPRISE VARCHAR2(80)  PRIMARY KEY NOT NULL,
    CONVENTION VARCHAR(20),
   DATE_DERNIERE_CONNEXION DATE   ----- au format DD/MM/YYYY
 
)
 
CREATE TABLE D_CALENDRIER
(
    ID_JOUR  DATE  PRIMARY KEY NOT NULL,  ---- au  format DD/MM/YYYY
    ID_MOIS NUMBER(10),                               ----  au format AAAAMM
  ID_ANNEE NUMBER(4)                                  ----- au format AAAA
 
)

Table Cible : F_INDICATEUR_WEB

Code :

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

1
2
3
4
5
6
7
8
9
 
 
CREATE TABLE F_INDICATEUR_WEB
(
     ID_MOIS_ANALYSE NUMBER(10) PRIMARY KEY NOT NULL,
    ID_MOIS_PHOTO NUMBER(10 )  PRIMARY KEY NOT NULL, ----  au format AAAAMM
    SOURCE              VARCHAR2(10)  PRIMARY KEY NOT NUL ----- au format AAAA
    CONVENTION VARCHAR(20),
    NB_CONNECTE_3_M NUMBER(10 )                                  ----- Nombre d'entreprises connectées les trois derniers mois

Rappelons que D_CALENDIRER contient tous les jours et mois sur 5 ans et que dans F_ENTREPRISE on n'a pas forcément tous les mois(en

effet il peut exister des mois où aucune entreprise se connecte,ces mois doivent bien sûr figurer dans le rapport avec une valeur à 0

pour l'indicateur
Jeux de 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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
 INSERT INTO F_ENTREPRISE VALUES("1", "A", 03/11/2013)
 
 INSERT INTO F_ENTREPRISE VALUES("2", "A", 15/11/2013)
 
INSERT INTO F_ENTREPRISE VALUES("3", "B", 17/11/2013)
 
 INSERT INTO F_ENTREPRISE VALUES("4", "C", 15/11/2013)
 
 INSERT INTO F_ENTREPRISE VALUES("5", "A", 04/12/2013)
 
 INSERT INTO F_ENTREPRISE VALUES("6", "B", 03/12/2013)
 
 INSERT INTO F_ENTREPRISE VALUES("7", "A", 14/01/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("8", "A", 14/02/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("9", "B", 27/02/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("10", "A", 13/03/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("11", "C", 02/03/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("12", "B", 02/04/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("13", "A", 03/04/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("15", "C", 02/06/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("16", "A", 06/06/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("16", "B", 02/07/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("16", "A", 09/07/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("16", "C", 10/07/2014)
 
 INSERT INTO F_ENTREPRISE VALUES("17", "C", 03/08/2014)

Pour alimenter ma table cible j'ai procédé en 2 étapes :
D'abord alimenter une table temporaire qui contiendrait le nombre d'entreprise connecté par mois et type de convention :
ici je mets ma table F_ENTREPRISE en jointure externe sur D_CALENDRIER qui contient tous les mois, comme cela je suis sûr de ramener un

mois même si il n'y eu aucune connexion

Code :

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

1
2
3
4
5
6
7
8
 
 
CREATE TABLE TABLE_NB_CONNECTE AS
 
SELECT COUNT(F.NUMERO_ENTREPRISE) AS NB_CONNECTE, D.ID_MOIS , F.CONVENTION
 FROM F_ENTREPRISE F, D_CALENDRIER D
WHERE F.DATE_DERNIERE_CONNEXION (+)  D.ID_JOUR
GROUP BY D.ID_MOIS, F.CONVENTION

Ensuite j'ai utilisé cette table temporaire pour alimenter ma table cible comme suit :

Code :

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

1
2
3
4
 
 
INSERT INTO F_INDICATEUR_WEB( ID_MOIS_ANALYSE,ID_MOIS_PHOTO, CONVENTION,SOURCE , NB_CONNECTE_3_M)
SELECT  ID_MOIS, SYSDATE - 1,  CONVENTION,"ENTREPRISE",SUM(NB_CONNECTE)OVER PARTITION BY  CONVENTION, ID_MOIS ORDER BY ID_MOIS ROWS 2 PRECEDING) FROM   TABLE_NB_CONNECTE

Comme dans le 1er message, je n'ai pas les résultats escomptés(en effet la fonction analytique utilisée marche dans le cas des conventions identiques. exemple
lors que j'ai A 3 201311
B 4 201311
A 2 201312
B 1 201312
A 5 201401
B 1 201401

j'obtiens bien le résultat suivant :
A 10 201401
B 6 201401

Par contre dès que je n'ai pas les mêmes conventions pour les différents mois par exmeple

A 3 201311
B 4 201311
A 2 201312
B 1 201312
A 5 201401
La fonction me renvoie le résulta suivant :
A 10 201401
B 4 201301
B 1 201301
Alors que celui attendu devrait être
A 10 201401
B 4 201401
B 1 201401

Voir le fichier des résultats attendus pour le jeux de données

resultat_attendu.xlsx

Merci d'avance

**aieeeuuuuu** · 04/08/2014, 12h35

Bonjour,

Il est en effet très utile de fournir un jeu d'essai dans ce type de demande. Malheureusement, le votre n'est pas jouable, ni pour la création des tables (vous mettez plusieurs clefs primaires par table !!!) ni au niveau de l'insertion des données (clefs primaires dupliquées)

De plus, je ne vois par comment vous arrivez au résultat voulu à partir du jeu d'essai fourni ?!? Par exemple, pourquoi la convention "C" a-t-elle 4 pour le mois de novembre 2013, alors qu'elle n'a eu qu'une seule connexion ??

Toutefois, de ce que j'ai cru comprendre de vos explications, je ferai une requete comme celle-ci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
 SELECT		c.ID_MOIS
			, SYSDATE
			, conv.CONVENTION
			, SUM(COUNT(NUMERO_ENTREPRISE)) OVER(PARTITION BY conv.CONVENTION ORDER BY C.ID_MOIS ROWS 2 PRECEDING ) AS Nb
 FROM		d_calendrier c
 CROSS JOIN (SELECT DISTINCT CONVENTION FROM f_entreprise)  conv
 LEFT JOIN 	f_entreprise e
	ON			e.DATE_DERNIERE_CONNEXION = c.ID_JOUR
	AND			e.convention = conv.convention
 GROUP BY    c.ID_MOIS, conv.CONVENTION
  ORDER BY c.ID_MOIS, conv.CONVENTION

Notez que si vous avez une table des conventions, remplacez la sous requete

**cisse3** · 04/08/2014, 21h28

Bonjour Flodelarab,
Merci pour ta réponse.
Il est vrai que la clé semble dupliquée mais c'est une erreur de ma part, j'ai voulu simplifier la clé en utilisant les nombres entiers sinon.j'en suis désolé.
Pour 201311 , il faut lire " il y a 4 entreprises de convention C qui se connectées en 201311.Comme il n'existe aucun mois précédent 201311, c'est raison pour laquelle le nombre 4 est attendu.
Que te dire sinon qu'avec l'aide d'un ami j'ai trouvé la solution à mon problème en procédant en 2 étape pour des raisons de lisibilité comme suit:

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
 
---- table temporaire
 CREATE TABLE NB_CONNEXION  AS
SELECT F.TYPE_CONVENTION,D.ID_AAAAMM,COUNT(NUMERO_ENTREPRISE)  AS NB_CONNEXION
FROM F_ENTREPRISE F, D_CALENDRIER D
WHERE F.DATE_DERNIERE_CONNEXION(+) =  D.ID_JOUR
 
------calcul de l'indicateur sur les trois mois précédent
select gr.TYPE_CONVENTION, gr.ID_AAAAMM, c.NB_CONNEXION,
SUM ( c.NB_CONNEXION) OVER (PARTITION BY gr.type_convention order by gr.id_aaaamm rows 2 preceding) nb_3_mois_precedent
from (
SELECT distinct N.TYPE_CONVENTION, B.ID_AAAAMM
FROM BASEDEV.NB_CONNEXION N, BASEDEV.NB_CONNEXION B
group by N.TYPE_CONVENTION, B.ID_AAAAMM
order by N.TYPE_CONVENTION, B.ID_AAAAMM ) gr
left outer join BASEDEV.NB_CONNEXION C
on (gr.ID_AAAAMM= c.ID_AAAAMM and gr.TYPE_CONVENTION= c.TYPE_CONVENTION )
order by gr.TYPE_CONVENTION, gr.ID_AAAAMM

Je te remercie infiniment pour le temps que t'as consacré à mon problème

**Chtulus** · 04/08/2014, 22h46

Bonjour cisse3,

Envoyé par cisse3

Bonjour Flodelarab,
Merci pour ta réponse.

Nan, la dernière réponse c'est aieeeuuuuu

**cisse3** · 04/08/2014, 23h45

Bonjour,
désolé aieeeuuuuu.
En tout cas je vous remercie vous tous qui avez pris part à cette discussion

**aieeeuuuuu** · 05/08/2014, 10h38

Envoyé par cisse3

Pour 201311 , il faut lire " il y a 4 entreprises de convention C qui se connectées en 201311.

Voulez-vous dire que la colonne NUMERO_ENTREPRISE (c'est à dire la clef primaire de la table entreprise) représente nom pas le code de l'entreprise, mais le nombre d'entreprises qui se sont connectées à cette date ???????

Ce serait une modélisation pour le moins... insolite !

**cisse3** · 06/08/2014, 06h37

Bonjour aieeeuuuuu,

Voulez-vous dire que la colonne NUMERO_ENTREPRISE (c'est à dire la clef primaire de la table entreprise) représente nom pas le code de l'entreprise, mais le nombre d'entreprises qui se sont connectées à cette date ???????

NUMERO_ENTREPRISE il représente bien le code de l'entreprise(malgré que quelques codes dupliqués se soient glissés dans le jeux de données) et non le nombre d'entreprise qui se sont connectées
Dans le jeux fourni il faut lire " 201311 C 1 " pour dire "En 201311 une entreprise de convention C s'est connecté"
Merci pour ta remarque pertinente sur le jeux de données pas facilement exploitable en l'état(Je ferai attention la prochaine fois)
Comme je te le signalais précédemment, j'ai pu trouver une solution à mon problème et te remercie pour tes interventions

**aieeeuuuuu** · 06/08/2014, 10h07

Envoyé par cisse3

Dans le jeux fourni il faut lire " 201311 C 1 " pour dire "En 201311 une entreprise de convention C s'est connecté"

Mouis, moi je parlais du jeu d'essai fournis dans le post #7 sous forme d'INSERT INTO, dans lequel il n'y a pas cette notion de nombre de connexions...
Du coup c'est incohérent avec le résultat attendu fourni dans le même post !

Mise en oeuvre de fonctions analytiques : calcul d'indicateur sur les trois derniers mois

Langage SQL

Discussions similaires

Partager

Partager