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

Откуда:
Сообщений: 181
Здравствуйте! Помогите составить вопрос, просто голову уже сломал на прочь

Есть таблица календарь, которая содержит минуты.
dt
-----------------------
2013-10-09 00:00:00.000
2013-10-09 00:01:00.000
2013-10-09 00:02:00.000
2013-10-09 00:03:00.000
2013-10-09 00:04:00.000
2013-10-09 00:05:00.000
2013-10-09 00:06:00.000
2013-10-09 00:07:00.000
2013-10-09 00:08:00.000
2013-10-09 00:09:00.000
2013-10-09 00:10:00.000
2013-10-09 00:11:00.000
2013-10-09 00:12:00.000
2013-10-09 00:13:00.000
2013-10-09 00:14:00.000
2013-10-09 00:15:00.000



Есть вторая таблица которая содержит данные в разные моменты времени. Значения могут быть несколько раз в минуту или так же отсутствовать за некоторый интервал
dt                                 power
----------------------- ----------------------
2013-10-09 00:00:16.000 167,234
2013-10-09 00:01:16.000 168,223
2013-10-09 00:02:11.000 168,977
2013-10-09 00:03:16.000 170,547
2013-10-09 00:04:10.000 170,082
2013-10-09 00:05:19.000 169,048
2013-10-09 00:05:59.000 169,048
2013-10-09 00:06:11.000 166,705
2013-10-09 00:07:19.000 167,312
2013-10-09 00:13:11.000 168,842
2013-10-09 00:14:11.000 170,08
2013-10-09 00:15:11.000 169,431
.....


Нужно их как то приджоинить чтобы значения были на каждую минуту. При отсутствии данных за некоторый период взять среднее значение. Например данные отсутствуют с 8 по 12 минуту, нужно взять среднее между 7 и 13 минутами.

Заранее благодарен
15 окт 13, 10:29    [14971124]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
aleks2
Guest
1. Преобразовать вторую таблицу в интервальную структуру

dt1                               dt1                                power1                   power2
-----------------------   -----------------------    -------------------- --------------------
2013-10-09 00:00:16.000 2013-10-09 00:01:16.000 167,234                   168,223
2013-10-09 00:01:16.000 2013-10-09 00:02:11.000 168,223                   168,977 
........


2. Опосля чего все сводится к тупому join с таблицей календарь и линейной интерполяции.
15 окт 13, 10:54    [14971246]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
serega063
Member

Откуда:
Сообщений: 181
Чето я не допонял.
А можно примерчик
15 окт 13, 12:02    [14971813]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
aleks2
Guest
serega063
Чето я не допонял.
А можно примерчик


Канешно. Тока ты данные обеспечь в таблицах. Ибо лениво мне их заполнять...
15 окт 13, 12:29    [14972094]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
serega063
Member

Откуда:
Сообщений: 181
Не вопрос
DECLARE @tbl TABLE (dt DATETIME, pow FLOAT)
DECLARE @cal TABLE (dt DATETIME)

INSERT INTO @tbl ([dt],[pow])
SELECT Cast('15.10.2013 00:00:00' AS DATETIME), 100 UNION ALL
SELECT Cast('15.10.2013 00:10:10' AS DATETIME), 100.5 UNION ALL
SELECT Cast('15.10.2013 00:10:55' AS DATETIME), 100.7 UNION ALL
SELECT Cast('15.10.2013 00:11:00' AS DATETIME), 120.1 UNION ALL
SELECT Cast('15.10.2013 00:15:00' AS DATETIME), 150.7 UNION ALL
SELECT Cast('15.10.2013 00:16:30' AS DATETIME), 151.5 UNION ALL
SELECT Cast('15.10.2013 00:16:40' AS DATETIME), 152.5 UNION ALL
SELECT Cast('15.10.2013 00:17:00' AS DATETIME), 155.7 UNION ALL
SELECT Cast('15.10.2013 00:18:01' AS DATETIME), 155.7 




INSERT INTO @cal ([dt])
SELECT Cast('15.10.2013 00:00:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:01:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:02:00' AS DATETIME)UNION ALL
SELECT Cast('15.10.2013 00:03:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:04:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:05:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:06:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:07:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:08:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:09:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:10:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:18:00' AS DATETIME) 


select * from @tbl
select * from @cal


Я просто не очень знаком с линейной интерполяцией)))
15 окт 13, 12:47    [14972266]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
it17
Member

Откуда:
Сообщений: 108
Извините за оффтоп, но какой практический смысл
автор
При отсутствии данных за некоторый период взять среднее значение. Например данные отсутствуют с 8 по 12 минуту, нужно взять среднее между 7 и 13 минутами.


Мне предстоит показать поминутную нагрузку по кол-ву платежей.
С одной стороны в это время платежи не принимались, с другой стороны наверное график будет очень резко подниматься/падать.
15 окт 13, 13:22    [14972555]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
aleks2
Guest
DECLARE @tbl TABLE (dt DATETIME, pow FLOAT)
DECLARE @cal TABLE (dt DATETIME)

INSERT INTO @tbl ([dt],[pow])
SELECT Cast('15.10.2013 00:00:00' AS DATETIME), 100 UNION ALL
SELECT Cast('15.10.2013 00:10:10' AS DATETIME), 100.5 UNION ALL
SELECT Cast('15.10.2013 00:10:55' AS DATETIME), 100.7 UNION ALL
SELECT Cast('15.10.2013 00:11:00' AS DATETIME), 120.1 UNION ALL
SELECT Cast('15.10.2013 00:15:00' AS DATETIME), 150.7 UNION ALL
SELECT Cast('15.10.2013 00:16:30' AS DATETIME), 151.5 UNION ALL
SELECT Cast('15.10.2013 00:16:40' AS DATETIME), 152.5 UNION ALL
SELECT Cast('15.10.2013 00:17:00' AS DATETIME), 155.7 UNION ALL
SELECT Cast('15.10.2013 00:18:01' AS DATETIME), 155.7 


INSERT INTO @cal ([dt])
SELECT Cast('15.10.2013 00:00:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:01:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:02:00' AS DATETIME)UNION ALL
SELECT Cast('15.10.2013 00:03:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:04:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:05:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:06:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:07:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:08:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:09:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:10:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:18:00' AS DATETIME) 

;with
tbln as ( select *, row_number() over(order by dt) n from @tbl )
,
tblranges as ( select t1.dt as dt1, t2.dt as dt2, t1.pow as pow1, t2.pow as pow2 
                 from tbln t1 inner join tbln t2 on t1.n+1 = t2.n
             )
select c.dt
     , tr.pow1 + ( tr.pow2 - tr.pow1 ) * datediff(second, tr.dt1, c.dt) / datediff(second, tr.dt1, tr.dt2) pow
  from @cal c inner join tblranges tr on tr.dt1 <= c.dt and c.dt < tr.dt2
15 окт 13, 13:22    [14972556]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
aleks2
Guest
DECLARE @tbl TABLE (dt DATETIME, pow FLOAT)
DECLARE @cal TABLE (dt DATETIME)

INSERT INTO @tbl ([dt],[pow])
SELECT Cast('15.10.2013 00:00:00' AS DATETIME), 100 UNION ALL
SELECT Cast('15.10.2013 00:10:10' AS DATETIME), 100.5 UNION ALL
SELECT Cast('15.10.2013 00:10:55' AS DATETIME), 100.7 UNION ALL
SELECT Cast('15.10.2013 00:11:00' AS DATETIME), 120.1 UNION ALL
SELECT Cast('15.10.2013 00:15:00' AS DATETIME), 150.7 UNION ALL
SELECT Cast('15.10.2013 00:16:30' AS DATETIME), 151.5 UNION ALL
SELECT Cast('15.10.2013 00:16:40' AS DATETIME), 152.5 UNION ALL
SELECT Cast('15.10.2013 00:17:00' AS DATETIME), 155.7 UNION ALL
SELECT Cast('15.10.2013 00:18:01' AS DATETIME), 155.7 


INSERT INTO @cal ([dt])
SELECT Cast('15.10.2013 00:00:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:01:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:02:00' AS DATETIME)UNION ALL
SELECT Cast('15.10.2013 00:03:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:04:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:05:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:06:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:07:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:08:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:09:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:10:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('15.10.2013 00:18:00' AS DATETIME) 

;with
tbln as ( select *, row_number() over(order by dt) n from @tbl )
,
tblranges as ( select t1.dt as dt1, t2.dt as dt2, t1.pow as pow1, t2.pow as pow2 
                 from tbln t1 inner join tbln t2 on t1.n+1 = t2.n
             )
select c.dt
     , tr.pow1 + ( tr.pow2 - tr.pow1 ) * datediff(second, tr.dt1, c.dt) / datediff(second, tr.dt1, tr.dt2) pow
  from @cal c inner join tblranges tr on tr.dt1 <= c.dt and c.dt < tr.dt2
15 окт 13, 13:23    [14972565]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
serega063
Member

Откуда:
Сообщений: 181
aleks2

Агонь, спасибо огромное

Как думаете использование LEAD не быстрее было бы? вместо лишнего join'a
15 окт 13, 13:55    [14972886]     Ответить | Цитировать Сообщить модератору
 Re: Построить временной интервал  [new]
serega063
Member

Откуда:
Сообщений: 181
it17
Извините за оффтоп, но какой практический смысл
автор
При отсутствии данных за некоторый период взять среднее значение. Например данные отсутствуют с 8 по 12 минуту, нужно взять среднее между 7 и 13 минутами.


Мне предстоит показать поминутную нагрузку по кол-ву платежей.
С одной стороны в это время платежи не принимались, с другой стороны наверное график будет очень резко подниматься/падать.


Ну у меня не прерывный процесс, идет работа станции, датчик не всегда может получать текущую нагрузку, поэтому линейная интерполяция разруливает данную проблему
15 окт 13, 13:57    [14972917]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить