Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
Друзья.
Есть допустим такая таблица Logs(someid,operation,old,new,date)

В нее приходит две операции: добавление (1) и удаление (3). Нужно получить некоторый "прототип дерева", отбражающего эти события пользователю, так же в виде "Старое Значение" и "Новое значение" + дата.

Упрощенно выглядит так:
 ;with x as (select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt
             union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt
             union all
             select 'B',null, 3 , '20130822 12:00:06.100' dt)
select *from x

Что нужно получить выглядит так
Старое Новое Время
A22-08-2013 12:00:01
AA|B22-08-2013 12:00:01
A|BA22-08-2013 12:00:06


В уме крутится что то про ранжирование и top(), но кажется мне это слишком громоздким, если использовать подзапрос...
22 авг 13, 17:18    [14743319]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
_Промешан_,

и без циклов точно. Т.к. мне этот запрос потом еще к другому присоединять.
22 авг 13, 17:22    [14743339]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
Glory
Member

Откуда:
Сообщений: 104751
_Промешан_
Упрощенно выглядит так:
 ;with x as (select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt
             union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt
             union all
             select 'B',null, 3 , '20130822 12:00:06.100' dt)
select *from x

А если будет так ?

 ;with x as (select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt
             union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt
             union all 
             select null,'C', 1 , '20130822 12:00:03.700' dt
             union all
             select 'B',null, 3 , '20130822 12:00:06.100' dt)
select *from x
22 авг 13, 17:25    [14743360]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
Glory
_Промешан_
Упрощенно выглядит так:
 ;with x as (select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt
             union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt
             union all
             select 'B',null, 3 , '20130822 12:00:06.100' dt)
select *from x

А если будет так ?

 ;with x as (select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt
             union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt
             union all 
             select null,'C', 1 , '20130822 12:00:03.700' dt
             union all
             select 'B',null, 3 , '20130822 12:00:06.100' dt)
select *from x

Пока что old формируется
;with x as (select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt
             union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt
             union all
             select null,'C', 1 , '20130822 12:00:05.700' dt
             union all
             select 'B',null, 3 , '20130822 12:00:06.100' dt)
  , y as (select (select new+'|' as 'text()' from x where dt<a.dt for xml path('')) old,a.new,a.dt,a.operation from x a)

А вот с new пока не допер
22 авг 13, 17:37    [14743413]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
_Промешан_,

но правда это без учета operation. ;)
22 авг 13, 17:40    [14743430]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
_Промешан_,

и с подзапросами. Ну да бог с ними. Хоть так.
но если более изощренный вариант, то как то надо вычислять диапазоны.

 ;with x as (
             select null old, 'A' new, 1 operation, cast('20130822 12:00:01.100' as datetime) dt        union all 
             select null,'B', 1 , '20130822 12:00:01.700' dt           union all
             select null,'C', 1 , '20130822 12:00:02.700' dt           union all
             select 'B',null, 3 , '20130822 12:00:05.100' dt           union all
             select 'C',null, 3 , '20130822 12:00:06.100' dt
             select null,'B', 1 , '20130822 12:00:08.100' dt
             )

Здесь уже надо получить так

Старое Новое Время
A22-08-2013 12:00:01
A|A|B22-08-2013 12:00:01
A|BA|B|C22-08-2013 12:00:02
A|BCA|C22-08-2013 12:00:05
A|CA22-08-2013 12:00:06
AA|B22-08-2013 12:00:08


Как то не очень получается у меня.
22 авг 13, 18:05    [14743578]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
_Промешан_,

Друзья, есть решение.

;with x(old, new, operation, dt) as (
    select null, 'A',  1, cast('20130822 12:00:01.100' as datetime) union all
    select null, 'B',  1, '20130822 12:00:01.700' dt union all
    select 'B',  null, 3, '20130822 12:00:02.100' dt union all
    select null, 'C',  1, '20130822 12:00:05.700' dt union all
    select 'C',  null, 3, '20130822 12:00:06.100' dt union all
    select null, 'B',  1, '20130822 12:00:08.700' dt
), y as (
    select x1.dt, isnull(x2.new, x2.old) u
    from x x1
    inner join x x2 on x1.dt >= x2.dt
    group by x1.dt, isnull(x2.new, x2.old)
    having sum((x2.operation - 2) * (-1)) > 0
)

select min(case i when 1 then new end) old
    , min(case i when 0 then new end) new
    , min(case i when 0 then dt end) dt
from (
    select distinct dt
        , dense_rank() over(order by dt) num
        , stuff ( (
            select '|' + u
            from y y2
            where y1.dt = y2.dt
            for xml path('')
        ), 1, 1, '') new
    from y y1
) t
cross join (values(0),(1))i(i)
group by num+i
having min(case i when 0 then dt end) is not null
order by dt

Ваши идеи/предложения по оптимизации сего добра?
22 авг 13, 20:07    [14743972]     Ответить | Цитировать Сообщить модератору
 Re: Как сформировать дерево лога из потока записанных событий?  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
Нету идей? :(

Беда в следующем.

На 2008, когда вместо первого with x as () я подставляю туда реальную выборку значений

with x as (select .... from abc
inner join bde
inner join ghj
left join A
left join A...
)

Во-первых выполняется 21 секунду (хотя реальных значений лога там штук 10 записей. Из всех 20 млн записей).
То в статистике я получаю жуткий компот с 2мя млн логический чтений по нескольким таблицам.

При этом, если сперва with x заменить на insert into @tmp, а все остальные опреации оставить как в последнем запросе - то все летает быстро и чтений на 5 порядков меньше.
23 авг 13, 14:15    [14747690]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить