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

Откуда:
Сообщений: 66
/* Таблица PARAMS содержит некоторые параметры технологических объектов, STATELOG это журнал состояний объектов. Есть некий объект (в данном случае насосный агрегат), который может самопроизвольно отключаться и выключаться - эти события фиксируются в журнале состояний: т.е.добавляются в таблицу STATELOG строки (1,1,<Дата время>) - включение насоса (2,1,<Дата время>) выключение. Насос может включаться и выключаться по несколько раз в сутки.
Вопрос в следующем: как красиво написать запрос, чтобы посчитать сколько времени работал агрегат в течении суток? Можно ли одним запросом вернуть это значение?
*/

DROP TABLE PARAMS;
CREATE TABLE PARAMS (
IdPar int NOT NULL,
NamePar varchar(250),
IdType int NOT NULL,
Comment varchar(250));
ALTER TABLE PARAMS
ADD CONSTRAINT PK_PARAMS
PRIMARY KEY (IdPar);

DROP TABLE STATELOG;
CREATE TABLE STATELOG (
IdPar int NOT NULL,
IdObj int NOT NULL,
TimeKey datetime NOT NULL);
ALTER TABLE STATELOG
ADD CONSTRAINT PK_STATELOG
PRIMARY KEY (IdPar, IdObj, TimeKey);

DROP TABLE OBJECTS;
CREATE TABLE OBJECTS (
IdObj int NOT NULL,
OwnObj int,
IdType int,
NameObj varchar(50),
Comment varchar(250));
ALTER TABLE OBJECTS
ADD CONSTRAINT PK_OBJECTS
PRIMARY KEY (IdObj);

INSERT INTO OBJECTS (IDOBJ,OWNOBJ,IDTYPE,NAMEOBJ,COMMENT) VALUES (1,1,1,'Двигатель насосного агрегата',NULL);
INSERT INTO PARAMS (IDPAR,NAMEPAR,IDTYPE,COMMENT) VALUES (1,'Запуск двигателя',1,NULL);
INSERT INTO PARAMS (IDPAR,NAMEPAR,IDTYPE,COMMENT) VALUES (2,'Останов двигателя',1,NULL);
16 мар 04, 09:47    [579457]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Как-то так можно

declare @t table(f1 datetime, f2 varchar(100))


insert @t
select '2003-11-26 03:00:36.000', '100.100.250.250 failed '
union all select '2003-11-26 03:00:37.000', '100.100.250.250 failed '
union all select '2003-11-26 03:04:09.000', '100.100.250.250 failed '
union all select '2003-11-26 03:04:09.000', '100.100.250.250 failed '
union all select '2003-11-30 03:00:36.000', '100.100.250.250 failed '
union all select '2003-11-30 03:00:37.000', '100.100.250.250 failed '
union all select '2003-11-30 03:04:09.000', '100.100.250.250 failed '
union all select '2003-11-30 03:04:09.000', '100.100.250.250 failed '
union all select '2003-12-03 03:00:36.000', '100.100.250.250 failed '
union all select '2003-12-03 03:00:37.000', '100.100.250.250 failed '

select f2, f3, max(f1), datediff(ss, f3, max(f1))
from (
select f1, (select top 1 b.f1 from @t b where b.f1 < a.f1 and datediff(ss, b.f1, a.f1) < 240 order by b.f1) as f3,f2 from @t a
) AS a
group by f2, f3
order by f2, f3
16 мар 04, 09:52    [579468]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
Trong
Member

Откуда: Novosibirsk
Сообщений: 759
Добавьте плз insert into ы датами и временем для таблицы STATELOG
16 мар 04, 09:52    [579470]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
Одним запросом можно..
нужен пример заполненной таблицы - и какой должен быть результат :)

Алгоритм простой..
Для каждой записи Тип1(включен) находим максимальную предыдущую Тип2
с переносом(если такая была за прошлый день) на начало дня...
Для каждой из найденных Тип2 записей выбираем запись Тип1 с максимальным положительным расстоянием от нее..
Время нахождения в состоянии 1 =
Сумме по всем Тип2 записям этих получившихся интервалов..
16 мар 04, 10:21    [579540]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5637
CREATE TABLE #PARAMS ( 

IdPar int NOT NULL,
NamePar varchar(250),
IdType int NOT NULL,
Comment varchar(250) null)
CREATE TABLE #STATELOG (
IdPar int NOT NULL,
IdObj int NOT NULL,
TimeKey datetime NOT NULL)
CREATE TABLE #OBJECTS (
IdObj int NOT NULL,
OwnObj int,
IdType int,
NameObj varchar(50),
Comment varchar(250) null)
INSERT INTO #OBJECTS (IDOBJ,OWNOBJ,IDTYPE,NAMEOBJ,COMMENT) VALUES (1,1,1,'Двигатель насосного агрегата',NULL)
INSERT INTO #PARAMS (IDPAR,NAMEPAR,IDTYPE,COMMENT) VALUES (1,'Запуск двигателя',1,NULL)
INSERT INTO #PARAMS (IDPAR,NAMEPAR,IDTYPE,COMMENT) VALUES (2,'Останов двигателя',1,NULL)
insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-16 00:55:00')
insert into #statelog (idpar, idobj, timekey) values (2,1,'2004-03-16 01:45:00')
insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-16 02:55:00')
insert into #statelog (idpar, idobj, timekey) values (2,1,'2004-03-16 03:05:00')

select idobj, sum(datediff(ss, tstart, tend))
from
(select idobj, timekey tstart,
(select top 1 timekey from #statelog sl1 where sl1.idobj = sl.idobj and idpar = 2 and sl.timekey<sl1.timekey) tend
from #statelog sl where idpar = 1
) a
group by idobj

DROP TABLE #PARAMS
DROP TABLE #STATELOG
DROP TABLE #OBJECTS



для спящего время бодрствования равносильно сну
16 мар 04, 10:33    [579588]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
2Алексей2003
А ежели они не обязательно чередуются??
(результаты получаются опросом состояния например)

Например
insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-16 00:55:00')

--Двигатель работает..

insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-16 02:00:00')
--Двигатель работает

insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-16 03:05:00')
--Двигатель работает

И с началом дня непонятно.
16 мар 04, 10:39    [579607]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5637
а ежели мир перевернется? что задал, то и получил...

а что с началом дня??

CREATE TABLE #PARAMS ( 

IdPar int NOT NULL,
NamePar varchar(250),
IdType int NOT NULL,
Comment varchar(250) null)

CREATE TABLE #STATELOG (
IdPar int NOT NULL,
IdObj int NOT NULL,
TimeKey datetime NOT NULL)

CREATE TABLE #OBJECTS (
IdObj int NOT NULL,
OwnObj int,
IdType int,
NameObj varchar(50),
Comment varchar(250) null)

INSERT INTO #OBJECTS (IDOBJ,OWNOBJ,IDTYPE,NAMEOBJ,COMMENT) VALUES (1,1,1,'Двигатель насосного агрегата',NULL)
INSERT INTO #PARAMS (IDPAR,NAMEPAR,IDTYPE,COMMENT) VALUES (1,'Запуск двигателя',1,NULL)
INSERT INTO #PARAMS (IDPAR,NAMEPAR,IDTYPE,COMMENT) VALUES (2,'Останов двигателя',1,NULL)
insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-15 00:55:00')
insert into #statelog (idpar, idobj, timekey) values (2,1,'2004-03-16 01:45:00')
insert into #statelog (idpar, idobj, timekey) values (1,1,'2004-03-16 02:55:00')
insert into #statelog (idpar, idobj, timekey) values (2,1,'2004-03-16 03:05:00')

select idobj, sum(datediff(ss, tstart, tend))
from
(select idobj, timekey tstart,
(select top 1 timekey from #statelog sl1 where sl1.idobj = sl.idobj and idpar = 2 and sl.timekey<sl1.timekey) tend
from #statelog sl where idpar = 1
) a
group by idobj

DROP TABLE #PARAMS
DROP TABLE #STATELOG
DROP TABLE #OBJECTS


для спящего время бодрствования равносильно сну
16 мар 04, 10:44    [579625]     Ответить | Цитировать Сообщить модератору
 Скрипт для заполнения  [new]
sanitar
Member

Откуда:
Сообщений: 66
/*скрипт для заполнения журнала состояний. Результат нужен скажем в секундах*/
declare @timekey datetime
declare @IdPar int
declare @IdObj int
declare @well int

truncate table STATELOG

set dateformat DMY
set @well=1
set @IdPar=2
set @IdObj=1
/*для одного объекта*/
while @well<2
begin
set @timekey='01/01/2003 00:00:00'
while @timekey<'01/01/2004 00:00:00'
begin
insert into STATELOG (IdPar,IdObj,TimeKey) values (@IdPar,@IdObj,@timekey)
if @idPar=2
set @IdPar=1
else set @IdPar=2
set @timekey=dateadd(minute,30+Rand()*20,@timekey)
end
set @well=@well+1
end
16 мар 04, 11:09    [579699]     Ответить | Цитировать Сообщить модератору
 Хронология фиксирования записей  [new]
sanitar
Member

Откуда:
Сообщений: 66
Хронология соблюдается ) т.е. двигатель не может отключаться или включаться два и более раз подряд
16 мар 04, 11:13    [579709]     Ответить | Цитировать Сообщить модератору
 начало дня  [new]
sanitar
Member

Откуда:
Сообщений: 66
в начале дня двигатель может быть как включен так и выключен, тоже самое с концом суток, т.е. если в начале дня был включен значит считаем что он работает с начала суток
16 мар 04, 11:15    [579724]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
т.е. двигатель не может отключаться или включаться два и более раз подряд

так не интересно!! :)

на всякий случай привожу еще и свой вариант..
declare @date datetime -- Date without time


declare @type int -- type val={1,2}

select @date='20040316',@type=1

select a.idobj,sum(datediff(ss,a.timekey,a.nexttime))
from
(select s1.idobj,s1.timekey,nexttime=min(isnull(s2.timekey,dateadd(dd,1,@date)))
from statelog s1
left join statelog s2 on(s1.idobj=s2.idobj and s1.timekey<s2.timekey and s1.idpar<>s2.idpar)
where s1.timekey>=@date and s1.timekey<dateadd(dd,1,@date)
and s1.idpar=@type
group by s1.idobj,s1.timekey
)as a
16 мар 04, 11:44    [579817]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
когда-то давно я писал чего-то подобное, посмотрите может подойдёт
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=5470
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=13696
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=1661
16 мар 04, 13:24    [580224]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
sanitar
Member

Откуда:
Сообщений: 66
/*ВСЕМ СПАСИБО ВСЕ ПОЛУЧИЛОСЬ. ЗАДЕЛА ТАК*/
if
exists(select * from sysobjects where name='fRunDuration' and xtype='FN')
drop function dbo.fRunDuration
go
/*Время работы объекта за сутки*/
create function fRunDuration (@IdObj int,@date datetime)
returns int
as
begin
declare @rd int
declare @BegTimeSlice datetime
declare @EndTimeSlice datetime

/*начало суток*/
set @BegTimeSlice=convert(datetime,convert(varchar(8),@date,112),112)
/*конец суток*/
set @EndTimeSlice=dateadd(hour,24,@BegTimeSlice)
select @rd=sum(datediff(minute, tstart, tend))
from
(select timekey tstart,
(select top 1 timekey
from statelog sl1
where sl1.idObj = @idObj and idPar = 2 and sl.timekey<sl1.timekey)
tend
from statelog sl where idObj=@IdObj and idPar = 1 and timekey>=@BegTimeSlice and timekey<@EndTimeSlice
) a
return @rd
end
go
16 мар 04, 15:56    [580793]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
ну и неправильно :)

select top 1 timekey 

from statelog sl1
where sl1.idObj = @idObj and idPar = 2 and sl.timekey<sl1.timekey
/*+*/
ОRDER BY timekey
16 мар 04, 16:07    [580839]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
sanitar
Member

Откуда:
Сообщений: 66
select @rd=sum(datediff(minute, tstart, tend))
надо переписать как
select @rd=sum(datediff(ss, tstart, tend)) чтобы в секундах

а так тесты и без order by нормально возвращают результаты в минутах
[SRC][/SRC]
16 мар 04, 17:06    [581088]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
sanitar
Member

Откуда:
Сообщений: 66
короче и так и так не правильно считает. Ошибка с границами суток
16 мар 04, 17:31    [581175]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
Ошибка с границами суток

Дак об чем и речь была !!
В приведенном мною примере должна была учитываеться правая граница дня , правда пропустил чуть в joinе , исправляю :

declare @date datetime -- Date without time


declare @type int -- type val={1,2}

select @date='20040316',@type=1

select a.idobj,sum(datediff(ss,a.timekey,a.nexttime))
from
(select s1.idobj,s1.timekey,nexttime=min(isnull(s2.timekey,dateadd(dd,1,@date)))
from statelog s1
left join statelog s2 on(s1.idobj=s2.idobj and s1.timekey<s2.timekey and
/*+ */
s2.timekey<dateadd(dd,1,@date)
and
/*+end*/
s1.idpar<>s2.idpar)
where s1.timekey>=@date and s1.timekey<dateadd(dd,1,@date)
and s1.idpar=@type
group by s1.idobj,s1.timekey
)as a


если с левой границей - еще надо будет финт один сделать..
16 мар 04, 19:16    [581453]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
Для варианта Алексея2003:

isnull((select top 1 timekey from #statelog sl1 where sl1.idobj = sl.idobj and idpar = 2 and sl.timekey<sl1.timekey 

and sl1.timekey<dateadd(dd,1,@date)),dateadd(dd,1,@date))
16 мар 04, 19:26    [581467]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
RatTail
Member [заблокирован]

Откуда: Z
Сообщений: 4517
Единственно правильный (и красивый) подход к сабжу можно увидеть здесь:
http://www12.brinkster.com/akuz3/topic.asp?TOPIC_ID=167
17 мар 04, 00:55    [581661]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
Поостерегся бы я это решение(по ссылке) называть Единственно правильным, ибо в принципе оно не работает в случае >2 состояний :)
17 мар 04, 08:39    [581820]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
_maxP_
Member

Откуда: Latvia
Сообщений: 3
declare @pbegin datetime, -- period begin
@pend datetime -- period end

set @pbegin = '2003-01-01 00:00:00.000'
set @pend = '2003-01-01 01:30:00.000'

select sum(type2) - sum(type1) + (case when sum(type2) < sum(type1) then datediff(mi, @pbegin, @pend) else 0 end) as work_time
from(
select top 100 percent case when idpar = 2 then datediff(mi, @pbegin, timekey) else 0 end as type2,
case when idpar = 1 then datediff(mi, @pbegin, timekey) else 0 end as type1
from statelog
where timekey between @pbegin and @pend
order by timekey
) as t
17 мар 04, 10:56    [582147]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
sanitar
Member

Откуда:
Сообщений: 66

/*Добавим такие значения*/
TRUNCATE TABLE STATELOG;
INSERT INTO STATELOG (1,1,'31.12.2002 1:00:00');
INSERT INTO STATELOG (2,1,'02.01.2003 0:50:00');

Итак, получается, что агрегат работал целые сутки а красивое решение, предложенное MaxP возвращает NULL. Остальные тоже неправильные - все.
17 мар 04, 12:42    [582485]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
sanitar
Member

Откуда:
Сообщений: 66
сутки я имею ввиду '01/01/2003'
17 мар 04, 12:47    [582501]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
rst
Member

Откуда: Йобурк
Сообщений: 1005
Остальные тоже неправильные - все

Ну дак... гхм .. конечно..

если с левой границей - еще надо будет финт один сделать..
:)

Акузовский вариант не плох - надо сообразить как бы еще универсально сделать...
17 мар 04, 12:54    [582521]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите пожалуйста красивое решение для такой задачки.  [new]
RatTail
Member [заблокирован]

Откуда: Z
Сообщений: 4517
Трудно сделать проще:


create table #t (dt datetime, f varchar(3))
insert into #t
select '2004/01/01 03:00', 'OUT' union all
select '2004/01/01 06:00', 'IN' union all
select '2004/01/01 11:00', 'OUT' union all
select '2004/01/01 14:00', 'IN' union all
select '2004/01/01 17:00', 'OUT' union all
select '2004/01/01 20:00', 'IN' union all
select '2004/01/05 02:00', 'OUT'

select dd,
case when ff<0 then ff+24 else ff end TotalTime
from
(
select dd=convert(varchar(10),dt,104),
ff=sum(case when f='OUT' then datepart(hh,dt) else -1*datepart(hh,dt) end)
from #t
group by convert(varchar(10),dt,104)
)tt

drop table #t

dd TotalTime
---------- -----------

01.01.2004 15
05.01.2004 2


Осталось:
добавить сюда даты, когда дежурство началось "вчера", а закончилось -
"послезавтра" (или позже, как в случае выше - заступил на смену 1-го
января в 20.00, а сдал пост аж 5-го числа в 02.00 часа ночи).

Но:
этих дат (2, 3, 4 января) нет в природе (в смысле - в таблице).......

Твой ход, rst :)
17 мар 04, 14:18    [582805]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить