Création d'intervalle sur une colonne Timestamp

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:

---

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 ?

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:

---

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 ?

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.

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

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:

---

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):

Citation:

---

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):

Citation:

---

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

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:

---

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)

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.

Ce que je trouve étrange c'est que vous chercher à créer des plages à partir de vos données.
Un calendrier pourrait vous donner un bon point d'appui !

Puisque j'ai la chance de tourner sur 11gR2, je me suis servi de cette nouvelle fonctionnalité : subquery_factoring_clause

Cela repend l'idée de Rams7s.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WITH l_date (date1) AS (SELECT TIMESTAMP '2012-05-01 13:00:00' FROM DUAL UNION ALL SELECT date1 - 1/24 FROM l_date WHERE date1 > TIMESTAMP '2012-04-30 13:00:00') SELECT date1 FROM l_date;   DATE1 ---------------------------------------------------- 01-MAY-12 01.00.00.000000000 PM 01-MAY-12 12.00.00.000000000 PM 01-MAY-12 11.00.00.000000000 AM 01-MAY-12 10.00.00.000000000 AM 01-MAY-12 09.00.00.000000000 AM 01-MAY-12 08.00.00.000000000 AM ... 30-APR-12 01.00.00.000000000 PM

---

Waldar : Je ne vois pas comment utiliser un calendrier. auriez vous un exemple ?