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

Имеется таблица с со следующими полями:
DeviceID;SwitchDateTime;IsON

То есть имеются аппараты, дата включения/выключения, IsOn(признак включен или выключен).
Нужно за заданный промежуток времени посчитать время работы каждого аппарата.

Заранее, Спасибо.
26 ноя 13, 17:08    [15193024]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
TCCT, а ты уверен, что у тебя дата включения выключения в периоде будет одна? Я бы сделал два аргумента...
26 ноя 13, 17:13    [15193067]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
MedBrat,

Хотя туплю. можно и так привязаться к дате и признаку вкл выкл... только писать больше.
26 ноя 13, 17:14    [15193077]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
Данные в таком формате приходят.
И тут не дата, а дата и время. Забыл уточнить.
26 ноя 13, 17:15    [15193085]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
ТССТ, полей мало. надо какой-то "номер операции". если одно включение и выключение в периоде, то можно вычислить. а так тебе надо найти предыдущую дату по этому аппарату. подняться на предыдущую запись можно только если их две или есть номер операции.
26 ноя 13, 17:16    [15193097]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
ТССТ
И тут не дата, а дата и время. Забыл уточнить.

Для каждой записи с "признак выключен" ищите запись с ментшей датой и "признак включен"
Разность этих дат и будет временем работы аппарата
26 ноя 13, 17:17    [15193105]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
TCCT
Guest
Glory,

Точно, спасибо.

А то я что-то загнался и решил отталкиваться от попарной выборки и застопорился на отсечении повторяющихся записей. )
26 ноя 13, 17:22    [15193150]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Glory,
на сколько меньшей? если в периоде раз пять он вкл и выкл. и пять включений... мы не сможем подняться на искомую операцию
26 ноя 13, 17:24    [15193165]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
MedBrat
Glory,
на сколько меньшей?

Не на сколько, а первой меньшей
26 ноя 13, 17:24    [15193172]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Glory, ок, если выключение-включение в период не одно, то под фильтр дата1 < даты5 попадет еще несколько...
26 ноя 13, 17:25    [15193188]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
MedBrat
Glory, ок, если выключение-включение в период не одно, то под фильтр дата1 < даты5 попадет еще несколько...

Еще раз - первой меньшей
26 ноя 13, 17:26    [15193196]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
Glory,

Но если последний показатель был "Вкл" - то он в эту выборку не попадет.
26 ноя 13, 17:26    [15193198]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
ТССТ
Но если последний показатель был "Вкл" - то он в эту выборку не попадет.

Понятно, что заданный промежуток времени будет являтся виртуальным "выключением"
26 ноя 13, 17:28    [15193216]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Glory,

ок, как будет выглядеть фильтр?
26 ноя 13, 17:29    [15193223]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
MedBrat
ок, как будет выглядеть фильтр?

Мда
вы знаете что такое TOP 1 ... ORDER BY в коррелированном запросе ?
26 ноя 13, 17:30    [15193235]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Glory,

ауч. согласен.
26 ноя 13, 17:32    [15193251]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
Glory
ТССТ
Но если последний показатель был "Вкл" - то он в эту выборку не попадет.

Понятно, что заданный промежуток времени будет являтся виртуальным "выключением"

Да и исходя из этого, я вижу что он может быть либо виртуально выключенным (теряем последнее время включения),
либо виртуально включенным (тогда теряется первое время выключения).
А нужно все эти данные учитывать.
26 ноя 13, 17:37    [15193297]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
ТССТ,

Хотя можно пару юнионов...
26 ноя 13, 17:38    [15193308]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
ТССТ
Да и исходя из этого, я вижу что он может быть либо виртуально выключенным (теряем последнее время включения),
либо виртуально включенным (тогда теряется первое время выключения).

Почему теряем то ? И как тогда с постановкой задачи "Нужно за заданный промежуток времени" ?
26 ноя 13, 17:38    [15193311]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
Glory,
Может немного ошибся с постановкой.

Задается @StartDate и @EndDate

@StartDate = 10.02.2013
@EndDate = 28.02.2013

Например имеем данные (для примера в днях):
ID; DateTime; IsOn
1; 19.02.2013; 0
1; 21.02.2013; 1
1; 23.02.2013; 0
1; 25.02.2013; 1
1; 26.02.2013; 0
1; 27.02.2013; 1
-------------------------------------------
Промежутки будут такими:
10.02.2013 - 19.02.2013
21.02.2013 - 23.02.2013
25.02.2013 - 26.02.2013
27.02.2013 - 28.02.2013
----------------------
Итого дней: 17
26 ноя 13, 17:54    [15193486]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
ТССТ
Промежутки будут такими:
10.02.2013 - 19.02.2013
21.02.2013 - 23.02.2013
25.02.2013 - 26.02.2013
27.02.2013 - 28.02.2013

И в чем проблема ?
26 ноя 13, 17:59    [15193535]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
Glory,

	SELECT
	     s.DeviceID,
       	(SELECT TOP 1 SwitchDateTime FROM tDevicesSwitch WHERE 
           	DeviceID = s.DeviceID AND IsOn = 1
            AND SwitchDateTime < s.SwitchDateTime
            ORDER BY SwitchDateTime DESC) AS DateOn,
        s.SwitchDateTime AS DateOff,
        s.IsOn
    FROM
    	tDevicesSwitch s
    WHERE
    	s.DeviceID = @DevID AND s.IsOn = 0
    ORDER BY
    	s.SwitchDateTime


Проблема в том, что при такого плана выборке, если последнее значение в промежутке было On, то оно теряется.
26 ноя 13, 18:05    [15193586]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
Glory
Member

Откуда:
Сообщений: 104751
Потому что сначала надо найти все пары вкл-выкл. А потом уже искать их пересечение с заданным периодом
26 ноя 13, 18:12    [15193636]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
ТССТ
Guest
Glory,

Точно, спасибо. Совсем уже мозги к концу дня запарились.
26 ноя 13, 18:15    [15193656]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во дней  [new]
aleks2
Guest
Glory
Потому что сначала надо найти все пары вкл-выкл. А потом уже искать их пересечение с заданным периодом

Вот нафега усложнять cебе жизнь?

Нужно за заданный промежуток времени посчитать время работы каждого аппарата

Время работы = Сумма по i(ВЫКЛ(i)-ВКЛ(i)), где @Beg <= ВЫКЛ(i) и ВКЛ(i) <= @End

= Сумма по i(ВЫКЛ(i)) - Сумма по i(ВКЛ(i))

Т.е. нахер не нужно никакие интервалы искать. Просто суммируем все записи в интервале ВЫКЛ с плюсом, ВКЛ с минусом.

Некоторую, кажущуюся, сложность представляет наличие границ @Beg и @End.
Но если чуть-чуть подумать, то все элементарно.

Сумма по i(ВЫКЛ(i)-ВКЛ(i)) = Сумма по i(ВЫКЛ(i)) - Сумма по i(ВКЛ(i)) + Обработка_концов

Обработка_концов = (Если первая запись ВЫКЛ, то -@Beg) + (Если последняя запись ВКЛ, то +@End)

Ну и частный случай "ни одной записи в интервале" - тоже элементарен.
27 ноя 13, 06:06    [15195943]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить