Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 сумма времени простоев  [new]
LikaDv
Guest
Здравствуйте!
Подскажите, пожалуйста, как без курсоров решить такую задачу: необходимо посчитать сумму времени простоев в период между @tm1 и @tm2. Простоями считается время разрыва >= 5 минутам.

declare @tm1 datetime, @tm2 datetime

set @tm1 = '20120102 0:00:00.000'; set @tm2 = '20120102 00:59:59.999'

create table #tbl1 (dt datetime, tn numeric(9, 2))

insert into #tbl1 (dt, tn)
	select '20120101 23:59:59', 5.9 union all
	select '20120102 0:00:01', 5.5 union all
	select '20120102 0:01:11', 5.7 union all
	select '20120102 0:03:22', 6.0 union all
	select '20120102 0:08:21', 5.2 union all
	select '20120102 0:21:21', 7.1 union all
	select '20120102 0:23:55', 5.2 union all
	select '20120102 0:28:21', 4.9 union all
	select '20120102 0:48:44', 5.6 union all
	select '20120102 0:59:21', 5.0 union all
	select '20120102 0:59:59', 5.5 union all
	select '20120102 1:00:29', 5.0

select dt, tn from #tbl1 where dt between @tm1 and @tm2

drop table #tbl1


т.е. здесь простоями должны считаться время с 0:28:21 до 0:48:44 и время с 0:48:44 до 0:59:21, всего простоев 0:31:00.
11 дек 12, 09:14    [13610501]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
aleks2
Guest
Учи row_number() over(). И будет тибе щастье.
11 дек 12, 09:23    [13610525]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
iap
Member

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

сервер-то какой? Не SQL2012 часом?
11 дек 12, 09:29    [13610535]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
LikaDv
Guest
iap,

Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)
11 дек 12, 09:32    [13610544]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SET NOCOUNT ON;
DECLARE @tm1 DATETIME, @tm2 DATETIME;
SELECT @tm1='20120102 0:00:00.000',@tm2='20120102 00:59:59.999';
DECLARE @tbl1 TABLE(dt DATETIME, tn NUMERIC(9,2));
INSERT @tbl1(dt, tn)
SELECT '20120101 23:59:59', 5.9 UNION ALL
SELECT '20120102 0:00:01', 5.5 UNION ALL
SELECT '20120102 0:01:11', 5.7 UNION ALL
SELECT '20120102 0:03:22', 6.0 UNION ALL
SELECT '20120102 0:08:21', 5.2 UNION ALL
SELECT '20120102 0:21:21', 7.1 UNION ALL
SELECT '20120102 0:23:55', 5.2 UNION ALL
SELECT '20120102 0:28:21', 4.9 UNION ALL
SELECT '20120102 0:48:44', 5.6 UNION ALL
SELECT '20120102 0:59:21', 5.0 UNION ALL
SELECT '20120102 0:59:59', 5.5 UNION ALL
SELECT '20120102 1:00:29', 5.0;

WITH Intervals(dt,delta) AS(SELECT T.dt,DATEDIFF(SECOND,T.dt,(SELECT MIN(TT.dt) FROM @tbl1 TT WHERE TT.dt>T.dt))FROM @tbl1 T)
SELECT [downtime (sec)]=SUM(delta) FROM Intervals WHERE delta>=300;

SELECT [downtime (sec)]=SUM(Intervals.delta)
FROM @tbl1 T
CROSS APPLY(SELECT DATEDIFF(SECOND,T.dt,(SELECT MIN(TT.dt) FROM @tbl1 TT WHERE TT.dt>T.dt))) Intervals(delta)
WHERE Intervals.delta>=300;
11 дек 12, 10:04    [13610677]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Ай-яй-яй! Тег-то где?!
SET NOCOUNT ON;
DECLARE @tm1 DATETIME, @tm2 DATETIME;
SELECT @tm1='20120102 0:00:00.000',@tm2='20120102 00:59:59.999';
DECLARE @tbl1 TABLE(dt DATETIME, tn NUMERIC(9,2));
INSERT @tbl1(dt, tn)
	SELECT '20120101 23:59:59', 5.9 UNION ALL
	SELECT '20120102 0:00:01', 5.5 UNION ALL
	SELECT '20120102 0:01:11', 5.7 UNION ALL
	SELECT '20120102 0:03:22', 6.0 UNION ALL
	SELECT '20120102 0:08:21', 5.2 UNION ALL
	SELECT '20120102 0:21:21', 7.1 UNION ALL
	SELECT '20120102 0:23:55', 5.2 UNION ALL
	SELECT '20120102 0:28:21', 4.9 UNION ALL
	SELECT '20120102 0:48:44', 5.6 UNION ALL
	SELECT '20120102 0:59:21', 5.0 UNION ALL
	SELECT '20120102 0:59:59', 5.5 UNION ALL
	SELECT '20120102 1:00:29', 5.0;

WITH Intervals(dt,delta) AS(SELECT T.dt,DATEDIFF(SECOND,T.dt,(SELECT MIN(TT.dt) FROM @tbl1 TT WHERE TT.dt>T.dt))FROM @tbl1 T)
SELECT [downtime (sec)]=SUM(delta) FROM Intervals WHERE delta>=300;

SELECT [downtime (sec)]=SUM(Intervals.delta)
FROM @tbl1 T
CROSS APPLY(SELECT DATEDIFF(SECOND,T.dt,(SELECT MIN(TT.dt) FROM @tbl1 TT WHERE TT.dt>T.dt))) Intervals(delta)
WHERE Intervals.delta>=300;
11 дек 12, 10:05    [13610692]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Про ограничение по времени забыл:
SET NOCOUNT ON;
DECLARE @tm1 DATETIME, @tm2 DATETIME;
SELECT @tm1='20120102',@tm2='20120103';
DECLARE @tbl1 TABLE(dt DATETIME, tn NUMERIC(9,2));
INSERT @tbl1(dt, tn)
	SELECT '20120101 23:59:59', 5.9 UNION ALL
	SELECT '20120102 0:00:01', 5.5 UNION ALL
	SELECT '20120102 0:01:11', 5.7 UNION ALL
	SELECT '20120102 0:03:22', 6.0 UNION ALL
	SELECT '20120102 0:08:21', 5.2 UNION ALL
	SELECT '20120102 0:21:21', 7.1 UNION ALL
	SELECT '20120102 0:23:55', 5.2 UNION ALL
	SELECT '20120102 0:28:21', 4.9 UNION ALL
	SELECT '20120102 0:48:44', 5.6 UNION ALL
	SELECT '20120102 0:59:21', 5.0 UNION ALL
	SELECT '20120102 0:59:59', 5.5 UNION ALL
	SELECT '20120102 1:00:29', 5.0;

WITH Intervals(dt,delta) AS(SELECT T.dt,DATEDIFF(SECOND,T.dt,(SELECT MIN(TT.dt) FROM @tbl1 TT WHERE TT.dt>T.dt))FROM @tbl1 T)
SELECT [downtime (sec)]=SUM(delta) FROM Intervals WHERE dt>=@tm1 AND dt<@tm2 AND delta>=300;

SELECT [downtime (sec)]=SUM(Intervals.delta)
FROM @tbl1 T
CROSS APPLY(SELECT DATEDIFF(SECOND,T.dt,(SELECT MIN(TT.dt) FROM @tbl1 TT WHERE TT.dt>T.dt))) Intervals(delta)
WHERE T.dt>=@tm1 AND T.dt<@tm2 AND Intervals.delta>=300;
11 дек 12, 10:40    [13610928]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
aleks2
Guest
declare @tm1 datetime, @tm2 datetime

set @tm1 = '20120102 0:00:00.000'; set @tm2 = '20120102 00:59:59.999'

declare @tbl1 table(dt datetime, tn numeric(9, 2))

insert into @tbl1 (dt, tn)
	select '20120101 23:59:59', 5.9 union all
	select '20120102 0:00:01', 5.5 union all
	select '20120102 0:01:11', 5.7 union all
	select '20120102 0:03:22', 6.0 union all
	select '20120102 0:08:21', 5.2 union all
	select '20120102 0:21:21', 7.1 union all
	select '20120102 0:23:55', 5.2 union all
	select '20120102 0:28:21', 4.9 union all
	select '20120102 0:48:44', 5.6 union all
	select '20120102 0:59:21', 5.0 union all
	select '20120102 0:59:59', 5.5 union all
	select '20120102 1:00:29', 5.0

declare @t table(dt datetime, tn numeric(9, 2), n int identity primary key clustered);

;with
 T as (select dt, tn from @tbl1 where dt between @tm1 and @tm2)
insert @t(dt, tn)
select dt, tn from T where not exists(select * from @tbl1 TT where T.dt<TT.dt and TT.dt between T.dt and dateadd(minute, 5, T.dt ))
union
select dt, tn from T where not exists(select * from @tbl1 TT where T.dt>TT.dt and TT.dt between dateadd(minute, -5, T.dt ) and T.dt )
order by dt ASC

select * from @t;

select sum(datediff(second, T.dt, TT.dt)) from @t T inner join @t TT on TT.n=T.n+1
11 дек 12, 10:43    [13610944]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
LikaDv
Guest
Ребята, большое Вам спасибо!
Я конечно ошиблась, т.к. еще один простой не заметила, т.е. в итоге 51 минута ровно.
aleks2, Ваше решение правильный результат дает.
iap, простите, может я что-то не правильно поняла, но у Вас дает неправильные результат.
А в любом случае спасибо! Буду сейчас пытаться понять, как это работает %)
11 дек 12, 10:59    [13611044]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
LikaDv
Guest
Помогите, пожалуйста, еще!
Вот в такой ситуации неправильно считает:
...
set @tm1 = '20121211 13:00:00'
set @tm2 = '20121211 13:59:59.997'
...
insert into @tbl1 (dt, tn)
	select '20121211 12:59:59', 5.9 union all
	select '20121211 13:10:01', 5.5 union all
	select '20121211 13:11:11', 5.7 union all
	select '20121211 13:13:22', 6.0 union all
	select '20121211 13:18:21', 5.2 union all
	select '20121211 13:18:21', 5.2 union all
	select '20121211 13:18:21', 5.2 union all
	select '20121211 13:21:21', 7.1 union all
	select '20121211 13:23:55', 5.2 union all
	select '20121211 13:28:21', 4.9 union all
	select '20121211 13:48:44', 5.6 union all
	select '20121211 13:59:21', 5.0 union all
	select '20121211 13:59:59', 5.5 union all
	select '20121211 14:00:29', 5.0


Должно быть 2461, а считает 2960 сек.
11 дек 12, 13:25    [13612263]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
aleks2
Guest
Ну низзя же на фсем готовом?
В этом случае один интервал "простоя" начинается раньше вашего интервала.
Вот и обработайте этот случай.
11 дек 12, 13:52    [13612502]     Ответить | Цитировать Сообщить модератору
 Re: сумма времени простоев  [new]
LikaDv
Guest
Вот сделала так, вроде работает правильно.

declare @tm1 datetime, @tm2 datetime

set @tm1 = '20121211 13:00:00.000'
set @tm2 = '20121211 13:59:59.997'

declare @tbl1 table(dt datetime, tn numeric(9, 2))

insert into @tbl1 (dt, tn)
	select '20121211 12:59:59', 5.9 union all
	select '20121211 13:05:01', 5.5 union all
	select '20121211 13:11:11', 5.7 union all
	select '20121211 13:13:22', 6.0 union all
	select '20121211 13:18:21', 5.2 union all
	select '20121211 13:21:21', 7.1 union all
	select '20121211 13:21:21', 7.1 union all
	select '20121211 13:21:21', 7.1 union all
	select '20121211 13:21:21', 7.1 union all
	select '20121211 13:23:55', 5.2 union all
	select '20121211 13:28:21', 4.9 union all
	select '20121211 13:48:44', 5.6 union all
	select '20121211 13:54:21', 5.0 union all
	select '20121211 13:54:50', 5.5 union all
	select '20121211 14:00:29', 5.0

declare @t2 table(dt datetime, n int identity primary key clustered)

;with
	T as (select dt from @tbl1 where dt between @tm1 and @tm2)

insert @t2 
	select @tm1 union all
	select dt from T union all
	select @tm2

select * from @t2

select T.dt, TT.dt, datediff(ss, T.dt, TT.dt) from @t2 T inner join @t2 TT on TT.n = T.n + 1 where datediff(ss, T.dt, TT.dt) >= 300

select sum(datediff(mi, T.dt, TT.dt)) from @t2 T inner join @t2 TT on TT.n = T.n + 1 where datediff(ss, T.dt, TT.dt) >= 300


все правильно? или можно было как-то оптимальнее?

и еще:
insert @t2 
	select @tm1 union all
	select dt from T union all 
	select @tm2

в @tbl1 записи записываются по порядку, но может быть здесь мне нужно было делать сортировку? select dt from T order by dt
или это лишнее? и будет ли так работать?
13 дек 12, 08:54    [13623452]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить