Discussion :

[mortimer.pw]

Bonjour à tous,

J'ai une table qui contient les données suivantes :

0040030 9009329329999 11/06/13 120744
0040036 4504204209999 11/06/13 120756
0040415 0300350359999 11/06/13 120823
0040021 4504534009999 11/06/13 120834
0040407 4504534019999 11/06/13 121114
0040276 0210040049999 11/06/13 121445
0040297 1001021019999 11/06/13 121504
0040318 1001051019999 11/06/13 121506
0040029 1001021019999 11/06/13 121653
0040093 1001021019999 11/06/13 121701
0040270 1001041019999 11/06/13 121710
0040087 1001051019999 11/06/13 121811
0040020 6509799799999 11/06/13 122108
0040116 7007638459999 11/06/13 122531

Les 2 dernières valeurs sont le jour au format date et l'heure sur 6 caractères.

Je n'arrive pas à compter le nombre de lignes dans chaque intervalle de 5min sur une journée, puis sur un intervalle de dates.

Pourriez-vous m'apporter votre aide ?

D'avance merci.

[skuatamad]

Un bon point de départ :
grouping by complex time periods

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
SQL> with data as (
  2  select 0040030 as c1, 9009329329999 as c2, to_date('11/06/13','dd/mm/yy') as d, '120744' as t from dual union all
  3  select 0040036, 4504204209999, to_date('11/06/13','dd/mm/yy'), '120756' from dual union all
  4  select 0040415, 0300350359999 , to_date('11/06/13','dd/mm/yy'), '120823' from dual union all
  5  select 0040021, 4504534009999 , to_date('11/06/13','dd/mm/yy'), '120834' from dual union all
  6  select 0040407, 4504534019999 , to_date('11/06/13','dd/mm/yy'), '121114' from dual union all
  7  select 0040276, 0210040049999 , to_date('11/06/13','dd/mm/yy'), '121445' from dual union all
  8  select 0040297, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121504' from dual union all
  9  select 0040318, 1001051019999 , to_date('11/06/13','dd/mm/yy'), '121506' from dual union all
 10  select 0040029, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121653' from dual union all
 11  select 0040093, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121701' from dual union all
 12  select 0040270, 1001041019999 , to_date('11/06/13','dd/mm/yy'), '121710' from dual union all
 13  select 0040087, 1001051019999 , to_date('11/06/13','dd/mm/yy'), '121811' from dual union all
 14  select 0040020, 6509799799999 , to_date('11/06/13','dd/mm/yy'), '122108' from dual union all
 15  select 0040116, 7007638459999 , to_date('11/06/13','dd/mm/yy'), '122531' from dual
 16  )
 17  select ceil((to_date(to_char(d,'dd/mm/yy') ||' '|| t, 'dd/mm/yy hh24:mi:ss')-date '-4712-01-01')*144)/144+date '-4712-01-01',
 18         count(*)
 19    from data
 20   group by ceil((to_date(to_char(d,'dd/mm/yy') ||' '|| t, 'dd/mm/yy hh24:mi:ss')-date '-4712-01-01')*144)/144+date '-4712-01-01';
 
CEIL((TO_DATE(TO_CH   COUNT(*)
------------------- ----------
11/06/0013 12:10:00          4
11/06/0013 12:20:00          8
11/06/0013 12:30:00          2
 
SQL>

[mortimer.pw]

Bonjour skuatamad,

Merci pour la réponse.

Puis-je abuser en demandant une explication ?

Faut-il spécifier les lignes 2 à 15 ? car je ne connais pas le nombre de lignes.

Merci.

[skuatamad]

Non les lignes 2 à 15 simulent la table en reprenant ton exemple.
La requête commence à SELECT ceil(.....
A adapter aux bons noms de colonnes et tables

[mortimer.pw]

Ok.

Et que représente le '-4712-01-01' ?

Est-ce portable sous PostgreSQL ?

Merci encore pour ta promptitude.

[pacmann]

Salut,

Le principe est juste de trouver une date de référence dans le passé.
Ainsi, la différence avec cette date t'es retournée par Oracle en nombre de jours. En multipliant par 24 * 60 / 10, tu as l'expression en nombre de tranches de dix minutes. Et ceil te permet de prenre la partie entière, c'est ce qui te permet de regrouper les lignes par tranches (tu prends donc 288 si tu veux des tranches de 5 minutes)... puis tu fais l'opération inverse pour reconvertir cette "offset de tranches de dix minutes" en date / heure.

Mais comme c'est algébrique, tu peux remplacer cette date étrange par trunc(sysdate), c'est la même chose :

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
 
SQL dts@QUALIF> WITH DATA AS (
  2  SELECT 0040030 AS c1, 9009329329999 AS c2, to_date('11/06/13','dd/mm/yy') AS d, '120744' AS t FROM dual union ALL
  3    SELECT 0040036, 4504204209999, to_date('11/06/13','dd/mm/yy'), '120756' FROM dual union ALL
  4      SELECT 0040415, 0300350359999 , to_date('11/06/13','dd/mm/yy'), '120823' FROM dual union ALL
  5      SELECT 0040021, 4504534009999 , to_date('11/06/13','dd/mm/yy'), '120834' FROM dual union ALL
  6      SELECT 0040407, 4504534019999 , to_date('11/06/13','dd/mm/yy'), '121114' FROM dual union ALL
  7      SELECT 0040276, 0210040049999 , to_date('11/06/13','dd/mm/yy'), '121445' FROM dual union ALL
  8      SELECT 0040297, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121504' FROM dual union ALL
  9      SELECT 0040318, 1001051019999 , to_date('11/06/13','dd/mm/yy'), '121506' FROM dual union ALL
 10     SELECT 0040029, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121653' FROM dual union ALL
 11     SELECT 0040093, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121701' FROM dual union ALL
 12     SELECT 0040270, 1001041019999 , to_date('11/06/13','dd/mm/yy'), '121710' FROM dual union ALL
 13     SELECT 0040087, 1001051019999 , to_date('11/06/13','dd/mm/yy'), '121811' FROM dual union ALL
 14     SELECT 0040020, 6509799799999 , to_date('11/06/13','dd/mm/yy'), '122108' FROM dual union ALL
 15     SELECT 0040116, 7007638459999 , to_date('11/06/13','dd/mm/yy'), '122531' FROM dual
 16     )
 17     SELECT ceil((to_date(to_char(d,'dd/mm/yy') ||' '|| t, 'dd/mm/yy hh24:mi:ss')-trunc(sysdate))*144)/144+trunc(sysdate),
 18            count(*)
 19       FROM DATA
 20      GROUP BY ceil((to_date(to_char(d,'dd/mm/yy') ||' '|| t, 'dd/mm/yy hh24:mi:ss')-trunc(sysdate))*144)/144+trunc(sysdate);
 
CEIL((TO   COUNT(*)
-------- ----------
11.06.13          4
11.06.13          8
11.06.13          2

Enfin en tous cas, bien joué Skuat

[pacmann]

Tiens, en y repensant, vu le format de ton horodatage, on peut un peu gruger : tu peux directement retrancher ce qui dépasse de ta tranche avec un modulo :

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
 
WITH DATA AS (
SELECT 0040030 AS c1, 9009329329999 AS c2, to_date('11/06/13','dd/mm/yy') AS d, '120744' AS t FROM dual union ALL
  SELECT 0040036, 4504204209999, to_date('11/06/13','dd/mm/yy'), '120756' FROM dual union ALL
    SELECT 0040415, 0300350359999 , to_date('11/06/13','dd/mm/yy'), '120823' FROM dual union ALL
    SELECT 0040021, 4504534009999 , to_date('11/06/13','dd/mm/yy'), '120834' FROM dual union ALL
    SELECT 0040407, 4504534019999 , to_date('11/06/13','dd/mm/yy'), '121114' FROM dual union ALL
    SELECT 0040276, 0210040049999 , to_date('11/06/13','dd/mm/yy'), '121445' FROM dual union ALL
    SELECT 0040297, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121504' FROM dual union ALL
    SELECT 0040318, 1001051019999 , to_date('11/06/13','dd/mm/yy'), '121506' FROM dual union ALL
   SELECT 0040029, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121653' FROM dual union ALL
   SELECT 0040093, 1001021019999 , to_date('11/06/13','dd/mm/yy'), '121701' FROM dual union ALL
   SELECT 0040270, 1001041019999 , to_date('11/06/13','dd/mm/yy'), '121710' FROM dual union ALL
   SELECT 0040087, 1001051019999 , to_date('11/06/13','dd/mm/yy'), '121811' FROM dual union ALL
   SELECT 0040020, 6509799799999 , to_date('11/06/13','dd/mm/yy'), '122108' FROM dual union ALL
   SELECT 0040116, 7007638459999 , to_date('11/06/13','dd/mm/yy'), '122531' FROM dual
   )
   SELECT d, t - mod(t, 1000),
          count(*)
     FROM DATA
    GROUP BY d, t - mod(t, 1000);
D        T-MOD(T,1000)   COUNT(*)
-------- ------------- ----------
11.06.13        120000          4
11.06.13        121000          8
11.06.13        122000          2

[skuatamad]

Enfin en tous cas, bien joué Skuat

Moi j'y suis pour rien, je suis pas hyper fortiche en astuce arithmétique. C'est Laurent Schneider qui a proposé cette solution
Par contre j'ai une bonne mémoire de ce que je vois passer et j'assure pas trop mal en google

Bien vu le coup du mod
A priori j'aurais pu le trouver, mais j'ai préféré googler plutôt que réfléchir

Est-ce portable sous PostgreSQL ?

La version avec MOD se portera très bien sur postgre