Блог

Прежде всего спасибо всем, кто помогает мне ответить на этот вопрос! Я погуглил и попытался узнать как можно больше о рекурсивных CTE и других расширенных функциях MS SQL 05-08, но я в тупике.

У меня есть таблица, которая выглядит следующим образом:

 AgentID, GoalReached, MonthEndDate
360123, 1, 1/22/2011
360123, Null, 2/22/2011
360123, 1, 3/22/2011
360123, 1, 4/22/2011
360123, 1, 5/22/2011
360123, 2, 6/22/2011
360124, 2, 1/22/2011
360124, 2, 2/22/2011
360124, 1, 3/22/2011
360124, 2, 4/22/2011
360124, 2, 5/22/2011
360124, 3, 6/22/2011
 

Как мне создать таблицу, которая группируется по идентификатору агента и показывает мне только идентификаторы агентов, которые достигли уровня для 4 из любых 6 месяцев подряд? А затем покажите, за какой 6-месячный период была достигнута эта цель?

Кроме того, если агент достиг уровня 2 или выше, он также достиг предыдущего уровня 1. Итак, если у агента есть три уровня 2 и три уровня 1 за 6-месячный период, тогда агент достиг только уровня 1.

Конечным результатом будет:

 AgentID, LevelReached, StartDate, EndDate
360123,1,3/22/2011,6/22/2011
360124,2,2/22/2011,6/22/2011
 

Спасибо за любую информацию, которую вы можете предоставить.

Я придумал этот, который выглядит так, как будто он работает:

 declare @t table ( AgentID int, GoalReached int, MonthEndDate date)
insert @t (AgentID , GoalReached , MonthEndDate )
values 
(360123, Null, '2/22/2011'),
(360123, 1, '1/22/2011'),
(360123, 1, '3/22/2011'),
(360123, 1, '4/22/2011'),
(360123, 1, '5/22/2011'),
(360123, 2, '6/22/2011'),
(360124, 1, '3/22/2011'),
(360124, 2, '1/22/2011'),
(360124, 2, '2/22/2011'),
(360124, 2, '4/22/2011'),
(360124, 2, '5/22/2011'),
(360124, 3, '6/22/2011')

select  t1.AgentID, t1.GoalReached, t1.MonthEndDate StartDate, max(t2.MonthEndDate) Enddate
from    @t t1
        inner join @t t2 
            on t1.AgentID = t2.AgentID
            and t1.GoalReached = t2.GoalReached
            and datediff(m, t1.MonthEndDate, t2.MonthEndDate) between 0 and 6  -- find any other rows for same agent within 6 months
group by t1.AgentID, t1.GoalReached, t1.MonthEndDate
having count(*) >= 4
 

Однако я извлекаю эти строки:

 360123  1   2011-01-22  2011-05-22
360124  2   2011-01-22  2011-05-22
 

Но я думаю, что приведенный вами пример вывода может быть отключен. (Конечная дата 6/22 для агента 360123 относится к уровню 2, а не 1. Для 360124 эта дата относится к уровню 3 вместо 2.

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

С уважением, GJ

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

Привет, ok сделал это, см. Ниже.

Чтобы удалить перекрывающиеся последовательности, я использовал рекурсивный CTE. Я думаю, что это единственный способ для группы «узнать», есть ли определенная строка уже в предыдущей группе. Было бы интересно увидеть другие подходы.

Лично я бы подумал об использовании более «процедурного» способа сделать это с помощью курсора или чего-то, что фактически обновляет строки, чтобы разделить их на группы. Этот запрос может быть трудным для понимания, может стать проблемой для будущего сопровождения кода.

Итак, с этим покончено, наслаждайтесь! И спасибо за это, милый маленький jawbreaker 😉

Rgds GJ

 declare @t table (AgentID int, GoalReached int, MonthEndDate date)
insert @t (AgentID , GoalReached , MonthEndDate )
values 
(360123, Null, '2/22/2011'),
(360123, 1, '1/22/2011'),
(360123, 1, '3/22/2011'),
(360123, 1, '4/22/2011'),
(360123, 1, '5/22/2011'),
(360123, 2, '6/22/2011'),
(360124, 1, '3/22/2011'),
(360124, 2, '1/22/2011'),
(360124, 2, '2/22/2011'),
(360124, 2, '4/22/2011'),
(360124, 2, '5/22/2011'),
(360124, 3, '6/22/2011'),

(100, 1, '1/1/2010'),
(100, 1, '2/1/2010'),
(100, 2, '3/1/2010'),
(100, 1, '4/1/2010'),
(100, 1, '5/1/2010'),
(100, 1, '6/1/2010'),
(100, 1, '7/1/2010'),
(100, 1, '8/1/2010'),
(100, 1, '9/1/2010')
;


with 
--1: find groups of rows that are within 6 months of eachother and number the rows
step1 as (
select  t1.AgentID, t1.MonthEndDate StartDate, t2.GoalReached, t2.MonthEndDate EndDate,  ROW_NUMBER() over (partition by t1.agentid, t1.MonthEndDate order by t2.monthenddate) RowRank
from    @t t1
        inner join @t t2 
            on t1.AgentID = t2.AgentID
            --and t1.GoalReached = t2.GoalReached
            and datediff(m, t1.MonthEndDate, t2.MonthEndDate) between 0 and 6  -- find any other rows for same agent within 6 months
)
--select * from step1
-- cut sequences off to no longer than 4 rows and get rid of shorter sequences
,step2 as (
    select  * 
    from    step1 t1
    where   exists (
        select *
        from    step1 t2
        where   t1.AgentID = t2.AgentID
                and t1.StartDate = t2.StartDate
                --t1.id1 = t2.id1       -- same group(same start row)
                and t2.RowRank  = 4 -- group has to have at least 4 rows 
                and t1.RowRank <= 4 -- get rid of rows that are beyond 4
    )
)
--select * from step2 
-- collapse groups to a single row (makes next step easier)
,grps as (
    select  AgentID,            
            MAX(GoalReached) MaxGoal,
            MIN(StartDate) StartDate,
            MAX(EndDate) EndDate,
            dense_rank() over (partition by AgentId order by StartDate) GrpRank
    from    step2
    group by agentid,
            StartDate
)
--select * from sub1
-- use common table expression to remove overlap (only way I could figure out how)
,cte as (
    -- anchor to first sequence of 4 rows for each agent
    select  AgentID,
            StartDate,
            EndDate,
            MaxGoal
    from    grps
    where   GrpRank = 1

    union all 

    -- repeat to find following sequences
    select  AgentID,
            StartDate,
            EndDate,
            MaxGoal     
    from    (
                select  g.*, row_number() over (partition by g.AgentId order by g.StartDate) grp_rank
                from    grps g              
                inner join cte c 
                        on  g.AgentID = c.AgentID   -- same agent                       
                        and g.StartDate > c.EndDate -- group must start after previous group has ended (here we remove the overlap)

            )s
    where   s.grp_rank = 1  -- only add 1 group per agent for each iteration of the CTE     
)
select  *
from    cte
order by AgentID, StartDate