Product
Query examples
Examples of SQL queries
/* Based on device data, this query returns the average
* of the battery level for every hour for each device_id
*/
WITH avg_metrics AS (
SELECT device_id,
DATE_BIN('1 hour'::INTERVAL, time, 0) AS period,
AVG(battery_level) AS avg_battery_level
FROM devices.readings
GROUP BY 1, 2
ORDER BY 1, 2
)
SELECT period,
t.device_id,
manufacturer,
avg_battery_level
FROM avg_metrics t, devices.info i
WHERE t.device_id = i.device_id
AND model = 'mustang'
LIMIT 10;
+---------------+------------+--------------+-------------------+
| period | device_id | manufacturer | avg_battery_level |
+---------------+------------+--------------+-------------------+
| 1480802400000 | demo000001 | iobeam | 49.25757575757576 |
| 1480806000000 | demo000001 | iobeam | 47.375 |
| 1480802400000 | demo000007 | iobeam | 25.53030303030303 |
| 1480806000000 | demo000007 | iobeam | 58.5 |
| 1480802400000 | demo000010 | iobeam | 34.90909090909091 |
| 1480806000000 | demo000010 | iobeam | 32.4 |
| 1480802400000 | demo000016 | iobeam | 36.06060606060606 |
| 1480806000000 | demo000016 | iobeam | 35.45 |
| 1480802400000 | demo000025 | iobeam | 12 |
| 1480806000000 | demo000025 | iobeam | 16.475 |
+---------------+------------+--------------+-------------------+
/* Based on reports from IoT devices, this query returns
* the voltage corresponding to the maximum
* global active power for each meter_id
*/
SELECT meter_id,
MAX_BY("Voltage", "Global_active_power") AS voltage_max_global_power
FROM iot.power_consumption
GROUP BY 1
LIMIT 10;
+------------+--------------------------+
| meter_id | voltage_max_global_power |
+------------+--------------------------+
| 840073190N | 233.57 |
| 840072401F | 233.53 |
| 840072655G | 234.1 |
| 840071893D | 234.47 |
| 840073950P | 231.73 |
| 840075260N | 235.51 |
| 840076398A | 234.56 |
| 84007B071E | 231.94 |
| 840075959Y | 237.21 |
| 840072534A | 231.96 |
+------------+--------------------------+
/* Based on the location of the International Space Station,
* this query returns the 10 closest capital cities from
* the last known position
*/
SELECT city as "City Name",
country as "Country",
DISTANCE(i.position, c.location)::LONG / 1000 AS "Distance [km]"
FROM demo.iss i
CROSS JOIN demo.world_cities c
WHERE capital = 'primary'
AND ts = (SELECT MAX(ts) FROM demo.iss)
ORDER BY 3 ASC
LIMIT 10;
+--------------+-----------------------------------+---------------+
| City Name | Country | Distance [km] |
+--------------+-----------------------------------+---------------+
| Papeete | French Polynesia | 3386 |
| Avarua | Cook Islands | 3708 |
| Wellington | New Zealand | 4565 |
| Alofi | Niue | 4628 |
| Nuku‘alofa | Tonga | 4887 |
| Pago Pago | American Samoa | 5063 |
| Santiago | Chile | 5112 |
| Apia | Samoa | 5182 |
| Stanley | Falkland Islands (Islas Malvinas) | 5266 |
| Suva | Fiji | 5611 |
+--------------+-----------------------------------+---------------+
/*
* Based on system event logs, this query calculates:
* - a filter for specific messages using a full-text index
* - the number of entries per minute
* - the average scoring ratio for each matched row
*/
SELECT DATE_TRUNC('minute', receivedat) AS event_time,
COUNT(*) AS entries,
AVG(_score) AS avg_score
FROM "syslog"."systemevents"
WHERE MATCH(message, 'authentication failure')
USING most_fields WITH (analyzer = 'whitespace')
AND MATCH(syslogtag, 'sshd')
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
+---------------+---------+--------------------+
| event_time | entries | avg_score |
+---------------+---------+--------------------+
| 1620220260000 | 4 | 1.5798743814229965 |
| 1620220200000 | 8 | 1.7750384211540222 |
| 1620220140000 | 10 | 1.6113891124725341 |
| 1620220080000 | 9 | 1.676726798216502 |
| 1620220020000 | 8 | 1.6908064410090446 |
| 1620219960000 | 8 | 1.690401442348957 |
| 1620219900000 | 7 | 1.7646006005150932 |
| 1620219840000 | 7 | 1.7795820917401994 |
| 1620219780000 | 10 | 1.5844267368316651 |
| 1620219720000 | 13 | 1.5637413492569556 |
+---------------+---------+--------------------+
More examples of time-series queries
/* To identify gaps on the readings, the following queries generates a series
* and by joining it with the original data, you can spot any gap */
with avg_battery AS (
SELECT battery_level, time
FROM devices.readings
WHERE device_id = 'demo000007'
AND time > 1480118400000
AND time < 1480301200000
ORDER BY 2
),
all_hours AS (
SELECT generate_series(1480118430000,1480301200000,'30 second'::interval) AS generated_hours
)
SELECT time, generated_hours, battery_level
FROM all_hours
LEFT JOIN avg_battery ON generated_hours = time
ORDER BY 2
LIMIT 20;
+---------------+---------------+---------------+
| time | hours | battery_level |
+---------------+---------------+---------------+
| 1480118430000 | 1480118430000 | 67 |
| 1480118460000 | 1480118460000 | 66 |
| 1480118490000 | 1480118490000 | 66 |
| 1480118520000 | 1480118520000 | 66 |
| 1480118550000 | 1480118550000 | 66 |
| 1480118580000 | 1480118580000 | 66 |
| 1480118610000 | 1480118610000 | 65 |
| 1480118640000 | 1480118640000 | NULL |
| 1480118670000 | 1480118670000 | 65 |
| 1480118700000 | 1480118700000 | 65 |
| 1480118730000 | 1480118730000 | 65 |
| 1480118760000 | 1480118760000 | 65 |
| 1480118790000 | 1480118790000 | 65 |
| 1480118820000 | 1480118820000 | 65 |
| 1480118850000 | 1480118850000 | 65 |
| 1480118880000 | 1480118880000 | 65 |
| 1480118910000 | 1480118910000 | 65 |
| 1480118940000 | 1480118940000 | 65 |
| 1480118970000 | 1480118970000 | NULL |
| 1480119000000 | 1480119000000 | NULL |
+---------------+---------------+---------------+
/* Based on device data, this query returns the number of battery charges
* per day for a given device_id */
WITH aux_charging AS (
SELECT time,
DATE_BIN('P1D'::INTERVAL,time,0) AS day,
battery_status,
LAG(battery_status) OVER (PARTITION BY device_id ORDER BY time) AS prev_battery_status
FROM devices.readings
WHERE device_id = 'demo000001'
ORDER BY time
),
count_start_charging AS (
SELECT day, (case when battery_status <> prev_battery_status then 1 else 0 end) AS start_charging
FROM aux_charging
ORDER BY 1
)
SELECT day, sum(start_charging) as charges_number
FROM count_start_charging
GROUP BY 1
ORDER BY 1;
+---------------+---------------+
| count_charges | day |
+---------------+---------------+
| 2 | 1479168000000 |
| 4 | 1479254400000 |
| 2 | 1479340800000 |
| 10 | 1479427200000 |
| 7 | 1479600000000 |
| 8 | 1479686400000 |
| 6 | 1479772800000 |
| 11 | 1479859200000 |
| 5 | 1480032000000 |
| 7 | 1480118400000 |
| 6 | 1480204800000 |
| 10 | 1480291200000 |
| 3 | 1480464000000 |
| 3 | 1480550400000 |
| 7 | 1480636800000 |
| 2 | 1480723200000 |
+---------------+---------------+
/* Based on device data, this query returns the average of the battery temperature
* for each OS version */
SELECT device_info['os_name'], avg(battery_temperature)
FROM "devices"."readings"
GROUP BY 1
LIMIT 100;
+---------+--------------------------+
| os_name | avg(battery_temperature) |
+---------+--------------------------+
| 4.4.4 | 90.85937893039049 |
| 5.1.0 | 90.86754559738132 |
| 6.0.1 | 90.84230101265824 |
| 5.0.0 | 90.8574802739726 |
+---------+--------------------------+
More examples of IoT queries
/* Based on IoT devices reports, this query returns the voltage variation over time
for a given meter_id */
WITH avg_voltage_all AS (
SELECT meter_id,
avg("Voltage") AS avg_voltage,
date_bin('1 hour'::INTERVAL, ts, 0) AS time
FROM iot.power_consumption
WHERE meter_id = '840072572S'
GROUP BY 1, 3
ORDER BY 3
)
SELECT time,
(avg_voltage - lag(avg_voltage) over (PARTITION BY meter_id ORDER BY time)) AS var_voltage
FROM avg_voltage_all
LIMIT 10;
+---------------+-----------------------+
| time | var_voltage |
+---------------+-----------------------+
| 1166338800000 | NULL |
| 1166479200000 | -2.30999755859375 |
| 1166529600000 | 4.17999267578125 |
| 1166576400000 | -0.3699951171875 |
| 1166734800000 | -3.7100067138671875 |
| 1166785200000 | -1.5399932861328125 |
| 1166893200000 | -3.839996337890625 |
| 1166997600000 | 9.25 |
| 1167044400000 | 0.4499969482421875 |
| 1167174000000 | 3.220001220703125 |
+---------------+-----------------------+
/* Based on IoT devices reports, this query returns the voltage corresponding to
the maximum global active power for each meter_id */
SELECT meter_id,
max_by("Voltage", "Global_active_power") AS voltage_max_global_power
FROM iot.power_consumption
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
+------------+--------------------------+
| meter_id | voltage_max_global_power |
+------------+--------------------------+
| 840070437W | 246.77 |
| 840073628P | 246.69 |
| 840074265G | 246.54 |
| 840070238E | 246.35 |
| 840070335K | 246.34 |
| 840075190M | 245.15 |
| 840072876X | 244.81 |
| 840070636M | 242.98 |
| 84007B113A | 242.93 |
| 840073250D | 242.28 |
+------------+--------------------------+
More examples of geospatial queries
/* Based on NYC taxi trips, this query lists the latest 5 trips starting
within 500m of the Times Square */
SELECT pickup_datetime AS "time",
trip_distance,
latitude(pickup_location) latitude,
longitude(pickup_location) longitude
FROM nyc_taxi.trips
WHERE distance('POINT(-73.985130 40.758896)'::GEO_POINT, pickup_location) < 500
AND pickup_datetime BETWEEN 1451602800000 AND 1483138800000
ORDER BY time DESC
LIMIT 5;
+---------------+---------------+--------------------+--------------------+
| time | trip_distance | latitude | longitude |
+---------------+---------------+--------------------+--------------------+
| 1467331195000 | 3.0 | 40.76302719116211 | -73.98388671875 |
| 1467331192000 | 1.8 | 40.761539459228516 | -73.98821258544922 |
| 1467331192000 | 4.59 | 40.75526809692383 | -73.98706817626953 |
| 1467331191000 | 3.6 | 40.75738525390625 | -73.99018096923828 |
| 1467331183000 | 1.7 | 40.75679016113281 | -73.98326110839844 |
+---------------+---------------+--------------------+--------------------+
/*
* Based on NYC taxi trips, this query calculates:
* - the distance of a given dropoff location to a fixed point of interest,
* partitioned in buckets of 50 meters each
* - the average trip distance in kilometers within that partition
* - the number of trips within that partition
*/
SELECT floor(
distance(t.dropoff_location, 'POINT(-73.984 40.758)') / 50
) * 50 AS point_distance,
avg(
distance(t.pickup_location, t.dropoff_location) / 1000.0
) AS trip_distance,
count(*) AS trips
FROM nyc_taxi.trips t
WHERE t.pickup_month = 1451606400000
GROUP BY 1
ORDER BY 1 ASC;
+----------------+---------------------------+--------+
| point_distance | trip_distance | trips |
+----------------+---------------------------+--------+
| 0 | 4.183248580260856 | 2258 |
| 50 | 3.5389173023149585 | 10891 |
| 100 | 2.973617177658283 | 23798 |
| 150 | 2.8757472450827946 | 41841 |
| 200 | 2.936262490313057 | 44880 |
| 250 | 2.901378357605571 | 44918 |
| 300 | 2.7757565505864257 | 49662 |
| 350 | 2.6239797352885708 | 57803 |
| 400 | 2.822579512102226 | 73603 |
| 450 | 2.5346381484458105 | 79051 |
| 500 | 2.4982467856661392 | 88705 |
| 550 | 2.613586563537282 | 93082 |
| 600 | 2.6103083633519786 | 98527 |
| 650 | 2.5381838408225286 | 113023 |
| 700 | 2.537684333523469 | 125640 |
| 750 | 2.5883437771402007 | 127099 |
| 800 | 2.617847584473263 | 119027 |
| 850 | 2.554740996422641 | 132532 |
| 900 | 2.499970010565691 | 164115 |
| 950 | 2.612569696294771 | 181091 |
| 1000 | 2.5512554347843093 | 152102 |
+----------------+---------------------------+--------+
/* Based on NYC taxi trips, this query returns the number
of trips per day with the region of the New York
Central Park as destination */
SELECT date_bin('24 hour'::interval, pickup_datetime, 0) AS day,
count(*) AS trips
FROM nyc_taxi.trips
WHERE within(
dropoff_location,
'POLYGON(
(-73.97308900174315 40.764422448981996,
-73.98192956265623 40.76812781417226,
-73.9584064734938 40.80087951931638,
-73.94982340464614 40.797240957024385,
-73.97308900174315 40.764422448981996)
)'
)
AND pickup_datetime BETWEEN 1417392000000 AND 1420070400000
GROUP BY 1
ORDER BY 1;
+---------------+-------+
| day | trips |
+---------------+-------+
| 1417392000000 | 5094 |
| 1417478400000 | 5580 |
| 1417564800000 | 5760 |
| 1417651200000 | 5970 |
| 1417737600000 | 5850 |
| 1417824000000 | 5472 |
| 1417910400000 | 5335 |
| 1417996800000 | 5573 |
| 1418083200000 | 5382 |
| 1418169600000 | 5484 |
| 1418256000000 | 6291 |
| 1418342400000 | 5953 |
| 1418428800000 | 5846 |
| 1418515200000 | 5177 |
| 1418601600000 | 5446 |
| 1418688000000 | 5639 |
| 1418774400000 | 6051 |
| 1418860800000 | 6161 |
| 1418947200000 | 6052 |
| 1419033600000 | 5695 |
| 1419120000000 | 4891 |
| 1419206400000 | 5050 |
| 1419292800000 | 4786 |
| 1419379200000 | 4637 |
| 1419465600000 | 2721 |
| 1419552000000 | 3789 |
| 1419638400000 | 4343 |
| 1419724800000 | 4048 |
| 1419811200000 | 4769 |
| 1419897600000 | 5126 |
| 1419984000000 | 5128 |
+---------------+-------+
More examples of log analysis queries
/*
* Based on system event logs, this query calculates:
* - a filter for specific messages using a full-text index
* - the number of entries per minute
* - the average scoring ratio for each matched row
*/
SELECT DATE_TRUNC('minute', receivedat) AS event_time,
COUNT(*) AS entries,
AVG(_score) AS avg_score
FROM "syslog"."systemevents"
WHERE MATCH(message, 'authentication failure')
USING most_fields WITH (analyzer = 'whitespace')
AND MATCH(syslogtag, 'sshd')
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
+---------------+---------+--------------------+
| event_time | entries | avg_score |
+---------------+---------+--------------------+
| 1620220260000 | 4 | 1.5798743814229965 |
| 1620220200000 | 8 | 1.7750384211540222 |
| 1620220140000 | 10 | 1.6113891124725341 |
| 1620220080000 | 9 | 1.676726798216502 |
| 1620220020000 | 8 | 1.6908064410090446 |
| 1620219960000 | 8 | 1.690401442348957 |
| 1620219900000 | 7 | 1.7646006005150932 |
| 1620219840000 | 7 | 1.7795820917401994 |
| 1620219780000 | 10 | 1.5844267368316651 |
| 1620219720000 | 13 | 1.5637413492569556 |
+---------------+---------+--------------------+