Skip to content

Instantly share code, notes, and snippets.

@dgnorton

dgnorton/cqs.txt

Created February 21, 2020 16:47
Show Gist options
  • Save dgnorton/28f75019adebdecaca1296bcaf15ac81 to your computer and use it in GitHub Desktop.
Save dgnorton/28f75019adebdecaca1296bcaf15ac81 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cqs.txt
CREATE CONTINUOUS QUERY downsample_to_final ON nexus
BEGIN
SELECT
LAST("analog_input_temperature_value") AS "analog_input_temperature_value",
LAST("analog_input_ai0_value") AS "analog_input_ai0_value",
LAST("analog_input_ai1_value") AS "analog_input_ai1_value",
LAST("analog_input_ai2_value") AS "analog_input_ai2_value",
LAST("analog_input_ai3_value") AS "analog_input_ai3_value",
LAST("battery_level") AS "battery_level",
LAST("clock") AS "clock",
LAST("created") AS "created",
LAST("digital_input_alarm_disabled_count") AS "digital_input_alarm_disabled_count",
LAST("digital_input_di0_disabled_count") AS "digital_input_di0_disabled_count",
LAST("digital_input_di1_disabled_count") AS "digital_input_di1_disabled_count",
LAST("digital_input_di2_disabled_count") AS "digital_input_di2_disabled_count",
LAST("digital_input_di3_disabled_count") AS "digital_input_di3_disabled_count",
LAST("digital_input_alarm_enabled_count") AS "digital_input_alarm_enabled_count",
LAST("digital_input_di0_enabled_count") AS "digital_input_di0_enabled_count",
LAST("digital_input_di1_enabled_count") AS "digital_input_di1_enabled_count",
LAST("digital_input_di2_enabled_count") AS "digital_input_di2_enabled_count",
LAST("digital_input_di3_enabled_count") AS "digital_input_di3_enabled_count",
LAST("digital_input_alarm_last_disabled") AS "digital_input_alarm_last_disabled",
LAST("digital_input_di0_last_disabled") AS "digital_input_di0_last_disabled",
LAST("digital_input_di1_last_disabled") AS "digital_input_di1_last_disabled",
LAST("digital_input_di2_last_disabled") AS "digital_input_di2_last_disabled",
LAST("digital_input_di3_last_disabled") AS "digital_input_di3_last_disabled",
LAST("digital_input_alarm_last_enabled") AS "digital_input_alarm_last_enabled",
LAST("digital_input_di0_last_enabled") AS "digital_input_di0_last_enabled",
LAST("digital_input_di1_last_enabled") AS "digital_input_di1_last_enabled",
LAST("digital_input_di2_last_enabled") AS "digital_input_di2_last_enabled",
LAST("digital_input_di3_last_enabled") AS "digital_input_di3_last_enabled",
LAST("digital_input_alarm_state") AS "digital_input_alarm_state",
LAST("digital_input_di0_state") AS "digital_input_di0_state",
LAST("digital_input_di1_state") AS "digital_input_di1_state",
LAST("digital_input_di2_state") AS "digital_input_di2_state",
LAST("digital_input_di3_state") AS "digital_input_di3_state",
LAST("digital_output_do0_disabled_count") AS "digital_output_do0_disabled_count",
LAST("digital_output_do1_disabled_count") AS "digital_output_do1_disabled_count",
LAST("digital_output_do2_disabled_count") AS "digital_output_do2_disabled_count",
LAST("digital_output_do3_disabled_count") AS "digital_output_do3_disabled_count",
LAST("digital_output_do0_disabled_count") AS "digital_output_do0_disabled_count",
LAST("digital_output_do1_disabled_count") AS "digital_output_do1_disabled_count",
LAST("digital_output_do2_disabled_count") AS "digital_output_do2_disabled_count",
LAST("digital_output_do3_disabled_count") AS "digital_output_do3_disabled_count",
LAST("digital_output_do0_enabled_count"enabled_count") AS "digital_output_do2_enabled_count",
LAST("digital_output_do3_enabled_count") AS "digital_output_do3_enabled_count",
LAST("digital_output_do0_last_disabled") AS "digital_output_do0_last_disabled",
LAST("digital_output_do1_last_disabled") AS "digital_output_do1_last_disabled",
LAST("digital_output_do2_last_disabled") AS "digital_output_do2_last_disabled",
LAST("digital_output_do3_last_disabled") AS "digital_output_do3_last_disabled",
LAST("digital_output_do0_last_enabled") AS "digital_output_do0_last_enabled",
LAST("digital_output_do1_last_enabled") AS "digital_output_do1_last_enabled",
LAST("digital_output_do2_last_enabled") AS "digital_output_do2_last_enabled",
LAST("digital_output_do3_last_enabled") AS "digital_output_do3_last_enabled",
LAST("digital_output_do0_state") AS "digital_output_do0_state",
LAST("digital_output_do1_state") AS "digital_output_do1_state",
LAST("digital_output_do2_state") AS "digital_output_do2_state",
LAST("digital_output_do3_state") AS "digital_output_do3_state",
LAST("duration") AS "duration",
LAST("firmware_version") AS "firmware_version",
LAST("geolocation_latitude") AS "geolocation_latitude",
LAST("geolocation_longitude") AS "geolocation_longitude",
LAST("geolocation_obtained") AS "geolocation_obtained",
LAST("geolocation_radius") AS "geolocation_radius",
LAST("metadata_lorawan_gateway") AS "metadata_lorawan_gateway",
LAST("metadata_lorasmc_repeater") AS "metadata_lorasmc_repeater",
LAST("metadata_lorasmc_emmr") AS "metadata_lorasmc_emmr",
LAST("metadata_sigfox_station") AS "metadata_sigfox_station",
LAST("outages") AS "outages",
LAST("rssi") AS "rssi",
LAST("uptime") AS "uptime"
INTO "1y"."device_reads"
FROM "60d"."device_reads"
GROUP BY "hardware_type","serial number", time(15m)
END
CREATE CONTINUOUS QUERY downsample_to_1y ON nexus
BEGIN
SELECT
LAST("analog_input_temperature_value") AS "analog_input_temperature_value",
LAST("analog_input_ai0_value") AS "analog_input_ai0_value",
LAST("analog_input_ai1_value") AS "analog_input_ai1_value",
LAST("analog_input_ai2_value") AS "analog_input_ai2_value",
LAST("analog_input_ai3_value") AS "analog_input_ai3_value",
LAST("battery_level") AS "battery_level",
LAST("clock") AS "clock",
LAST("created") AS "created",
LAST("digital_input_alarm_disabled_count") AS "digital_input_alarm_disabled_count",
LAST("digital_input_di0_disabled_count") AS "digital_input_di0_disabled_count",
LAST("digital_input_di1_disabled_count") AS "digital_input_di1_disabled_count",
LAST("digital_input_di2_disabled_count") AS "digital_input_di2_disabled_count",
LAST("digital_input_di3_disabled_count") AS "digital_input_di3_disabled_count",
LAST("digital_input_alarm_enabled_count") AS "digital_input_alarm_enabled_count",
LAST("digital_input_di0_enabled_count") AS "digital_input_di0_enabled_count",
LAST("digital_input_di1_enabled_count") AS "digital_input_di1_enabled_count",
LAST("digital_input_di2_enabled_count") AS "digital_input_di2_enabled_count",
LAST("digital_input_di3_enabled_count") AS "digital_input_di3_enabled_count",
LAST("digital_input_alarm_last_disabled") AS "digital_input_alarm_last_disabled",
LAST("digital_input_di0_last_disabled") AS "digital_input_di0_last_disabled",
LAST("digital_input_di1_last_disabled") AS "digital_input_di1_last_disabled",
LAST("digital_input_di2_last_disabled") AS "digital_input_di2_last_disabled",
LAST("digital_input_di3_last_disabled") AS "digital_input_di3_last_disabled",
LAST("digital_input_alarm_last_enabled") AS "digital_input_alarm_last_enabled",
LAST("digital_input_di0_last_enabled") AS "digital_input_di0_last_enabled",
LAST("digital_input_di1_last_enabled") AS "digital_input_di1_last_enabled",
LAST("digital_input_di2_last_enabled") AS "digital_input_di2_last_enabled",
LAST("digital_input_di3_last_enabled") AS "digital_input_di3_last_enabled",
LAST("digital_input_alarm_state") AS "digital_input_alarm_state",
LAST("digital_input_di0_state") AS "digital_input_di0_state",
LAST("digital_input_di1_state") AS "digital_input_di1_state",
LAST("digital_input_di2_state") AS "digital_input_di2_state",
LAST("digital_input_di3_state") AS "digital_input_di3_state",
LAST("digital_output_do0_disabled_count") AS "digital_output_do0_disabled_count",
LAST("digital_output_do1_disabled_count") AS "digital_output_do1_disabled_count",
LAST("digital_output_do2_disabled_count") AS "digital_output_do2_disabled_count",
LAST("digital_output_do3_disabled_count") AS "digital_output_do3_disabled_count",
LAST("digital_output_do0_disabled_count") AS "digital_output_do0_disabled_count",
LAST("digital_output_do1_disabled_count") AS "digital_output_do1_disabled_count",
LAST("digital_output_do2_disabled_count") AS "digital_output_do2_disabled_count",
LAST("digital_output_do3_disabled_count") AS "digital_output_do3_disabled_count",
LAST("digital_output_do0_enabled_count") AS "digital_output_do0_enabled_count",
LAST("digital_output_do1_enabled_count") AS "digital_output_do1_enabled_count",
LAST("digital_output_do2_enabled_count") AS "digital_output_do2_enabled_count",
LAST("digital_output_do3_enabled_count") AS "digital_output_do3_enabled_count",
LAST("digital_output_do0_last_disabled") AS "digital_output_do0_last_disabled",
LAST("digital_output_do1_last_disabled") AS "digital_output_do1_last_disabled",
LAST("digital_output_do2_last_disabled") AS "digital_output_do2_last_disabled",
LAST("digital_output_do3_last_disabled") AS "digital_output_do3_last_disabled",
LAST("digital_output_do0_last_enabled") AS "digital_output_do0_last_enabled",
LAST("digital_output_do1_last_enabled") AS "digital_output_do1_last_enabled",
LAST("digital_output_do2_last_enabled") AS "digital_output_do2_last_enabled",
LAST("digital_output_do3_last_enabled") AS "digital_output_do3_last_enabled",
LAST("digital_output_do0_state") AS "digital_output_do0_state",
LAST("digital_output_do1_state") AS "digital_output_do1_state",
LAST("digital_output_do2_state") AS "digital_output_do2_state",
LAST("digital_output_do3_state") AS "digital_output_do3_state",
LAST("duration") AS "duration",
LAST("firmware_version") AS "firmware_version",
LAST("geolocation_latitude") AS "geolocation_latitude",
LAST("geolocation_longitude") AS "geolocation_longitude",
LAST("geolocation_obtained") AS "geolocation_obtained",
LAST("geolocation_radius") AS "geolocation_radius",
LAST("metadata_lorawan_gateway") AS "metadata_lorawan_gateway",
LAST("metadata_lorasmc_repeater") AS "metadata_lorasmc_repeater",
LAST("metadata_lorasmc_emmr") AS "metadata_lorasmc_emmr",
LAST("metadata_sigfox_station") AS "metadata_sigfox_station",
LAST("outages") AS "outages",
LAST("rssi") AS "rssi",
LAST("uptime") AS "uptime"
INTO "1y"."device_reads"
FROM "60d"."device_reads"
GROUP BY "hardware_type","serial number", time(15m)
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment