Помогите с запросом

[euronymous]

Уважаемы SQL-профи, помогите, пожалуйста, написать запрос:

Исходные данные:
1. База данных MS SQL Server 2005, состоит из нескольких таблиц.
2. Одна из них Archive: (ID as int, ObjID as int, MetDate as datetime, Value as float, Source as int).
3. Записи распределяются примерно равномерно с 01.01.2007г. по 31.12.2009г.
4. Значения поля Value дискретные, т.е. 1 или 0 заполняются хаотично. Таблица определяет дискретное состояние работы двигателя с определенным ObjID: 1 – работает, 0 – двигатель не работает.
5. Вторая таблица Objects: (ID as int, Name as varchar(50))

Создать запрос, выдающий за заданный период (StartDate, EndDate) все периоды работы двигателя. Поля запроса: (Name, StartDT, EndDT, Period=EndDT-StartDT, ) Т.е. каждая строка запроса должна содержать один период. Периоды не должны пересекаться. Name – наименование запроса, связанного с Objects по полю ID.

[soleil@mmc]

если речь про решение задачи одним запросом, то явно не чистым СКЛ
здесь нужны знания аналитических фунок в МССКЛ (я силен в оракле)

либо, если условия задачи не ограничивают применение ХП, то юзать ХП

[euronymous]

Сообщение от soleil@mmc

если речь про решение задачи одним запросом, то явно ен чистым СКЛ
здесь нужны знания аналитических фунок в МССКЛ (я силен в оракле)

либо, если условия задачи не ограничивают применение ХП, то юзать ХП

Это необходимо сделать одним запросом.

[soleil@mmc]

оказалось все намного проще

не забудь сходить по этой ссылке

with
  t1 as
  (
  select
    o.name Name, 
    trunc(a.metdate) fDate,
    a.Value fValue
  from    
    objects o,
    archive a
  where 0=0
    and a.objid = o.id
    and trunc(a.metdate) < :p1+1  -- дата_ПО
    and trunc(a.metdate) >= :p0   -- дата_С  
  )

select
  Name,
  min(fDate) StartDT,
  max(fDate) EndDT,  
  max(fDate) - min(fDate) + 1 Period,
  fValue
from t1
group by Name, fValue
order by 2

[euronymous]

Большое спасибо, но, если не трудно, хотелось бы получить некоторые комментарии по решению.
Для чего нужно поле fValue в результирующем запросе? По условию оно содержит данные о включении и выключении двигателя (1 - двигатель включили, 0 - двигатель выключили). А периоды, которые нам нужно вывести в запросе - это непрерывные промежутки времени между значениями 1 и 0 для каждого двигателя (этих промежутков, рабочих состояний двигателя, может быть очень много, например он может включаться и выключаться несколько раз в день).
Т.е. либо Вы немного не правильно поняли условие задачи, либо я не до конца понимаю логики Вашего решения.

[soleil@mmc]

уточнил - получи

with
  t1 as
  (
  select
    o.name Name, 
    a.metdate fDate,
    a.Value fValue
  from    
    objects o,
    archive a
  where 0=0
    and a.objid = o.id
    and trunc(a.metdate) < :p1+1  -- дата_ПО
    and trunc(a.metdate) >= :p0   -- дата_С  
  )

select
  Name,
  min(fDate) StartDT,
  max(fDate) EndDT,  
  max(fDate) - min(fDate) + 1 Period,
  fValue
from t1
group by Name, fValue
order by 2

Period я считаю в днях ибо никакого уточнения на этот счет не было
если потребуется его считать в часа/минутах, то нужно будет вводить аналитическую функцию LEAD - есть такая в МССКЛ?

fValue как раз показывает в каком состоянии твои объекты были в эти периоды, а то получается какой-то сферический конь в вакууме

ты запрос-то выполни или ты из теоретиков?
что не понятно спрашивай

[euronymous]

Поскольку я не силен в SQL, хотелось бы вот что еще узнать:
1) Зачем в where условие 0=0?
2) Для чего в строке max(fDate) - min(fDate) + 1 Period нужно прибавлять 1
3) в строке order by 2 - 2 - это какое поле?

Заранее благодарю за ответы.

[soleil@mmc]

Сообщение от euronymous

Поскольку я не силен в SQL, хотелось бы вот что еще узнать:
1) Зачем в where условие 0=0?

для красоты

Сообщение от euronymous

2) Для чего в строке max(fDate) - min(fDate) + 1 Period нужно прибавлять 1

если период нужно получать в днях, то к разнице между макс и мин нужно прибавлять 1, если в часах/мин/сек, то не нужно, но нужно вводить дополнительно анал.функу и получим нечто такое

with
  t1 as
  (
  select
    o.name Name, 
    a.metdate fDate,
    a.Value fValue
  from    
    objects o,
    archive a
  where 0=0
    and a.objid = o.id
    and trunc(a.metdate) < :p1+1  -- дата_ПО
    and trunc(a.metdate) >= :p0   -- дата_С  
  )
select
  Name,
  StartDT,
  lead(StartDT) over (order by StartDT) EndDT,
  fValue
from
    (select
      Name,
      min(fDate) StartDT,  
      fValue
    from t1
    group by Name, fValue)
order by 2

период сам сможешь оформить

Сообщение от euronymous

3) в строке order by 2 - 2 - это какое поле?

второе поле в запросе

[euronymous]

Если не трудно, поясните, пожалуйста вот этот блок

select
  Name,
  StartDT,
  lead(StartDT) over (order by StartDT) EndDT,
  fValue
from
    (select
      Name,
      min(fDate) StartDT,  
      fValue
    from t1
    group by Name, fValue)
order by 2

[soleil@mmc]

сложно объяснять суть запроса человеку, который забыл скл и при этом не знал его

внутренний запрос группирует данные, схлопывая все записи, в которых повторяются имя_объекта и его состояние, т.е. если друг за другом идет несколько строк объект_1, состояние 0, то эти нексколько строк превратятся в одну, при этом в поле дата будет стоять минимальное значение из всех возможных в схлопнутых (на самом деле они сгруппированы, но так понятнее, я надеюсь )
внешний запрос позволяет найти вторую часть периода - EndDT, которая берется как следующее значение, но тут есть некая загвоздка - 1) для последней строки неясно что возьмется и 2) правильно ли будет если для объекта_1 в конце его перечисления период закроется начальной датой для объекта_2?! 3) перевод в часы/минуты/сек сделаешь самостоятельно