# Predict whether window was open in the last 600 seconds

bigquery_ml_predict.sh

# 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