t-sql - 在特定条件下原始转换为字符串

Question

根据下面提供的代码，我需要实现所需的表:

规则是:

每个 Ticker 返回 ErrorCode 链/簇，其中仅当错误连续发生的时间差小于 10 分钟时才会生成簇。

在其他帖子中，我请求有关该任务的更简单版本的帮助:每天都会单独生成错误链。最好的选择(以非常有效的方式工作)是 STUFF + For XML Path。

我们可以用它来完成这个更复杂的任务吗？或者应该用游标来实现？

如果有任何建议，我将不胜感激。

问候，阿雷克

DECLARE @table1 TABLE
(
    [Ticket] INT,
    [ErrorCode] CHAR(1),
    [Date] DATETIME
);

INSERT INTO @table1
VALUES
(1, 'A', '01.07.2018  10:00:00'),
(1, 'B', '01.07.2018  10:02:00'),
(1, 'C', '01.07.2018  10:08:00'),
(1, 'A', '01.07.2018  10:30:09'),
(1, 'B', '01.07.2018  10:50:00'),
(1, 'D', '01.07.2018  10:55:00'),
(1, 'D', '01.07.2018  15:55:00'),
(1, 'D', '02.07.2018  10:55:00'),
(2, 'A', '20.10.2018  15:00:00'),
(2, 'C', '20.10.2018  17:00:00'),
(2, 'C', '20.10.2018  17:07:00'),
(2, 'A', '21.10.2018  09:00:00');

Answer 1

一种选择是使用条件标志，然后通过窗口函数 sum() 聚合该标志

示例

;with cte as (
                Select * 
                      ,Flg = case when datediff(MINUTE,lag(Date,1) over (Partition by Ticket Order By Date) , Date) > 10  then 1 else 0 end
                 From  @table1 
), cte1 as (
                Select *
                      ,Grp = sum(Flg) over (Partition By Ticket Order by Date)
                 From  cte
)
Select Distinct 
       Grp
      ,Ticket
      ,Cluster =  Stuff((Select '/' +ErrorCode From cte1 Where Ticket=A.Ticket and Grp=A.Grp For XML Path ('')),1,1,'') 
 From  cte1 A
 Order by Ticket,Grp

返回

Grp Ticket  Cluster
0   1       A/B/C
1   1       A
2   1       B/D
3   1       D
4   1       D
0   2       A
1   2       C/C
2   2       A

EDIT - Requested Update

;with cte as (
                Select * 
                      ,Flg = case when datediff(MINUTE,lag(Date,1) over (Partition by Ticket Order By Date) , Date) > 10  then 1 else 0 end
                 From  @table1 
), cte1 as (
                Select *
                      ,Grp = sum(Flg) over (Partition By Ticket Order by Date)
                 From  cte
)
Select Distinct 
       Grp
      ,Ticket
      ,LastDate = convert(date,max(Date) over (Partition By Ticket,Grp))
      ,Times    = Stuff((Select ',' +format(Date,'HH:mm') From cte1 Where Ticket=A.Ticket and Grp=A.Grp For XML Path ('')),1,1,'') 
      ,Cluster  =  Stuff((Select '/' +ErrorCode From cte1 Where Ticket=A.Ticket and Grp=A.Grp For XML Path ('')),1,1,'') 
 From  cte1 A
 Order by Ticket,Grp

返回

EDIT - Tweak Performance

通过将 XML/STUFF 迁移到最终/简化集，您可能会获得更高的性能。字符串聚合一开始就受到了沉重打击。我们只是减少了通话次数。

另一种选择是使用 TEMP 表而不是 CTE。

;with cte as (
                Select * 
                      ,Flg = case when datediff(MINUTE,lag(Date,1) over (Partition by Ticket Order By Date) , Date) > 10  then 1 else 0 end
                 From  @table1 
), cte1 as (
                Select *
                      ,Grp = sum(Flg) over (Partition By Ticket Order by Date)
                 From  cte
), cte2 as (
                Select Distinct 
                       Grp
                      ,Ticket
                      ,LastDate = convert(date,max(Date) over (Partition By Ticket,Grp))
                 From  cte1 A
)
Select *
      ,Times    = Stuff((Select ',' +format(Date,'HH:mm') From cte1 Where Ticket=A.Ticket and Grp=A.Grp For XML Path ('')),1,1,'') 
      ,Cluster  =  Stuff((Select '/' +ErrorCode From cte1 Where Ticket=A.Ticket and Grp=A.Grp For XML Path ('')),1,1,'') 
 From  cte2 A
 Order by Ticket,Grp

EDIT - Step-By-Step

cte 生成注意Flg是1或0

cte1 生成

cte2 生成注意 Grp 是 Fl​​ag 的运行总计