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

Откуда:
Сообщений: 6201
Дано: таблица, содержащая данные о работе некоего устройства. Данные просты: дата/время начала работы, и дата/время окончания работы. Требуется: для заданного промежутка суток в пределах месяца посчитать длительность работы в каждых сутках из промежутка. Характер исходных данных:
1) в пределах суток может быть несколько периодов начала/окончания работы;
2) начало и окончание работы могут приходиться на разные сутки;
3) между началом и окончанием работы может быть более суток (в общем случае - сколько угодно суток);
4) даты окончания работы может не быть (она равна null); такая запись в выборке по всей таблице может быть только одна, и при упорядочивании по возрастанию времени эта запись - последняя (устройство запустили, но не остановили);
4) промежутки времени начала/окончания из разных записей не пересекаются.
Входные условия: например, промежуток дат с 5 по 10-е число какого-то месяца (какого - неважно). По описанным выше данным нужно получить выборку N | T, где N - календарное число месяца, T - время работы в пределах этого календарного числа (в часах). Пример:
Входные данные о времени работы:
СтартСтоп
01.08.2011 23:00:0002.08.2011 03:00:00
02.08.2011 06:00:0002.08.2011 09:00:00
02.08.2011 21:00:0003.08.2011 01:00:00

Если нужно получить данные с 1-го по 4-е число, то правильная выходная выборка должна быть такой:
NT
11
29
31
40

Вроде бы, ничего такого особенного. Однако то, что получилось у меня в итоге, наводит на мысли, что что-то в полученном решении не так - оно, по моему скромному, как минимум достаточно громоздко:
create function uf_GetWorkTime(@from_day int, @to_day int, @base_date datetime)
returns
  @t_worktime_result table (dt_start datetime, dt_stop datetime, worktime float)
as
begin
  declare @t_calendar table (dt_from datetime, dt_to datetime, n int);
  declare @t_worktime_src table (dt_start datetime, dt_stop datetime);
  declare @t_worktime table (dt_start datetime, dt_stop datetime, worktime float)
  set @base_date=cast(convert(char(6),@base_date,112)+'01' as datetime);
  insert into @t_calendar
    select distinct convert(datetime,dateadd(day,number-1,@base_date)),
      dateadd(ss,-1,dateadd(day,number,@base_date)),number
    from master..spt_values where number between @from_day and @to_day;
  insert into @t_worktime_src 
    select start_time, stop_time from some_device_worklog 
    where start_time>=dateadd(day,@from_day-1,@base_date)
      and stop_time<=dateadd(day,@to_day,@base_date);
  insert into @t_worktime
    select k.dt_from,k.dt_to,sum(datediff(ss,w.dt_start,w.dt_stop)) from @t_calendar k, @t_worktime_src w
      where w.dt_start between k.dt_from and k.dt_to and w.dt_stop between k.dt_from and k.dt_to
      group by k.dt_from,k.dt_to
    union all
    select v1.dt_from,v1.dt_to,sum(datediff(ss,v1.dt_start,v1.dt_stop)) from (
      select k.dt_from,k.dt_to,
      case 
        when v.fact_start>k.dt_from
          then v.fact_start
        else
          k.dt_from
        end dt_start,
      case 
        when v.fact_stop<=k.dt_to
          then v.fact_stop
        else
          k.dt_to
        end dt_stop
      from @t_calendar k
      inner join (
        select dt_start fact_start,CONVERT(datetime,CONVERT(varchar,dt_start,1),1) dt_start,
        dt_stop fact_stop, dateadd(ss,-1,dateadd(day,1,CONVERT(datetime,CONVERT(varchar,coalesce(dt_stop,getdate()),1),1))) dt_stop
        from @t_worktime_src where DATEPART(day,dt_start)<>DATEPART(day,coalesce(dt_stop,getdate()))
      ) v on (k.dt_from between v.dt_start and v.dt_stop and k.dt_to between v.dt_start and v.dt_stop)
    ) v1 group by v1.dt_from,v1.dt_to;
  insert into @t_worktime
    select c.dt_from,c.dt_to,0 from @t_calendar c where not exists (
      select dt_start from @t_worktime x where x.dt_start=c.dt_from);
  insert into @t_worktime_result 
    select dt_start,dt_stop, round(sum(worktime)/3600,2)
      from @t_worktime group by dt_start,dt_stop order by 1;
  return;
end;
Вопросы касательно всего вышеизложенного следующие:
1) Не изобрёл ли я велосипед, и нет ли более изящного способа получить требуемое?
2) Что можно оптимизировать в вышеприведенном коде? (разумеется, если в п.1 не будет предложено что-то кардинально другое).
3) Как продолжение п. 2: в реальности таблица времени работы объекта содержит данные о работе энного количества объектов, и вышеприведенная функция нужна для сводного отчета о работе объектов в заданный период - т.е. к ней в итоге будет применяться cross apply, и ко всей выборке - pivot. Я погонял то, что получилось в первом приближении - результаты не ахти: на общую выборку примерно для 300 объектов уходит в среднем 25 секунд. А всего объектов ~80000. Как можно вообще оптимизировать данную задачу - может, какие-то данные (навскидку - те же данные, которые рассчитывает функция) хранить постоянно в отдельной таблице, обновляемой джобом, и запросы строить к ней?
30 авг 11, 16:48    [11202204]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм вычисления суммы времени в разрезе суток по заданным промежуткам (+)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Сон Веры Павловны,

честно говоря лень разбираться, а получить результат можно так:
set dateformat dmy
declare @t table (Start	datetime, Stop datetime)
insert @t values
('01.08.2011 23:00:00','02.08.2011 03:00:00'),
('02.08.2011 06:00:00','02.08.2011 09:00:00'),
('02.08.2011 21:00:00','03.08.2011 01:00:00')

declare @from datetime, @to datetime
select @from = '20110801', @to='20110804'

select dt1, isnull(SUM(datediff(hh, case when dt1 > Start then dt1 else Start end,
							case when dt2 < Stop then dt2 else Stop end)),0) s
from(
	select DATEADD(d, number, @from) dt1, dateadd(d, number+1, @from) dt2
	from master..spt_values
	where type = 'P' and number between 0 and DATEDIFF(d, @from, @to)
)t1 left join @t t2 on t2.Start < dt2 and dt1 < t2.Stop
group by dt1
30 авг 11, 17:08    [11202340]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм вычисления суммы времени в разрезе суток по заданным промежуткам (+)  [new]
aleks2
Guest
declare @t table (Старт datetime, Стоп datetime)

insert @t
select 
'01.08.2011 23:00:00', '02.08.2011 03:00:00' 
union all select 
'02.08.2011 06:00:00', '02.08.2011 09:00:00'
union all select 
'02.08.2011 21:00:00', '03.08.2011 01:00:00' 
 
declare @d table (День datetime)

insert @d
select '01.08.2011'
union all select 
'02.08.2011'
union all select 
'03.08.2011'
union all select 
'04.08.2011'

-- пересечение интервалов (для лучшего вкуривания)
select d.День
       , (select MAX(x) FROM (select D.День x union all select T.Старт) Y) Старт
       , (select MIN(x) FROM (select DATEADD(day, 1, D.День) x union all select T.Стоп) Y) Стоп
FROM @d d inner join @t t on T.Старт <DATEADD(day, 1, D.День) AND D.День <T.Стоп

-- группировка
select z.День, SUM(DATEDIFF(minute, z.Старт, z.Стоп))/60 Длительность
FROM
(
select d.День
       , (select MAX(x) FROM (select D.День x union all select T.Старт) Y) Старт
       , (select MIN(x) FROM (select DATEADD(day, 1, D.День) x union all select T.Стоп) Y) Стоп
FROM @d d inner join @t t on T.Старт <DATEADD(day, 1, D.День) AND D.День <T.Стоп
) Z
GROUP BY z.День
30 авг 11, 17:15    [11202399]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить