Created
February 6, 2021 00:48
-
-
Save doit-mattporter/69853142cbf60f3feb827fc9f13ee780 to your computer and use it in GitHub Desktop.
# Predict whether window was open in the last 600 seconds
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Predict whether window was open in the last 30 seconds | |
cat >predict.sql << EOL | |
DECLARE now_time DATETIME; | |
DECLARE lookback_time DATETIME; | |
DECLARE monitor_window INT64; | |
DECLARE lookback_time_monitor_window DATETIME; | |
SET now_time = CURRENT_DATETIME(); | |
SET lookback_time = DATETIME_SUB(now_time, INTERVAL 2400 SECOND); | |
SET monitor_window = 600; --The amount of time a window should be allowed to be open. Max value of 1800s. | |
SET lookback_time_monitor_window = DATETIME_SUB(now_time, INTERVAL monitor_window SECOND); | |
CREATE TEMP TABLE past_2400_temps AS SELECT * FROM | |
( | |
SELECT | |
timestamp_utc, | |
ANY_VALUE(IF(device_id = 2753085432003927, temp_f, null)) AS device_FrontOfLivingRoom, | |
ANY_VALUE(IF(device_id = 2582277131565505, temp_f, null)) AS device_LivingRoomCycleWindow, | |
ANY_VALUE(IF(device_id = 2707176363363894, temp_f, null)) AS device_LivingRoomKitchenCorner | |
FROM ${PROJECT_ID}.sensordata.temperature t | |
WHERE t.timestamp_utc BETWEEN lookback_time AND now_time | |
GROUP BY timestamp_utc | |
) | |
WHERE device_FrontOfLivingRoom IS NOT NULL | |
AND device_LivingRoomCycleWindow IS NOT NULL | |
AND device_LivingRoomKitchenCorner IS NOT NULL; | |
CREATE TEMP TABLE past_2400_temps_timediff AS SELECT * FROM ( | |
SELECT | |
timestamp_utc, | |
device_FrontOfLivingRoom, | |
device_LivingRoomCycleWindow, | |
device_LivingRoomKitchenCorner, | |
ROUND(device_FrontOfLivingRoom_600s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_600s_diff, | |
ROUND(device_FrontOfLivingRoom_300s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_300s_diff, | |
ROUND(device_FrontOfLivingRoom_180s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_180s_diff, | |
ROUND(device_FrontOfLivingRoom_60s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_60s_diff, | |
ROUND(device_FrontOfLivingRoom_30s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_30s_diff, | |
ROUND(device_FrontOfLivingRoom_15s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_15s_diff, | |
ROUND(device_FrontOfLivingRoom_5s_ago - device_FrontOfLivingRoom, 1) AS device_FrontOfLivingRoom_5s_diff, | |
ROUND(device_LivingRoomCycleWindow_600s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_600s_diff, | |
ROUND(device_LivingRoomCycleWindow_300s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_300s_diff, | |
ROUND(device_LivingRoomCycleWindow_180s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_180s_diff, | |
ROUND(device_LivingRoomCycleWindow_60s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_60s_diff, | |
ROUND(device_LivingRoomCycleWindow_30s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_30s_diff, | |
ROUND(device_LivingRoomCycleWindow_15s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_15s_diff, | |
ROUND(device_LivingRoomCycleWindow_5s_ago - device_LivingRoomCycleWindow, 1) AS device_LivingRoomCycleWindow_5s_diff, | |
ROUND(device_LivingRoomKitchenCorner_600s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_600s_diff, | |
ROUND(device_LivingRoomKitchenCorner_300s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_300s_diff, | |
ROUND(device_LivingRoomKitchenCorner_180s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_180s_diff, | |
ROUND(device_LivingRoomKitchenCorner_60s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_60s_diff, | |
ROUND(device_LivingRoomKitchenCorner_30s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_30s_diff, | |
ROUND(device_LivingRoomKitchenCorner_15s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_15s_diff, | |
ROUND(device_LivingRoomKitchenCorner_5s_ago - device_LivingRoomKitchenCorner, 1) AS device_LivingRoomKitchenCorner_5s_diff | |
FROM ( | |
SELECT | |
temp.timestamp_utc, | |
temp.device_FrontOfLivingRoom, | |
temp600.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_600s_ago, | |
temp300.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_300s_ago, | |
temp180.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_180s_ago, | |
temp60.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_60s_ago, | |
temp30.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_30s_ago, | |
temp15.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_15s_ago, | |
temp5.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_5s_ago, | |
temp.device_LivingRoomCycleWindow, | |
temp600.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_600s_ago, | |
temp300.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_300s_ago, | |
temp180.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_180s_ago, | |
temp60.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_60s_ago, | |
temp30.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_30s_ago, | |
temp15.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_15s_ago, | |
temp5.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_5s_ago, | |
temp.device_LivingRoomKitchenCorner, | |
temp600.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_600s_ago, | |
temp300.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_300s_ago, | |
temp180.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_180s_ago, | |
temp60.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_60s_ago, | |
temp30.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_30s_ago, | |
temp15.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_15s_ago, | |
temp5.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_5s_ago | |
FROM past_2400_temps temp | |
INNER JOIN past_2400_temps temp600 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 600 SECOND) = temp600.timestamp_utc | |
INNER JOIN past_2400_temps temp300 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 300 SECOND) = temp300.timestamp_utc | |
INNER JOIN past_2400_temps temp180 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 180 SECOND) = temp180.timestamp_utc | |
INNER JOIN past_2400_temps temp60 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 60 SECOND) = temp60.timestamp_utc | |
INNER JOIN past_2400_temps temp30 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 30 SECOND) = temp30.timestamp_utc | |
INNER JOIN past_2400_temps temp15 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 15 SECOND) = temp15.timestamp_utc | |
INNER JOIN past_2400_temps temp5 ON DATETIME_SUB(temp.timestamp_utc, INTERVAL 5 SECOND) = temp5.timestamp_utc | |
WHERE temp.timestamp_utc BETWEEN lookback_time AND now_time | |
AND temp.device_FrontOfLivingRoom IS NOT NULL | |
AND temp.device_LivingRoomCycleWindow IS NOT NULL | |
AND temp.device_LivingRoomKitchenCorner IS NOT NULL | |
) | |
WHERE timestamp_utc BETWEEN lookback_time_monitor_window AND now_time | |
); | |
SELECT | |
predicted_object_code, | |
COUNT(*) AS prediction_count | |
FROM | |
ML.PREDICT(MODEL ${PROJECT_ID}.sensordata.temperature_ml_model, | |
(SELECT * EXCEPT (timestamp_utc) FROM past_2400_temps_timediff)) | |
GROUP BY predicted_object_code | |
ORDER BY prediction_count DESC; | |
EOL | |
bq query --nouse_legacy_sql < predict.sql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment