String z wierszy pod określonymi warunkami

arhetyp

2019-01-24T21:11:45+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 92

0

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

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

Kopiuj

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

Panczo

2019-01-24T22:01:30+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

0

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

arhetyp

2019-01-25T08:28:30+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 92

0

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

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

Pozdrawiam,
Arek

Panczo

2019-01-25T14:56:00+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

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ć:

Kopiuj

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:

Ticket	przedzial	errorcode
1	1	A
1	1	B
1	1	C
1	2	A
1	3	B
1	3	D
1	4	D
1	5	D
2	1	A
2	2	C
2	2	C
2	3	A

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

Kopiuj

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

Wynik:

ticket	errorcode
1	A/B/C
1	A
1	B/D
1	D
1	D
2	A
2	C/C
2	A

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

ipsd

2019-01-25T15:06:42+01:00

Rejestracja: dni
Ostatnio: dni
Lokalizacja: London

2

Jedno wielu mozliwych rozwiazan ;)

Kopiuj

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];

Panczo

2019-01-25T15:36:10+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

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

Kopiuj

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:

ipsd

2019-01-25T15:43:29+01:00

Rejestracja: dni
Ostatnio: dni
Lokalizacja: London

0

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

Panczo

2019-01-25T15:58:59+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

3

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

Kopiuj

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:

arhetyp

2019-01-27T20:09:23+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 92

0

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

arhetyp

2019-02-04T15:32:50+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 92

0

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ę.

Kopiuj

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

Panczo

2019-02-04T16:00:43+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

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:

Kopiuj

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:

mr	b	Ticket	ErrorCode	DATE
2018-07-02 10:55:00.000	1	1	A	2018-07-01 10:00:00.000
2018-07-02 10:55:00.000	0	1	B	2018-07-01 10:02:00.000
2018-07-02 10:55:00.000	0	1	C	2018-07-01 10:08:00.000
2018-07-02 10:55:00.000	1	1	A	2018-07-01 10:30:09.000
2018-07-02 10:55:00.000	1	1	B	2018-07-01 10:50:00.000
2018-07-02 10:55:00.000	0	1	D	2018-07-01 10:55:00.000
2018-07-02 10:55:00.000	1	1	D	2018-07-01 15:55:00.000
2018-07-02 10:55:00.000	1	1	D	2018-07-02 10:55:00.000
2018-10-21 09:00:00.000	1	2	A	2018-10-20 15:00:00.000
2018-10-21 09:00:00.000	1	2	C	2018-10-20 17:00:00.000
2018-10-21 09:00:00.000	0	2	C	2018-10-20 17:07:00.000
2018-10-21 09:00:00.000	1	2	A	2018-10-21 09:00:00.000

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

mr	b	Ticket	ErrorCode	DATE
2018-07-02 10:55:00.000	1	1	A	2018-07-01 10:00:00.000
2018-07-02 10:55:00.000	1	1	A	2018-07-01 10:30:09.000
2018-07-02 10:55:00.000	1	1	B	2018-07-01 10:50:00.000
2018-07-02 10:55:00.000	1	1	D	2018-07-01 15:55:00.000
2018-07-02 10:55:00.000	1	1	D	2018-07-02 10:55:00.000
2018-10-21 09:00:00.000	1	2	A	2018-10-20 15:00:00.000
2018-10-21 09:00:00.000	1	2	C	2018-10-20 17:00:00.000
2018-10-21 09:00:00.000	1	2	A	2018-10-21 09:00:00.000

I teraz linijka o którą pytasz:

Kopiuj

, 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 10:00:00.000, następna w kolejności jest 2018-07-01 10:30:09.000, więc odejmuje od niej sekundę i mam przedział: od 2018-07-01 10:00:00.000 -2018-07-01 10:30:08.000, odejmuje sekundę by przedziały nie zawierały się w sobie, w efekcie tworzę tabele przedziałów (widok p)

ticket	Przedzial	START	stop
1	1	2018-07-01 10:00:00.000	2018-07-01 10:30:08.000
1	2	2018-07-01 10:30:09.000	2018-07-01 10:49:59.000
1	3	2018-07-01 10:50:00.000	2018-07-01 15:54:59.000
1	4	2018-07-01 15:55:00.000	2018-07-02 10:54:59.000
1	5	2018-07-02 10:55:00.000	2018-07-02 10:55:00.000
2	1	2018-10-20 15:00:00.000	2018-10-20 16:59:59.000
2	2	2018-10-20 17:00:00.000	2018-10-21 08:59:59.000
2	3	2018-10-21 09:00:00.000	2018-10-21 09:00:00.000

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

Kopiuj

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

arhetyp

2019-02-04T21:30:56+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 92

0

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:

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

Kopiuj

 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

Panczo

2019-02-04T21:46:31+01:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

0

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:

Kopiuj

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