sql-server - SQL 按日期分组

Question

我已经阅读了一些关于按顺序分组的主题，这几乎是我所需要的，但我找不到解决我的问题的方法。

我有一个这样的表:

PlanificatorPozitieID JalonID     DataStart               DataFinal               
--------------------- ----------- ----------------------- ----------------------- 
26                    46          2012-05-21 00:00:00.000 2012-05-31 00:00:00.000 
28                    48          2012-06-01 00:00:00.000 2012-06-01 00:00:00.000 
27                    60          2012-06-02 00:00:00.000 2012-06-02 00:00:00.000 
29                    60          2012-06-07 00:00:00.000 2012-06-08 00:00:00.000 
37                    60          2012-06-08 00:00:00.000 2012-06-10 00:00:00.000 
30                    65          2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 
31                    65          2012-06-18 00:00:00.000 2012-06-24 00:00:00.000 
32                    65          2012-06-23 00:00:00.000 2012-07-01 00:00:00.000 
33                    66          2012-07-02 00:00:00.000 2012-07-02 00:00:00.000 
34                    66          2012-07-02 00:00:00.000 2012-07-05 00:00:00.000 
36                    66          2012-07-06 00:00:00.000 2012-07-10 00:00:00.000 


Desired output:

PlanificatorPozitieID JalonID     DataStart               DataFinal               
--------------------- ----------- ----------------------- ----------------------- 
26                    46          2012-05-21 00:00:00.000 2012-05-31 00:00:00.000 
28                    48          2012-06-01 00:00:00.000 2012-06-01 00:00:00.000 
27                    60          2012-06-02 00:00:00.000 2012-06-02 00:00:00.000 
29                    60          2012-06-07 00:00:00.000 2012-06-10 00:00:00.000 
30                    65          2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 
31                    65          2012-06-18 00:00:00.000 2012-07-01 00:00:00.000 
33                    66          2012-07-02 00:00:00.000 2012-07-05 00:00:00.000 
36                    66          2012-07-06 00:00:00.000 2012-07-10 00:00:00.000 

所以我必须按 JalonID 进行分组，但只有在 DataFinal >= DataStart 时才应进行分组。我想获取每个 JalonID 的时间段，但我只想获取没有暂停时间的时间段。

回家我说清楚了。

select MIN(pp.DataStart) as DataStart, MAX(pp.DataFinal) as DataFinal, pp.JalonID FROM #PlanPozitii pp
GROUP BY pp.JalonID 

但是这个查询不满足我按连续时间段分组的条件。

至于澄清。举个例子

30                    65          2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 
31                    65          2012-06-18 00:00:00.000 2012-06-24 00:00:00.000 
32                    65          2012-06-23 00:00:00.000 2012-07-01 00:00:00.000

2012-06-13 00:00:00.000 <2012-06-18 00:00:00.000 所以在 PlanificatorPozitieID 30 之间不会发生分组 和 31。但是 2012-06-24 00:00:00.000 > 2012-06-23 00:00:00.000 所以现在 PlanificatorPozitieID 31< 之间会有一个组 和 32。

所以从这 3 行中我们将得到两行。

30                    65          2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 
31                    65          2012-06-18 00:00:00.000 2012-07-01 00:00:00.000 





DECLARE @YourTable TABLE(PlanificatorPozitieID INT, JalonID INT,DataStart DATETIME, DataFinal DATETIME)
INSERT INTO @YourTable VALUES
(39,1223,'2015-02-16 00:00:00.000','2015-02-20 00:00:00.000'),
(43,1223,'2015-02-19 00:00:00.000','2015-02-24 00:00:00.000'),
(40,1223,'2015-02-23 00:00:00.000','2015-02-27 00:00:00.000'),
(42,1223,'2015-03-09 00:00:00.000','2015-03-13 00:00:00.000')
;WITH cte AS
(
SELECT  a.PlanificatorPozitieID, 
        a.JalonID, 
        a.DataStart, 
        COALESCE(b.DataFinal,a.datafinal) AS [DataFinal],
        ROW_NUMBER() OVER (PARTITION BY  a.JalonID ORDER BY DATEDIFF(dd,a.datastart, COALESCE(b.DataFinal,a.datafinal))) [rn],
        COUNT(*) OVER (PARTITION BY  a.JalonID) [cnt]
FROM    @YourTable a
        LEFT JOIN  @YourTable b 
        ON  a.JalonID = b.JalonID AND 
            b.DataStart BETWEEN a.DataStart AND a.DataFinal AND  
            a.PlanificatorPozitieID <> b.PlanificatorPozitieID AND 
            DATEDIFF(dd,a.DataStart,a.DataFinal) < DATEDIFF(dd,a.DataStart,b.DataFinal)
)
SELECT * 
FROM   cte 
WHERE  rn= 1 OR rn=cnt

结果:

PlanificatorPozitieID JalonID     DataStart               DataFinal               rn                   cnt
--------------------- ----------- ----------------------- ----------------------- -------------------- -----------
40                    1223        2015-02-23 00:00:00.000 2015-02-27 00:00:00.000 1                    4
43                    1223        2015-02-19 00:00:00.000 2015-02-27 00:00:00.000 4                    4

预期结果:

PlanificatorPozitieID JalonID     DataStart               DataFinal               
--------------------- ----------- ----------------------- ----------------------- 
39                    1223        2015-02-16 00:00:00.000 2015-02-27 00:00:00.000 
42                    1223        2015-03-09 00:00:00.000 2015-03-13 00:00:00.000 

Answer 1

我不知道它是否适用于实际数据，因为我没有对其进行严格测试，但这是一个解决方案:

DECLARE @YourTable TABLE(PlanificatorPozitieID INT, JalonID INT,DataStart DATETIME, DataFinal DATETIME)
INSERT INTO @YourTable VALUES
(26,46,'2012-05-21 00:00:00.000','2012-05-31 00:00:00.000'), 
(28,48,'2012-06-01 00:00:00.000','2012-06-01 00:00:00.000'), 
(27,60,'2012-06-02 00:00:00.000','2012-06-02 00:00:00.000'), 
(29,60,'2012-06-07 00:00:00.000','2012-06-08 00:00:00.000'), 
(37,60,'2012-06-08 00:00:00.000','2012-06-10 00:00:00.000'), 
(30,65,'2012-06-10 00:00:00.000','2012-06-13 00:00:00.000'), 
(31,65,'2012-06-18 00:00:00.000','2012-06-24 00:00:00.000'), 
(32,65,'2012-06-23 00:00:00.000','2012-07-01 00:00:00.000'), 
(33,66,'2012-07-02 00:00:00.000','2012-07-02 00:00:00.000'), 
(34,66,'2012-07-02 00:00:00.000','2012-07-05 00:00:00.000'), 
(36,66,'2012-07-06 00:00:00.000','2012-07-10 00:00:00.000')

;WITH cte AS
(
SELECT  a.PlanificatorPozitieID, 
        a.JalonID, 
        a.DataStart, 
        COALESCE(b.DataFinal,a.datafinal) AS [DataFinal],
        ROW_NUMBER() OVER (PARTITION BY  a.JalonID ORDER BY DATEDIFF(dd,a.datastart, COALESCE(b.DataFinal,a.datafinal))) [rn],
        COUNT(*) OVER (PARTITION BY  a.JalonID) [cnt]
FROM    @YourTable a
        LEFT JOIN  @YourTable b 
        ON  a.JalonID = b.JalonID AND 
            b.DataStart BETWEEN a.DataStart AND a.DataFinal AND  
            a.PlanificatorPozitieID <> b.PlanificatorPozitieID AND 
            DATEDIFF(dd,a.DataStart,a.DataFinal) < DATEDIFF(dd,a.DataStart,b.DataFinal)
)
SELECT * 
FROM   cte 
WHERE  rn= 1 OR rn=cnt