Create a streaming temperature data pivot table such that all temps for any given second are recorded in one row, one column per device

temperature_pivoted.sh

# Create a streaming temperature data pivot table such that all temps for any given second are recorded in one row, one column per device
# Also, exclude timepoints where not all three devices were powered
bq query --nouse_legacy_sql \
"CREATE OR REPLACE TABLE ${PROJECT_ID}.sensordata.temperature_pivoted PARTITION BY DATE(timestamp_utc) CLUSTER BY timestamp_epoch AS (
  SELECT * FROM (
    SELECT
      t.timestamp_utc,
      ANY_VALUE(t.timestamp_epoch) AS timestamp_epoch,
      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
    GROUP BY timestamp_utc
  )
  WHERE Device_FrontOfLivingRoom IS NOT NULL
    AND Device_LivingRoomCycleWindow IS NOT NULL
    AND Device_LivingRoomKitchenCorner IS NOT NULL
)
"