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

Откуда:
Сообщений: 6
Доброго дня, уважаемые форумчане.
Есть задача, помогите, пожалуйста понять как реализовать, возможно ли это

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

Исходник:
device_idstart_dateend_datesboy_idfinished
1127009.01.2017 8:3509.01.2017 19:351011
1127009.01.2017 19:3510.01.2017 8:351001
1127010.01.2017 8:3510.01.2017 19:3511
1127010.01.2017 19:3511.01.2017 8:35-101
1127011.01.2017 8:3511.01.2017 19:35231
1127011.01.2017 19:3512.01.2017 8:35581
1127012.01.2017 8:3512.01.2017 10:051011
1127012.01.2017 10:0512.01.2017 10:15341
1127012.01.2017 10:1512.01.2017 19:35281
1127012.01.2017 19:35NULL50


Что требуется:

device_idsboy_iddatehourduration,mi
1127010109.01.20170:000
1127010109.01.20171:000
1127010109.01.20172:000
1127010109.01.20173:000
1127010109.01.20174:000
1127010109.01.20175:000
1127010109.01.20176:000
1127010109.01.20177:000
1127010109.01.20178:0025
1127010109.01.20179:0060
1127010109.01.201710:0060
1127010109.01.201711:0060
1127010109.01.201712:0060
1127010109.01.201713:0060
1127010109.01.201714:0060
1127010109.01.201715:0060
1127010109.01.201716:0060
1127010109.01.201717:0060
1127010109.01.201718:0060
1127010109.01.201719:0035
1127010009.01.201719:0025
1127010009.01.201720:0060
1127010009.01.201721:0060
1127010009.01.201722:0060
1127010009.01.201723:0060
1127010010.01.20170:0060
1127010010.01.20171:0060
1127010010.01.20172:0060
1127010010.01.20173:0060
1127010010.01.20174:0060
1127010010.01.20175:0060
1127010010.01.20176:0060
1127010010.01.20177:0060
1127010010.01.20178:0035
11270110.01.20178:0025
1 авг 17, 11:06    [20690663]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Владислав Колосов
Member

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

создайте почасовой календарь и считайте count(*) между временем от и до.
1 авг 17, 11:24    [20690799]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Korzhech
Member

Откуда:
Сообщений: 6
Владислав Колосов,
Спасибо за совет, но честно говоря не понял как продолжительность считать count(*) -ом
1 авг 17, 14:14    [20691741]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Browny
Member

Откуда:
Сообщений: 53
Если я правильно понял пример, группировать (считать, сколько конкретный станок дал сбоев за один час) не требуется - надо просто вывести перечень сбоев, "привязав" их к часам?

Вариант уже был предложен - часовое расписание...
declare @sb table(device_id int,start_date datetime,end_date datetime,sboy_id int,finished int);
set dateformat dmy;
insert into @sb(device_id,start_date,end_date,sboy_id,finished) 
	Select 11270,	'09.01.2017 8:35',		'09.01.2017 19:35',	101,	1 union
	Select 11270,	'09.01.2017 19:35',	'10.01.2017 8:35',		100,	1 union
	Select 11272,	'10.01.2017 8:35',	'10.01.2017 19:35',		1,		1 union
	Select 11272,	'11.01.2017 8:35',	'11.01.2017 19:35',		23,		1 union
	Select 11272,	'11.01.2017 19:35',	'12.01.2017 8:35',		58,		1 union
	Select 11270,	'12.01.2017 8:35',	'12.01.2017 10:05',		101,	1 union
	Select 11270,	'12.01.2017 10:05',	'12.01.2017 10:15',		34,		1 union
	Select 11270,	'12.01.2017 10:15',	'12.01.2017 19:35',		28,		1

--Select * from @sb

;with 
borderdates(maxdate,startdate) as (
	Select max(end_date), dateadd(hour, datepart(hour, min(start_date)), Cast(Floor(Cast(min(start_date) as Float)) as Datetime)) from @sb
),
schedule(datestart, datefinish) as (
	Select borderdates.startdate, dateadd(hour, 1, borderdates.startdate) from borderdates
	union all
	Select schedule.datefinish, dateadd(hour, 1, schedule.datefinish) from schedule
	cross join borderdates
	where schedule.datefinish <= borderdates.maxdate

)
Select 
Cast(Floor(Cast(s.datestart as float)) as datetime) as [date],
Right('0' + convert(varchar,datepart(hour, s.datestart)) + ':00',5) as [time],
b.*
from schedule s
inner join @sb b on s.datestart between b.start_date and b.end_date
order by s.datestart asc
1 авг 17, 14:27    [20691796]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Korzhech
Member

Откуда:
Сообщений: 6
Browny,
Верно + подсчитать сколько минут он простоял в конкретный час в минутах(duration, mi)
1 авг 17, 14:37    [20691831]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Browny
Member

Откуда:
Сообщений: 53
declare @sb table(device_id int,start_date datetime,end_date datetime,sboy_id int,finished int);
set dateformat dmy;
insert into @sb(device_id,start_date,end_date,sboy_id,finished) 
	Select 11270,	'09.01.2017 8:35',		'09.01.2017 19:35',	101,	1 union
	Select 11270,	'09.01.2017 19:35',	'10.01.2017 8:35',		100,	1 union
	Select 11272,	'10.01.2017 8:35',	'10.01.2017 19:35',		1,		1 union
	Select 11272,	'11.01.2017 8:35',	'11.01.2017 19:35',		23,		1 union
	Select 11272,	'11.01.2017 19:35',	'12.01.2017 8:35',		58,		1 union
	Select 11270,	'12.01.2017 8:35',	'12.01.2017 10:05',		101,	1 union
	Select 11270,	'12.01.2017 10:05',	'12.01.2017 10:15',		34,		1 union
	Select 11270,	'12.01.2017 10:15',	'12.01.2017 19:35',		28,		1
;with 
borderdates(maxdate,startdate) as (
	Select max(end_date), dateadd(hour, datepart(hour, min(start_date)), Cast(Floor(Cast(min(start_date) as Float)) as Datetime)) from @sb
),
schedule(datestart, datefinish) as (
	Select borderdates.startdate, dateadd(hour, 1, borderdates.startdate) from borderdates
	union all
	Select dateadd(hour, 1, schedule.datestart), dateadd(hour, 2, schedule.datestart) from schedule
	cross join borderdates
	where schedule.datefinish <= borderdates.maxdate

)
Select 
Cast(Floor(Cast(s.datestart as float)) as datetime) as [date],
Right('0' + convert(varchar,datepart(hour, s.datestart)) + ':00',5) as [time],
s.datestart,
s.datefinish,
datediff(minute, 
	case when b.start_date between s.datestart and s.datefinish then b.start_date else s.datestart end,
	case when b.end_date between s.datestart and s.datefinish then b.end_date else s.datefinish end
) as [duration,min]
b.*
from schedule s
inner join @sb b on not (s.datestart > b.end_date or s.datefinish < b.start_date)
order by sboy_id, s.datestart


Нуждается в выверке.
1 авг 17, 15:27    [20691996]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Korzhech
Member

Откуда:
Сообщений: 6
Browny,
Расчет верный) Спасибо

только встречается вот такая ошибка
(строк обработано: 34)
Сообщение 530, уровень 16, состояние 1, строка 30
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
1 авг 17, 17:08    [20692489]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Browny
Member

Откуда:
Сообщений: 53
Поставьте в самом конце, ниже сортировки:
option (maxrecursion 32767)
. К сожалению, увеличить значение нельзя, да и рекурсия сильно напрягает сервер. Если нужен почасовой график за много лет, стройте его без рекурсии.
1 авг 17, 17:56    [20692615]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Browny
Поставьте в самом конце, ниже сортировки:
option (maxrecursion 32767)
. К сожалению, увеличить значение нельзя, да и рекурсия сильно напрягает сервер. Если нужен почасовой график за много лет, стройте его без рекурсии.
Да ну?
С каких это пор maxrecursion 0 перестало работать? Другое дело, что выставлять его в 0 нужно на свой страх и риск, предохранившись от бесконечной рекурсии.
1 авг 17, 18:13    [20692668]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Browny
Member

Откуда:
Сообщений: 53
Minamoto, информация была полезной, спасибо. Нет, в MSDN не забанили :)
2 авг 17, 10:57    [20694093]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
rnk
Member

Откуда:
Сообщений: 126
Browny
Если нужен почасовой график за много лет, стройте его без рекурсии.


Почасовой график без рекурсии:
with tnum as (
	select ROW_NUMBER() OVER (order by object_id) as num from sys.all_columns
) select DATEADD(HH,num-1,convert(datetime,'20170101')) as datehour from tnum
2 авг 17, 16:31    [20695536]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Korzhech
Member

Откуда:
Сообщений: 6
rnk,
Спасибо!
Только ни фига не понимаю как прикрутить к данным, через join ??
4 авг 17, 10:01    [20699731]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
rnk
Member

Откуда:
Сообщений: 126
Korzhech
Только ни фига не понимаю как прикрутить к данным, через join ??

Угу. Так же, как было прикручено сверху
4 авг 17, 10:35    [20699854]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
set nocount on;
use tempdb;
go
declare @t table(
  dv int not null check(dv>0),
  bg datetime not null,
  fn datetime null,
  fid int not null,
  rcv bit not null,
  check(bg<=isnull(fn,getdate()))
);
insert @t(dv,bg,fn,fid,rcv)
values(11270,'20170109 08:35','20170109 19:35',101,1),
      (11270,'20170109 19:35','20170110 08:35',100,1),
      (11272,'20170110 08:35','20170110 19:35',1,1),
      (11272,'20170110 19:23','20170110 19:35',-10,1),
      (11272,'20170110 19:35','20170111 08:35',-10,1),
      (11272,'20170111 08:35','20170111 19:35',23,1),
      (11272,'20170111 19:35','20170112 08:35',58,1),
      (11270,'20170112 08:35','20170112 10:05',103,1),
      (11270,'20170112 10:05','20170112 10:15',34,1),
      (11270,'20170112 10:15','20170112 19:35',28,1),
      (42170,'20170323 23:59','20170324 00:05',170,1),
      (11270,'20170803 19:15',null,5,0);
with
  b as(select dv,bg,fid,isnull(fn,getdate()) fn,
       dateadd(hh,datediff(hh,0,bg)+1,0) hh from @t
       union all
       select dv,bg,fid,fn,dateadd(hh,1,hh) from b where hh<fn),
  c as(select *,
         iif(datediff(mi,bg,fn)<60,datediff(mi,bg,fn),
             iif(fn<hh,datepart(mi,fn),
                 iif(datediff(mi,bg,hh)<60,datediff(mi,bg,hh),60))) mi
       from b)
select dv,fid,dateadd(hh,-1,hh) hh,sum(mi) mi from c
--where fid in(100,101,1,170,34,-10)
group by dv,bg,hh,fid
order by dv,bg,hh
option(maxrecursion 0);
5 авг 17, 17:47    [20702707]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
В предыдущем варианте не правильно вычислял столбец mi (под спойлером)
+
set nocount on;
use tempdb;
go
declare @t table(
  dv int not null check(dv>0),
  bg datetime not null,
  fn datetime null,
  fid int not null,
  rcv bit not null,
  check(bg<=isnull(fn,getdate()))
);
insert @t(dv,bg,fn,fid,rcv)
values(11270,'20170109 08:35','20170109 19:35',101,1),
      (11270,'20170109 19:35','20170110 08:35',100,1),
      (11272,'20170110 08:35','20170110 19:35',1,1),
      (11272,'20170110 19:23','20170110 19:35',-10,1),
      (11272,'20170110 19:35','20170111 08:35',-10,1),
      (11272,'20170111 08:35','20170111 19:35',23,1),
      (11272,'20170111 19:35','20170112 08:35',58,1),
      (11270,'20170112 08:35','20170112 10:05',103,1),
      (11270,'20170112 10:05','20170112 10:15',34,1),
      (11270,'20170112 10:15','20170112 19:35',28,1),
      (42170,'20170323 23:55','20170323 23:59',170,1),
      (42170,'20170323 23:59','20170324 00:05',170,1),
      (42170,'20170324 00:05','20170324 00:07',170,1),
      (11270,'20170803 19:15',null,5,0);
with
  b as(select dv,bg,fid,isnull(fn,getdate()) fn,
       dateadd(hh,datediff(hh,0,bg)+1,0) hh from @t
       union all
       select dv,bg,fid,fn,dateadd(hh,1,hh) from b where hh<fn),
  c as(select dv,bg,fid,fn,dateadd(hh,-1,hh) hh,
         iif(fn<hh,iif(0<datediff(hh,bg,fn),datepart(mi,fn),datediff(mi,bg,fn)),
                   iif(datediff(mi,bg,hh)<60,datediff(mi,bg,hh),60)) mi
       from b)
select dv,fid,hh,sum(mi) mi
from c
--where fid in(28,34,170,5)
group by dv,hh,fid
option(maxrecursion 0);
Таблицу чисел без рекурсии можно построить так.
6 авг 17, 09:13    [20703283]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
Вариант с виртуальной таблицей чисел:
+
set nocount on;
use tempdb;
go
if object_id('dbo.nums') is not null drop function dbo.nums;
go
create function dbo.nums(@n bigint) returns table return
with
  L0 AS(SELECT 1 c UNION ALL SELECT 1),
  L1 AS(SELECT 1 c FROM L0 A CROSS JOIN L0 B),
  L2 AS(SELECT 1 c FROM L1 A CROSS JOIN L1 B),
  L3 AS(SELECT 1 c FROM L2 A CROSS JOIN L2 B),
  L4 AS(SELECT 1 c FROM L3 A CROSS JOIN L3 B),
  L5 AS(SELECT 1 c FROM L4 A CROSS JOIN L4 B),
  nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) n FROM L5)
  SELECT TOP (@n) n FROM nums ORDER BY n;
go
declare @t table(
  dv int not null check(dv>0),
  bg datetime not null,
  fn datetime null,
  fid int not null,
  rcv bit not null,
  check(bg<=isnull(fn,getdate()))
);
insert @t(dv,bg,fn,fid,rcv)
values(11270,'20170109 08:35','20170109 19:35',101,1),
      (11270,'20170109 19:35','20170110 08:35',100,1),
      (11272,'20170110 08:35','20170110 19:35',1,1),
      (11272,'20170110 19:23','20170110 19:35',-10,1),
      (11272,'20170110 19:35','20170111 08:35',-10,1),
      (11272,'20170111 08:35','20170111 19:35',23,1),
      (11272,'20170111 19:35','20170112 08:35',58,1),
      (11270,'20170112 08:35','20170112 10:05',103,1),
      (11270,'20170112 10:05','20170112 10:15',34,1),
      (11270,'20170112 10:15','20170112 19:35',28,1),
      (42170,'20170323 23:55','20170323 23:59',170,1),
      (42170,'20170323 23:59','20170324 00:05',170,1),
      (42170,'20170324 00:05','20170324 00:07',170,1),
      (11270,'20170805 19:15',null,5,0);
with
  b as(select dv,bg,fid,isnull(fn,getdate()) fn,
         dateadd(hh,datediff(hh,0,bg)+n,0) xh
       from @t
       cross apply dbo.nums(datediff(hh,bg,isnull(fn,getdate()))+1)),
  c as(select dv,bg,fid,fn,dateadd(hh,-1,xh) hh,
         iif(fn<xh,iif(0<datediff(hh,bg,fn),datepart(mi,fn),datediff(mi,bg,fn)),
                   iif(datediff(mi,bg,xh)<60,datediff(mi,bg,xh),60)) mi
       from b)
select dv,fid,hh,sum(mi) mi
from c
--where fid in(28,34,170,5)
group by dv,hh,fid;
6 авг 17, 09:53    [20703298]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
Korzhech
Member

Откуда:
Сообщений: 6
andrey odegov,

The INSERT statement conflicted with the CHECK constraint "CK__#BD716DF9__BF59B66B". The conflict occurred in database "tempdb", table "@t".

Как победить ?
9 авг 17, 10:58    [20710206]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL SERVER Разложить простой оборудования по часам  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Korzhech,
не совать
bg<=isnull(fn,getdate())
9 авг 17, 11:31    [20710271]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить