Discussion :

[capblans]

Bonjour,

Je souhaite faire une requête qui me permettent d'agréger les données par tranche de 5 minutes puis par jour.

J'ai trouvé pour faire le regroupement par jour mais je ne vois pas comment faire un premier regroupement par tranche de cinq minutes.

Voici la requête que j'ai utilisé:

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT 
EXTRACT(DAY FROM fich_log_date),
  COUNT(terminal)
FROM track.fich_log
WHERE fich_log_date >= to_date('01/02/09',   'DD/MM/RR')
 AND fich_log_date < to_date('01/03/09',   'DD/MM/RR')
 AND id_terminal_type = '3'
GROUP BY EXTRACT(DAY FROM fich_log_date)

[Waldar]

Je n'ai pas trouvé de format "tout fait" pour afficher un pas de cinq minutes sur les heures.
J'ai donc rusé en multipliant par cinq l'arrondi inférieur de la division des minutes par cinq.

Essayez la requête suivante :

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
select
    trunc(fich_log_date) as day,
    decode(grouping(to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09')), 1, 'Total jour', to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09')) as min,
    count(*)
from
    track.fich_log
where
    fich_log_date >= to_date('01/02/2009', 'DD/MM/YYYY')
AND fich_log_date < to_date('01/03/2009', 'DD/MM/YYYY')
AND id_terminal_type = '3'
group by
    trunc(fich_log_date),
    rollup(to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09'))
order by
    trunc(fich_log_date) asc,
    to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09') asc

Voici une partie du résultat pour aujourd'hui :

Code :

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

1
2
3
4
5
6
DAY		MIN		COUNT(*)
11/03/2009	15:25		9
11/03/2009	15:55		9
11/03/2009	16:25		9
11/03/2009	16:55		9
11/03/2009	Total jour	496

Evidement il n'y a pas les heures minutes où il ne se passe rien, mais on pourrait les rajouter en continuant à ruser.

[capblans]

Merci beaucoup pour cette réponse!

Cela fonctionne sur mes données et me donne un résultat par tranche de 5 minutes!

Cependant lorsque j'imbrique cette requête dans une requête qui groupe par jour, le résultat que j'obtiens est erroné!

Voilà ce que je fais :

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
 
SELECT 
    EXTRACT (DAY FROM SR.day),
    SUM (SR.terminal)
FROM
    (SELECT
        trunc(fich_log_date) AS day,
        decode(grouping(to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09')), 1,
        'Total jour', to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09')) AS min,
        count(*) as terminal
    FROM
         track.fich_log
    WHERE
         fich_log_date >= to_date('01/02/2009', 'DD/MM/YYYY')
    AND fich_log_date < to_date('01/03/2009', 'DD/MM/YYYY')
    AND id_terminal_type = '3'
    GROUP BY
         trunc(fich_log_date),
         rollup(to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09'))
    ORDER BY
         trunc(fich_log_date) ASC,
         to_char(fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fich_log_date, 'mi'))/5)*5, 'FM09') ASC
    ) SR
GROUP BY EXTRACT (DAY FROM SR.day)

L'explication de cette double imbrication vient du fait que je vais utiliser cette requête sur des moyennes (une première moyenne par plage de cinq minutes puis moyenne par jour)

[Waldar]

Dans cette requête il y a déjà le total par jour :

Code :

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

1
2
3
4
5
SELECT 
    EXTRACT(DAY FROM SR.day),
    SR.terminal
FROM (...) SR
WHERE SR.min = 'Total jour'

Néanmoins si vous ne le voulez plus il faut enlever le grouping ainsi que le rollup.

[Waldar]

Regardez cette requête qui vous mettra des 0 quand vous n'avez pas de donnée :

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
WITH user_day AS
    (select to_date('01/02/2009', 'dd/mm/yyyy') + level - 1 as day from dual
     connect by level <= to_date('01/03/2009', 'dd/mm/yyyy') - to_date('01/02/2009', 'dd/mm/yyyy')),
     usr_hour AS (select to_char(level-1, 'FM09') || ':' as hour from dual connect by level <= 24),
     usr_min AS (select to_char((level-1)*5, 'FM09') as min from dual connect by level <= 12)
SELECT
    ud.day,
    uh.hour || um.min as time,
    count(*) nb
FROM
    user_day ud
    cross join usr_hour uh
    cross join usr_min um
    left outer join track.fich_log fl
      on trunc(fl.fich_log_date) = ud.day
     and to_char(fl.fich_log_date, 'hh24:') || to_char(floor(to_number(to_char(fl.fich_log_date, 'mi'))/5)*5, 'FM09') = uh.hour || um.min
     and fl.id_terminal_type = '3'
GROUP BY
    ud.day,
    uh.hour || um.min
ORDER BY
    ud.day asc,
    uh.hour || um.min asc

[capblans]

Dans cette requête il y a déjà le total par jour :

Code :

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

1
2
3
4
5
SELECT 
    EXTRACT(DAY FROM SR.day),
    SR.terminal
FROM (...) SR
WHERE SR.min = 'Total jour'

Néanmoins si vous ne le voulez plus il faut enlever le grouping ainsi que le rollup.

Merci et désolée!

Je n'avais pas vu le "Total jour" !

[capblans]

J'ai retesté cela fonctionne très bien!

Merci beaucoup pour cette solution!