Блог

У меня есть таблица, в которой у меня есть данные о курьерской доставке. У меня доставка запланирована на каждый день. Я хочу подсчитать количество неудачных поставок. Если доставка не удалась в последующие дни, то она должна быть засчитана как 1. Например, доставка запланирована на каждый день апреля 2021 года. Доставка не состоялась в день 15 апреля. После этого доставка снова не удалась с 18 по 20 апреля. Хотя количество дней, в течение которых доставка не удалась, составляет 4 дня, но я хочу посчитать количество сбоев как 2, потому что непрерывные дни сбоев будут засчитываться как 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
 

Мне нужна новая колонка, в которой будет показан идентификатор доставки первого сбоя, как показано ниже.

 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     
 

Я попробовал несколько вариантов, но не смог достичь вышеуказанного результата. Я использовал функцию ЗАДЕРЖКИ в своем запросе, чтобы найти предыдущий статус доставки. Но проблема в том, что если доставка не выполняется более 3 или 4 дней, мне придется использовать функцию ЗАДЕРЖКИ на 3 или 4 дня назад и проверить статус. Я хочу сделать это динамическим запросом. Ниже приведено то, что я использовал

 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
 

…

 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; */
 

Один метод использует только оконные функции. Вы можете назначить каждой группе неудач группу на основе количества успехов до каждой строки. Затем для каждой группы сбоев просто введите минимальный идентификатор сбоя:

 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;
 

Вот скрипка db<>.

На самом деле, немного более простая версия назначает группы в обратном порядке, поэтому первая запись является неудачной, а не успешной, поэтому case в этом min() нет необходимости:

 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;
 

Хммм…. другой подход используется lag() для обнаружения изменений состояния. Затем используйте накопительный максимум только для изменений:

 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;
 

Это вопрос «пробелов и островов». Моя любимая статья-это https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/, совет №4.

Ключом к разрешению островов является использование ключа(т. е. даты) — row_number, и это группирует острова с одинаковым номером. Результат аналогичен вашему, но он будет работать с любым количеством сбоев без использования lag . Вы хотите использовать только ошибки здесь:

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

Теперь, когда у вас это есть, вы можете просто добавить dense_rank() поверх (порядок по grp), чтобы получить количество неудач, возможно, также объединить успехи:

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

Вот моя незавершенная работа. Нужно найти обходной путь для повторного запуска подсчета, как только Status изменения вернутся к Success

Поиграйте с ним, используя 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