#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
Мне нужно выбрать телефонные номера, которые имеют последние 3 последовательных статуса как Failed или Send_Failed . Позвольте мне объяснить подробнее на примере (Microsoft SQL Server)
Моя таблица:
| Phone Number Attempted | Date Sent | SMS Return Code |
|*456 | 2020-11-17| SEND_FAILED*|
|*456 | 2020-11-16| SEND_FAILED*|
|*456 | 2020-11-15| FAILED* |
| 456 | 2020-11-14| DELIVERED |
|*457 | 2020-11-17| SEND_FAILED*|
|*457 | 2020-11-16| SEND_FAILED*|
|*457 | 2020-11-15| SEND_FAILED*|
| 457 | 2020-11-14| SEND_FAILED |
| 455 | 2020-11-17| DELIVERED |
| 455 | 2020-11-16| FAILED |
| 455 | 2020-11-15| DELIVERED |
| 455 | 2020-11-14| DELIVERED |
| 454 | 2020-11-17| DELIVERED |
| 454 | 2020-11-16| DELIVERED |
| 454 | 2020-11-15| DELIVERED |
| 454 | 2020-11-14| DELIVERED |
| 453 | 2020-11-17| SEND_FAILED |
| 453 | 2020-11-16| SEND_FAILED |
| 452 | 2020-11-17| SEND_FAILED |
| 452 | 2020-11-16| SEND_FAILED |
| 452 | 2020-11-15| DELIVERED |>
Ожидаемый результат:
456
457
Хотя приведенный ниже запрос дает результаты, очень близкие к тому, что мне нужно, он терпит неудачу, поскольку он также выбирает запись 452. По-видимому, в моем запросе не удается установить условие, что три более поздних статуса должны быть сбойными для каждого из них.
select
i.[Phone Number Attempted], [SMS Return Code]
from
(select [Phone Number Attempted], [Date Sent]
from [MyTable]) i
cross apply
(select top 3 *
from [MyTable] ti
where i.[Phone Number Attempted] = ti.[Phone Number Attempted]
and ti.[SMS Return Code] != 'DELIVERED') C
group by
i.[Phone Number Attempted], [SMS Return Code], C.[Date Sent]
having
MIN(C.[SMS Return Code]) !='DELIVERED'
and MAX(C.[SMS Return Code]) !='DELIVERED'
and count (*) >= 3
and C.[Date Sent] = MAX(i.[Date Sent])
Комментарии:
1. Ваш код не будет выполняться в MySQL. Вы уверены, что используете эту базу данных?
2. @GordonLinoff Извиняюсь, я использую Microsoft SQL Server Management Studio 2012
Ответ №1:
Вы можете использовать lag() :
select distinct phone
from (select t.*,
lag(status) over (partition by phone order by date) as prev_status,
lag(status, 2) over (partition by phone order by date) as prev2_status,
from t
) t
where status in ('Failed', 'Send_Failed') and
prev_status in ('Failed', 'Send_Failed') and
prev2_status in ('Failed', 'Send_Failed') ;
Комментарии:
1. Спасибо за ответ. По какой-то причине это не работает, когда есть случаи, когда более 6 статусов ДОСТАВЛЯЮТСЯ после того, как SEND_FAILED перечислены. Мне действительно нужны были идентификаторы, в которых 3 самых последних последовательных статуса либо SEND_FAILED, либо FAILED
2. @John . , , Вы можете настроить db<>скрипку? Этот запрос не должен быть чувствителен к количеству доставленных данных.
Ответ №2:
Приведенный ниже запрос дал мне правильные результаты для моего собственного вопроса
select
distinct A.[Phone Number Attempted]
from
(
Select distinct [Phone Number Attempted], [SMS Return Code], [Date Sent]
,LAG([SMS Return Code]) over (partition by [Phone Number Attempted] order by [Sent Date]) as prev_status
,lag([SMS Return Code], 2) over (partition by [Phone Number Attempted] order by [Sent Date]) as prev2_status
from [MyTable] ) A
where
prev_status in ('FAILED', 'SEND_FAILED') and
prev2_status in ('FAILED', 'SEND_FAILED') and
[SMS Return Code] in ('FAILED', 'SEND_FAILED') and not exists
(Select 1 from [MyTable] B where A.[Phone Number Attempted] =B.[Phone Number Attempted] and B.[Sent Date] > A.[Sent Date])