Skip to content

Instantly share code, notes, and snippets.

@ldenson11

ldenson11/IoT Applications.sql Secret

Created March 13, 2020 14:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ldenson11/73802f725d09e09b4cd5d67a55e97f89 to your computer and use it in GitHub Desktop.
Save ldenson11/73802f725d09e09b4cd5d67a55e97f89 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
IoT Applications.sql
-- production IoT application job
-- display range remaining every 15 seconds
SELECT boardid,
tumble_end(eventTimestamp, interval '15' second) as TS,
CAST(ROUND(MIN(CAST(battery_level as numeric)),2) AS varchar)||'%' as state_of_charge,
MIN(CAST(trip_distance AS numeric)) AS distance_covered,
100-MIN(CAST(battery_level as numeric)) AS battery_pct_used,
MIN(CAST(trip_distance AS numeric))/(100-MIN(CAST(battery_level as numeric))) AS foot_per_battery_pct,
MIN(CAST(battery_level as numeric))*(MIN(CAST(trip_distance AS numeric))/(100-MIN(CAST(battery_level as numeric)))) as range_in_feet
FROM kickflips
GROUP by boardid, tumble(eventTimestamp, interval '15' second)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment