Create a table with changes in temperature recorded from a device's current value to the value x seconds ago: 5, 15, 30, 60, 180, 300, and 600 seconds

temperature_pivoted_timediff.sh

# Create a table with changes in temperature recorded from a device's current value to the value x seconds ago: 5, 15, 30, 60, 180, 300, and 600 seconds
bq query --nouse_legacy_sql \
"CREATE OR REPLACE TABLE ${PROJECT_ID}.sensordata.temperature_pivoted_timediff PARTITION BY DATE(timestamp_utc) CLUSTER BY timestamp_epoch AS (
  SELECT
    timestamp_utc,
    timestamp_epoch,
    object_code,
    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
      tp.timestamp_utc,
      tp.timestamp_epoch,
      COALESCE(woc.ObjectCode, 0) AS object_code,
      tp.device_FrontOfLivingRoom,
      tpj600.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_600s_ago,
      tpj300.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_300s_ago,
      tpj180.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_180s_ago,
      tpj60.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_60s_ago,
      tpj30.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_30s_ago,
      tpj15.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_15s_ago,
      tpj5.device_FrontOfLivingRoom AS device_FrontOfLivingRoom_5s_ago,
      tp.device_LivingRoomCycleWindow,
      tpj600.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_600s_ago,
      tpj300.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_300s_ago,
      tpj180.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_180s_ago,
      tpj60.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_60s_ago,
      tpj30.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_30s_ago,
      tpj15.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_15s_ago,
      tpj5.device_LivingRoomCycleWindow AS device_LivingRoomCycleWindow_5s_ago,
      tp.device_LivingRoomKitchenCorner,
      tpj600.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_600s_ago,
      tpj300.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_300s_ago,
      tpj180.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_180s_ago,
      tpj60.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_60s_ago,
      tpj30.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_30s_ago,
      tpj15.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_15s_ago,
      tpj5.device_LivingRoomKitchenCorner AS device_LivingRoomKitchenCorner_5s_ago
    FROM ${PROJECT_ID}.sensordata.temperature_pivoted tp
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj600 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 600 SECOND) = tpj600.timestamp_utc
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj300 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 300 SECOND) = tpj300.timestamp_utc
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj180 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 180 SECOND) = tpj180.timestamp_utc
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj60 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 60 SECOND) = tpj60.timestamp_utc
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj30 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 30 SECOND) = tpj30.timestamp_utc
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj15 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 15 SECOND) = tpj15.timestamp_utc
    INNER JOIN ${PROJECT_ID}.sensordata.temperature_pivoted tpj5 ON DATETIME_SUB(tp.timestamp_utc, INTERVAL 5 SECOND) = tpj5.timestamp_utc
    LEFT JOIN ${PROJECT_ID}.sensordata.window_opened_closed_utc woc ON EXTRACT(DATE FROM tp.timestamp_utc) = EXTRACT(DATE FROM woc.StartTime) AND tp.timestamp_utc BETWEEN woc.StartTime AND woc.EndTime
  )
)
"