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

Откуда:
Сообщений: 26
Здравствуйте все!
Есть таблица, содержащая нарастающие показания счетчиков (счетчиков много, соответственно, полей COUNTERx тоже много) , с дискретностью в час :
IDDTCOUNTER1COUNTER2COUNTER3....
12011.06.10 01:00:00111013...
22011.06.10 02:00:00151316...
32011.06.10 03:00:00211823...

Как построить запрос, возвращающий разницу между текущим и предыдущим по времени значением?
DTCOUNTER1COUNTER2COUNTER3....
2011.06.10 01:00:00111013...
2011.06.10 02:00:00433...
2011.06.10 03:00:00657...

Полей COUNTER много, и городить на каждое поле подзапрос на предыдущее значение - получается долго и некрасиво..
30 июн 11, 14:08    [10899534]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
KamskyPaul
Полей COUNTER много, и городить на каждое поле подзапрос на предыдущее значение - получается долго и некрасиво..
CROSS APPLY, если версия позволит
select ...
from T as t1
    cross apply (select top 1 * from T as t2 where t2.DT < t1.DT order by t2.DT desc) as t2
Eсли версия не позволит, то можно и по другому написать...
30 июн 11, 14:20    [10899654]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
-=DiM@n=-
Member

Откуда: Москва
Сообщений: 1564
SELECT t1.DT, 
           CASE WHEN t2.COUNTER1 IS NULL THEN t1.COUNTER1 ELSE t2.COUNTER1 - t1.COUNTER1 END AS COUNTER1,
           CASE WHEN t2.COUNTER2 IS NULL THEN t2.COUNTER1 ELSE t2.COUNTER2 - t1.COUNTER2 END AS COUNTER2,
           ...
FROM table t1
    LEFT JOIN table t2
        ON t2.DT = DATEADD(hh, 1, t1.DT)
30 июн 11, 14:23    [10899693]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
userSql_m
Guest
как-то так
select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.date=dateadd(h,-1,t2.date)
30 июн 11, 14:24    [10899712]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
KamskyPaul
Member

Откуда:
Сообщений: 26
Eсли версия не позволит, то можно и по другому написать...

Версия 2000, вроде не позволяет такого
как-то так
select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.date=dateadd(h,-1,t2.date)

Могут быть и пропуски в строках, разница в час не гарантирована (ситуация нештатная в системе, но все же может быть)
30 июн 11, 14:26    [10899742]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
-=DiM@n=-
Member

Откуда: Москва
Сообщений: 1564
CREATE TABLE #t (ID int NOT NULL PRIMARY KEY, IDNext int NULL);

INSERT INTO #t
(
    ID, IDNext
)
SELECT ID, (SELECT TOP 1 ID FROM table t2 WHERE t2.DT > t1.DT ORDER BY t2.DT)
FROM table t;

SELECT t.DT,
           CASE WHEN t2.COUNTER1 IS NULL THEN t.COUNTER1 ELSE t2.COUNTER1 - t.COUNTER1 END AS COUNTER1,
           CASE WHEN t2.COUNTER2 IS NULL THEN t.COUNTER2 ELSE t2.COUNTER2 - t.COUNTER2 END AS COUNTER2,
           ...
FROM table t 
    INNER JOIN #t t1
        ON t.ID = t1.ID
    INNER JOIN table t2
        ON t1.IDNext = t2.ID;
30 июн 11, 14:32    [10899835]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
KamskyPaul
Eсли версия не позволит, то можно и по другому написать...

Версия 2000, вроде не позволяет такого
Ну тогда по другому - подзапрос с кореллированным запросом предыдущего ID и далее джойн.

Вот как -=DiM@n=- написал, только вместо временной таблицы подзапрос.
30 июн 11, 14:44    [10899971]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
userSql_m
Guest
userSql_m
как-то так
select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.date=dateadd(h,-1,t2.date)


при определённый условия можно и так:

select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.id=t1.id-1

если id не актуален то пронумеровать по возрастанию даты и
тем же способом как по ID
30 июн 11, 14:51    [10900042]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
userSql_m
Guest
userSql_m
userSql_m
как-то так
select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.date=dateadd(h,-1,t2.date)


при определённый условия можно и так:

select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.id=t1.id-1

если id не актуален то пронумеровать по возрастанию даты и
тем же способом как по ID


select t1.date,t1.cou1-t2.cou1 from t1
inner join t2 on t1.id=t2.id-1
30 июн 11, 14:53    [10900052]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
Врунгель
Member

Откуда:
Сообщений: 6
;WITH data_CTE (DT, counter1, counterx, rn)
AS
(SELECT
t0.DT,
t0.counter1,
t0.counterx,
ROW_NUMBER() OVER(ORDER BY t0.DT) AS rn
FROM data_table t0)
SELECT
t1.DT,
t1.counter1 - ISNULL(t2.counter1, 0),
t1.counterx - ISNULL(t2.counterx, 0)
FROM
data_CTE AS t1,
data_CTE AS t2
WHERE t1.rn - 1 = t2.rn
30 июн 11, 15:29    [10900503]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
KamskyPaul
Member

Откуда:
Сообщений: 26
Врунгель,
Версия MS SQL 2000, OVER не знает
30 июн 11, 15:46    [10900719]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
KamskyPaul
Версия 2000, разница в час не гарантирована


как-то так

select t1.date,t1.cou1-t2.cou1 
from t t1 
  inner join t t2 on t1.date > t2.date 
where not exists(select * from t where date > t2.date and date < t1.date);
30 июн 11, 15:48    [10900746]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
KamskyPaul
Member

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

Откуда: Яженичеловек!!!
Сообщений: 65007
KamskyPaul
Верблюд,
жуть как долго выполняется такой запрос..


индексы прикрути
30 июн 11, 16:57    [10901597]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
KamskyPaul
Member

Откуда:
Сообщений: 26
alexeyvg
Ну тогда по другому - подзапрос с кореллированным запросом предыдущего ID и далее джойн.

Вот как -=DiM@n=- написал, только вместо временной таблицы подзапрос.

Уважаемый alexeyvg , а можно поподробнее? Не пойму, как вместо этой временной таблицы подзапрос подставить..
30 июн 11, 16:58    [10901620]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
KamskyPaul
alexeyvg
Ну тогда по другому - подзапрос с кореллированным запросом предыдущего ID и далее джойн.

Вот как -=DiM@n=- написал, только вместо временной таблицы подзапрос.

Уважаемый alexeyvg , а можно поподробнее? Не пойму, как вместо этой временной таблицы подзапрос подставить..
SELECT	t1.DT,
		t2.COUNTER1 - t.COUNTER1 AS COUNTER1,
		t2.COUNTER2 - t.COUNTER2 AS COUNTER2,
		...
FROM (
	SELECT	*, 
			(SELECT TOP 1 ID FROM table t2 WHERE t2.DT > t1.DT ORDER BY t2.DT) as IDNext
	FROM table t1
) as t1
    INNER JOIN table t2
        ON t1.IDNext = t2.ID;
30 июн 11, 17:05    [10901691]     Ответить | Цитировать Сообщить модератору
 Re: Вычесть разницу с предыдущими полями строки  [new]
KamskyPaul
Member

Откуда:
Сообщений: 26
Спасибо alexeyvg за идею!
Конечный вариант (с учетом первой строки в таблице, когда предыдущего значения нет) получился такой:

SELECT	t1.DT, 
  CASE WHEN t1.IdPrev IS NULL THEN  t1.COUNTER1 ELSE t1.COUNTER1 - t2.COUNTER1 END AS  COUNTER1, 
  CASE WHEN t1.IdPrev IS NULL THEN  t1.COUNTER2 ELSE t1.COUNTER2 - t2.COUNTER2 END AS  COUNTER2, 
  .....
FROM  ( SELECT	*, 
       (SELECT TOP 1 [ID] FROM table t2 WHERE t2.DT < t1.DT ORDER BY t2.DT DESC) as IDPrev
	FROM table T1
) as T1 
  LEFT OUTER JOIN table t2
  ON t1.IDPrev = t2.[ID]
ORDER by t1.DT;

1 июл 11, 16:10    [10907619]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить