Discussion :

[alex_972]

Bonjour,

Je possède une table de log qui commence par une colonne de type timestamp. J'essaie de créer une requete qui me permette de faire des calculs sur les autres champs avec un intervalle qui peut etre de 5 min ou d'une semaine.

Comme expliqué dans mon poste précédent, j'ai essayé avec

Code :

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

over ( partition by to_char(date_time, 'J') || '.' || trunc (to_char(date_time, 'SSSSS')/300)

Malheureusement, cette requete ne fonctionne pas si je divise par 86400 (24 heures en seconde)

Je suis en train de me pencher sur cette solution mais je pense qu'il me manque la solution pour gérer mes jours en secondes car la division de mon intervalle me pose problème.

Auriez vous une solution à me proposer ?

[alex_972]

Je n'arrive pas à résoudre le passage au mois suivant.

Dans ce cas, je voudrais qu'il me renvoie 1 intervalle (d'un peu moins d'une semaine) or il m'en renvoie 2.

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
WITH selection AS
 (SELECT date_time, trunc(to_char(date_time, 'J')/7) intrvl
    FROM Table_log
   WHERE date_time BETWEEN
         to_date('31/03/2012 10:36:21', 'DD/MM/YYYY HH24:MI:SS') AND
         to_date('06/04/2012 10:31:21', 'DD/MM/YYYY HH24:MI:SS')),
Ordering AS
 (SELECT date_time,
         intrvl,
         row_number() over(ORDER BY date_time desc) - row_number() over(partition by intrvl order by date_time desc) as grp
    FROM selection)
select count(distinct(intrvl))
  from (Select date_time st,
               intrvl,
               count(*) over(partition by intrvl, grp) as cnt
          FROM ordering
         order by 1 desc);

Une solution ?

[Waldar]

Il nous faudrait quelques données en source et comment vous voulez les voir après la requête histoire de bien comprendre ce que vous voulez faire.
En étant hors contexte c'est trop obscur pour être à même de vous répondre correctement.

[alex_972]

Oui en effet, je n'ai pas été très claire...

J'ai une table de log avec une vintaine de colonnes de chiffre et une colonne de type timestamp nommée date_time.

Jusqu'a maintenant, je faisais des calculs sur un intervalle de 5 minutes maximum sur la meme journée. J'utilisais donc ce type de requête pour faire mes intervalles :

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
WITH SELECTION AS
(
SELECT to_date('31/03/2012 10:36:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 10:37:29', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 10:36:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 11:26:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 11:49:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 11:50:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 11:53:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 12:24:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 12:32:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 12:32:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('31/03/2012 23:59:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('01/04/2012 00:02:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('01/04/2012 10:36:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('01/04/2012 10:37:29', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('01/04/2012 10:36:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('02/04/2012 11:26:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('02/04/2012 11:49:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('06/04/2012 10:35:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('06/04/2012 11:50:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('06/04/2012 11:53:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('06/04/2012 12:24:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('06/04/2012 12:32:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual union ALL
SELECT to_date('06/04/2012 13:36:21', 'DD/MM/YYYY HH24:MI:SS') AS date_time FROM dual
)
SELECT to_char(date_time,'DD/MM/YYYY HH24:MI:SS'), count(*) over (partition by trunc(to_char(date_time,'SSSSS')/300)) from selection

Maintenant, j'aurais besoin d'intervalle plus strict qui font reellement 1 heure, 1 semaine... Je m'explique pour l,exemple précédent, j'aimerai qu'il me retourne :
Pour un intervalle d'une heure (date du debut de l'intervalle et le nb de lignes durant l'heure):

date_time cnt
-------------------- ----
31/03/2012 10:36:21 3
31/03/2012 11:36:21 1
31/03/2012 12:36:21 6
31/03/2012 13:36:21 0
31/03/2012 14:36:21 0
31/03/2012 15:36:21 0
...

Pour un intervalle d'une semaine (date du debut de l'intervalle et le nb de ligne durant la semaine):

date_time cnt
-------------------- ----
31/03/2012 10:36:21 16
06/04/2012 10:36:21 5
13/04/2012 10:36:21 0
20/04/2012 10:36:21 0
...

Pour les exemples précédents, l'astuce du "count(*) over (partition by trunc(to_char(date_time,'SSSSS')/300)) " ne fonctionne plus.
Je me casse donc la tête pour générer simplement un intervalle d'une semaine (par exemple) sans surcharger de calcul car c'est ma base pour après faire des statistiques.

J'ai essayé la piste de la méthode de Tabibitosan mais sans trop de résultat pour le moment. Je bloque sur la determination de l'intervalle sans prendre trop de ressources.

J'espère avoir été plus claire.

Merci d'avance pour vos idées et votre aide

[Rams7s]

Juste une idee: si vous faite un join avec une table de ce genre et la condition avec votre date_time between inter_min et inter_max:

Code :

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

1
2
3
4
5
6
7
8
9
 
variable nbr_interval number;
exec :nbr_interval := 10;
variable inter_heure number;
--Toutes les demi-heures
exec :inter_heure :=0.5;
with inter_set as
   (select rownum group_id, sysdate-((level-1)*:inter_heure/24) as inter_min, sysdate-((level)*:inter_heure/24) as inter_max from dual connect by level<:nbr_interval)
select * from inter_set

Ca vous donne un group_id pour faire votre group by, non?

(Je suis desole, mais ca merdouille avec votre with une fois sur deux sur mon pc. Un truc chelou il reconnnait pas la table dual en plein milieu du with)

[alex_972]

Bonne idée Rams7s mais pour le moment je me suis fait une fonction en PL/SQL par période avec une boucle qui fait un UNION ALL.

J'aurais préféré tout mettre dans une seule requete SQL mais je n'ai pas trop le temps en ce moment. Je testerai ta solution dès que j'ai un peu le temps pour voir les performance sur une grosse table.