SQL Server
J'ai une table avec 2 timbres horaires, Time_Start et Time_end.
E.g. et je voudrais les regrouper afin que je puisse avoir le nombre de lignes regroupées par un intervalle de temps variable.
par exemple Mon intervalle pourrait être 1 heure, 1 minute, 30 minutes, 1 jour, etc ... Pensez-y en tant que journal Dans la situation de déconnexion et de déconnexion, et je souhaite voir comment les utilisateurs peuvent être enregistrés à une minute, heure, jour, etc ...
3 Réponses :
Essayez ceci,
DECLARE @start_date datetime='2019-01-01',
@end_date datetime='2019-01-02',
@i_minutes int=60
DECLARE @t TABLE
(
id int identity(1,1),time_start datetime,time_end datetime
)
INSERT INTO @t(time_start,time_end)VALUES
('2019-01-01 08:30:00','2019-01-01 09:40:00'),
('2019-01-01 09:10:24','2019-01-01 15:14:19'),
('2019-01-01 09:21:15','2019-01-01 09:21:19'),
('2019-01-01 10:39:45','2019-01-01 10:58:12'),
('2019-01-01 11:39:45','2019-01-01 11:40:10')
--SELECT @start_date=min(time_start),@end_date=max(time_end)
--FROM @t
;WITH CTE_time_Interval AS
(
SELECT @start_date AS time_int,@i_minutes AS i_minutes
UNION ALL
SELECT dateadd(minute,@i_minutes,time_int),i_minutes+ @i_minutes
FROM CTE_time_Interval
WHERE time_int<=@end_date
)
,CTE1 AS
(
SELECT ROW_NUMBER()OVER(ORDER BY time_int)AS r_no,time_int
FROM CTE_time_Interval
)
,CTE2 AS
(
SELECT a.time_int AS Int_start_time,b.time_int AS Int_end_time
FROM CTE1 a
INNER JOIN CTE1 b ON a.r_no+1=b.r_no
)
SELECT a.Int_start_time,a.Int_end_time,sum(iif(b.time_start is not null,1,0)) AS cnt
FROM CTE2 a
LEFT JOIN @t b ON
(
b.time_start BETWEEN a.Int_start_time AND a.Int_end_time
OR
b.time_end BETWEEN a.Int_start_time AND a.Int_end_time
OR
a.Int_start_time BETWEEN b.time_start AND b.time_end
OR
a.Int_end_time BETWEEN b.time_start AND b.time_end
)
GROUP BY a.Int_start_time,a.Int_end_time
Dans votre solution, vous n'avez pas le nombre d'utilisateurs connectés à un long intervalle. Par exemple, il existe un utilisateur qui vous connecte à 09:10:24 et vous déconnectez à 15:14:19, vous devez donc compter cet utilisateur à 9h00, 10h00, 11h00, 12:00, 13 : 00, 14h00 et 15h00
@ Víctorbeltrán, j'ai apporté des changements, maintenant ça marche
J'ai fini par utiliser cette version. Merci.
Salut c'est ma solution de contournement.
J'ai créé une table "Test" avec vos données.
D'abord, je reçois les intervalles min et max et après, je reçois tous les intervalles entre ces valeurs avec un CTE. Enfin, avec ce CTE et une jointure gauche avec les intervalles entre Time_Start et Time_end j'ai eu la réponse. P>
Ceci est pour intervalles de 1 heure p>
+---------------------+-----------+ | time_interval | row_count | +---------------------+-----------+ | 01/01/2019 08:00:00 | 1 | | 01/01/2019 09:00:00 | 3 | | 01/01/2019 10:00:00 | 2 | | 01/01/2019 11:00:00 | 2 | | 01/01/2019 12:00:00 | 1 | | 01/01/2019 13:00:00 | 1 | | 01/01/2019 14:00:00 | 1 | | 01/01/2019 15:00:00 | 1 | +---------------------+-----------+
Vous devez spécifier les intervalles de temps. Le reste est un Si vous avez beaucoup de données et de nombreuses périodes de temps, Vous pourriez constater que cela a une mauvaise performance. Si tel est le cas, demandez à une question nouvelle , avec plus d'informations sur le dimensionnement et la performance. rejoindre / groupe par ou sous-requête corrélée:
Rejoignez extérieure une CTE (ou une table) récursive renvoyant les valeurs de temps requises. PAR GROUPE. Fait!
Cela ressemble à un problème de «comptage des intervalles de chevauchement».
Ou un décompte de l'époque, puis les regrouper.