SQL Query, чтобы получить Процент прошедшего времени, вычисляя общее время и время для следующей строки

У меня есть таблица MS SQL, подобная этой (с переменным числом возвращаемых строк):

TimeStamp               State

2015-04-27 09:49:14.567 SETUP
2015-04-27 10:10:07.340 EXECUTING
2015-04-27 14:15:12.017 WAITING
2015-04-27 14:15:48.263 EXECUTING
2015-04-27 14:29:10.773 WAITING
2015-04-27 14:29:38.177 EXECUTING
2015-04-28 01:58:54.340 WAITING
2015-04-28 02:03:25.257 CLOSED

теперь мне нужен запрос, чтобы подсчитать каждый раз штампы процентного начала и конца как это (фактические значения являются только символическими):

TimeStamp               State         PercentageStart PercentageEnd

2015-04-27 09:49:14.567 SETUP         0               8
2015-04-27 10:10:07.340 EXECUTING     8               20
2015-04-27 14:15:12.017 WAITING       20              26
2015-04-27 14:15:48.263 EXECUTING     26              28
2015-04-27 14:29:10.773 WAITING       28              30
2015-04-27 14:29:38.177 EXECUTING     30              90
2015-04-28 01:58:54.340 WAITING       90              100
2015-04-28 02:03:25.257 CLOSED        100             NULL

Таким образом, время между первой и последней записью TimeStamp составляет 100%. Я думаю, мне нужен указатель или что-то в этом роде, но не уверен: /

Приветствуем и благодарим заранее!

sql,sql-server,

0

Ответов: 2

2 принят

Что-то вроде этого должно работать:

;WITH CTE_DIFF AS (
   SELECT [TimeStamp], [State], 
          DATEDIFF ( second , 
                    [TimeStamp] , 
                    LEAD([TimeStamp]) OVER (ORDER BY [TimeStamp])) AS time_diff 
   FROM mytable
), CTE_PERC AS (
   SELECT [TimeStamp], [State], time_diff ,
          SUM(time_diff) OVER (ORDER BY [TimeStamp]) * 1.0 / 
          SUM(time_diff) OVER () * 100 AS perc
   FROM CTE_DIFF
)
SELECT [TimeStamp], [State], 
       COALESCE(LAG(perc) OVER (ORDER BY [TimeStamp]), 0) AS PercentageStart,
       perc AS PercentageEnd
FROM CTE_PERC

Демо здесь

2 
declare @t table ([TimeStamp] datetime, [State] varchar(32));
insert into @t values 
  ('2015-04-27T09:49:14.567','SETUP'),
  ('2015-04-27T10:10:07.340','EXECUTING'),
  ('2015-04-27T14:15:12.017','WAITING'),
  ('2015-04-27T14:15:48.263','EXECUTING'),
  ('2015-04-27T14:29:10.773','WAITING'),
  ('2015-04-27T14:29:38.177','EXECUTING'),
  ('2015-04-28T01:58:54.340','WAITING'),
  ('2015-04-28T02:03:25.257','CLOSED')

Select firstTS, startts, endts, duration,
   100 * Datediff(second, firstTS, startts) / duration startPcnt,
   100 * Datediff(second, firstTS, endts)  / duration  endPcnt
From (Select s.timestamp startts, s.state, e.timestamp endts,
    (Select Min(timestamp) from @t) firstTS,
    (Select cast(DateDiff(second, Min(timestamp), Max(timestamp)) as float) from @t) duration
From @t s join @t e
    on e.timestamp =
       (Select Min(timestamp) from @t
        where timestamp > s.timestamp))x
SQL, SQL-сервер,
Похожие вопросы
Яндекс.Метрика