Discussion :

[staff88]

Bonjour,

Je me questionne sur la construction d'une requête sous SQL Server 2008 R2.
Prenons une table représentant des conférences avec une date et heure de début et date et heure de fin.
Une table représentant chaque connexion à ces conférences avec une date et heure de début de connexion et une date et heure de fin de connexion.

Il me faut compter le nombre maximum de connexions simultanées pour chaque conférence (le pic de connexions en gros).

Exemple simple pour une conférence commençant à 8h00 et finissant à 9h00 :
connexion 1 entre 8h05 et 8h10
connexion 2 entre 8h07 et 8h23
connexion 3 entre 8h25 et 8h50

Le résultat doit être égal à 2 pour les deux premières qui ont été connectées simultanément entre 8h07 et 8h10.

Je me demande donc si cela est possible juste avec une requête et si oui comment réaliser cela.
J'y réfléchie mais je ne vois pas vraiment.

Merci d'avance pour votre aide.

[taibag]

Bonjour,

Postez ici le script de la création de vos tables avec un exemple de données afin qu'on puisse vous aider.

Merci.

[StringBuilder]

Je pense que malheureusement, pas d'autre moyen que d'énumérer toutes les minutes entre le début et la fin de chaque conférence.

Il suffit ensuite de faire une jointure avec "minute between session_debut and session_fin"
Et effectuer un count() des lignes retournées pour chaque minute.
Puis un max() sur le résultat avec pour chaque conférence.

En gros, en imaginant une table "t_minute" qui contient, pour chaque conference, l'ensemble des minutes comprises entre son heure de début et son heure de fin (on peut utiliser une table physique, ou une vue basée sur une CTE récursive par exemple).

Code sq :

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

1
2
3
4
5
6
7
8
9
10
 
select conf.id, conf.nom, conf.debut, conf.fin, max(cpt.nb_sessions) simultanees
from t_conference conf
inner join (
   select min.conf_id, min.minute, count(ses.id) nb_sessions
   from t_minute min
   inner join t_session ses on ses.conf_id = min.conf_id and min.minute between ses.debut and ses.fin
   group by min.conf_id, min.minute
) cpt on cpt.conf_id = conf.id
group by conf.id, conf.nom, conf.debut, conf.fin

Y'a certainement moyen de faire plus propre, il est tard :o

[staff88]

Merci StringBuilder je vais analyser et tester ta réponse.

Je prends ta remarque en considération taibag, je ferai ça si je ne m'en sors pas avec la solution de StringBuilder, merci.

[staff88]

Je dois retrouver cette donné au sein d'une procédure stockée. Je vais voire à créer une fonction qui me permettent de faire cela, peut-être en itérant sur les minutes.

[StringBuilder]

Non, pas d'itération. C'est lent.

Une table de minutes ou une CTE récursive, mais pas de boucle !

[pcaboche]

Je pense que malheureusement, pas d'autre moyen que d'énumérer toutes les minutes entre le début et la fin de chaque conférence.

Pas besoin.

On a deux types d'événements :
- quelqu'un se connecte (le nombre de connexions simultanées est incrémenté)
- quelqu'un se déconnecte (le nombre de connexions simultanées est décrémenté)

Donc on commence par faire la liste de ces événements :

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
with evnt as
(
  -- connexion
  select distinct
      conf_id
    , heure_debut as heure
  from t_session
 
  union
  -- deconnexion
  select distinct
      conf_id
    , heure_fin
  from t_session
)

À partir de là, on cherche le nombre de connexions qu'il y a eu pour chacun de ce événements :

Code :

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

1
2
3
4
from evnt e
inner join t_session s
  on (    e.conf_id = s.id
     and e.heure between s.heure_debut and s.heure_fin )

On compte le nombre de connexions simultanées :

Code :

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

count(s.id) as nb_sessions ... group by e.conf_id, e.heure

Et enfin on prend le max pour chaque conférence.


Ça, c'est pour trouver le nombre de connexions simultanées.
Pour le nombre de connexions pendant une plage horaire particulière (1 minute), c'est différent.

[StringBuilder]

Ca, ça va donner le nombre de connexions totales.

Si une même personne s'est connectée et s'est faite jetée 25 fois, elle est comptée 25 fois.

Idem si une personne s'est connectée que les 5 premières minutes et une autre les 5 dernières minutes.

Ce qui nous intéresse ici, c'est de savoir combien étaient connectée "en même temps" tout au long de la session, et retrouver le nombre au moment où ils y en avait le plus (pour dimensionner la bande passante par exemple).

[StringBuilder]

En revanche, là j'ai pas le temps de poster la requête...

Mais en faisant une auto-jointure (ou union) sur les sessions pour avoir une seule colonne qui contient toutes les heures de connexion/déconnexion, avec un flag +1 / -1 selon si c'est une connexion ou une déconnexion, et une requête avec un cumul progressif dessus (sum() orver partition order by), on peut au fil des connexions et déconnexions, connaître le nombre de sessions simultanées.

Si suffit alors de faire un MAX() sur ce résultat et le tour est joué, sans devoir passer par une CTE récursive ou une table des minutes. Ça devrait être à peut près 1000 fois plus rapide

[staff88]

Pas besoin.

On a deux types d'événements :
- quelqu'un se connecte (le nombre de connexions simultanées est incrémenté)
- quelqu'un se déconnecte (le nombre de connexions simultanées est décrémenté)

Donc on commence par faire la liste de ces événements :

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
with evnt as
(
  -- connexion
  select distinct
      conf_id
    , heure_debut as heure
  from t_session
 
  union
  -- deconnexion
  select distinct
      conf_id
    , heure_fin
  from t_session
)

À partir de là, on cherche le nombre de connexions qu'il y a eu pour chacun de ce événements :

Code :

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

1
2
3
4
from evnt e
inner join t_session s
  on (    e.conf_id = s.id
     and e.heure between s.heure_debut and s.heure_fin )

On compte le nombre de connexions simultanées :

Code :

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

count(s.id) as nb_sessions ... group by e.conf_id, e.heure

Et enfin on prend le max pour chaque conférence.


Ça, c'est pour trouver le nombre de connexions simultanées.
Pour le nombre de connexions pendant une plage horaire particulière (1 minute), c'est différent.

En me basant sur ton exemple, j'ai construis 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
with evnt as
(
	select connexion_id, conf_id, heure_debut as heure from t_connexion
	union all
	select connexion_id, conf_id, heure_fin from t_connexion
)
 
select e.conf_id, count(c.connexion_id) as nombre
from evnt e
inner join tConnections c
  on (    e.connexion_id = c.connexion_id
     and e.heure between c.heure_debut and c.heure_fin )
group by e.conf_id, e.heure
order by e.conf_id, count(c.connexion_id) DESC

Or pour chaque conférence, je récupère le nombre d'heures (DateTime) égales entre elles.
Pour ces données de connexion :

Code :

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

1
2
3
4
5
6
7
8
9
10
3610	2015-08-24 14:04:00.000	2015-08-24 15:33:00.000
3610	2015-08-24 14:05:00.000	2015-08-24 15:32:00.000
3610	2015-08-24 14:05:00.000	2015-08-24 15:32:00.000
3610	2015-08-24 14:05:00.000	2015-08-24 15:32:00.000
3610	2015-08-24 14:05:00.000	2015-08-24 15:32:00.000
3611	2015-08-24 14:11:00.000	2015-08-24 14:14:00.000
3611	2015-08-24 14:19:00.000	2015-08-24 14:24:00.000
3611	2015-08-24 14:16:00.000	2015-08-24 14:21:00.000
3611	2015-08-24 14:17:00.000	2015-08-24 14:26:00.000
3611	2015-08-24 14:18:00.000	2015-08-24 14:26:00.000

Je récupère ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
3610	4
3610	4
3610	1
3610	1
3611	2
3611	1
3611	1
3611	1
3611	1
3611	1
3611	1
3611	1
3611	1

Peux tu m'aider à définir ce qui cloche ?
Ce que tu appelle 't_session' sont bien des connexions ?

[pcaboche]

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
with evnt as
(
  select DISTINCT
    connexion_id, conf_id, heure_debut as heure
  from t_connexion
 
  UNION
 
  select DISTINCT
    connexion_id, conf_id, heure_fin
  from t_connexion
)
 
select e.conf_id, e.heure, count(c.connexion_id) as nombre
from evnt e
inner join tConnections c
  on (    e.connexion_id = c.connexion_id
     and e.heure between c.heure_debut and c.heure_fin )
group by e.conf_id, e.heure
order by e.conf_id, count(c.connexion_id) DESC