SQL Server: найдите Cust с непрерывной регистрацией

sql-server tsql continuous

383 просмотра

2 ответа

У меня есть задача решить хорошо известную в отрасли задачу - идентифицировать тех CustID, которые работают непрерывно, в течение заданного периода времени, и мы допускаем небольшие перерывы между контрактами.

Я выполнил первую часть, заполняя матричную таблицу, как показано во фрагменте ниже, на весь период времени и устанавливая флаг, если она активна на эту дату, я думаю, что это единственный надежный способ сделать это, поскольку контракты могут иметь перекрытия и т. Д.

Итак, теперь мне нужно проверить, является ли CustID 1/0 для продолжительной активности, я застрял в задаче, как это отследить, скажем, в моем примере есть 3-дневный перерыв, что нормально, но мне нужно убедиться, что эти дни один за другим.

У вас есть какие-нибудь хорошие идеи, как я могу сделать это красиво, ценю вашу помощь и руководство. Я видел несколько примеров, но они сделаны в SAS, так что это трудно понять.

declare @maxBreak int = 3 -- 3 days max allowed for continuse contract
declare @PeriodStart date =  '2015-1-11', @PeriodEnd date =  '2015-1-19'; 

;with matrix_dd as 
(
    select *  
    from 
        (select    111 CustID, '2015-1-11' dd, 1 Active   union  
         select    111 CustID, '2015-1-12' dd, 0 Active   union
         select    111 CustID, '2015-1-13' dd, 0 Active   union 
         select    111 CustID, '2015-1-14' dd, 0 Active   union
         select    111 CustID, '2015-1-15' dd, 1 Active   union 
         select    111 CustID, '2015-1-16' dd, 1 Active   union
         select    111 CustID, '2015-1-17' dd, 1 Active   union 
         select    111 CustID, '2015-1-18' dd, 1 Active   union
         select    111 CustID, '2015-1-19' dd, 0 Active   union 
         select    111 CustID, '2015-1-20' dd, 0 Active) a
)
select * 
from matrix_dd

Бест М

Автор: Mario Trento Источник Размещён: 08.11.2019 10:57

Ответы (2)


1 плюс

Это решение вычисляет активные диапазоны и продолжительность перерыва с момента окончания последнего интервала:

declare @maxBreak int = 3 -- 3 days max allowed for continuse contract
declare @PeriodStart date =  '2015-1-11', @PeriodEnd date =  '2015-1-19'; 

with matrix_dd as 
(
    select *  from ( values
        (111, '2015-1-11', 1 ),
        (111, '2015-1-12', 0 ),
        (111, '2015-1-13', 0 ),
        (111, '2015-1-14', 0 ),
        (111, '2015-1-15', 1 ),
        (111, '2015-1-16', 1 ),
        (111, '2015-1-17', 1 ),
        (111, '2015-1-18', 1 ),
        (111, '2015-1-19', 0 ),
        (111, '2015-1-20', 0 )
    ) as x(CustID, dd, Active)
), active_with_groups as (
    select *,
        row_number() over (partition by CustID order by dd) - 
           datediff(day, '2000-01-01', dd) as gid
    from matrix_dd
    where active = 1
        and dd between @PeriodStart and @PeriodEnd
), islands as (
    select CustId, min(dd) as islandStart, max(dd) as islandEnd
    from active_with_groups
    group by CustID, gid
), islands_with_gaps as (
    select *, 
        datediff(
            day, 
            lag(islandEnd, 1, islandStart) 
               over (partition by CustID order by islandStart), 
            islandStart
        ) - 1 as [break]
    from islands
)
select * 
from islands_with_gaps
where [break] >= @maxBreak
order by islandStart

Давайте разберемся с этим. В общем табличном выражении "active_with_groups" (CTE) все, что я делаю, - это преобразовываю даты в целые числа, которые имеют одинаковые отношения с помощью datediff(). Почему? Целые числа легче работать с этой проблемой. Обратите внимание, что я также использую, row_number()чтобы получить непрерывную последовательность, а затем получить разницу между этим и datediff()значением. Ключевое наблюдение состоит в том, что, если дни также не будут проходить непрерывно, эта разница будет, ну, в общем, другой. Аналогично, если даты действительно увеличиваются непрерывно, тогда разница будет одинаковой. Поэтому мы можем использовать это значение в качестве идентификатора группы для значений, которые находятся в непрерывном диапазоне.

Затем мы используем этот идентификатор группы для группировки (держу пари, что вы этого не ожидали!). Это дает нам начало и конец каждого интервала. Ничего очень умного здесь не происходит.

Следующим шагом является вычисление времени, прошедшего между окончанием последнего и текущим интервалом. Для этого мы используем простой вызов lag()функции. Единственное, что следует отметить, это то, что я выбрал lag()функцию, которая будет выдавать значение по умолчанию islandStartв случае первого интервала. С таким же успехом это могло бы быть просто по умолчанию (что могло бы привести к тому, что он выдал бы значение NULL).

Наконец, мы ищем интервалы с разрывом выше указанного порога.

Автор: Ben Thul Размещён: 20.08.2016 08:02

1 плюс

Похоже на ответ Бена. Я предполагаю, что все ваши даты представлены в данных. Так что на самом деле нам просто нужно убедиться, что ноль не длиннее 3.

with inactive_runs as (
    select
        CustID,
        row_number() over (partition by CustID order by dd)
            - datediff(day, min(dd) over (partition by CustID), dd) as grp
    from matrix_dd
    where Active = 0
)
select distinct CustID from matrix_dd m
where 3 >= all (
    select count(*) from inactive_runs ir
    where ir.CustID = m.CustID
    group by grp
);

http://rextester.com/AHI22250

Использование allне особенно распространено. Вот альтернатива:

...
with inactive_runs as (
    select
        CustID, dd, /* <-- had to add dd */
        row_number() over (partition by CustID order by dd)
            - datediff(day, min(dd) over (partition by CustID), dd) as grp
    from #matrix_dd
    where Active = 0
)
select distinct CustID from matrix_dd m
where not exists (
    select 1 from inactive_runs ir
    where ir.CustID = m.CustID
    group by grp
    having datediff(day, min(dd), max(dd)) > 2
);

Я посмотрел на ваш комментарий выше. Я думаю, это подтверждает мое подозрение, что у вас есть одна строка на каждую дату. Если у вас есть новая версия SQL Server, вы можете просто суммировать за предыдущие три строки. К сожалению, вы не сможете использовать переменную для размера окна, если длина переменная:

with cust as (
    select
        CustID,
        case when
        sum(case when Active = 0 then 1 end) over (
                partition by CustID
                order by dd
                rows between 3 preceding and current row 
            ) = 4 then 1
        end as isBrk
    from matrix_dd
)
select CustID
from cust
group by CustID
having count(isBrk) = 0;

Редактировать:

На основании вашего комментария с данными в формате «pre-matrix», да, это более простой запрос. В этот момент вы просто смотрите на предыдущую дату окончания и дату начала текущей строки.

with data as (
    select * from (
        values (111, 1230, '2014-12-11', '2015-01-11'),
               (111, 1231, '2015-01-15', '2015-01-18'),
               (111, 1232, '2015-03-22', '2015-04-01') 
        ) as t (CustID, ContractID, StartDD, EndDD)
), gaps as (
    select
        CustID,
        datediff(day,
            lag(EndDD, 1, StartDD) over (partition by CustID order by StartDD),
            StartDD
        ) as days
    from data
)
select CustID
from gaps
group by CustID;
having max(days) <= 3;
Автор: shawnt00 Размещён: 20.08.2016 08:31
Вопросы из категории :
32x32