Aide pour compréhension de calcul dans une vue

Bonjour à tous!!

Voila en reprenant le projet de quelqu'un on me demande d'analyser les calculs dans un tableau de reporting issus d'une vue.

voici les données de la vue:

Pièce jointe 179760

La requète qui a créé la vue:

Code:

---

1 2 3 4 5 6 7 8

CREATE OR REPLACE FORCE VIEW "AMI2PPR1"."POP_DM_STAT_REACTIF" ("ID_SITE", "NB", "MOIS", "ANS") AS SELECT ID_SITE,AVG((trunc(DATE_T_PROTO,'HH') - trunc(DATE_ENVOI,'HH'))*24) AS NB,to_char(DATE_T_PROTO,'mm') AS MOIS,to_char(DATE_T_PROTO,'YYYY') AS ANS FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE DATE_T_PROTO is not null AND DATE_ENVOI is not null GROUP BY ID_SITE,to_char(DATE_T_PROTO,'mm'),to_char(DATE_T_PROTO,'YYYY') ORDER BY ID_SITE,ANS,MOIS;

---

J'essaie de m’intéresser au calcul du 263 au mois de mai 2015 avec l'id_site =2.

J'ai beau essayé de farfouillé avec des requêtes pour rassembler les données pour le calcul je ne retrouve pas la valeur 263.

voici les données pour le champ DATE_T_PROTO pour le moi de mai:

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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132

05/01/15 10:00:00,000000000 17/12/14 17:00:00,000000000 12/01/15 14:00:00,000000000 14/01/15 14:00:00,000000000 20/01/15 15:00:00,000000000 21/01/15 09:00:00,000000000 17/12/14 15:00:00,000000000 18/12/14 16:00:00,000000000 17/12/14 15:00:00,000000000 18/12/14 15:00:00,000000000 18/12/14 15:00:00,000000000 05/01/15 10:00:00,000000000 05/03/15 10:00:00,000000000 15/12/14 15:00:00,000000000 17/12/14 09:00:00,000000000 15/01/15 18:00:00,000000000 05/02/15 18:00:00,000000000 05/02/15 18:00:00,000000000 10/02/15 15:00:00,000000000 19/02/15 16:00:00,000000000 11/02/15 08:00:00,000000000 11/02/15 08:00:00,000000000 07/04/15 10:00:00,000000000 29/01/15 14:00:00,000000000 22/01/15 17:00:00,000000000 21/05/15 10:00:00,000000000 12/01/15 12:00:00,000000000 14/01/15 17:00:00,000000000 21/01/15 15:00:00,000000000 13/01/15 09:00:00,000000000 14/01/15 17:00:00,000000000 24/02/15 09:00:00,000000000 18/02/15 11:00:00,000000000 13/02/15 14:00:00,000000000 17/03/15 15:00:00,000000000 16/02/15 08:00:00,000000000 20/02/15 08:00:00,000000000 18/02/15 15:00:00,000000000 19/01/15 16:00:00,000000000 22/01/15 17:00:00,000000000 03/02/15 13:00:00,000000000 19/03/15 15:00:00,000000000 16/02/15 18:00:00,000000000 03/02/15 11:00:00,000000000 24/02/15 09:00:00,000000000 03/03/15 09:00:00,000000000 07/04/15 11:00:00,000000000 27/03/15 09:00:00,000000000 17/03/15 18:00:00,000000000 10/03/15 10:00:00,000000000 13/04/15 10:00:00,000000000 09/02/15 17:00:00,000000000 27/03/15 09:00:00,000000000 17/03/15 12:00:00,000000000 26/02/15 09:00:00,000000000 17/03/15 11:00:00,000000000 18/03/15 11:00:00,000000000 27/03/15 09:00:00,000000000 24/04/15 15:00:00,000000000 25/02/15 17:00:00,000000000 27/03/15 08:00:00,000000000 31/03/15 17:00:00,000000000 01/04/15 17:00:00,000000000 08/04/15 16:00:00,000000000 04/05/15 18:00:00,000000000 15/04/15 15:00:00,000000000 10/04/15 11:00:00,000000000 07/01/15 14:00:00,000000000 13/03/15 17:00:00,000000000 24/03/15 10:00:00,000000000 19/05/15 09:00:00,000000000 27/03/15 08:00:00,000000000 30/03/15 08:00:00,000000000 27/03/15 13:00:00,000000000 14/04/15 18:00:00,000000000 06/03/15 12:00:00,000000000 11/03/15 09:00:00,000000000 02/04/15 10:00:00,000000000 03/04/15 09:00:00,000000000 07/04/15 10:00:00,000000000 07/04/15 10:00:00,000000000 07/04/15 10:00:00,000000000 09/03/15 09:00:00,000000000 10/03/15 16:00:00,000000000 13/04/15 18:00:00,000000000 03/04/15 16:00:00,000000000 13/04/15 18:00:00,000000000 13/04/15 18:00:00,000000000 14/04/15 11:00:00,000000000 16/04/15 18:00:00,000000000 16/04/15 18:00:00,000000000 06/05/15 18:00:00,000000000 19/03/15 09:00:00,000000000 01/04/15 09:00:00,000000000 24/03/15 16:00:00,000000000 13/04/15 18:00:00,000000000 14/04/15 11:00:00,000000000 20/02/15 11:00:00,000000000 20/02/15 10:00:00,000000000 20/02/15 10:00:00,000000000 24/02/15 11:00:00,000000000 03/03/15 14:00:00,000000000 12/03/15 18:00:00,000000000 22/04/15 08:00:00,000000000 06/05/15 11:00:00,000000000 23/04/15 15:00:00,000000000 05/05/15 18:00:00,000000000 23/04/15 15:00:00,000000000 23/04/15 16:00:00,000000000 04/05/15 18:00:00,000000000 04/05/15 18:00:00,000000000 04/05/15 18:00:00,000000000 04/05/15 17:00:00,000000000 29/04/15 10:00:00,000000000 04/05/15 17:00:00,000000000 13/05/15 10:00:00,000000000 17/04/15 16:00:00,000000000 19/05/15 15:00:00,000000000 20/05/15 11:00:00,000000000 27/05/15 11:00:00,000000000 27/05/15 17:00:00,000000000 28/05/15 17:00:00,000000000 28/05/15 17:00:00,000000000 03/06/15 15:00:00,000000000 04/05/15 18:00:00,000000000 19/05/15 10:00:00,000000000 03/06/15 15:00:00,000000000 03/06/15 15:00:00,000000000 07/05/15 16:00:00,000000000 11/05/15 18:00:00,000000000 13/05/15 16:00:00,000000000

---

Avec cette requête:

Code:

---

1 2 3 4

SELECT DATE_T_PROTO FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE ID_SITE = '2';

---

voici les données pour le champ DATE_ENVOI pour le moi de mai:

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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139

17/12/14 13:00:00,000000000 08/01/15 11:00:00,000000000 12/01/15 13:00:00,000000000 12/01/15 13:00:00,000000000 13/01/15 18:00:00,000000000 17/12/14 12:00:00,000000000 18/12/14 11:00:00,000000000 17/12/14 15:00:00,000000000 18/12/14 11:00:00,000000000 18/12/14 15:00:00,000000000 18/12/14 16:00:00,000000000 05/03/15 10:00:00,000000000 15/01/15 15:00:00,000000000 05/02/15 17:00:00,000000000 05/02/15 14:00:00,000000000 09/02/15 17:00:00,000000000 10/02/15 16:00:00,000000000 11/02/15 08:00:00,000000000 11/02/15 08:00:00,000000000 15/01/15 17:00:00,000000000 15/01/15 11:00:00,000000000 22/01/15 15:00:00,000000000 13/03/15 09:00:00,000000000 12/01/15 11:00:00,000000000 12/01/15 11:00:00,000000000 14/01/15 16:00:00,000000000 12/01/15 19:00:00,000000000 13/01/15 17:00:00,000000000 19/02/15 12:00:00,000000000 18/02/15 11:00:00,000000000 13/02/15 12:00:00,000000000 17/03/15 15:00:00,000000000 16/02/15 08:00:00,000000000 16/02/15 13:00:00,000000000 18/02/15 15:00:00,000000000 19/01/15 13:00:00,000000000 22/01/15 10:00:00,000000000 02/02/15 18:00:00,000000000 06/02/15 15:00:00,000000000 26/01/15 15:00:00,000000000 26/01/15 17:00:00,000000000 13/02/15 15:00:00,000000000 03/03/15 09:00:00,000000000 27/02/15 11:00:00,000000000 25/03/15 09:00:00,000000000 09/03/15 09:00:00,000000000 09/03/15 11:00:00,000000000 13/03/15 17:00:00,000000000 13/03/15 18:00:00,000000000 05/02/15 11:00:00,000000000 13/03/15 10:00:00,000000000 25/02/15 15:00:00,000000000 25/02/15 17:00:00,000000000 17/03/15 11:00:00,000000000 17/03/15 10:00:00,000000000 18/03/15 11:00:00,000000000 20/03/15 15:00:00,000000000 25/02/15 16:00:00,000000000 26/03/15 11:00:00,000000000 31/03/15 17:00:00,000000000 01/04/15 14:00:00,000000000 07/04/15 08:00:00,000000000 07/04/15 08:00:00,000000000 07/04/15 08:00:00,000000000 07/04/15 17:00:00,000000000 08/04/15 18:00:00,000000000 07/01/15 10:00:00,000000000 13/03/15 14:00:00,000000000 24/03/15 09:00:00,000000000 27/03/15 13:00:00,000000000 25/03/15 17:00:00,000000000 27/03/15 11:00:00,000000000 27/03/15 11:00:00,000000000 30/03/15 11:00:00,000000000 05/03/15 16:00:00,000000000 10/03/15 19:00:00,000000000 02/04/15 08:00:00,000000000 01/04/15 18:00:00,000000000 02/04/15 09:00:00,000000000 03/04/15 19:00:00,000000000 03/04/15 16:00:00,000000000 07/04/15 09:00:00,000000000 13/04/15 09:00:00,000000000 27/02/15 18:00:00,000000000 03/03/15 18:00:00,000000000 16/03/15 14:00:00,000000000 03/04/15 16:00:00,000000000 13/04/15 13:00:00,000000000 13/04/15 18:00:00,000000000 13/04/15 19:00:00,000000000 16/04/15 15:00:00,000000000 16/04/15 16:00:00,000000000 17/04/15 18:00:00,000000000 18/03/15 17:00:00,000000000 27/03/15 13:00:00,000000000 24/03/15 16:00:00,000000000 13/04/15 09:00:00,000000000 14/04/15 10:00:00,000000000 20/02/15 10:00:00,000000000 20/02/15 09:00:00,000000000 20/02/15 10:00:00,000000000 24/02/15 11:00:00,000000000 02/03/15 16:00:00,000000000 12/03/15 12:00:00,000000000 21/04/15 16:00:00,000000000 22/04/15 16:00:00,000000000 23/04/15 11:00:00,000000000 23/04/15 10:00:00,000000000 23/04/15 11:00:00,000000000 23/04/15 15:00:00,000000000 28/04/15 13:00:00,000000000 27/04/15 11:00:00,000000000 27/04/15 10:00:00,000000000 28/04/15 14:00:00,000000000 28/04/15 14:00:00,000000000 30/04/15 19:00:00,000000000 05/05/15 09:00:00,000000000 17/04/15 16:00:00,000000000 19/05/15 15:00:00,000000000 20/05/15 10:00:00,000000000 22/05/15 10:00:00,000000000 22/05/15 15:00:00,000000000 26/05/15 17:00:00,000000000 28/05/15 14:00:00,000000000 28/05/15 14:00:00,000000000 03/06/15 14:00:00,000000000 04/06/15 11:00:00,000000000 28/04/15 11:00:00,000000000 13/05/15 14:00:00,000000000 03/06/15 13:00:00,000000000 03/06/15 12:00:00,000000000 05/05/15 10:00:00,000000000 07/05/15 08:00:00,000000000 13/05/15 15:00:00,000000000 11/05/15 18:00:00,000000000 13/05/15 16:00:00,000000000 18/05/15 14:00:00,000000000 18/05/15 18:00:00,000000000

---

Avec cette requête:

Code:

---

1 2 3 4

SELECT DATE_ENVOI FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE ID_SITE = '2';

---

Quelqu'un pourrai-t-il m'aider ? Car la je bloque.

vous ne trouverez pas le 263 directement dans la base ....

la formule utilisée est
AVG((trunc(DATE_T_PROTO,'HH') - trunc(DATE_ENVOI,'HH'))*24)

avec une valeur de group by
GROUP BY ID_SITE,to_char(DATE_T_PROTO,'mm'),to_char(DATE_T_PROTO,'YYYY')


Donc, c'est le calcule qu'il faut faire :
Par site, par mois, par année, faire la moyenne des (date de DATE_T_PROTO limité à l'heure moins la date d'envoi limité à l'heure, et multipliée par 24)

Ca donne donc la moyenne du nombre d'heures entre la date DATE_T_PROTO et la date DATE_ENVOI
pour chaque site, par mois et par année



(Pour info, l'écart entre deux dates va etre donnée en nombre de jour, donc on multiplie par 24 pour l'avoir en nombre d'heures.)

Merci pour ta réponse!

Oui c'est ce que j'ai fait j'ai pris en gros chacune des heures dans les dates du moi de mais 2015 de DATE_ENVOI et DATE_T_PROTO. J'ai sommé toute les heures de chacune des deux puis je les ai soustraite, fait la moyenne et enfin multiplié par 24 mais je trouve toujours pas. C'est pour cela que je demande de l'aide.

Essaie de créer une nouvelle vue, de la façon suivante :

Code:

---

1 2 3 4 5 6 7 8 9

CREATE OR REPLACE FORCE VIEW "AMI2PPR1"."POP_DM_STAT_REACTIF" ("ID_SITE", "NB", "MOIS", "ANS") AS SELECT ID_SITE,AVG((trunc(DATE_T_PROTO,'HH') - trunc(DATE_ENVOI,'HH'))*24) AS NB,to_char(DATE_T_PROTO,'mm') AS MOIS,to_char(DATE_T_PROTO,'YYYY') AS ANS, count(*) as nb_produits, min(date_envoi) as date_envoi_mini , max( date_envoi) as date_envoi_maxi FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE DATE_T_PROTO is not null AND DATE_ENVOI is not null GROUP BY ID_SITE,to_char(DATE_T_PROTO,'mm'),to_char(DATE_T_PROTO,'YYYY') ORDER BY ID_SITE,ANS,MOIS;

---

J'ai juste ajouté la ligne en italique.
Avec ces informations supplémentaires, tu pourras plus facilement vérifier si les calculs que tu fais à la main sont corrects.

Bonjour,

votre trunc permet d'arrondir à l'heure.
Pour retrouver 263, il faut donc calculer la moyenne du nombre d'heures entre les deux dates (deux à deux)
pour le mois de mai, il y a en moyenne 263 heures d’écart entre DATE_T_PROTO et DATE_ENVOI