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
| SQL> with tranche as (
2 select lpad(rownum - 1,2,'00') ||':00 - '|| lpad(mod(rownum,24),2,'00') ||':00' as heure
3 from dual
4 connect by level <= 24
5 ),
6 containerid_min_max as (
7 select CONTAINERID
8 , min(CONTACTDATETIME) as min_date
9 , max(CONTACTDATETIME) as max_date
10 from mailing
11 where CONTAINERID in (378, 380, 377) /*filtre à supprimer ou modifier pour traiter plus ou moins de CONTAINERID */
12 group by CONTAINERID
13 ),
14 liste_jour as (
15 select CONTAINERID, min_date, max_date
16 , to_char(min_date,'yyyymmdd') + row_number() over (partition by CONTAINERID order by 1) - 1 as jour
17 , to_char(min_date,'yyyymmdd') as date_deb
18 , to_char(min_date, 'hh24') || ':00 - ' || lpad(to_number(to_char(min_date, 'hh24')) + 1, 2, '00') || ':00' as tranche_date_deb
19 , to_char(max_date,'yyyymmdd') as date_fin
20 , to_char(max_date, 'hh24') || ':00 - ' || lpad(to_number(to_char(max_date, 'hh24')) + 1, 2, '00') || ':00' as tranche_date_fin
21 from containerid_min_max cmm
22 connect by level <= max_date - min_date +1
23 AND PRIOR CONTAINERID = CONTAINERID
24 AND prior sys_guid() IS NOT NULL
25 ),
26 toute_tranche_liste_jour as (
27 select lj.*, t.*, lj.jour ||' '|| t.heure as jour_heure
28 from liste_jour lj
29 join tranche t
30 on lj.jour || t.heure >= lj.date_deb || lj.tranche_date_deb
31 and lj.jour || t.heure <= lj.date_fin || lj.tranche_date_fin
32 )
33 select ttlj.CONTAINERID
34 , ttlj.min_date as debut
35 , ttlj.max_date as fin
36 , ttlj.jour_heure as tranche_horraire
37 , count(m.CONTACTDATETIME) as nb
38 from toute_tranche_liste_jour ttlj
39 left join MAILING m
40 on m.CONTAINERID = ttlj.CONTAINERID
41 and to_char(m.CONTACTDATETIME, 'yyyymmdd')||' '||
42 to_char(m.CONTACTDATETIME, 'hh24') || ':00 - ' ||
43 lpad(to_number(to_char(m.CONTACTDATETIME, 'hh24')) + 1, 2, '00') || ':00' = ttlj.jour_heure
44 group by ttlj.CONTAINERID, ttlj.min_date, ttlj.max_date, ttlj.jour_heure
45 order by ttlj.CONTAINERID, ttlj.jour_heure;
CONTAINERID DEBUT FIN TRANCHE_HORRAIRE NB
----------- ------------------- ------------------- ------------------------- ----------
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 17:00 - 18:00 9
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 18:00 - 19:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 19:00 - 20:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 20:00 - 21:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 21:00 - 22:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 22:00 - 23:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131217 23:00 - 00:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 00:00 - 01:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 01:00 - 02:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 02:00 - 03:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 03:00 - 04:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 04:00 - 05:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 05:00 - 06:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 06:00 - 07:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 07:00 - 08:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 08:00 - 09:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 09:00 - 10:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 10:00 - 11:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 11:00 - 12:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 12:00 - 13:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 13:00 - 14:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 14:00 - 15:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 15:00 - 16:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 16:00 - 17:00 0
377 17/12/2013 17:14:54 18/12/2013 17:14:54 20131218 17:00 - 18:00 4
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 09:00 - 10:00 3
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 10:00 - 11:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 11:00 - 12:00 1
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 12:00 - 13:00 1
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 13:00 - 14:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 14:00 - 15:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 15:00 - 16:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 16:00 - 17:00 2
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 17:00 - 18:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 18:00 - 19:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 19:00 - 20:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 20:00 - 21:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 21:00 - 22:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 22:00 - 23:00 1
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140206 23:00 - 00:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 00:00 - 01:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 01:00 - 02:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 02:00 - 03:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 03:00 - 04:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 04:00 - 05:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 05:00 - 06:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 06:00 - 07:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 07:00 - 08:00 3
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 08:00 - 09:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 09:00 - 10:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 10:00 - 11:00 0
378 06/02/2014 09:45:07 07/02/2014 11:45:08 20140207 11:00 - 12:00 2
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 13:00 - 14:00 8
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 14:00 - 15:00 0
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 15:00 - 16:00 2
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 16:00 - 17:00 0
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 17:00 - 18:00 0
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 18:00 - 19:00 1
380 13/05/2014 13:01:05 13/05/2014 19:01:05 20140513 19:00 - 20:00 2
59 rows selected.
SQL> |
Partager