String z wierszy pod określonymi warunkami

Cześć,

chciałbym z danych z kodu poniżej uzyskać takie pogrupowanie:

Ticket ErrorCluster
1 A / B /C
1 A
1 B / D
1 D
1 D
2 A
2 C / C
2 A

Oczekiwany_rezultat.png

Czyli dla każdego Ticketu zwócić ciąg kodów/ErrorCluster, który będzie ciągiem tylko wtedy jeżeli różnica czasowa między kolejnymi wystapieniami błędu będzie mniejsza niż 10 minut.
W innym wątku rozpatrywałem prostszą wersje tego zadania tzn. że ciąg powstaje dla wszystkich kodów z jednego dnia.
Wyszło w praktyce, że najłatwiej można to zrobić przy pomocy STUFF + For XML Path.

Czy w tej sytuacji można to zrobić podobnie (bardzo skutecznie to działało) czy trzeba iść np. w kursory?
Będę wdzięczny za wszelkie sugestie.

Pozdrawiam,
Arek

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');

Sporo pytań zadajesz a ani razu nie napisałeś z której wersji SQL serwera korzystasz.

Wersja: Microsoft SQL Server 2016 (SP1-CU5).

Będę od dziś to wpisywał, dzięki.

Pozdrawiam,
Arek

No tu wystarczy wyznaczyć początki przedziałów, a to będą wszystkie te rekordy (w ramach ticket), które poprzedzający error mają wcześniej o 10 lub więcej minut albo są pierwsze, jak to wiemy to łatwo sprawdzić granicę końca, czyli następny początek minus 1, na tej podstawie łatwo zrobić przedziały i po nich grupować:

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

INSERT INTO @table1
VALUES
(1, 'A', convert(datetime,'01.07.2018  10:00:00',104)),
(1, 'B', convert(datetime,'01.07.2018  10:02:00',104)),
(1, 'C', convert(datetime,'01.07.2018  10:08:00',104)),
(1, 'A', convert(datetime,'01.07.2018  10:30:09',104)),
(1, 'B', convert(datetime,'01.07.2018  10:50:00',104)),
(1, 'D', convert(datetime,'01.07.2018  10:55:00',104)),
(1, 'D', convert(datetime,'01.07.2018  15:55:00',104)),
(1, 'D', convert(datetime,'02.07.2018  10:55:00',104)),
(2, 'A', convert(datetime,'20.10.2018  15:00:00',104)),
(2, 'C', convert(datetime,'20.10.2018  17:00:00',104)),
(2, 'C', convert(datetime,'20.10.2018  17:07:00',104)),
(2, 'A', convert(datetime,'21.10.2018  09:00:00',104));
; with dane as (
    select 
	   --kolumna numeru wiersz (można pominąć i użyć dla samego date)
	   row_number() over (partition by ticket order by date) r
	   --zwróci 1 jeżeli poprzednie zdarzenie jest starsze o 10 minut
	   ,case when datediff(n,lag(date,1,dateadd(n,-11,date)) over (partition by ticket order by date),date) > 10 then 1 else 0 end  AS b,
	    * 
    from 
	   @table1
)
, p as (
    select 
	   ticket
	   --numer przedziału do grupowania
	   ,row_number() over (partition by ticket order by r) Przedzial 
	   --początek przedziału 
	   ,r [start]
	   --koniec przedziału 
	   , lead(r,1,r+1) over (partition by ticket order by r)-1 [stop]
    from 
	   dane 
    where 
	   --weż tylko początek przedziału
	   b=1
), result as (
select 
    d.Ticket
    ,p.przedzial 
    ,d.errorcode
from
    dane d
    --pbierz informacje do ktorego predziału należy rekord
    inner join p on p.Ticket=d.Ticket
				and d.r between p.[start] and p.[stop]
)
select *
 from result

W wyniku otrzymasz:

Ja połączyć stringi w grupowaniu masz w swoim poprzednim temacie, dla sql- >= 2017 to będzie:

select 
ticket
,string_agg(errorcode,'/') errorcode
from 
result
group by 
ticket,przedzial
 order by ticket,przedzial

Wynik:

P.S. Nie musisz pisać w postach jaka masz wersje, wystarczy odpowiedni tag do postu...

Jedno wielu mozliwych rozwiazan ;)

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

INSERT INTO @table1
VALUES
(1, 'A', '2018-07-01 10:00:00'),
(1, 'B', '2018-07-01 10:02:00'),
(1, 'C', '2018-07-01 10:08:00'),
(1, 'A', '2018-07-01 10:30:09'),
(1, 'B', '2018-07-01 10:50:00'),
(1, 'D', '2018-07-01 10:55:00'),
(1, 'D', '2018-07-01 15:55:00'),
(1, 'D', '2018-07-02 10:55:00'),
(2, 'A', '2018-07-20 15:00:00'),
(2, 'C', '2018-07-20 17:00:00'),
(2, 'C', '2018-07-20 17:07:00'),
(2, 'A', '2018-07-21 09:00:00');


;WITH start_date_cte
AS (SELECT TOP 1
           Date AS [StartDate]
    FROM @table1),
      ranges_cte
AS (SELECT ROW_NUMBER() OVER (ORDER BY Diff) AS [Range],
           MIN([Date]) [Range_Start],
           MAX([Date]) [Range_End]
    FROM
    (
        SELECT [Date],
               DATEDIFF(MINUTE, s1.[StartDate], [Date]) / 10 Diff
        FROM @table1,
             start_date_cte s1
    ) AS t1
    GROUP BY Diff)
SELECT t2.[Ticket],
       STUFF(
       (
           SELECT ',' + [Error]
           FROM @table1 t1
               INNER JOIN ranges_cte r2
                   ON t1.Date >= r2.[Range_Start]
                      AND t1.Date <= r2.[Range_End]
           WHERE t1.[Ticket] = t2.[Ticket]
                 AND r2.[Range] = t2.[Range]
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS [ErrorCluster]
FROM
(
    SELECT DISTINCT
           t1.[Ticket],
           r1.[Range]
    FROM @table1 t1
        INNER JOIN ranges_cte r1
            ON t1.[Date] >= r1.[Range_Start]
               AND t1.[Date] <= r1.[Range_End]
) AS t2
ORDER BY t2.[Ticket];

@ipsd sprytnie ale jak zmodyfikujemy trochę dane wejściowe:

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

zmieniłem 3 rekord

to mój wynik się nie zmienia, u ciebie natomiast zwraca:

Mam swiadomosc, ze to rozwiazanie troche na skroty, bardzie chcialem pokazac ze problem mozna ugrysc z roznej strony a nie na sile wciaskac kursory ;)

Spoko sam znalazłem u siebie błąd, moje nie sprawdzi się jak wszystkie recordy trzeba pogrupować w jeden... poprawiłem:

DECLARE @table1 TABLE
(
    [Ticket] INT,
    [ErrorCode] CHAR(1),
    [DATE] DATETIME
);
 
INSERT INTO @table1
VALUES
(1, 'A', '2018-07-01 10:00:00'),
(1, 'B', '2018-07-01 10:05:00'),
(1, 'C', '2018-07-01 10:10:00'),
(1, 'A', '2018-07-01 10:15:00'),
(1, 'B', '2018-07-01 10:20:00'),
(1, 'D', '2018-07-01 10:25:00'),
(1, 'D', '2018-07-01 10:30:00'),
(1, 'D', '2018-07-01 10:35:00'),
(2, 'A', '2018-07-20 15:00:00'),
(2, 'C', '2018-07-20 17:00:00'),
(2, 'C', '2018-07-20 17:07:00'),
(2, 'A', '2018-07-21 09:00:00');
 ; WITH dane AS (
    SELECT 
       --maksymalna data w ramach ticket
	   max(date) over (partition by ticket) mr
       --zwróci 1 jeżeli poprzednie zdarzenie jest starsze o 10 minut
       ,CASE WHEN datediff(n,lag(DATE,1,dateadd(n,-11,DATE)) OVER (partition BY ticket ORDER BY DATE),DATE) > 10 THEN 1 ELSE 0 END  AS b,
        * 
    FROM 
       @table1
)
, p AS (
    SELECT 
       ticket
       --numer przedziału do grupowania
       ,ROW_NUMBER() OVER (partition BY ticket ORDER BY date) Przedzial 
       --początek przedziału 
       ,date [START]
       --koniec przedziału 
       , dateadd(s,-1,lead(date,1,dateadd(s,1,mr)) OVER (partition BY ticket ORDER BY date)) [stop]
    FROM 
       dane 
    WHERE 
       --weż tylko początek przedziału
       b=1
), RESULT AS (
SELECT 
    d.Ticket
    ,p.przedzial 
    ,d.errorcode
FROM
    dane d
    --pbierz informacje do ktorego predziału należy rekord
    INNER JOIN p ON p.Ticket=d.Ticket
                AND d.date BETWEEN p.[START] AND p.[stop]
)

SELECT 
ticket
,string_agg(errorcode,'/') errorcode
FROM 
RESULT
GROUP BY 
ticket,przedzial
 ORDER BY ticket,przedzial
 

Wynik:

Dzięki Panczo, faktycznie o coś takiego mi chodziło - wszystko wydaje się działać super, posprawdzam jeszcze dokładnie.
Pozdrawiam,
Arek

Panczo, analizuję to jeszcze na spokojnie po czasie i mam pytanie.
Co się dokładnie dzieje w poniższym fragmencie bo nie rozumiem dokładnie w jaki sposób tworzymy koniec tego przedziału.
Może łatwiej bedzie mi zrozumieć na przykładzie liczb naturalnych, nie dat - jak wtedy wyglądałaby ta linijka?

Pozdrawiam i dziękuję.

 --koniec przedziału 
       , dateadd(s,-1,lead(DATE,1,dateadd(s,1,mr)) 

No to od początku, najpierw pobieram dane dane, gdzie zaznaczam sobie gdzie mam początek przedziału czyli poprzednie jest młodsze o co najmniej 10 minut, dokładnie kolumna b:

CASE WHEN datediff(n,lag(DATE,1,dateadd(n,-11,DATE)) OVER (partition BY ticket ORDER BY DATE),DATE) > 10 THEN 1 ELSE 0 END  AS b

co dla danych (z Twojego pierwszego posta zwróci mi dane:

W kolejnym kroku biorę tylko początki przedziałów b=1 co da mi w wyniku:

I teraz linijka o którą pytasz:

, dateadd(s,-1,lead(DATE,1,dateadd(s,1,mr)) OVER (partition BY ticket ORDER BY DATE)) [stop]

Działa tak, weź następną datę w ramach ticket sortując po DATE, (jeżeli jesteś na ostatnim wierszu, to weź datę mr (maksymalna data w ramach ticket)) i dodaj do niej jedną sekundę
) i odejmij od niej jedną sekundę
Obrazowo: dla pierwszego rekordu z ticketu A date = 2018-07-01 1000.000, następna w kolejności jest 2018-07-01 1009.000, więc odejmuje od niej sekundę i mam przedział: od 2018-07-01 1000.000 -2018-07-01 1008.000, odejmuje sekundę by przedziały nie zawierały się w sobie, w efekcie tworzę tabele przedziałów (widok p)

I ten widok pozwala mi się połączyć z tabela ticket i grupować wg. kolumny przedział:

INNER JOIN p ON p.Ticket=d.Ticket
                AND d.DATE BETWEEN p.[START] AND p.[stop]

Dzięki Panczo, już mi się zaczyna to układać ale brakuje mi jeszcze kilku puzzli.
Aby łatwiej było mi to zrozumieć, przestawmy się proszę na liczby zamiast dat (np. Cycles).

Co poniżej robię nie tak, że:

1. brakuje mi tych pierwszych przedziałów dla obu ticketów (od 100 i do 1)
2. jak w to wkomponować ten mr abym nie miał NULL na końcach przedziałów:

 DECLARE @table1 TABLE
(
    [TicketUID] INT,
    [ErrorCode] CHAR(1),
    [Cycles] INT
);
 
INSERT INTO @table1
VALUES
(1, 'A', 100),
(1, 'B', 105),
(1, 'C', 107),
(1, 'A', 150),
(1, 'B', 170),
(1, 'D', 201),
(1, 'D', 209),
(1, 'D', 300),
(2, 'A', 1),
(2, 'C', 7),
(2, 'C', 200),
(2, 'A', 210);
 
 WITH dane AS (
    SELECT 
       
       MAX(Cycles) OVER (partition BY ticketUID) mr
	   ,CASE WHEN Cycles - LAG(Cycles) over (order by TicketUID,Cycles) > 10 THEN 1 ELSE 0 END  AS b,
	   *
    FROM 
     @table1
)


, p AS (
    SELECT 
       ticketUID
       ,ROW_NUMBER() OVER (partition BY ticketUID ORDER BY Cycles) Przedzial 
       ,Cycles [START]
       ,LEAD(Cycles) OVER (partition BY ticketUID ORDER BY Cycles) -1 [stop]
    FROM	
       dane 
    WHERE 
       b=1
)

SELECT * FROM p

zbyt uprościłeś, w cte dane

Zamiast CASE WHEN Cycles - LAG(Cycles) over (order by TicketUID,Cycles) > 10 THEN 1 ELSE 0 END AS b musisz dać:

CASE WHEN Cycles - LAG(Cycles,1,Cycles-11) over (partition BY ticketUID order by Cycles) > 10 THEN 1 ELSE 0 END AS b,

Kluczowy jest 3 argument funkcji LAG, który odpowiada za to co ma być zwrócone jeśli nie ma poprzedzającej wartości, w naszym wypadku trzeba wziąć aktualny Cycles - 11, aby zaznaczyć jako początek przedziału.
Brakowało w twoim kodzie partition by

W cte p Lead musi wyglądać tak:

LEAD(Cycles,1,mr+1) OVER (partition BY ticketUID ORDER BY Cycles) -1 [stop]

Czyli jak nie ma następnego, to zwróć maksymalny mr + 1

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9d1a8db2adef57714268e06de2fa2a5e