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

Откуда:
Сообщений: 61
--ИСХОДНЫЕ ДАННЫЕ-------------------------------------
DECLARE @tbl TABLE (dt DATETIME, pow FLOAT)
DECLARE @cal TABLE (dt DATETIME)

INSERT INTO @tbl ([dt],[pow])
SELECT Cast('27.01.2013 00:00:00' AS DATETIME), 100 UNION ALL
SELECT Cast('27.01.2013 00:10:10' AS DATETIME), 100.5 UNION ALL
SELECT Cast('27.01.2013 00:10:55' AS DATETIME), 100.7 UNION ALL
SELECT Cast('27.01.2013 00:11:00' AS DATETIME), 120.1 UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME), 150.7 UNION ALL
SELECT Cast('27.01.2013 00:16:30' AS DATETIME), 151.5 UNION ALL
SELECT Cast('27.01.2013 00:16:40' AS DATETIME), 152.5 UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME), 155.7 UNION ALL
SELECT Cast('27.01.2013 00:17:31' AS DATETIME), 155.7 UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME), 154.0 UNION ALL
SELECT Cast('27.01.2013 00:20:30' AS DATETIME), 169.5936 union all
SELECT Cast('27.01.2013 00:20:34' AS DATETIME), 171.7632 union all
SELECT Cast('27.01.2013 00:22:40' AS DATETIME), 171.1008 union all
SELECT Cast('27.01.2013 00:23:23' AS DATETIME), 170.496 union all
SELECT Cast('27.01.2013 00:24:13' AS DATETIME), 170.9856 union all
SELECT Cast('27.01.2013 00:25:23' AS DATETIME), 169.2 union all
SELECT Cast('27.01.2013 00:26:23' AS DATETIME), 170.1024 union all
SELECT Cast('27.01.2013 00:27:23' AS DATETIME), 169.4304 union all
SELECT Cast('27.01.2013 00:28:54' AS DATETIME), 169.8624 union all
SELECT Cast('27.01.2013 00:29:32' AS DATETIME), 170.1312 union all
SELECT Cast('27.01.2013 00:40:00' AS DATETIME), 171.36 union all
SELECT Cast('27.01.2013 00:40:10' AS DATETIME), 170.1888 union all
SELECT Cast('27.01.2013 00:40:50' AS DATETIME), 170.5056 union all
SELECT Cast('27.01.2013 00:43:40' AS DATETIME), 172.1568 union all
SELECT Cast('27.01.2013 00:44:30' AS DATETIME), 171.3504 union all
SELECT Cast('27.01.2013 00:45:54' AS DATETIME), 169.4112 union all
SELECT Cast('27.01.2013 00:46:00' AS DATETIME), 171.3696


INSERT INTO @cal ([dt])

SELECT Cast('27.01.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:18:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:20:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:21:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:22:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:23:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:24:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:25:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:26:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:27:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:28:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:29:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:30:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:31:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:32:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:33:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:34:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:35:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:36:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:37:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:38:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:39:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:40:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:41:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:42:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:43:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:44:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:45:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:46:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:47:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:48:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:49:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:50:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:51:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:52:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:53:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:54:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:55:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:56:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:57:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:58:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:59:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 01:00:00' AS DATETIME)
--ИСХОДНЫЕ ДАННЫЕ КОНЕЦ---------------------------------


--В таблице @tbl данные со счетчиков в определенный момент времени
--Таблица @cal это календарь

--ЗАДАНИЕ №1 Нужно построить запрос, который выведет нагрузку на каждую минуту времени, без пустых значений


Я так понимаю главная таблица это @cal, из нее выводим все возможные значения поминутно цепляя таблицу @tbl. Т.е. примерно такой результат.


27.01.2013 00:00:00 100
27.01.2013 00:01:00 100
27.01.2013 00:02:00 100
27.01.2013 00:03:00 100
27.01.2013 00:04:00 100
27.01.2013 00:05:00 100
27.01.2013 00:06:00 100
27.01.2013 00:07:00 100
27.01.2013 00:08:00 100
27.01.2013 00:09:00 100
27.01.2013 00:10:00 100.5
14 май 15, 20:30    [17641854]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
У вас календарь начинается с 27.01.2013 00:11:00, откуда берется 00:00:00?
14 май 15, 20:37    [17641873]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
CheaterX
Member

Откуда:
Сообщений: 61
Гавриленко Сергей Алексеевич, ошибся простите

INSERT INTO @cal ([dt])

SELECT Cast('27.01.2013 00:00:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:01:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:02:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:03:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:04:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:05:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:06:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:07:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:08:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:09:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:10:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:18:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:20:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:21:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:22:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:23:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:24:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:25:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:26:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:27:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:28:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:29:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:30:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:31:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:32:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:33:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:34:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:35:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:36:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:37:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:38:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:39:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:40:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:41:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:42:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:43:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:44:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:45:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:46:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:47:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:48:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:49:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:50:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:51:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:52:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:53:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:54:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:55:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:56:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:57:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:58:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:59:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 01:00:00' AS DATETIME)
14 май 15, 21:00    [17641979]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
CheaterX,

SET LANGUAGE RUSSIAN 

--ИСХОДНЫЕ ДАННЫЕ-------------------------------------
DECLARE @tbl TABLE (dt DATETIME, pow FLOAT)
DECLARE @cal TABLE (dt DATETIME)

INSERT INTO @tbl ([dt],[pow])
SELECT Cast('27.01.2013 00:00:00' AS DATETIME), 100 UNION ALL
SELECT Cast('27.01.2013 00:10:10' AS DATETIME), 100.5 UNION ALL
SELECT Cast('27.01.2013 00:10:55' AS DATETIME), 100.7 UNION ALL
SELECT Cast('27.01.2013 00:11:00' AS DATETIME), 120.1 UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME), 150.7 UNION ALL
SELECT Cast('27.01.2013 00:16:30' AS DATETIME), 151.5 UNION ALL
SELECT Cast('27.01.2013 00:16:40' AS DATETIME), 152.5 UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME), 155.7 UNION ALL
SELECT Cast('27.01.2013 00:17:31' AS DATETIME), 155.7 UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME), 154.0 UNION ALL
SELECT Cast('27.01.2013 00:20:30' AS DATETIME), 169.5936 union all
SELECT Cast('27.01.2013 00:20:34' AS DATETIME), 171.7632 union all
SELECT Cast('27.01.2013 00:22:40' AS DATETIME), 171.1008 union all
SELECT Cast('27.01.2013 00:23:23' AS DATETIME), 170.496 union all
SELECT Cast('27.01.2013 00:24:13' AS DATETIME), 170.9856 union all
SELECT Cast('27.01.2013 00:25:23' AS DATETIME), 169.2 union all
SELECT Cast('27.01.2013 00:26:23' AS DATETIME), 170.1024 union all
SELECT Cast('27.01.2013 00:27:23' AS DATETIME), 169.4304 union all
SELECT Cast('27.01.2013 00:28:54' AS DATETIME), 169.8624 union all
SELECT Cast('27.01.2013 00:29:32' AS DATETIME), 170.1312 union all
SELECT Cast('27.01.2013 00:40:00' AS DATETIME), 171.36 union all
SELECT Cast('27.01.2013 00:40:10' AS DATETIME), 170.1888 union all
SELECT Cast('27.01.2013 00:40:50' AS DATETIME), 170.5056 union all
SELECT Cast('27.01.2013 00:43:40' AS DATETIME), 172.1568 union all
SELECT Cast('27.01.2013 00:44:30' AS DATETIME), 171.3504 union all
SELECT Cast('27.01.2013 00:45:54' AS DATETIME), 169.4112 union all
SELECT Cast('27.01.2013 00:46:00' AS DATETIME), 171.3696


INSERT INTO @cal ([dt])

SELECT Cast('27.01.2013 00:00:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:01:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:02:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:03:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:04:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:05:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:06:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:07:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:08:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:09:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:10:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:18:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:20:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:21:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:22:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:23:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:24:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:25:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:26:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:27:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:28:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:29:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:30:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:31:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:32:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:33:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:34:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:35:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:36:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:37:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:38:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:39:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:40:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:41:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:42:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:43:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:44:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:45:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:46:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:47:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:48:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:49:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:50:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:51:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:52:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:53:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:54:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:55:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:56:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:57:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:58:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:59:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 01:00:00' AS DATETIME)



--SELECT * FROM @tbl

SELECT c.dt, pow_SUM = ISNULL(SUM(pow), 0) 
FROM @cal C 
OUTER APPLY (
	SELECT POW FROM @tbl t
	WHERE t.dt >= C.dt AND t.dt < DATEADD(minute, 1, C.dt)
) t 
GROUP BY C.dt
ORDER BY C.dt
14 май 15, 21:07    [17642001]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
CheaterX
Member

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

Спасибо большое! Я тоже думал про Apply. Вариант красивый. Но может есть вариант классическим Т/SQL...пусть менее элегантный но с LEFT OUTER JOIN.
14 май 15, 21:28    [17642078]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
invm
Member

Откуда: Москва
Сообщений: 9837
CheaterX
Но может есть вариант классическим Т/SQL
Замените outer apply на left join и будет вам классический вариант:
SELECT c.dt, pow_SUM = ISNULL(SUM(pow), 0) 
FROM @cal C LEFT JOIN
 @tbl t	on t.dt >= C.dt AND t.dt < DATEADD(minute, 1, C.dt)
GROUP BY C.dt
ORDER BY C.dt
14 май 15, 21:44    [17642120]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
o-o
Guest
у меня чего-то совсем не получается ответ ТС
при запуске предложенного запроса.

да и как вообще можно показания прибора складывать?
если это изменения температуры(по Фаренгейту, например)
и 10:10 было 100.5, а в 10:55 -- 100.7,
то что, в 10:00 был 201 градус???
-------

а еще мне непонятно, откуда взялось
27.01.2013 00:10:00 100.5

если 100.5 было только в 10:10?

27.01.2013 00:00:00 100
27.01.2013 00:10:10 100.5

или это линейная интерполяция?
тогда в 00:10:00 будет 100.4918, а не 100.5
да и в остальных узлах в интервале до 00:10:00 совсем не 100,
типа
00:01:00 -- 100.0492
00:02:00 -- 100.0984
00:03:00 -- 100.1475
00:04:00 -- 100.1967
00:05:00 -- 100.2459
00:06:00 -- 100.2951
00:07:00 -- 100.3443
00:08:00 -- 100.3934
00:09:00 -- 100.4426
00:10:00 -- 100.4918
---
но раз ТС все устраивает, то я чего-то в этой жизни не понимаю
14 май 15, 22:14    [17642239]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
CheaterX
Member

Откуда:
Сообщений: 61
o-o,

Я опять все перепутал. Пора видимо спать.
Вы правы, значение 120.1 будет с 11 минуты. А до 11 ( с 1 по 10 будет видимо 100, учитывая условия данной задачи (последнее зафиксированное с датчика). С линейной интерполяцией вообще было бы очень красиво. Если кто и это сможет реализовать, будет вообще красота.
14 май 15, 22:32    [17642325]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
CheaterX
Member

Откуда:
Сообщений: 61
CheaterX,
SELECT c.dt, max(pow)
FROM @cal C LEFT JOIN
@tbl t on t.dt = C.dt 
AND t.dt < DATEADD(minute, 1, C.dt)
GROUP BY C.dt
ORDER BY C.dt


вот этот запрос выдает мне

2013-01-27 00:00:00.000 100
2013-01-27 00:01:00.000 NULL
2013-01-27 00:02:00.000 NULL
2013-01-27 00:03:00.000 NULL
2013-01-27 00:04:00.000 NULL
2013-01-27 00:05:00.000 NULL
2013-01-27 00:06:00.000 NULL
2013-01-27 00:07:00.000 NULL
2013-01-27 00:08:00.000 NULL
2013-01-27 00:09:00.000 NULL
2013-01-27 00:10:00.000 NULL
2013-01-27 00:11:00.000 120,1
2013-01-27 00:12:00.000 NULL
2013-01-27 00:13:00.000 NULL


А мне надо чтобы он вместо NULL выдавал последнее значение с датчика хотя бы. Может кто помочь?

2013-01-27 00:00:00.000 100
2013-01-27 00:01:00.000 100
2013-01-27 00:02:00.000 100
2013-01-27 00:03:00.000 100
2013-01-27 00:04:00.000 100
2013-01-27 00:05:00.000 100
2013-01-27 00:06:00.000 100
2013-01-27 00:07:00.000 100
2013-01-27 00:08:00.000 100
2013-01-27 00:09:00.000 100
2013-01-27 00:10:00.000 100
2013-01-27 00:11:00.000 120,1
2013-01-27 00:12:00.000 120,1
2013-01-27 00:13:00.000 120,1
15 май 15, 09:24    [17643206]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
CheaterX, если последние значения, это просто - куча таких задач на форуме, не знаю только, какую фразу для поиска подобрать, чтобы их найти, поэтому мне проще написать.

select	cal.dt, tbl.pow
from	@cal cal
		cross apply (select top 1 tbl.dt, pow
					from @tbl tbl
					where tbl.dt <= cal.dt
					order by tbl.dt desc) tbl
15 май 15, 10:15    [17643446]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Вариант с линейной интерполяцией.
Если одно из значений (верхнее или нижнее) отсутствует - берется то, которое есть (т.е. до 2013-01-27 00:00:00.000 всегда будет 100, после 27.01.2013 00:46:00 - всегда 171.3696).

Правда, я считаю, что линейная интерполяция здесь неприменима, видно, что зависимость явно нелинейная - у линейной интерполяции будет большая погрешность.

select	cal.dt, coalesce(tbl1.pow + (tbl2.pow-tbl1.pow)*datediff(ms, tbl1.dt, cal.dt)/nullif(datediff(ms, tbl1.dt, tbl2.dt), 0),  tbl1.pow, tbl2.pow)
from	@cal cal
		outer apply (select top 1 tbl.dt, tbl.pow
					from @tbl tbl
					where tbl.dt <= cal.dt
					order by tbl.dt desc) tbl1
		outer apply (select top 1 tbl.dt, tbl.pow
					from @tbl tbl
					where tbl.dt >= cal.dt
					order by tbl.dt asc) tbl2
15 май 15, 10:39    [17643579]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
CheaterX
Member

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

Спасибо большое! Я просто хотел чтобы без cross apply, самому нравится этот метод, но он тормозной бывает порой...
15 май 15, 11:14    [17643800]     Ответить | Цитировать Сообщить модератору
 Re: Задачку задали хитрую  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
CheaterX
Minamoto,

Спасибо большое! Я просто хотел чтобы без cross apply, самому нравится этот метод, но он тормозной бывает порой...



Индексы надо создавать правильно и будет он летать
15 май 15, 11:58    [17644176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить