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

Откуда:
Сообщений: 65
Не смог придумать более корректное название темы QQ

Имеем таблицу с некой историей, хранящей ID объекта, флаг и дату изменения, примерно такого вида:
HISTORY_IDOBJECT_IDFLAGDATESTAMP
112302012-01-01
212312012-02-01
312312013-03-01
412302013-04-01
512302012-05-01
612312012-06-01
712302012-07-01

Нужно на основе этой таблицы создать некую вьюху, которая бы показывала временные интервалы, в которые FLAG был изменен на 1 и сохранялся в таком положении. На основе таблички выше должно выйти:
OBJECT_IDDATE_STARTDATE_END
1232012-01-012013-03-01
1232012-05-012012-06-01


есть ли простые способы решения без написания курсоров или функций, которые бы перебирали историю по каждому OBJECT_ID, сравнивая поочередно все его [историческая запись] и [историческая запись+1] на случай изменения и записи данных в новую таблицу?
26 мар 14, 17:18    [15790669]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
iap
Member

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

а если первым значением FLAG сразу будет 1, то какая DATA_START должна быть?
26 мар 14, 17:31    [15790727]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
-
Guest
Не понятно как вы из начальных данных получили конечные.
Например, первый интервал у вас покрывает второй.
26 мар 14, 17:31    [15790728]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
eXeLe,

R.T.F.F.
26 мар 14, 17:36    [15790748]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
iap,

скорее всего в этом случае дата начала и дата кончала должны совпадать.
26 мар 14, 17:37    [15790753]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
-
Не понятно как вы из начальных данных получили конечные.
Например, первый интервал у вас покрывает второй.
Дык, для начальной даты он почему-то берёт дату последнего 0.
Спрашивается, а если первого 0 и не было?!
26 мар 14, 17:39    [15790763]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
-,

есть подозрение, что ТС опечатался.
26 мар 14, 17:39    [15790764]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Добрый Э - Эх
iap,

скорее всего в этом случае дата начала и дата кончала должны совпадать.
Сервер не хочет и слышать про "скорее всего"!
Он такой противный...
26 мар 14, 17:39    [15790769]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
iap,

ну тогда нужно ждать ТС, пока он дозреет и даст полную и точную постановку задачи. Ибо пока - словесное описание задачи слабо коррелирует с приведенными тестовыми данными и желаемым результатом на них.
26 мар 14, 17:43    [15790786]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
да, с годами опечатался, будем считать, что везде 2012. темку поправлю.
первой единицы быть не может.
по факту будет джойниться таблица, в которой будет OBJECT_ID, FLAG=0 и DATESTAMP='1990-01-01'
26 мар 14, 17:46    [15790798]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
ну то есть не поправлю =)
26 мар 14, 17:46    [15790801]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
eXeLe,

яснее не стало. :)
но на всякий случай - версию сервера озвучь. А то приведут решения на LEAD/LAG-ах а оно у тебя не взлетит...
26 мар 14, 17:49    [15790815]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
2008-й

в таблице истории первая запись - всегда с нулем.
для избежания косяков была мысль к истории юнионом добавить строку истории для каждого объекта с нулем и датой 1990-01-01, чтоб если каким-то чудом в истории была единица на первой исторической записи, то запись с FLAG=0 и DATESTAMP='1990-01-01' спасла от проблем
26 мар 14, 17:53    [15790846]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
eXeLe,

тебя спросили о том, почему дата начала действия единицы берется как дата последнего нуля перед этой единицей. ты не ответил...
iap
для начальной даты он почему-то берёт дату последнего 0.

такой алгоритм изначально и задумывался или тут ты тоже опечатался?
26 мар 14, 17:56    [15790863]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
нет, все верно, может быть излишне упростил описание просто.


по факту, имеем таблицу с текущими данными объектов. И имеем таблицу с историей, куда пишутся старые данные при их изменении.
То есть допустим в таблице актуальных значений произошло какое-то изменение (не обязательно FLAG'а, другие поля просто опущены), в история записалось:
HISTORY_IDOBJECT_IDFLAGDATESTAMP
112302012-01-01

Затем произошли какие-то еще изменения в таблице актуальных данных, в истории получили вторую запись:
HISTORY_IDOBJECT_IDFLAGDATESTAMP
112302012-01-01
212312012-02-01

так как во второй записи истории у нас FLAG стал =1, значит в актуальнах данных он был =1 все время после первого изменения.
поэтому мы и считаем интервалы между последним из нулей (если несколько подряд в истории) и последней из единиц (если несколько подряд в истории). в первом примере обрисовал эти варианты.
26 мар 14, 18:04    [15790905]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
правильный пример из первого сообщения должен был выглядеть так (исправлены даты).
исходные данные:

HISTORY_IDOBJECT_IDFLAGDATESTAMP
112302012-01-01
212312012-02-01
312312012-03-01
412302012-04-01
512302012-05-01
612312012-06-01
712302012-07-01


требуется:
OBJECT_IDDATE_STARTDATE_END
1232012-01-012012-03-01
1232012-05-012012-06-01
26 мар 14, 18:06    [15790911]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
eXeLe
так как во второй записи истории у нас FLAG стал =1, значит в актуальнах данных он был =1 все время после первого изменения.
поэтому мы и считаем интервалы между последним из нулей (если несколько подряд в истории) и последней из единиц (если несколько подряд в истории). в первом примере обрисовал эти варианты.
тогда в этом случае непонятно, почему в качестве даты окончания действия единицы берется последняя дата. Исходя из описанной тобой логики должно браться предпоследнее значение... Ты уж поточнее с этим определись :)
26 мар 14, 18:45    [15791069]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
eXeLe, условие задачи возможно не до конца правильно понял...
да и мозг уже плохо варит...
в общем на ваш суд, мой вариант...
with Tmp(id, dBgn, dEnd) as (
  select *
          , (select top 1 DATESTAMP 
               from H 
               where [object_id] = t0.id 
                 and FLAG = 1 
                 and DATESTAMP > t0.dBgn) as dEnd  
    from (
             select [object_id] as id 
                    , DATESTAMP AS dBgn
               from H 
               where flag = 0
             ) as t0
) 
select t0.*
  from Tmp as t0
    join (select id 
                   , Max(dBgn) as dBgnMax
                   , dEnd 
             from Tmp 
             group by id, dEnd) as t1
      on t0.id = t1.id
        and  t0.dBgn = t1.dBgnMax    
  where t0.dEnd is not null
27 мар 14, 00:24    [15792334]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
Добрый Э - Эх,

нет, все верно. в каждой смене 0->1, интервалы от последнего из нулей (когда их несколько подряд, либо просто от нуля) до последней из единиц (когда их несколько подряд, либо просто до единицы)
если брать "до предпоследний единицы", то логика бы не работала как минимум в случае, если единица всего одна.

а если на примере, то если мы получаем третью запись истории с единицей
HISTORY_IDOBJECT_IDFLAGDATESTAMP
112302012-01-01
212312012-02-01
312312012-03-01

значит на момент DATESTAMP='2012-03-01' значение FLAG было равно =1.
НО, в тот же момент времени в актуальных данных мог появиться ноль, значит DATESTAMP='2012-03-01' - одновременно дата окончания действия 1 и начала действия 0.
27 мар 14, 10:03    [15792966]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
MihaZ,

посыл хороший, но не совсем верный

в твоем запросе в первой части мы ко всем FLAG=0 добавляем первую дату с FLAG=1, но это не верно, когда у нас в истории идет подряд две записи с единицей, то и добавлять надо вторую. А если перефразировать, то к дате с нулем мы должны добавить ПОСЛЕДНЮЮ из единиц ДО следующего нуля.

во второй части мы джойним сгруппированную таблицу и оставляем только последний из нулей (Max(dBgn)), а это портит логику в том плане, что когда у нас есть несколько временных отрезков, которые надо получить, то при выборе Max(dBgn) мы получаем только один.
27 мар 14, 10:25    [15793086]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
Добрый Э - Эх
Guest
eXeLe,

Как вариант....
27 мар 14, 10:38    [15793182]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
Добрый Э - Эх,

да, именно то что нужно, спасибо!
С добавлением даты предыдущего нуля к каждой записи с единицей - вполне логично, но с группировкой по датам начала(+ID) и обсчетом максимальной даты конца после группировки - сам бы сходу не догадался сходу =)
27 мар 14, 11:04    [15793348]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
eXeLe, попытка №2
with Tmp(id, dBgn, dEnd) as (
  select *
          , (select top 1 DATESTAMP 
               from H 
               where [object_id] = t0.id 
                 and FLAG = 1 
                 and DATESTAMP > t0.dBgn
                 and DATESTAMP <= ISNULL((SELECT top 1 DATESTAMP 
                                            FROM h 
                                            WHERE [object_id] = t0.id  
                                              AND FLAG = 0 
                                              AND DATESTAMP > t0.dBgn 
                                            ORDER BY DATESTAMP 
                                          ), DATESTAMP)
                ORDER BY DATESTAMP desc) as dEnd  
    from (
             select [object_id] as id 
                    , DATESTAMP AS dBgn
               from H 
               where flag = 0
             ) as t0
) 
select *
  from Tmp 
  where dEnd is not null
27 мар 14, 11:31    [15793500]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
eXeLe
Member

Откуда:
Сообщений: 65
MihaZ,

угу, теперь работает верно, спасибо!
правда решение Добрый Э - Эх выполняется в несколько раз быстрее :P
27 мар 14, 11:49    [15793675]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет интервалов изменений в исторических записях  [new]
MihaZ
Member

Откуда: СПб
Сообщений: 17
eXeLe, да я когда отвечал не увидел, что уже есть решение проблемы.
Ну по крайней мере сам нашел решение ;)
27 мар 14, 12:35    [15794025]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить