sql-server - 如果值几乎相同，如何按日期时间对行进行分组？

Question

如果某个字段与其他行中的其他字段之间的时间差几乎相同，如何对行进行分组？

例如:

GUID - No    - sDateTime            - eDateTime           - Name  
------------------------------------------------------------------    
0000 - 01    - 2013-02-02 08:00:00  - 2013-02-02 08:40:00 - A 
0000 - 02    - 2013-02-02 08:45:00  - 2013-02-02 09:45:00 - A 
0000 - 03    - 2013-02-02 11:30:00  - 2013-02-02 12:00:00 - A 
0000 - 04    - 2013-02-02 09:55:00  - 2013-02-02 11:00:00 - A   
0000 - 05    - 2013-02-02 11:05:00  - 2013-02-02 12:30:00 - B   

如果差异不超过 10 分钟，如何从行中获取 sDateTime 与其他行中的 eDateTime ？

GUID - No      - SDateTime            - EDateTime (From other row) - Name  
----------------------------------------------------------------------------  
0000 -01,02,04 - 2013-02-02 08:00:00  - **2013-02-02 11:00:00**    - A 
0000 - 03      - 2013-02-02 11:30:00  - 2013-02-02 12:00:00        - A
0000 - 05      - 2013-02-02 11:05:00  - 2013-02-02 12:30:00        - B 

Answer 1

这里没有游标，但这仍然是使用递归 CTE 来确定哪些行应该分组在一起的行循环。临时表#T 用于保存将行相互连接的ID。它可以在没有临时表的情况下完成，但出于性能原因，最好使用临时表来完成，而不是使用另一个 CTE 作为递归 CTE 的源。

在最终查询中，我使用 for xml path 技巧来连接 No 中的值。这意味着 CTE C 被第二次使用，因此它将被执行两次。您可能还想将该 CTE 转换为临时表以避免这种情况。

SQL Fiddle

MS SQL Server 2008 架构设置:

create table YourTable
(
  GUID varchar(4),
  No varchar(2),
  sDateTime datetime,
  eDateTime datetime,
  Name varchar(1)
);

insert into YourTable values
('0000', '01', '2013-02-02 08:00:00', '2013-02-02 08:40:00', 'A'),
('0000', '02', '2013-02-02 08:45:00', '2013-02-02 09:45:00', 'A'),
('0000', '03', '2013-02-02 11:30:00', '2013-02-02 12:00:00', 'A'),
('0000', '04', '2013-02-02 09:55:00', '2013-02-02 11:00:00', 'A'),
('0000', '05', '2013-02-02 11:05:00', '2013-02-02 12:30:00', 'B');

查询 1:

create table #T
(
  ID int,
  GUID varchar(4),
  No varchar(2),
  sDateTime datetime,
  eDateTime datetime,
  Name varchar(1),
  primary key(ID, GUID, Name)
);

insert into #T(ID, GUID, No, sDateTime, eDateTime, Name)
select row_number() over(partition by GUID, Name order by sDateTime),
       GUID, No, sDateTime, eDateTime, Name
from YourTable;

with C as
(
  select T.ID, T.GUID, T.No, T.sDateTime, T.eDateTime, T.Name, 1 as Grp
  from #T as T
  where T.ID = 1
  union all
  select T.ID, T.GUID, T.No, T.sDateTime, T.eDateTime, T.Name, 
         C.Grp + case when datediff(minute, C.eDateTime, T.sDateTime) > 10 
                   then 1 
                   else 0 
                 end
  from #T as T
    inner join C 
      on T.ID = C.ID + 1 and
         T.Name = C.Name and
         T.GUID = C.GUID
)
select C.GUID,
       (
       select ','+C2.No
       from C as C2
       where C.GUID = C2.GUID and
             C.Name = C2.Name and
             C.Grp = C2.Grp
       order by C2.No
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as No,
       min(C.sDateTime) as sDateTime,
       max(C.eDateTime) as eDateTime,
       C.Name
from C
group by C.GUID, C.Name, C.Grp

drop table #T;

<强> Results :

| GUID |       NO |                       SDATETIME |                       EDATETIME | NAME |
----------------------------------------------------------------------------------------------
| 0000 | 01,02,04 | February, 02 2013 08:00:00+0000 | February, 02 2013 11:00:00+0000 |    A |
| 0000 |       03 | February, 02 2013 11:30:00+0000 | February, 02 2013 12:00:00+0000 |    A |
| 0000 |       05 | February, 02 2013 11:05:00+0000 | February, 02 2013 12:30:00+0000 |    B |

只是为了好玩，SQL Server 2012 版本。

with CMinDiff as
(
  select GUID, No, sDateTime, eDateTime, Name, 
         case when datediff(minute, 
                            coalesce(lag(eDateTime) over(partition by GUID, Name 
                                                         order by eDateTime), 
                                     sDateTime), 
                            sDateTime) <= 10 
           then 0 
           else 1 
         end as MinDiff
  from YourTable
), CSumMinDiff as
(
  select GUID, No, sDateTime, eDateTime, Name, 
         sum(MinDiff) over(partition by GUID, Name 
                           order by sDateTime 
                           rows between unbounded preceding and current row) as Grp
  from CMinDiff
)
select C.GUID,
       (
       select ','+C2.No
       from CSumMinDiff as C2
       where C.GUID = C2.GUID and
             C.Name = C2.Name and
             C.Grp = C2.Grp
       order by C2.No
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as No,
       min(C.sDateTime) as sDateTime,
       max(C.eDateTime) as eDateTime,
       C.Name
from CSumMinDiff as C
group by C.GUID, C.Name, C.Grp