Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a streaming temperature data pivot table such that all temps for any given second are recorded in one row, one column per device
# 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
)
"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment