В развивающемся “Интернете вещей” существует множество устройств, собирающих данные с разными частотами дискретизации. Интеграция этих данных таким образом, чтобы они имели общую гранулярность во времени, важна не только для точного анализа и обработки, но также поможет уменьшить объем данных, которые нужно хранить и обрабатывать.

В этом блоге мы покажем, как использовать функцию AVG и оператор GROUP BY в T-SQL для преобразования данных, собранных с двух устройств с разными частотами дискретизации, в одну строку в секунду.

Давайте начнем с создания двух таблиц, одна для данных устройства A и другая для данных устройства B:

create table dbo.tblDeviceA 
(
  pkDeviceA integer identity(1,1) primary key,
  recordingTime datetime2,
  xAxis decimal(5,3),
  yAxis decimal(5,3),
  zAxis decimal(5,3)
)

create table dbo.tblDeviceB 
(
  pkDeviceB integer identity(1,1) primary key,
  recordingTime datetime2,
  temperature decimal(5,2),
  humidity decimal(5,2)
)

Затем мы заполним таблицу устройства A симулированными данными от устройства с частотой дискретизации 100 Гц:

declare @i as integer
declare @dateTime as datetime2
set @i=0
set @dateTime='2014-11-15 12:00:00.000';
while @i<1000
begin
  insert into dbo.tblDeviceA values 
  (dateadd(ms,@i*10,@dateTime), 2.0+rand()-0.5, rand()-0.5, rand()/10)
  set @i=@i+1
end

После вставки строк мы можем выбрать верхние 10 строк из таблицы для проверки.

Аналогично, мы заполним таблицу устройства B симулированными данными от устройства с частотой дискретизации 40 Гц:

declare @i as integer
declare @dateTime as datetime2
set @i=0
set @dateTime='2014-11-15 12:00:00.000';
while @i<400
begin
  insert into dbo.tblDeviceB values 
  (dateadd(ms,@i*25,@dateTime), 72.0+rand()/10, 45.0+rand()/10)
  set @i=@i+1
end

После вставки строк мы можем выбрать верхние 10 строк из таблицы для проверки.

В следующих шагах мы свернем данные для каждого устройства в одну строку данных в секунду. Принятый способ сделать это – усреднить записанные значения данных в каждую секунду.

Для устройства A мы можем использовать следующий запрос:

select CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120)) as recordingTime, 
cast(avg(xAxis) as decimal(5,3)) as averageXAxis,
cast(avg(yAxis) as decimal(5,3)) as averageYAxis,
cast(avg(zAxis) as decimal(5,3)) as averageZAxis
from dbo.tblDeviceA
group by CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120))
order by CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120))

Результаты этого запроса для устройства A покажут, что данные уменьшились с 100 строк в секунду до 1 строки в секунду.

Аналогично, для устройства B мы можем использовать следующий запрос:

select CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120)) as recordingTime, 
cast(avg(temperature) as decimal(5,2)) as averageTemperature,
cast(avg(humidity) as decimal(5,2)) as averageHumidity
from dbo.tblDeviceB
group by CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120))
order by CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120))

Результаты этого запроса для устройства B покажут, что данные уменьшились с 40 строк в секунду до 1 строки в секунду.

Последний шаг – отобразить данные с обоих устройств вместе на одной строке. Это можно сделать с помощью INNER JOIN по столбцам времени записи:

select
a.recordingTime,
a.averageXAxis,
a.averageYAxis,
a.averageZAxis,
b.averageTemperature,
b.averageHumidity  
from
(
   select CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120)) as recordingTime, 
   cast(avg(xAxis) as decimal(5,3)) as averageXAxis,
   cast(avg(yAxis) as decimal(5,3)) as averageYAxis,
   cast(avg(zAxis) as decimal(5,3)) as averageZAxis
   from dbo.tblDeviceA
   group by CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120))
)as A
inner join
( 
   select CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120)) as recordingTime, 
   cast(avg(temperature) as decimal(5,2)) as averageTemperature,
   cast(avg(humidity) as decimal(5,2)) as averageHumidity
   from dbo.tblDeviceB
   group by CONVERT(DATETIME, CONVERT(VARCHAR(20), recordingTime, 120))
) as B
on A.recordingTime=B.recordingTime
order by A.recordingTime

Результаты из вышеприведенного запроса покажут данные с обоих устройств, объединенные в одну строку.

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