Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Группировка с учетом граничных условий  [new]
anpv
Member

Откуда: Novosibirsk
Сообщений: 68
Добрый день.
SQL Server >= 2005.
Имеется таблица проходов.
DECLARE @t TABLE (
	[employee_id] int NOT NULL, -- id сотрудника
	[time] datetime NOT NULL, -- время прохода
	[target] int -- если NULL - то выход на улицу, иначе id территории
);

INSERT INTO @t ([employee_id], [time], [target])
SELECT 1, '2012-08-01 09:41:57', NULL UNION ALL
SELECT 1, '2012-08-01 10:17:45', 2  UNION ALL
SELECT 1, '2012-08-01 10:18:28', 2  UNION ALL
SELECT 1, '2012-08-01 18:41:54', 3  UNION ALL
SELECT 1, '2012-08-01 18:42:24', NULL UNION ALL
SELECT 1, '2012-08-01 22:53:56', 2  UNION ALL
SELECT 1, '2012-08-01 23:26:05', NULL UNION ALL
SELECT 1, '2012-08-02 09:14:25', 2  UNION ALL
SELECT 1, '2012-08-02 10:22:12', 2  UNION ALL
SELECT 1, '2012-08-02 10:26:55', 4;

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

employee_id entryTime exitTime
1 NULL 2012-08-01 09:41:57
1 2012-08-01 10:17:45 2012-08-01 18:42:24
1 2012-08-01 22:53:56 2012-08-01 23:26:05
1 2012-08-02 09:14:25 NULL
Т.е. условие группировки такое:
  • выход всегда - [target] IS NULL
  • вход - если раньше по времени был выход ([target] IS NULL) и [target] IS NOT NULL

    К сообщению приложен файл. Размер - 8Kb
  • 29 авг 12, 11:59    [13081123]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    TungusXan
    Member

    Откуда: Хочу туда где нет труда и каждый день зарплата! =)
    Сообщений: 1686
    Тут например обсуждалось
    Пользуйтесь поиском :)
    29 авг 12, 13:26    [13081913]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    anpv
    Member

    Откуда: Novosibirsk
    Сообщений: 68
    TungusXan,
    В той теме которая обсуждалась другое условие.
    В поиске сложно найти что нибудь подходящее.
    29 авг 12, 13:33    [13081993]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    anpv, у меня как то так получилось:


    ;with enters as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select e.employee_id, e.[time] as start, MAX(t1.time) as [end]
    from enters e
    inner join @t t1 
            on t1.employee_id = e.employee_id
           and t1.time > e.time
           and (t1.time < (select MIN(time) from enters e1 where e1.time > e.time)
            or (select count(time) from enters e1 where e1.time > e.time) = 0)
    group by e.employee_id, e.time
    

    В SQL 2012 должно быть проще, поскольку там есть подходящие функции, но я в них пока еще не настолько разобрался.
    29 авг 12, 13:54    [13082179]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    anpv,

    Не обратил внимания, что у вас сотрудники на территории рождаются и на нее же умирать приходят, поэтому первой даты входа и последней даты выхода нет, поэтому перепутал местами входы и выходы :)

    Тогда даже проще:

    ;with exits as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select isnull(t1.employee_id, e.employee_id), max(t1.time) as enter, e.[time] as [exit]
    from exits e
    full join @t t1 
            on t1.employee_id = e.employee_id
           and t1.time < e.time
    group by e.employee_id, e.time, t1.employee_id
    order by ISNULL(e.time, MAX(t1.time))
    
    29 авг 12, 14:39    [13082617]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 46999
    DECLARE @t TABLE (
    	[employee_id] int NOT NULL, -- id сотрудника
    	[time] datetime NOT NULL, -- время прохода
    	[target] int -- если NULL - то выход на улицу, иначе id территории
    );
    
    INSERT INTO @t ([employee_id], [time], [target])
    SELECT 1, '2012-08-01 09:41:57', NULL UNION ALL
    SELECT 1, '2012-08-01 10:17:45', 2  UNION ALL
    SELECT 1, '2012-08-01 10:18:28', 2  UNION ALL
    SELECT 1, '2012-08-01 18:41:54', 3  UNION ALL
    SELECT 1, '2012-08-01 18:42:24', NULL UNION ALL
    SELECT 1, '2012-08-01 22:53:56', 2  UNION ALL
    SELECT 1, '2012-08-01 23:26:05', NULL UNION ALL
    SELECT 1, '2012-08-02 09:14:25', 2  UNION ALL
    SELECT 1, '2012-08-02 10:22:12', 2  UNION ALL
    SELECT 1, '2012-08-02 10:26:55', 4;
    
    SELECT [employee_id]=ISNULL(En.[employee_id],Ex.[employee_id]),En.[EntryTime],Ex.[ExitTime]
    FROM
    (
     SELECT N=ROW_NUMBER()OVER(PARTITION BY T.[employee_id] ORDER BY T.[time]),T.[employee_id],[EntryTime]=T.[time]
     FROM @t T
     WHERE T.[target] IS NOT NULL
       AND (SELECT TOP(1) TT.[target] FROM @t TT WHERE TT.[employee_id]=T.[employee_id] AND TT.[time]<T.[time] ORDER BY TT.[time] DESC) IS NULL
    )En
    FULL JOIN
    (
     SELECT N=ROW_NUMBER()OVER(PARTITION BY T.[employee_id] ORDER BY T.[time]),T.[employee_id],[ExitTime]=T.[time]
     FROM @t T
     WHERE T.[target] IS NULL
    )Ex
    ON En.[employee_id]=Ex.[employee_id] AND ABS(En.N-Ex.N)=1 AND En.[EntryTime]<Ex.[ExitTime]
    ORDER BY [employee_id],[EntryTime];
    
    29 авг 12, 14:42    [13082647]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    aleks2
    Guest
    iap
    AND ABS(En.N-Ex.N)=1
    

    Это такое тонкое извращение?
    29 авг 12, 14:50    [13082741]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    HandKot
    Member

    Откуда: Sergiev Posad
    Сообщений: 2994
    мои пять копеек
    ; with cte 
    	as (
    		select
    			t1.employee_id
    			, t1.time
    			, t1.target
    			, SUM(case when t2.target is null and t2.time is not null then 1 else 0 end) grp
    		from	
    			@t t1
    		left join @t t2 on t2.employee_id = t1.employee_id and t2.time < t1.time
    		group by
    			t1.employee_id
    			, t1.time
    			, t1.target)
    select
    	c.employee_id
    	, min(case when c.target is not null then c.time else null end) 
    	, max(case when c.target is null then c.time else null end )
    from
    	cte c
    group by
    	c.employee_id
    	, c.grp
    



    2 Minamoto , Ваш запрос некорректно работает
    29 авг 12, 16:16    [13083575]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 46999
    aleks2
    iap
    AND ABS(En.N-Ex.N)=1
    

    Это такое тонкое извращение?
    Ага!
    29 авг 12, 16:46    [13083829]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 46999
    iap
    aleks2
    пропущено...

    Это такое тонкое извращение?
    Ага!
    Причёсывать сейчас нет никакой возможности
    29 авг 12, 16:47    [13083844]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    HandKot
    2 Minamoto , Ваш запрос некорректно работает


    Точно, спасибо, а все потому, что тороплюсь.

    Вот так больше похоже на ваш результат:

    ;with exits as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select isnull(t1.employee_id, e.employee_id), min(t1.time) as enter, e.[time] as [exit]
    from exits e
    full join @t t1 
            on t1.employee_id = e.employee_id
           and t1.time < e.time
           and (t1.time > (select Max(time) from exits e1 where e1.time < e.time)
            or (select count(time) from exits e1 where e1.time < e.time) = 0)
    group by e.employee_id, e.time, t1.employee_id
    order by ISNULL(e.time, MAX(t1.time))
    
    29 авг 12, 19:56    [13085064]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    Minamoto
    HandKot
    2 Minamoto , Ваш запрос некорректно работает


    Точно, спасибо, а все потому, что тороплюсь.

    Вот так больше похоже на ваш результат:

    +
    ;with exits as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select isnull(t1.employee_id, e.employee_id), min(t1.time) as enter, e.[time] as [exit]
    from exits e
    full join @t t1 
            on t1.employee_id = e.employee_id
           and t1.time < e.time
           and (t1.time > (select Max(time) from exits e1 where e1.time < e.time)
            or (select count(time) from exits e1 where e1.time < e.time) = 0)
    group by e.employee_id, e.time, t1.employee_id
    order by ISNULL(e.time, MAX(t1.time))
    


    И опять напортачил... Попытка №4:

    	;with exits as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select isnull(t1.employee_id, e.employee_id), min(t1.time) as enter, e.[time] as [exit]
    from exits e
    full join @t t1 
            on t1.employee_id = e.employee_id
           and t1.time < e.time
           and (t1.time > (select Max(time) from exits e1 where e1.time < e.time and e1.employee_id = e.employee_id)
            or (select count(time) from exits e1 where e1.time < e.time and e1.employee_id = e.employee_id) = 0)
    group by e.employee_id, e.time, t1.employee_id
    order by ISNULL(e.time, MAX(t1.time))
    
    29 авг 12, 19:59    [13085075]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    anpv
    Member

    Откуда: Novosibirsk
    Сообщений: 68
    Огромное спасибо всем кто откликнулся.
    На тестовых данных работает все правильно, кроме варианта Minamoto, который выдает:

    (No column name) enter exit
    1 NULL 2012-08-01 09:41:57.000
    1 2012-08-01 10:17:45.000 2012-08-01 18:42:24.000
    1 2012-08-01 22:53:56.000 2012-08-01 23:26:05.000
    1 2012-08-01 09:41:57.000 NULL
    Но к сожалению на реальных данных получается совсем другая картина.
    Ну и конечно скорость выполнения оставляет желать лучшего.

    К сообщению приложен файл (data.zip - 44Kb) cкачать
    30 авг 12, 16:04    [13089720]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    anpv
    Member

    Откуда: Novosibirsk
    Сообщений: 68
    К примеру если поменять немного исходные тестовые данные (тестовые данные которые я привел охватывают все возможные варианты):
    DECLARE @t TABLE (
    	[employee_id] int NOT NULL, -- id сотрудника
    	[time] datetime NOT NULL, -- время прохода
    	[target] int -- если NULL - то выход на улицу, иначе id территории
    );
    
    INSERT INTO @t ([employee_id], [time], [target])
    SELECT 1, '2012-08-01 10:17:45', 2  UNION ALL
    SELECT 1, '2012-08-01 10:18:28', 2  UNION ALL
    SELECT 1, '2012-08-01 18:41:54', 3  UNION ALL
    SELECT 1, '2012-08-01 18:42:24', NULL UNION ALL
    SELECT 1, '2012-08-01 22:53:56', 2  UNION ALL
    SELECT 1, '2012-08-01 23:26:05', NULL UNION ALL
    SELECT 1, '2012-08-02 09:14:25', 2  UNION ALL
    SELECT 1, '2012-08-02 10:22:12', 2  UNION ALL
    SELECT 1, '2012-08-02 10:26:55', 4 UNION ALL
    SELECT 1, '2012-08-02 11:41:57', NULL 
    
    ;with exits as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select isnull(t1.employee_id, e.employee_id), min(t1.time) as enter, e.[time] as [exit]
    from exits e
    full join @t t1 
            on t1.employee_id = e.employee_id
           and t1.time < e.time
           and (t1.time > (select Max(time) from exits e1 where e1.time < e.time and e1.employee_id = e.employee_id)
            or (select count(time) from exits e1 where e1.time < e.time and e1.employee_id = e.employee_id) = 0)
    group by e.employee_id, e.time, t1.employee_id
    order by ISNULL(e.time, MAX(t1.time))
    
    SELECT [employee_id]=ISNULL(En.[employee_id],Ex.[employee_id]),En.[EntryTime],Ex.[ExitTime]
    FROM
    (
     SELECT N=ROW_NUMBER()OVER(PARTITION BY T.[employee_id] ORDER BY T.[time]),T.[employee_id],[EntryTime]=T.[time]
     FROM @t T
     WHERE T.[target] IS NOT NULL
       AND (SELECT TOP(1) TT.[target] FROM @t TT WHERE TT.[employee_id]=T.[employee_id] AND TT.[time]<T.[time] ORDER BY TT.[time] DESC) IS NULL
    )En
    FULL JOIN
    (
     SELECT N=ROW_NUMBER()OVER(PARTITION BY T.[employee_id] ORDER BY T.[time]),T.[employee_id],[ExitTime]=T.[time]
     FROM @t T
     WHERE T.[target] IS NULL
    )Ex
    ON En.[employee_id]=Ex.[employee_id] AND ABS(En.N-Ex.N)=1 AND En.[EntryTime]<Ex.[ExitTime]
    ORDER BY [employee_id],[EntryTime];
    
    ; with cte 
    	as (
    		select
    			t1.employee_id
    			, t1.time
    			, t1.target
    			, SUM(case when t2.target is null and t2.time is not null then 1 else 0 end) grp
    		from	
    			@t t1
    		left join @t t2 on t2.employee_id = t1.employee_id and t2.time < t1.time
    		group by
    			t1.employee_id
    			, t1.time
    			, t1.target)
    select
    	c.employee_id
    	, min(case when c.target is not null then c.time else null end) 
    	, max(case when c.target is null then c.time else null end )
    from
    	cte c
    group by
    	c.employee_id
    	, c.grp
    

    То все они выдают разный результат:

    (No column name) enter exit
    1 2012-08-01 10:17:45.000 2012-08-01 18:42:24.000
    1 2012-08-01 22:53:56.000 2012-08-01 23:26:05.000
    1 2012-08-02 09:14:25.000 2012-08-02 11:41:57.000
    1 2012-08-01 18:42:24.000 NULL

    employee_id EntryTime ExitTime
    1 NULL 2012-08-01 18:42:24.000
    1 2012-08-01 10:17:45.000 2012-08-01 23:26:05.000
    1 2012-08-01 22:53:56.000 2012-08-02 11:41:57.000
    1 2012-08-02 09:14:25.000 NULL

    employee_id (No column name) (No column name)
    1 2012-08-01 10:17:45.000 2012-08-01 18:42:24.000
    1 2012-08-01 22:53:56.000 2012-08-01 23:26:05.000
    1 2012-08-02 09:14:25.000 2012-08-02 11:41:57.000
    30 авг 12, 16:22    [13089916]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    HandKot
    Member

    Откуда: Sergiev Posad
    Сообщений: 2994
    и что неверно в моем варианте?
    и если делать на реальной таблице, а не на табличной переменной, то скорость конечно же будет намного больше при больших объемах
    30 авг 12, 16:30    [13089979]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    anpv
    Member

    Откуда: Novosibirsk
    Сообщений: 68
    HandKot
    и что неверно в моем варианте?
    и если делать на реальной таблице, а не на табличной переменной, то скорость конечно же будет намного больше при больших объемах

    Ваш вариант на этих тестовых данных работает единственно правильно.
    А вот на реальных данных я не могу объяснить 8 строк в которых присутствуют NULL в столбце с временем входа
    min(case when c.target is not null then c.time else null end)
    

    Получается 8 раз человек выходил без входа, но по идее такого не может быть, с учетом структуры таблицы это может быть только один раз.
    Да, на реальной таблице скорость выше.
    Но при количестве строк в реальной таблице 803073, этот запрос выполняется 00:07:32.
    30 авг 12, 16:58    [13090212]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    anpv
    На тестовых данных работает все правильно, кроме варианта Minamoto, который выдает:

    (No column name) enter exit
    1 NULL 2012-08-01 09:41:57.000
    1 2012-08-01 10:17:45.000 2012-08-01 18:42:24.000
    1 2012-08-01 22:53:56.000 2012-08-01 23:26:05.000
    1 2012-08-01 09:41:57.000 NULL

    А я упорный...

    Вариант 5 :)

    ;with exits as
    (select employee_id, [time] 
       from @t 
      where target is NULL)
    select isnull(t1.employee_id, e.employee_id), min(t1.time) as enter, e.[time] as [exit]
      from exits e
           full join @t t1 
                  on t1.employee_id = e.employee_id
                 and e.time > t1.time
                 and (t1.time > (select Max(time) 
                                   from exits e1 
                                  where e1.time < e.time 
                                    and e1.employee_id = e.employee_id) or 
                                (select count(time) 
                                   from exits e1 
                                  where e1.time < e.time 
                                    and e1.employee_id = e.employee_id) = 0)
     where t1.target is not null or 
           t1.time is null
     group by e.employee_id, e.time, t1.employee_id
     order by ISNULL(e.time, MAX(t1.time))
    


    И согласен с предыдущим автором.

    HandKot
    и что неверно в моем варианте?
    и если делать на реальной таблице, а не на табличной переменной, то скорость конечно же будет намного больше при больших объемах


    Насколько я вижу, его скрипт отрабатывает корректно - по тем данным, которые вы представляете, как реальные.
    Хотя там не хватает множественности сотрудников - по одному не все ошибки можно увидеть.
    И еще там есть по несколько выходов без входа для одного сотрудника - т.е. две записи подряд с target=NULL, поэтому получается, что сотрудник выходил два раза.
    30 авг 12, 16:59    [13090218]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    anpv
    Member

    Откуда: Novosibirsk
    Сообщений: 68
    Minamoto,

    Вариант №5 работает правильно.
    Только сейчас обнаружил, что действительно существуют по несколько выходов подряд.
    Из-за этого появляются 8 строк с временем входа NULL.
    Это из-за недобросовестных посетителей, которые забывают отмечаться при входе:).
    30 авг 12, 17:19    [13090401]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    anpv
    Получается 8 раз человек выходил без входа, но по идее такого не может быть, с учетом структуры таблицы это может быть только один раз.

    С учетом структуры таблицы такое быть может. То что вы надеетесь, что у вас нет кривых данных, еще не значит, что их действительно нет. Смотрите на данные в тех промежутках времени, которые в группировке обрели NULL на входе.

    HandKot, мой скрипт на порядки медленнее вашего:

    Переделал таблицу во временную с индексом:

    create TABLE #t (
    	[employee_id] int NOT NULL, -- id сотрудника
    	[time] datetime NOT NULL, -- время прохода
    	[target] int -- если NULL - то выход на улицу, иначе id территории
    );
    
    create clustered index t_idx1 on #t (time);
    


    Ваш скрипт на 8562 строках, имеющихся в в файле, дает результат 4 секунды, мой - 201 секунду.

    Кроме того, и данные получаются разные.

    Выгрузил результат во временные таблицы, сравнил:

    select * from #var1 -- Ваш вариант
    except
    select * from #var2; --Мой вариант
    
    select * from #var2
    except
    select * from #var1
    


    Получил такие различия:

    employee_id	enter					exit
    582 2008-06-30 19:34:16.000 2008-06-30 19:34:16.000

    employee_id	enter					exit
    582 NULL 2008-06-30 19:34:16.000
    582 2008-06-30 19:34:16.000 NULL

    Т.е. записи, когда дата входа = дате выхода до секунды, вах скрипт обрабатывает корректно, мой - нет.

    Сдаюсь, ваш вариант предпочтительнее :)
    30 авг 12, 17:22    [13090421]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    anpv
    Minamoto,

    Вариант №5 работает правильно.
    Только сейчас обнаружил, что действительно существуют по несколько выходов подряд.
    Из-за этого появляются 8 строк с временем входа NULL.
    Это из-за недобросовестных посетителей, которые забывают отмечаться при входе:).

    К сожалению, опять неправильно, и очень долго, подробности в предыдущем сообщении :)
    30 авг 12, 17:23    [13090430]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    HandKot
    Member

    Откуда: Sergiev Posad
    Сообщений: 2994
    anpv
    Но при количестве строк в реальной таблице 803073, этот запрос выполняется 00:07:32.

    а какие индексы есть на этой тблице?

    и да Minamoto прав

    ...
    SELECT 582, '20090122 17:58:14', NULL UNION ALL
    SELECT 582, '20090122 17:58:17', NULL UNION ALL
    ...
    SELECT 582, '20120117 18:19:42', NULL UNION ALL
    SELECT 582, '20120117 18:19:44', NULL UNION ALL
    ...
    и т.д
    


    anpv
    Получается 8 раз человек выходил без входа, но по идее такого не может быть, с учетом структуры таблицы это может быть только один раз.

    как структура таблицы может такое организовать?
    30 авг 12, 17:23    [13090435]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    anpv
    Member

    Откуда: Novosibirsk
    Сообщений: 68
    HandKot,

    Имеющиеся индексы:
    NONCLUSTERED INDEX: [employee_id] ASC, [time] ASC
    NONCLUSTERED INDEX: [time] ASC
    NONCLUSTERED INDEX: [target] ASC

    HandKot
    как структура таблицы может такое организовать?

    Не правильно выразился, не структура, а данные.
    Я считал что есть всегда вход перед не первым выходом, а оказалось не совсем так.
    30 авг 12, 17:34    [13090547]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    HandKot
    Member

    Откуда: Sergiev Posad
    Сообщений: 2994
    добавьте в индекс
    NONCLUSTERED INDEX: [employee_id] ASC, [time] ASC
    

    поле target, как инклуд
    или сделайте его кластерным

    покрайней мере в плане должно пропать лукап букмарк
    30 авг 12, 17:39    [13090590]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    aleks2
    Guest
    DECLARE @t TABLE (
    	[employee_id] int NOT NULL, -- id сотрудника
    	[time] datetime NOT NULL, -- время прохода
    	[target] int -- если NULL - то выход на улицу, иначе id территории
    );
    
    INSERT INTO @t ([employee_id], [time], [target])
    SELECT 1, '2012-08-01 10:17:45', 2  UNION ALL
    SELECT 1, '2012-08-01 10:18:28', 2  UNION ALL
    SELECT 1, '2012-08-01 18:41:54', 3  UNION ALL
    SELECT 1, '2012-08-01 18:42:24', NULL UNION ALL
    SELECT 1, '2012-08-01 22:53:56', 2  UNION ALL
    SELECT 1, '2012-08-01 23:26:05', NULL UNION ALL
    SELECT 1, '2012-08-02 09:14:25', 2  UNION ALL
    SELECT 1, '2012-08-02 10:22:12', 2  UNION ALL
    SELECT 1, '2012-08-02 10:26:55', 4 UNION ALL
    SELECT 1, '2012-08-02 11:41:57', NULL UNION ALL
    SELECT 1, '2012-08-02 12:26:55', 4
    
    ;with
      nums as (select *, ROW_NUMBER() over(PARTITION by [employee_id] order by [time]) n from @t)
    , enters as (select n.*, ROW_NUMBER() over(PARTITION by n.[employee_id] order by n.[time]) nn  from nums n left outer join (select * from nums where [target] is not null) n1 on n.[employee_id] = n1.[employee_id] and n.n-1 = n1.n where n1.employee_id is null)
    , exits  as (select *, ROW_NUMBER() over(PARTITION by [employee_id] order by [time]) nn from @t where [target] is null )
    select en.employee_id, en.time entrytime, ex.time exittime from enters en left outer join exits ex on en.[employee_id] = ex.[employee_id] and en.nn = ex.nn
    
    30 авг 12, 19:21    [13091344]     Ответить | Цитировать Сообщить модератору
     Re: Группировка с учетом граничных условий  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    aleks2, некорректно работает, если первой записью идет выход - как в первом примере.
    31 авг 12, 09:48    [13092895]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить