MS SQL Server
  1. MS SQL Server
  3. Puis-je utiliser un CTE SQL Server pour merge des dates croisées?
Intereting Posts
Erreur "… La source OLE DB.Outputs .Columns sur la sortie sans erreur n'a pas de sortie correspondante Obtenir plusieurs lignes à partir d'une list d'ID Comment puis-je réinitialiser le nom d'user et le mot de passe pour SQL Server 2008 SQL Server: une instruction UPDATE est-elle plus rapide si la table a des index? Compter les secondes sur l'intervalle de commutation SQL Server fonction définie par l'user (TSQL) colonne persistante non déterministe et calculée Puis-je raccourcir cette requête SQL? Comment get le path relatif du file en mode SQLCMD dans SSMS? comment écrire une requête sql pour calculer le time différent Sql Server 2008 Replicate Synonym? SQL Server: conventions pour nommer un schéma TSQL: NULL dans la clause "IN" Vérifier si une valeur existe dans une collection stockée dans une colonne de type de données XML Comment fonctionne un index sur un type défini par l'user SQL (UDT)? Table SQL lignes vers colonnes – PIVOT possible?

Puis-je utiliser un CTE SQL Server pour merge des dates croisées?

J'écris une application qui gère le time de planification pour certains de nos employés. Dans le cadre de cela, j'ai besoin de calculer combien de minutes tout au long de la journée qu'ils ont demandé.

Dans la première version de cet outil, nous avons refusé les requests d'expiration qui se chevauchaient, car nous voulions pouvoir append le total de StartTime less EndTime pour toutes les requests. Empêcher les chevauchements rend ce calcul très rapide.

Ceci est devenu problématique, car les gestionnaires veulent maintenant programmer des réunions d'équipe mais ne peuvent pas le faire quand quelqu'un a déjà demandé le jour de congé.

Ainsi, dans la nouvelle version de l'outil, nous avons besoin de permettre des requests qui se chevauchent.

Voici un exemple de données comme ce que nous avons: 

 UserId | StartDate | EndDate ---------------------------- 1 | 2:00 | 4:00 1 | 3:00 | 5:00 1 | 3:45 | 9:00 2 | 6:00 | 9:00 2 | 7:00 | 8:00 3 | 2:00 | 3:00 3 | 4:00 | 5:00 4 | 1:00 | 7:00

Le résultat que je dois get, aussi efficacement que possible, est ceci: 

 UserId | StartDate | EndDate ---------------------------- 1 | 2:00 | 9:00 2 | 6:00 | 9:00 3 | 2:00 | 3:00 3 | 4:00 | 5:00 4 | 1:00 | 7:00

Nous pouvons facilement détecter les chevauchements avec cette requête: 

 select * from requests r1 cross join requests r2 where r1.RequestId < r2.RequestId and r1.StartTime < r2.EndTime and r2.StartTime < r1.EndTime

C'est, en fait, comment nous détectons et prévenons les problèmes à l'origine.

Maintenant, nous essayons de merge les éléments qui se chevauchent, mais j'atteins les limites de mes compétences de ninja SQL.

Il ne serait pas trop difficile de find une méthode utilisant des tables temporaires, mais nous voulons éviter cela dans la mesure du possible.

Existe-t-il une méthode basée sur un set pour merge des lignes qui se chevauchent?

Modifier:

Il serait également acceptable que toutes les lignes apparaissent, à condition qu'elles soient effondrées en leur time. Par exemple, si quelqu'un veut passer de trois heures à cinq heures, et de quatre heures à six heures, il serait acceptable qu'il ait deux rangées, une de trois heures à cinq heures, une autre de cinq heures à six heures, une autre de trois heures prochaine de quatre à six.

Aussi, voici un petit banc d'essai: 

 DECLARE @requests TABLE ( UserId int, StartDate time, EndDate time ) INSERT INTO @requests (UserId, StartDate, EndDate) VALUES (1, '2:00', '4:00'), (1, '3:00', '5:00'), (1, '3:45', '9:00'), (2, '6:00', '9:00'), (2, '7:00', '8:00'), (3, '2:00', '3:00'), (3, '4:00', '5:00'), (4, '1:00', '7:00');

Ok, il est possible de faire avec des CTE. Je ne savais pas comment les utiliser au début de la nuit, mais voici les résultats de mes searchs:

Un CTE récursif comporte deux parties, l'instruction "anchor" et les instructions "récursives".

La partie cruciale de l'instruction récursive est que lorsqu'elle est évaluée, seules les lignes qui n'ont pas encore été évaluées apparaîtront dans la récursivité.

Ainsi, par exemple, si nous voulions utiliser des CTE pour get une list exhaustive de fois pour ces users, nous pourrions utiliser quelque chose comme ceci: 

 WITH sorted_requests as ( SELECT UserId, StartDate, EndDate, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY StartDate, EndDate DESC) Instance FROM @requests ), no_overlap(UserId, StartDate, EndDate, Instance) as ( SELECT * FROM sorted_requests WHERE Instance = 1 UNION ALL SELECT s.* FROM sorted_requests s INNER JOIN no_overlap n ON s.UserId = n.UserId AND s.Instance = n.Instance + 1 ) SELECT * FROM no_overlap

Ici, l'instruction "anchor" n'est que la première instance de chaque user, WHERE Instance = 1 .

L'instruction "récursive" joint chaque ligne à la ligne suivante de l'set, en utilisant le s.UserId = n.UserId AND s.Instance = n.Instance + 1

Maintenant, nous pouvons utiliser la propriété des données, lorsqu'elles sont sortingées par date de début, que toute ligne qui se chevauche aura une date de début inférieure à la date de fin de la ligne précédente. Si nous continuons à propager le numéro de ligne de la première ligne entrecroisée, chaque ligne qui se chevauchera ultérieurement partagera ce numéro de ligne.

En utilisant cette requête: 

 WITH sorted_requests as ( SELECT UserId, StartDate, EndDate, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY StartDate, EndDate DESC) Instance FROM @requests ), no_overlap(UserId, StartDate, EndDate, Instance, ConnectedGroup) as ( SELECT UserId, StartDate, EndDate, Instance, Instance as ConnectedGroup FROM sorted_requests WHERE Instance = 1 UNION ALL SELECT s.UserId, s.StartDate, CASE WHEN n.EndDate >= s.EndDate THEN n.EndDate ELSE s.EndDate END EndDate, s.Instance, CASE WHEN n.EndDate >= s.StartDate THEN n.ConnectedGroup ELSE s.Instance END ConnectedGroup FROM sorted_requests s INNER JOIN no_overlap n ON s.UserId = n.UserId AND s.Instance = n.Instance + 1 ) SELECT UserId, MIN(StartDate) StartDate, MAX(EndDate) EndDate FROM no_overlap GROUP BY UserId, ConnectedGroup ORDER BY UserId

Nous nous regroupons par la "première ligne entrecroisée" mentionnée ci-dessus (appelée ConnectedGroup dans cette requête) et trouvons l'heure de début minimum et l'heure de fin maximum dans ce groupe.

La première ligne croisée est propagée en utilisant cette instruction: 

 CASE WHEN n.EndDate >= s.StartDate THEN n.ConnectedGroup ELSE s.Instance END ConnectedGroup

Ce qui dit essentiellement, "si cette ligne croise la ligne précédente (en fonction de notre sorting par date de début), considérez cette ligne comme ayant le même" groupement de lignes "que la ligne précédente, sinon, utilisez le numéro de ligne de cette ligne le «groupement de rangs» pour lui-même.

Cela nous donne exactement ce que nous cherchions.

MODIFIER

Quand je l'avais initialement pensé sur mon tableau blanc, je savais que je devrais avancer la fin de chaque ligne, pour m'assurer qu'elle croiserait la ligne suivante, si l'une des rangées précédentes du groupe connecté se croisait. J'ai accidentellement laissé tomber ça. Cela a été corrigé.

Réécriture complète: 

 ;WITH new_grp AS ( SELECT r1.UserId, r1.StartTime FROM @requests r1 WHERE NOT EXISTS ( SELECT * FROM @requests r2 WHERE r1.UserId = r2.UserId AND r2.StartTime < r1.StartTime AND r2.EndTime >= r1.StartTime) GROUP BY r1.UserId, r1.StartTime -- there can be > 1 ),r AS ( SELECT r.RequestId, r.UserId, r.StartTime, r.EndTime ,count(*) AS grp -- guaranteed to be 1+ FROM @requests r JOIN new_grp n ON n.UserId = r.UserId AND n.StartTime <= r.StartTime GROUP BY r.RequestId, r.UserId, r.StartTime, r.EndTime ) SELECT min(RequestId) AS RequestId ,UserId ,min(StartTime) AS StartTime ,max(EndTime) AS EndTime FROM r GROUP BY UserId, grp ORDER BY UserId, grp

Produit maintenant le résultat demandé et couvre réellement tous les cas possibles, y compris les sous-groupes disjoints et les duplicates. Jetez un oeil aux commentaires sur datatables de test dans la démo de travail à data.SE.

  • CTE 1
    Trouvez les points (uniques!) Dans le time où commence un nouveau groupe d'intervalles qui se chevauchent.

  • CTE 2
    Comptez les débuts du nouveau groupe jusqu'à (et y compris) chaque intervalle individuel, formant ainsi un numéro de groupe unique par user.

  • Dernier SELECT
    Fusionnez les groupes, commencez les débuts et la dernière fin pour les groupes.

J'ai rencontré quelques difficultés, car les fonctions de window T-SQL max() ou sum() n'acceptent pas une clause ORDER BY dans une window. Ils ne peuvent calculer qu'une valeur par partition, ce qui rend impossible le calcul d'une sum / nombre en cours par partition. Travaillerait dans PostgreSQL ou Oracle (mais pas dans MySQL, bien sûr – il n'a ni fonctions de window ni CTE).

La solution finale utilise un CTE supplémentaire et devrait être tout aussi rapide.

Cela fonctionne pour postgres. Microsoft pourrait avoir besoin de quelques modifications. 

 SET search_path='tmp'; DROP TABLE tmp.schedule CASCADE; CREATE TABLE tmp.schedule ( person_id INTEGER NOT NULL , dt_from timestamp with time zone , dt_to timestamp with time zone ); INSERT INTO schedule( person_id, dt_from, dt_to) VALUES ( 1, '2011-12-03 02:00:00' , '2011-12-03 04:00:00' ) , ( 1, '2011-12-03 03:00:00' , '2011-12-03 05:00:00' ) , ( 1, '2011-12-03 03:45:00' , '2011-12-03 09:00:00' ) , ( 2, '2011-12-03 06:00:00' , '2011-12-03 09:00:00' ) , ( 2, '2011-12-03 07:00:00' , '2011-12-03 08:00:00' ) , ( 3, '2011-12-03 02:00:00' , '2011-12-03 03:00:00' ) , ( 3, '2011-12-03 04:00:00' , '2011-12-03 05:00:00' ) , ( 4, '2011-12-03 01:00:00' , '2011-12-03 07:00:00' ); ALTER TABLE schedule ADD PRIMARY KEY (person_id,dt_from) ; CREATE UNIQUE INDEX ON schedule (person_id,dt_to); SELECT * FROM schedule ORDER BY person_id, dt_from; WITH RECURSIVE ztree AS ( -- Terminal part SELECT p1.person_id AS person_id , p1.dt_from AS dt_from , p1.dt_to AS dt_to FROM schedule p1 UNION -- Recursive part SELECT p2.person_id AS person_id , LEAST(p2.dt_from, zzt.dt_from) AS dt_from , GREATEST(p2.dt_to, zzt.dt_to) AS dt_to FROM ztree AS zzt , schedule AS p2 WHERE 1=1 AND p2.person_id = zzt.person_id AND (p2.dt_from < zzt.dt_from AND p2.dt_to >= zzt.dt_from) ) SELECT * FROM ztree zt WHERE NOT EXISTS ( SELECT * FROM ztree nx WHERE nx.person_id = zt.person_id -- the recursive query returns *all possible combinations of -- touching or overlapping intervals -- we'll have to filter, keeping only the biggest ones -- (the ones for which there is no bigger overlapping interval) AND ( (nx.dt_from <= zt.dt_from AND nx.dt_to > zt.dt_to) OR (nx.dt_from < zt.dt_from AND nx.dt_to >= zt.dt_to) ) ) ORDER BY zt.person_id,zt.dt_from ;

Résultat: 

 DROP TABLE CREATE TABLE INSERT 0 8 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "schedule_pkey" for table "schedule" ALTER TABLE CREATE INDEX person_id | dt_from | dt_to -----------+------------------------+------------------------ 1 | 2011-12-03 02:00:00+01 | 2011-12-03 04:00:00+01 1 | 2011-12-03 03:00:00+01 | 2011-12-03 05:00:00+01 1 | 2011-12-03 03:45:00+01 | 2011-12-03 09:00:00+01 2 | 2011-12-03 06:00:00+01 | 2011-12-03 09:00:00+01 2 | 2011-12-03 07:00:00+01 | 2011-12-03 08:00:00+01 3 | 2011-12-03 02:00:00+01 | 2011-12-03 03:00:00+01 3 | 2011-12-03 04:00:00+01 | 2011-12-03 05:00:00+01 4 | 2011-12-03 01:00:00+01 | 2011-12-03 07:00:00+01 (8 rows) person_id | dt_from | dt_to -----------+------------------------+------------------------ 1 | 2011-12-03 02:00:00+01 | 2011-12-03 09:00:00+01 2 | 2011-12-03 06:00:00+01 | 2011-12-03 09:00:00+01 3 | 2011-12-03 02:00:00+01 | 2011-12-03 03:00:00+01 3 | 2011-12-03 04:00:00+01 | 2011-12-03 05:00:00+01 4 | 2011-12-03 01:00:00+01 | 2011-12-03 07:00:00+01 (5 rows)