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

Откуда:
Сообщений: 61
Имеем график следующего вида:Картинка с другого сайта.

И соответственно таблицу значений для него:
CREATE TABLE t (dt datetime, val float);
INSERT INTO t VALUES 
('20110823 08:00', 840),
('20110823 09:15', 840),
('20110823 09:25', 892.5),
('20110823 10:15', 1055),
('20110823 12:31', 1055),
('20110823 13:00', 840),
('20110823 14:00', 840),
('20110823 15:00', 1025.066),
('20110823 16:00', 973.08),
('20110823 17:00', 840),
('20110823 20:00', 840),
('20110823 21:07', 953.832716666667),
('20110823 21:28', 1055),
('20110823 23:50', 1055),
('20110824 00:20', 840),
('20110824 02:00', 840);

Нужно добавить в таблицу недостающие значения за все ровные часы.
В данном случае это будет:
dtval
2011-08-23 09:00:00.000840
2011-08-23 10:00:00.0001006,25
2011-08-23 11:00:00.0001055
2011-08-23 12:00:00.0001055
2011-08-23 18:00:00.000840
2011-08-23 19:00:00.000840
2011-08-23 21:00:00.000941,939746268657
2011-08-23 22:00:00.0001055
2011-08-23 23:00:00.0001055
2011-08-24 02:00:00.000840


Имеется функция, которая возвращает значиние "val" для искомого времени из данных предыдущей и следующей записи:
SELECT YCalc (
   <@dtX, datetime,>
  ,<@dtLeft, datetime,>
  ,<@dtRight, datetime,>
  ,<@ValueLeft, float,>
  ,<@ValueRight, float,>)
где:
@dtX - время искомого "val"
@dtLeft - время из предыдущей записи
@dtRight - время из следующей записи
@ValueLeft - значение "val" из предыдущей записи
@ValueRight - значение "val" из следующей записи

Естествено что это всё надо сделать без использования курсоров.
Я застрял на получении предыдущих и следующих от искомого значений.

Коллеги, подскажите у кого есть какие-либо соображения по данной задаче.

P.S. FAQ читал. И про нарастающей итог тоже.
P.P.S. Microsoft SQL Server 2008 (SP1) - 10.0.2723.0
26 авг 11, 14:38    [11185401]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Albert71, вставку можно сделать при помощи merge данной таблицы с таблицей целых часов, например. соседние значения можно получить либо через номера строк, предварительно их пронумеровав row_number()'ом, либо через подзапросы по дате с min и max
26 авг 11, 14:52    [11185550]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Albert71
Member

Откуда:
Сообщений: 61
Shakill
Albert71, вставку можно сделать при помощи merge данной таблицы с таблицей целых часов, например. соседние значения можно получить либо через номера строк, предварительно их пронумеровав row_number()'ом, либо через подзапросы по дате с min и max

Пробовал я с помощью merge и нумерации строк, но тоже до конца так и не смог решить поставленную задачу.
Если не затруднит, мог бы ты написать конкретное решение?
26 авг 11, 15:00    [11185644]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Albert71, а в чем именно затруднение было, покажите вариант
26 авг 11, 15:11    [11185740]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Albert71
Member

Откуда:
Сообщений: 61
Shakill
Albert71, а в чем именно затруднение было, покажите вариант
Конкрктный вариант уже не смогу показать, т.к. не сохранял. Но затруднение было именно в получении предыдущего и следующего значения. Сами недостающие часы получилось добавить, а вот написать правильный запрос, который бы получал значения "dt" и "val" из предыдущей и следующей записи так и не получилось. Естественно, я всё это без циклов пытаюсь сделать.
26 авг 11, 15:21    [11185821]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Albert71, а какие значения будут считаться предыдущим и последующим, если идет заполнение нескольких "дырок", идущих подряд?
26 авг 11, 15:29    [11185897]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Albert71
Member

Откуда:
Сообщений: 61
Shakill
Albert71, а какие значения будут считаться предыдущим и последующим, если идет заполнение нескольких "дырок", идущих подряд?
Да, вы правильно поняли в чём именно моя загвоздка.
Например для недостающего 12:00 предыдущим будет 10:10, а следующим 12:31.
Также как и для недостающего 11:00 предыдущем бедет 10:10, а следующим 12:31.
26 авг 11, 15:37    [11185982]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Albert71,
даты, надеюсь, уникальны
вот как можно получить аргументы для вашей функции

;WITH cte AS (
	SELECT dt
	FROM (
		SELECT DISTINCT DATEADD(hh, number, '20110823 08:00') dt
		FROM master.dbo.spt_values  
		WHERE number >= 0 and number <= 18			
	) dts
)
SELECT 
	cte.dt, 
	t1.val,
	tl.dt,
	tl.val,
	tr.dt,
	tr.val
FROM cte
LEFT JOIN t t1 ON t1.dt = cte.dt
LEFT JOIN t tl ON tl.dt = (SELECT MAX(dt) FROM t WHERE dt < cte.dt) AND t1.dt IS NULL
LEFT JOIN t tr ON tr.dt = (SELECT MIN(dt) FROM t WHERE dt > cte.dt) AND t1.dt IS NULL
 
26 авг 11, 15:42    [11186064]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Уленшпигель
Member

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

а в случае большого разрыва, (с 17 до 20 в ваших данных) для 18 и 19 часов расчетная величина одинакова?
26 авг 11, 16:08    [11186426]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Уленшпигель
Member

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

в общем, как-то так

DECLARE @startdate datetime

SET @startdate = '20110823';

WITH hourly_cte(nhour) as 
(SELECT @startdate
 UNION ALL
 SELECT dateadd(hh, 1, nhour)
 from hourly_cte
 WHERE datediff(hh, @startdate, nhour) < 46
)

INSERT INTO #t
SELECT nhour, 
      (lval + rval)/2 -- здесь подставляете вашу функцию
FROM (
select a.*, b.val as lval, c.val as rval 
from (
select nhour, 
	 prev = (select max(dt)
		    FROM #t 
		    WHERE dt <= nhour), 
	 nnext = (select min(dt)
		     FROM #t 
		     WHERE dt >= nhour)
from hourly_cte) a 
join #t b
on a.prev = b.dt 
join #t c 
on a.nnext = c.dt
where prev <> nnext 
) a

select * 
from #t 
order by dt
26 авг 11, 16:41    [11186857]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
Albert71
Member

Откуда:
Сообщений: 61
Уленшпигель
Albert71,

а в случае большого разрыва, (с 17 до 20 в ваших данных) для 18 и 19 часов расчетная величина одинакова?
Да, в данном случае одинакова, потаму что значения за 17 и 20 часов совпадают, но они могут и разлечаться и тогда расчетные значения для 18 и 19 часов будут разными, но считаться они будут оба из значений для 17 и 20 часа.
Немного путанно, но надеюсь, можно понять смысл :-)
26 авг 11, 16:46    [11186918]     Ответить | Цитировать Сообщить модератору
 Re: Вставить недостающие значения  [new]
iljy
Member

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

declare @t table (dt datetime, val float);
INSERT INTO @t VALUES 
('20110823 08:00', 840),
('20110823 09:15', 840),
('20110823 09:25', 892.5),
('20110823 10:15', 1055),
('20110823 12:31', 1055),
('20110823 13:00', 840),
('20110823 14:00', 840),
('20110823 15:00', 1025.066),
('20110823 16:00', 973.08),
('20110823 17:00', 840),
('20110823 20:00', 840),
('20110823 21:07', 953.832716666667),
('20110823 21:28', 1055),
('20110823 23:50', 1055),
('20110824 00:20', 840),
('20110824 02:00', 840);

;with Numbers as(
	select ROW_NUMBER() over(order by (select 1)) - 1 N
	from sys.objects o1,sys.objects o2,sys.objects o3
),cte as(
	select ROW_NUMBER() over(order by dt) RN, * from @t
)
select val1 + (val2-val1) * datediff(mi, dd1, DATEADD(hh,N, d1)) / DATEDIFF(mi, dd1,dd2), DATEADD(hh,N, d1) dt
from(
	select t1.val val1, t2.val val2, t1.dt dd1, t2.dt dd2,
		 dateadd(hh, datediff(hh,0,dateadd(mi, 59, t1.dt)),0) d1,
		 dateadd(hh, datediff(hh,0,dateadd(mi, -1, t2.dt)),0) d2
	from cte t1 join cte t2 on t1.RN+1 = t2.RN
) t
cross apply
(
	select top(isnull(nullif(DATEDIFF(hh, d1,d2) + 1, -1),0)) N from Numbers
)tt
26 авг 11, 17:34    [11187334]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить