Compter un nombre maximum de connexions simultanées sur une plage horaire

**staff88** · 20/08/2015, 18h53

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** · 20/08/2015, 21h08

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** · 21/08/2015, 02h05

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** · 21/08/2015, 11h40

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** · 21/08/2015, 11h54

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** · 21/08/2015, 12h03

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

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

**staff88** · 21/08/2015, 17h34

Envoyé par StringBuilder

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

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

Merci de l'info

**pcaboche** · 21/08/2015, 21h09

Envoyé par 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.

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** · 22/08/2015, 09h52

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** · 22/08/2015, 09h59

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

**pcaboche** · 22/08/2015, 13h19

Envoyé par StringBuilder

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

Oui, c'est le nombre de de connexions totales... à un moment précis (i.e. quand quelqu'un se connecte / déconnecte).

Par exemple, si quelqu'un se connecte (ou se déconnecte) à 8h07, on cherche à savoir combien de personnes étaient connectées à ce moment là.
Idem pour toutes les fois où quelqu'un se connecte / déconnecte (c'est ce que je fais dans la deuxième partie du code : jointure entre evnt et t_session ).

Oui, on peut faire cela avec un cumul progressif.
Il y a juste un problème avec le cumul progressif (pas applicable à ce problème, mais c'est bon de le garder à l'esprit) : on est obligé de reparcourir tout l'historique pour connaître le nombre de connexions simultanées à un moment donné.

**StringBuilder** · 22/08/2015, 13h26

Ah oui, effectivement, je n'avais pas fait attention à la jointure suppélementaire entre les "événements" et les sessions.
En effet, ça doit marcher aussi.
A voir laquelle est la plus performante sur un gros volume

Voici un exemple complet avec cumul progressif, sans la table des minutes ni la CTE récursive.

Code sql :

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
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

Sortie :

Code :

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

1
2
3
4
5
6
7
8
 
id          nom              maximum
----------- ---------------- -----------
2           Présentation C#  2
3           Présentation PHP 4
1           Présentation SQL 4
 
(3 row(s) affected)

**staff88** · 26/08/2015, 09h55

Bonjour,

Je m'excuse de ne pas être repassé plus tôt, j'ai été pris sur une autre activité.

Je vous remercie grandement d'avoir réfléchi au sujet. Je vais prendre le temps d'analyser vos réponses.

En effet le besoin est de connaitre le pic de connexion de chaque conférence. Combien il y a eu de participants maximum au même moment.

**pcaboche** · 26/08/2015, 10h50

Envoyé par StringBuilder

A voir laquelle est la plus performante sur un gros volume

ça dépend du volume et du type de requête.

Par exemple, si on fait une restriction précise sur un historique très long, une jointure pourrait s'appuyer sur les index pour ne sélectionner que les enregistrments appropriés pour le calcul.

Par contre, si on doit parcourir l'ensemble de l'historique, un cumul progressif devrait être beaucoup plus rapide.

**staff88** · 26/08/2015, 11h31

Ce sera pour des rapports mensuels, voir 40 jours.
Je ne connais pas le volume par contre.

**pcaboche** · 26/08/2015, 11h46

Envoyé par staff88

Ce sera pour des rapports mensuels, voir 40 jours.
Je ne connais pas le volume par contre.

Commence par le cumul progressif, ça devrait aller.

Je fais juste la remarque au cas où quelqu'un voudrait appliquer cela sur un historique très long (devoir faire un cumul sur plusieurs années juste pour avoir une valeur sur une période donnée, ça ne paraît pas très efficace). Mais bon, comme de moins en moins de personnes font de recherches sur internet avant de poster, il y a peu de chances que cela serve à quelqu'un...

**staff88** · 26/08/2015, 12h11

Envoyé par StringBuilder

Ah oui, effectivement, je n'avais pas fait attention à la jointure suppélementaire entre les "événements" et les sessions.
En effet, ça doit marcher aussi.
A voir laquelle est la plus performante sur un gros volume

Voici un exemple complet avec cumul progressif, sans la table des minutes ni la CTE récursive.

Code sql :

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
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

Sortie :

Code :

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

1
2
3
4
5
6
7
8
 
id          nom              maximum
----------- ---------------- -----------
2           Présentation C#  2
3           Présentation PHP 4
1           Présentation SQL 4
 
(3 row(s) affected)

Je suis en train de tester ta solution, mais il semblerait que SQLServer 2008 ne permette pas 'ORDER BY' dans la fonction 'OVER' (ta ligne 52)
Comment puis-je palier cette contrainte ?

**StringBuilder** · 26/08/2015, 12h50

Euh... Utilise la méthode de pcaboche du coup

SQL Server 2008 R2 ne support pas le ORDER BY dans les fonctions de fenêtrage d'aggrégation

**staff88** · 26/08/2015, 17h26

Envoyé par pcaboche

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

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

**pcaboche** · 26/08/2015, 17h36

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

Compter un nombre maximum de connexions simultanées sur une plage horaire

Développement SQL Server

Discussions similaires

Partager

Partager