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
|
with conference (id, nom)
as
(
select 1, 'Présentation SQL'
union all
select 2, 'Présentation C#'
union all
select 3, 'Présentation PHP'
),
connexion (id, conference_id, debut, fin)
as
(
select 1, 1, cast('2015-08-22 09:00:00' as datetime), cast('2015-08-22 10:05:00' as datetime)
union all
select 2, 1, cast('2015-08-22 09:05:00' as datetime), cast('2015-08-22 09:06:00' as datetime)
union all
select 3, 1, cast('2015-08-22 09:35:00' as datetime), cast('2015-08-22 09:45:00' as datetime)
union all
select 4, 1, cast('2015-08-22 09:01:00' as datetime), cast('2015-08-22 10:02:00' as datetime)
union all
select 5, 1, cast('2015-08-22 09:20:00' as datetime), cast('2015-08-22 09:35:00' as datetime)
union all
select 6, 1, cast('2015-08-22 09:30:00' as datetime), cast('2015-08-22 10:05:00' as datetime)
union all
select 7, 2, cast('2015-08-22 09:00:00' as datetime), cast('2015-08-22 10:05:00' as datetime)
union all
select 8, 2, cast('2015-08-22 09:50:00' as datetime), cast('2015-08-22 09:55:00' as datetime)
union all
select 9, 2, cast('2015-08-22 09:05:00' as datetime), cast('2015-08-22 09:35:00' as datetime)
union all
select 10, 3, cast('2015-08-22 09:40:00' as datetime), cast('2015-08-22 09:56:00' as datetime)
union all
select 11, 3, cast('2015-08-22 09:05:00' as datetime), cast('2015-08-22 09:25:00' as datetime)
union all
select 12, 3, cast('2015-08-22 09:15:00' as datetime), cast('2015-08-22 10:05:00' as datetime)
union all
select 13, 3, cast('2015-08-22 09:07:00' as datetime), cast('2015-08-22 09:52:00' as datetime)
union all
select 14, 3, cast('2015-08-22 09:25:00' as datetime), cast('2015-08-22 09:45:00' as datetime)
),
evenement (conference_id, instant, flag)
as
(
select conference_id, debut, 1 from connexion
union all
select conference_id, fin, -1 from connexion
),
suivi_session (conference_id, instant, nb)
as
(
select conference_id, instant, sum(flag) over (partition by conference_id order by instant)
from evenement
)
select conf.id, conf.nom, max(suivi.nb) maximum
from conference conf
inner join suivi_session suivi on suivi.conference_id = conf.id
group by conf.id, conf.nom |
Partager