USE HydroPoC;
DROP PROCEDURE IF EXISTS getSensorHistoryData;
GO
CREATE PROCEDURE getSensorHistoryData @time BIGINT, @interval SMALLINT, @sensor NVARCHAR(200)
AS
BEGIN
DECLARE @end BIGINT = CAST(DATEDIFF(s, '1970-01-01 00:00:00.000', CAST(SYSDATETIME() AS NVARCHAR(200))) AS BIGINT);
DECLARE @start BIGINT = @end - @time;
EXECUTE sp_execute_external_script @language = N'R'
, @script = N'OutputDataSet <- InputDataSet
measured <- OutputDataSet[c(sensor, "timestamp")] # Nur die angegebenen Spalten nehmen
min <- min(get(sensor, measured))
max <- max(get(sensor, measured))
median <- median(get(sensor, measured))
min_row <- measured[measured[sensor] == min, ] # Die Zeile nehmen, der in der Spalte des enstprechenden Sensors den ermittelten Wert hat
max_row <- measured[measured[sensor] == max, ] # Dito
min_timestamp <- sort(c(min_row$timestamp))[c(1)] # Wenn es den Wert mehrmals gibt, in ein Array packen, sortieren und den ersten, kleinsten Wert nehmen
max_timestamp <- sort(c(max_row$timestamp))[c(1)] # Ditto!
OutputDataSet <- data.frame(
min_timestamp = min_timestamp,
max_timestamp = max_timestamp,
median_timestamp = measured$timestamp,
measured_timestamp = measured$timestamp,
min_value = min,
max_value = max,
median_value = median,
measured_value = measured[sensor]
)'
, @input_data_1 = N'SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rownum
FROM sensordata
WHERE timestamp BETWEEN @start AND @end
)
AS t
WHERE t.rownum % @interval = 0
ORDER BY t.timestamp;'
, @params = N'@end BIGINT OUTPUT, @start BIGINT OUTPUT, @interval SMALLINT OUTPUT, @sensor NVARCHAR(200) OUTPUT'
, @end = @end
, @start = @start
, @interval = @interval
, @sensor = @sensor
-- Die vom Script zurückgegebenen Daten
WITH RESULT SETS(
(
[min_timestamp] BIGINT,
[max_timestamp] BIGINT,
[median_timestamp] BIGINT,
[measured_timestamp] BIGINT,
[min_value] FLOAT,
[max_value] FLOAT,
[median_value] FLOAT,
[measured_value] FLOAT
)
);
END;
GO
DROP PROCEDURE IF EXISTS getSensorData;
GO
CREATE PROCEDURE getSensorData @time BIGINT, @interval SMALLINT, @sensor NVARCHAR(200)
AS
BEGIN
DECLARE @end BIGINT = CAST(DATEDIFF(s, '1970-01-01 00:00:00.000', CAST(SYSDATETIME() AS NVARCHAR(200))) AS BIGINT);
DECLARE @start BIGINT = @end - @time;
EXECUTE sp_execute_external_script @language = N'R'
, @script = N'OutputDataSet <- InputDataSet
measured <- OutputDataSet[c(sensor, "timestamp")] # Nur die angegebenen Spalten nehmen
median <- median(get(sensor, measured))
OutputDataSet <- data.frame(
median_timestamp = measured$timestamp,
measured_timestamp = measured$timestamp,
median_value = median,
measured_value = measured[sensor]
)'
, @input_data_1 = N'SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rownum
FROM sensordata
WHERE timestamp BETWEEN @start AND @end
)
AS t
WHERE t.rownum % @interval = 0
ORDER BY t.timestamp;'
, @params = N'@end BIGINT OUTPUT, @start BIGINT OUTPUT, @interval SMALLINT OUTPUT, @sensor NVARCHAR(200) OUTPUT'
, @end = @end
, @start = @start
, @interval = @interval
, @sensor = @sensor
-- Die vom Script zurückgegebenen Daten
WITH RESULT SETS(
(
[median_timestamp] BIGINT,
[measured_timestamp] BIGINT,
[median_value] FLOAT,
[measured_value] FLOAT
)
);
END;
GO
EXEC getSensorHistoryData @time = 165658600, @interval = 30, @sensor = 'temperature_air';