Mam tabelkę, w której mam dane do dostawy kurierem. Mam zaplanowaną dostawę każdego dnia. Chcę policzyć liczbę nieudanych dostaw. Jeżeli dostawa nie powiedzie się w kolejnych dniach, należy ją liczyć jako 1. Np. dostawa planowana jest na każdy dzień kwietnia 2021 r. Doręczenie nie powiodło się w dniu 15 kwietnia. Po tej dostawie ponownie się nie udało od 18 kwietnia do 20 kwietnia. Wprawdzie liczba dni niepowodzenia dostawy wynosi 4 dni, ale chcę liczyć liczbę niepowodzeń jako 2, ponieważ ciągłe dni niepowodzenia będą liczone jako 1.

DeliveryId     DeliveryDate     Status
1              2021-04-14       Success
2              2021-04-15       Failure
3              2021-04-16       Success
4              2021-04-17       Success
5              2021-04-18       Failure
6              2021-04-19       Failure
7              2021-04-20       Failure
8              2021-04-21       Success

Chcę nową kolumnę, która pokaże identyfikator dostawy pierwszego niepowodzenia, jak poniżej.

DeliveryId     DeliveryDate     Status     FailedDeliveryId
1              2021-04-14       Success     
2              2021-04-15       Failure     2
3              2021-04-16       Success     
4              2021-04-17       Success     
5              2021-04-18       Failure     5
6              2021-04-19       Failure     5
7              2021-04-20       Failure     5
8              2021-04-21       Success     

Próbowałem kilku opcji, ale nie udało mi się osiągnąć powyższego wyniku. Użyłem funkcji LAG w moim zapytaniu, aby znaleźć poprzedni status dostawy. Ale problem polega na tym, że jeśli dostawa nie powiedzie się przez więcej niż 3 lub 4 dni, będę musiał użyć funkcji LAG do 3 lub 4 dni wstecz i sprawdzić status. Chcę, aby było to dynamiczne zapytanie. Poniżej jest to, czego użyłem

SELECT *, 
       CASE WHEN Status='Failure' AND Prev_Status='Success' THEN DeliveryId 
            WHEN Status='Failure' AND Prev_Status='Failure' THEN Prev_DeliveryId 
            END AS FailureInstance 
FROM (
         SELECT *, 
                LAG(Status,1) OVER(ORDER BY DeliveryDate ASC) Prev_Status,
                LAG(DeliveryId,1) OVER(ORDER BY DeliveryDate ASC) Prev_DeliveryId
         FROM   table1 
     ) A
0
Jessica Yoskovich 28 czerwiec 2021, 09:22

4 odpowiedzi

Najlepsza odpowiedź

...

declare @t table(DeliveryId int, DeliveryDate date, Status varchar(10));

insert into @t(DeliveryId, DeliveryDate, Status)
values
(10, '2021-04-14', 'Success'),
(20, '2021-04-15', 'Failure'),
(30, '2021-04-16', 'Success'),
(40, '2021-04-17', 'Success'),
(70, '2021-04-18', 'Failure'),
(60, '2021-04-19', 'Failure'),
(50, '2021-04-20', 'Failure'),
(80, '2021-04-21', 'Success');


select *, 
case when Status='Failure' then min(grpDeliveryId) over(partition by grp) end as FailedDeliveryId,
case when Status='Failure' then datediff(minute, min(DeliveryDate) over(partition by grp), min(grpSuccessDate) over(partition by grp) ) end as MinutesDiffFailSuccess
from
(
select *, 
sum(addorcountme) over(order by DeliveryDate) as grp
from
(
select *, 
case when Status='Failure' and lag(Status) over(order by DeliveryDate)='Failure' then null else 1 end as addorcountme,
case when Status='Failure' and lag(Status) over(order by DeliveryDate)='Failure' then null else DeliveryId end as grpDeliveryId,
case when Status='Failure' and lead(Status) over(order by DeliveryDate)='Success' then lead(DeliveryDate) over(order by DeliveryDate) end as grpSuccessDate
from @t
) as t
) as g;



/*
select *, case when Status='Failure' then min(DeliveryId) over(partition by grp) end as FailedDeliveryId
from
(
select *, 
sum(sumorcountme) over(order by DeliveryDate) as grp
from
(
select *, case when Status='failure' and lag(Status) over(order by DeliveryDate)='Failure' then null else 1 end as sumorcountme
from @t
) as t
) as g; */
1
lptr 28 czerwiec 2021, 10:32

Jedna metoda wykorzystuje tylko funkcje okna. Każdej grupie niepowodzeń można przypisać grupę na podstawie liczby sukcesów do każdego wiersza. Następnie dla każdej grupy awarii po prostu weź minimalny identyfikator awarii:

select t.*,
       (case when status = 'Failure'
             then min(case when status = 'Failure' then DeliveryId end) over (partition by grp)
        end) as first_failureId
from (select t.*,
             sum(case when status = 'Success' then 1 else 0 end) over (order by DeliveryDate) as grp
      from t
     ) t;

Tu jest db<>skrzypce.

Właściwie nieco prostsza wersja przypisuje grupy w odwrotnej kolejności, więc pierwszy rekord to porażka, a nie sukces, więc case w min() nie jest wtedy konieczne:

select t.*,
       (case when status = 'Failure'
             then min(DeliveryId) over (partition by grp)
        end) as first_failureId
from (select t.*,
             sum(case when status = 'Success' then 1 else 0 end) over (order by DeliveryDate desc) as grp
      from t
     ) t
order by DeliveryId;

Hmmm . . . inne podejście wykorzystuje lag() do wykrywania zmian statusu. Następnie użyj skumulowanego maksimum tylko na zmianach:

select t.*,
       (case when status = 'Failure'
             then max(case when prev_status is null or prev_status <> status then DeliveryId end) over (order by DeliveryDate)
        end) as first_failureId
from (select t.*,
             lag(status) over (order by DeliveryDate) as prev_status
      from t
     ) t
order by DeliveryId;
0
Gordon Linoff 28 czerwiec 2021, 10:51

Oto moja praca w toku. Musisz znaleźć obejście dla ponownego rozpoczęcia liczenia, gdy Status zmieni się z powrotem na Success

Pobaw się nim za pomocą https://dbfiddle.uk/

CREATE TABLE test
(
    DeliveryId int NOT NULL,
    DeliveryDate date NOT NULL,
    Status varchar(max) NOT NULL)

INSERT INTO test (DeliveryId,DeliveryDate,Status) 
values 
    (1, '2021-04-14', 'Success'),
    (2, '2021-04-15', 'Failure'),
    (3, '2021-04-16', 'Success'),
    (4, '2021-04-17', 'Success'),
    (5, '2021-04-18', 'Failure'),
    (6, '2021-04-19', 'Failure'),
    (7, '2021-04-20', 'Failure'),
    (8, '2021-04-21', 'Success')

with cte as
(
  SELECT DeliveryId,
         DeliveryDate,
         Status,
         1 as FailedDeliveryId
  FROM test
  WHERE DeliveryId = 1
    UNION all
  SELECT t.DeliveryId,
         t.DeliveryDate,
         t.Status,
         cte.FailedDeliveryId + CASE WHEN cte.Status <> t.Status THEN 1 ELSE 0 END
  FROM test as t INNER JOIN cte ON t.DeliveryId - 1 = cte.DeliveryId
)


SELECT 
       DeliveryId,
       DeliveryDate,
       Status,
       FailedDeliveryId
FROM cte       
ORDER BY DeliveryId
0
marcin2x4 28 czerwiec 2021, 09:54

To jest pytanie „luki i wyspy”. Mój ulubiony artykuł to https://blog.jooq.org/2016/04/25/10-sql-tricks-that-nie-pomyślałeś-byłeś-możliwe/, wskazówka nr 4.

Kluczem do rozwiązania wysp jest użycie klucza (tj. daty) - numer_wiersza, który grupuje wyspy o tym samym numerze. Wynik jest podobny do twojego, ale będzie działać z dowolną liczbą błędów bez użycia lag. Chcesz używać tylko błędów tutaj:

select *,DeliveryDate-row_number() over (order by DeliveryId asc) as grp
from table1
where Status='Failure'

Teraz, gdy już to masz, możesz po prostu dodać gęstość_rank() over (kolejność według grp), aby uzyskać liczbę niepowodzeń, może również połączyć sukcesy:

;with cte as
(
    select *,DeliveryDate-row_number() over (order by DeliveryId asc) as grp
    from table1
    where Status='Failure'
)
    select 
        cte.*,dense_rank() over (order by grp) as FailureNum
    from cte
union all
    select *,null as grp,null as FailureNum
    from table1
    where Status='Success'
0
George Menoutis 28 czerwiec 2021, 08:50