MS SQL Server
  1. MS SQL Server
  3. Groupe de table en intervalles de 15 minutes
Intereting Posts
SSIS 2008 – Comment append des champs de date et d'heure set Entity Framework JOINs lorsque le type de données ne correspond pas Procédure stockée qui renvoie une table différente en fonction du paramètre sp_help_jobhistory avec l'erreur RESULT SETS Les requêtes Ef Linq ont expiré, mais les mêmes requêtes durent less d'une seconde sur SSMS Erreur de syntaxe dans sql causée par des données NULL Classe de requête paramétrée appropriée pour le server asp.net – c # application – sql Suppression des duplicates et combinaison des données d'une table dans une requête SQL Comment InsertOrSelect avec Entity Framework Besoin d'aide pour écrire le file bat qui exécute les scripts sql (sql server 2008 et 3 autres files.? Oracle vs SQL Server pour un projet .NET SQL Server | Ma procédure stockée est-elle OK? Erreur de server SQL: L'instruction INSERT est en conflit avec la contrainte CHECK Instructions SET par défaut lors de la création / manipulation avec des index? Comment créer un triggersur de database avec ADO.NET

Groupe de table en intervalles de 15 minutes

T-SQL, SQL Server 2008 et plus

Étant donné un exemple de tableau de

StatusSetDateTime | ID user | Statut | StatusEndDateTime | StatusDuration (en secondes) ========================================== =============================== 2012-01-01 12:00:00 | myID | Disponible | 2012-01-01 13:00:00 | 3600

Je dois décomposer cela en une vue qui utilise des intervalles de 15 minutes, par exemple:

IntervalStart | ID user | Statut | Durée

=====================================

2012-01-01 12:00:00 | myID | Disponible | 900

2012-01-01 12:15:00 | myID | Disponible | 900

2012-01-01 12:30:00 | myID | Disponible | 900

2012-01-01 12:45:00 | myID | Disponible | 900

2012-01-01 13:00:00 | myID | Disponible | 0

etc….

Maintenant, j'ai pu faire des searchs et find des requêtes qui vont tomber en panne. J'ai trouvé quelque chose de similaire pour MySql ici :

Et quelque chose pour T-SQL ici

Mais sur le second exemple, ils additionnent les résultats alors que je dois split la durée totale par l'intervalle de time (900 secondes) par user par statut.

J'ai été capable d'adapter les exemples dans le second lien pour split tout en intervalles, mais la durée totale est returnnée et je n'arrive pas à comprendre comment split les durées d'Intervalle (et encore résumer à la durée totale d'origine).

Merci d'avance pour toute idée!

edit: Première tentative 

;with cte as (select MIN(StatusDateTime) as MinDate , MAX(StatusDateTime) as MaxDate , convert(varchar(14),StatusDateTime, 120) as StartDate , DATEPART(minute, StatusDateTime) /15 as GroupID , UserID , StatusKey , avg(StateDuration) as AvgAmount from AgentActivityLog group by convert(varchar(14),StatusDateTime, 120) , DATEPART(minute, StatusDateTime) /15 , Userid,StatusKey) select dateadd(minute, 15*GroupID, CONVERT(datetime,StartDate+'00')) as [Start Date] , UserID, StatusKey, AvgAmount as [Average Amount] from cte

edit: Deuxième tentative 

 ;With cte As (Select DateAdd(minute , 15 * (DateDiff(minute, '20000101', StatusDateTime) / 15) , '20000101') As StatusDateTime , userid, statuskey, StateDuration From AgentActivityLog) Select StatusDateTime, userid,statuskey,Avg(StateDuration) From cte Group By StatusDateTime,userid,statuskey; 

 ;with cte_max as ( select dateadd(mi, -15, max(StatusEndDateTime)) as EndTime, min(StatusSetDateTime) as StartTime from AgentActivityLog ), times as ( select StartTime as Time from cte_max union all select dateadd(mi, 15, c.Time) from times as c cross join cte_max as cm where c.Time <= cm.EndTime ) select t.Time, A.UserID, A.Status, case when t.Time = A.StatusEndDateTime then 0 else A.StatusDuration / (count(*) over (partition by A.StatusSetDateTime, A.UserID, A.Status) - 1) end as Duration from AgentActivityLog as A left outer join times as t on t.Time >= A.StatusSetDateTime and t.Time <= A.StatusEndDateTime

sql fiddle démo

Je n'ai jamais été à l'aise avec l'utilisation des maths de date pour split les choses en partitions. Il semble qu'il y ait toutes sortes d'embûches à tomber.

Ce que je préfère faire est de créer une table (fonction prédéfinie, table-évalué, variable de table) où il y a une rangée pour chaque gamme de partition de date. L'approche de la fonction table est particulièrement utile car vous pouvez la build pour des plages arbitraires et des tailles de partition selon vos besoins. Ensuite, vous pouvez vous joindre à cette table pour split les choses. 

 paritionid starttime endtime ---------- ------------- ------------- 1 8/1/2012 5:00 8/1/2012 5:15 2 8/1/2012 5:15 8/1/2012 5:30 ...

Je ne peux pas parler de la performance de cette méthode, mais je trouve que les requêtes sont beaucoup plus intuitives.

C'est relativement simple si vous avez une table d'aide avec chaque horodatage de 15 minutes, que vous joignez à votre table de base via BETWEEN. Vous pouvez créer la table d'aide à la volée ou la conserver en permanence dans votre database. Simple pour le prochain type de votre entreprise à comprendre aussi: 

 // declare a table and a timestamp variable declare @timetbl table(t datetime) declare @t datetime // set the first timestamp set @t = '2012-01-01 00:00:00' // set the last timestamp, can easily be extended to cover many years while @t <= '2013-01-01' begin // populate the table with a new row, every 15 minutes insert into @timetbl values (@t) set @t = dateadd(mi, 15, @t) end // now the Select query: select tt.t, aal.UserID, aal.Status, case when aal.StatusEndDateTime <= tt.t then 0 else 900 end as Duration // using a shortcut for Duration, based on your comment that Start/End are always on the quarter-hour, and thus always 900 seconds or zero from @timetbl tt INNER JOIN AgentActivityLog aal on tt.t between aal.StatusSetDateTime and aal.StatusEndDateTime order by aal.UserID, tt.t

Vous pouvez utiliser une expression de table commune récursive , où vous continuez d'append votre durée alors que StatusEndDateTime est supérieur à IntervalStart, par exemple 

 ;with cte as ( select StatusSetDateTime as IntervalStart ,UserID ,Status ,StatusDuration/(datediff(mi, StatusSetDateTime, StatusEndDateTime)/15) as Duration , StatusEndDateTime From AgentActivityLog Union all Select DATEADD(ss, Duration, IntervalStart) as IntervalStart , UserID , Status , case when DATEADD(ss, Duration, IntervalStart) = StatusEndDateTime then 0 else Duration end as Duration , StatusEndDateTime From cte Where IntervalStart < StatusEndDateTime ) select IntervalStart, UserID, Status, Duration from cte

Voici une requête qui fera le travail pour vous sans nécessiter de tables auxiliaires. (Je n'ai rien contre les tables auxiliaires, elles sont utiles et je les utilise.Il est également possible de ne pas les utiliser parfois.) Cette requête permet de démarrer et de terminer les activités à tout moment, même si pas toutes les minutes se terminant par: 00, : 15,: 30,: 45. S'il y aura des portions de millisecondes, alors vous devrez faire des expériences car, suivant votre model, je ne suis passé qu'à la deuxième résolution.

Si vous avez une durée maximale maximale connue, supprimez @MaxDuration et remplacez-la par cette valeur, en minutes. N <= @MaxDuration est crucial pour que la requête fonctionne bien. 

 DECLARE @MaxDuration int; SET @MaxDuration = (SELECT Max(StatusDuration) / 60 FROM #AgentActivityLog); WITH L0 AS(SELECT 1 c UNION ALL SELECT 1), L1 AS(SELECT 1 c FROM L0, L0 B), L2 AS(SELECT 1 c FROM L1, L1 B), L3 AS(SELECT 1 c FROM L2, L2 B), L4 AS(SELECT 1 c FROM L3, L3 B), L5 AS(SELECT 1 c FROM L4, L4 B), Nums AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) n FROM L5) SELECT S.IntervalStart, Duration = DateDiff(second, S.IntervalStart, E.IntervalEnd) FROM #AgentActivityLog L CROSS APPLY ( SELECT N, Offset = (NN - 1) * 900 FROM Nums N WHERE N <= @MaxDuration ) N CROSS APPLY ( SELECT Edge = DateAdd(second, N.Offset, DateAdd(minute, DateDiff(minute, '20000101', L.StatusSetDateTime) / 15 * 15, '20000101') ) ) G CROSS APPLY ( SELECT IntervalStart = Max(T.BeginTime) FROM ( SELECT L.StatusSetDateTime UNION ALL SELECT G.Edge ) T (BeginTime) ) S CROSS APPLY ( SELECT IntervalEnd = Min(T.EndTime) FROM ( SELECT L.StatusEndDateTime UNION ALL SELECT G.Edge + '00:15:00' ) T (EndTime) ) E WHERE N.Offset <= L.StatusDuration ORDER BY L.StatusSetDateTime, S.IntervalStart;

Voici le script de configuration si vous voulez l'essayer: 

 CREATE TABLE #AgentActivityLog ( StatusSetDateTime datetime, StatusEndDateTime datetime, StatusDuration AS (DateDiff(second, 0, StatusEndDateTime - StatusSetDateTime)) ); INSERT #AgentActivityLog -- weird end times SELECT '20120101 12:00:00', '20120101 13:00:00' UNION ALL SELECT '20120101 13:00:00', '20120101 13:27:56' UNION ALL SELECT '20120101 13:27:56', '20120101 13:28:52' UNION ALL SELECT '20120101 13:28:52', '20120120 11:00:00' INSERT #AgentActivityLog -- 15-minute quantized end times SELECT '20120101 12:00:00', '20120101 13:00:00' UNION ALL SELECT '20120101 13:00:00', '20120101 13:30:00' UNION ALL SELECT '20120101 13:30:00', '20120101 14:00:00' UNION ALL SELECT '20120101 14:00:00', '20120120 11:00:00'

En outre, voici une version qui n'attend que les heures qui ont des minutes entières se terminant par: 00,: 15,: 30 ou: 45. 

 DECLARE @MaxDuration int; SET @MaxDuration = (SELECT Max(StatusDuration) / 60 FROM #AgentActivityLog); WITH L0 AS(SELECT 1 c UNION ALL SELECT 1), L1 AS(SELECT 1 c FROM L0, L0 B), L2 AS(SELECT 1 c FROM L1, L1 B), L3 AS(SELECT 1 c FROM L2, L2 B), L4 AS(SELECT 1 c FROM L3, L3 B), L5 AS(SELECT 1 c FROM L4, L4 B), Nums AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) n FROM L5) SELECT S.IntervalStart, Duration = CASE WHEN Offset = StatusDuration THEN 0 ELSE 900 END FROM #AgentActivityLog L CROSS APPLY ( SELECT N, Offset = (NN - 1) * 900 FROM Nums N WHERE N <= @MaxDuration ) N CROSS APPLY ( SELECT IntervalStart = DateAdd(second, N.Offset, L.StatusSetDateTime) ) S WHERE N.Offset <= L.StatusDuration ORDER BY L.StatusSetDateTime, S.IntervalStart;

Il semble vraiment que la dernière ligne de la durée 0 n'est pas correcte, parce que vous ne pouvez pas simplement ordonner par IntervalStart car il y a des valeurs IntervalStart en double. Quel est l'avantage d'avoir des lignes qui ajoutent 0 au total?