Усреднение во времени T-SQL
НЕТ - нет ни одного языка программирования, который требовал бы комментариев.
Язык для компьютера. Комментарии для людей. Вы можете написать программу с 0% комментариев. Это будет выполнено, правильно или неправильно. Вы не можете написать программу со 100% комментариями. Он либо не будет компилироваться - без main () и т. Д. - либо для языков сценариев ничего не будет делать.
И, кроме того, настоящие программисты не комментируют свой код . Так же, как клингоны.
5 ответов
Вы можете выбрать и сгруппировать по DatePart вашей временной метки.
Например:
SELECT
DATEPART(hh, [timestamp]),
DATEPART(mi, [timestamp]),
AVG([value])
FROM
YourTable
WHERE
[timestamp] BETWEEN '2009-01-01 00:00:00.000' AND '2009-02-01 00:00:00.000'
GROUP BY
DATEPART(hh, [timestamp]),
DATEPART(mi, [timestamp])
РЕДАКТИРОВАТЬ: Для более сложных временных интервалов, например 5 минут, вы разделить datepart можно следующим образом:
DATEPART(mi, [timestamp]) / 5 * 5
Если вы собираетесь иметь высокий коэффициент чтения / записи для этих данных, вы можете рассмотреть возможность индексированного представления. Я использовал этот подход повсюду для агрегирования по периодам времени. Я только что написал в блоге пример , вот код:
create table timeSeries (
timeSeriesId int identity primary key clustered
,updateDate datetime not null
,payload float not null
)
insert timeSeries values ('2009-06-16 12:00:00', rand())
insert timeSeries values ('2009-06-16 12:00:59', rand())
insert timeSeries values ('2009-06-16 12:01:00', rand())
insert timeSeries values ('2009-06-16 12:59:00', rand())
insert timeSeries values ('2009-06-16 01:00:00', rand())
insert timeSeries values ('2009-06-16 1:30:00', rand())
insert timeSeries values ('2009-06-16 23:59:00', rand())
insert timeSeries values ('2009-06-17 00:01:00', rand())
insert timeSeries values ('2009-06-17 00:01:30', rand())
create view timeSeriesByMinute_IV with schemabinding as
select
dayBucket = datediff(day, 0, updateDate)
,minuteBucket = datediff(minute, 0, (updateDate - datediff(day, 0, updateDate)))
,payloadSum = sum(payLoad)
,numRows = count_big(*)
from dbo.timeSeries
group by
datediff(day, 0, updateDate)
,datediff(minute, 0, (updateDate - datediff(day, 0, updateDate)))
go
create unique clustered index CU_timeSeriesByMinute_IV on timeSeriesByMinute_IV (dayBucket, minuteBucket)
go
create view timeSeriesByMinute as
select
dayBucket
,minuteBucket
,payloadSum
,numRows
,payloadAvg = payloadSum / numRows
from dbo.timeSeriesByMinute_IV with (noexpand)
go
declare @timeLookup datetime, @dayBucket int, @minuteBucket int
select
@timeLookup = '2009-06-16 12:00:00'
,@dayBucket = datediff(day, 0, @timeLookup)
,@minuteBucket = datediff(minute, 0, (@timeLookup - datediff(day, 0, @timeLookup)))
select * from timeSeriesByMinute where dayBucket = @dayBucket and minuteBucket = @minuteBucket
Вы можете увидеть поиск в примере в конце блока кода. Очевидно, что вы можете определить диапазоны для запроса, а не просто искать конкретную пару dayBucket / minuteBucket.
WITH cal(m) AS
(
SELECT 1
UNION ALL
SELECT m + 1
FROM cal
WHERE m < 60
)
SELECT DATEADD(minute, m, @start), AVG(value)
FROM cal
LEFT JOIN
timestamp
ON timestamp > DATEADD(minute, m, @start)
AND timestamp <= DATEADD(minute, m + 1, @start)
GROUP BY
m
Здесь будут выбраны средние значения для всех минут в пределах данного часа, даже для тех, для которых нет записей.
В дополнение к сообщению Робина Дэя вы можете сгруппировать по 5-минутным интервалам, например:
GROUP BY
DATEPART(hh, [timestamp]),
DATEPART(mi, [timestamp]) / 5
И если вы хотите охватить несколько дней, сгруппируйте по dy для дня года :
GROUP BY
DATEPART(dy, [timestamp]),
DATEPART(hh, [timestamp]),
DATEPART(mi, [timestamp]) / 5
Я не смог заставить ответ Квассного работать без следующих изменений:
WITH cal(m) AS
(
SELECT 1
UNION ALL
SELECT m + 1
FROM cal
WHERE m < 60
)
SELECT DATEADD(minute, m, @start) m, AVG(value)
FROM cal
LEFT JOIN
YourTable
ON timestamp > DATEADD(minute, m, @start)
AND timestamp <= DATEADD(minute, m + 1, @start)
GROUP BY
m