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 |
+---------------+------------+--------------+-------------------+
SELECT text, _score
FROM word_embeddings
WHERE knn_match(embedding,[0.3, 0.6, 0.0, 0.9], 2)
ORDER BY _score DESC;
|------------------------|--------|
| text | _score |
|------------------------|--------|
|Discovering galaxies |0.917431|
|Discovering moon |0.909090|
|Exploring the cosmos |0.909090|
|Sending the mission |0.270270|
|------------------------|--------|
SELECT show_id, title, director, country, release_year, rating, _score
FROM "netflix_catalog"
WHERE MATCH(title_director_description_ft, 'title^2 Friday') USING best_fields
AND type='Movie'
ORDER BY _score DESC;
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| show_id | title | director | country | release_year | rating | _score |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| s1674 | Black Friday | Anurag Kashyap | India | 2004 | TV-MA | 5.6455536 |
| s6805 | Friday the 13th | Marcus Nispel | United States | 2009 | R | 3.226806 |
| s1038 | Tuesdays & Fridays | Taranveer Singh | India | 2021 | TV-14 | 3.1089375 |
| s7494 | Monster High: Friday Night Frights | Dustin McKenzie | United States | 2013 | TV-Y7 | 3.0620003 |
| s3226 | Little Singham: Mahabali | Prakash Satam | NULL | 2019 | TV-Y7 | 3.002901 |
| s8233 | The Bye Bye Man | Stacy Title | United States, China | 2017 | PG-13 | 2.9638999 |
| s8225 | The Brawler | Ken Kushner | United States | 2019 | TV-MA | 2.8108454 |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
/* 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 |
+--------------+-----------------------------------+---------------+
/* The query returns for each device_id the average, minimum and maximum temperature
* over the last 100 temperature readings. */
SELECT r.device_id,
AVG(battery['temperature']) OVER w AS "last 100 temperatures",
MIN(battery['temperature']) OVER w AS "min temperature",
MAX(battery['temperature']) OVER w AS "max temperature"
FROM devices_readings r
JOIN devices_info i ON r.device_id = i.device_id
WINDOW w AS (ORDER BY "ts" DESC ROWS BETWEEN 100 PRECEDING AND CURRENT ROW);
+------------+-----------------------+-----------------+-----------------+
| device_id | last 100 temperatures | min temperature | max temperature |
+------------+-----------------------+-----------------+-----------------+
| demo000230 | 88.9 | 88.9 | 88.9 |
| demo000263 | 89.55000000000001 | 88.9 | 90.2 |
| demo000836 | 90.36666666666667 | 88.9 | 92.0 |
| demo001960 | 90.65 | 88.9 | 92.0 |
| demo001780 | 90.1 | 87.9 | 92.0 |
| demo001556 | 90.18333333333334 | 87.9 | 92.0 |
| demo001851 | 90.42857142857143 | 87.9 | 92.0 |
| demo001609 | 90.075 | 87.6 | 92.0 |
| demo002840 | 90.43333333333334 | 87.6 | 93.3 |
| demo002666 | 90.76 | 87.6 | 93.7 |
+------------+-----------------------+-----------------+-----------------+
SELECT
title AS title,
protagonist['first_name'] AS name,
date_format(
'%D %b %Y',
'GMT',
protagonist['details']['birthday']
) AS born,
quotation['words'] AS quote
FROM quotes limit 100;
+---------------+---------+--------------------+
| 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 vector queries
SELECT text, _score
FROM word_embeddings
WHERE knn_match(embedding, (SELECT embedding FROM word_embeddings WHERE text ='Discovering galaxies'), 2)
ORDER BY _score DESC
|------------------------|--------|
| text | _score |
|------------------------|--------|
|Discovering galaxies |1 |
|Discovering moon |0.952381|
|Exploring the cosmos |0.840336|
|Sending the mission |0.250626|
|------------------------|--------|
More examples of search engine queries
/* The table stores data about netflix movies and series. The composite fulltext
* index is defined on two columns with the analyzer based on English language. */
CREATE TABLE "netflix_catalog" (
"show_id" TEXT PRIMARY KEY,
"type" TEXT,
"title" TEXT,
"director" TEXT,
"cast" ARRAY(TEXT),
"country" TEXT,
"date_added" TIMESTAMP,
"release_year" TEXT,
"rating" TEXT,
"duration" TEXT,
"listed_in" ARRAY(TEXT),
"description" TEXT,
INDEX title_director_description_ft using FULLTEXT (title, director, description)
WITH (analyzer = 'english')
);
/* This query searches for records where the combined fields of title, director, and
* description match the phrase 'mission impossible', with a different weights given to
* title and description. The results are ordered by their relevance score in desc order. */
SELECT show_id, type, title, director, country, rating, description, _score
FROM "netflix_catalog"
WHERE MATCH((title_director_description_ft, title 1.5, description 1), 'mission impossible')
ORDER BY _score DESC;
+---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| show_id | type | title | director | country | rating | description | _score |
+---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| s1000 | Movie | Stowaway | Joe Penna | Germany, United States | TV-MA | A three-person crew on a mission to Mars faces an impossible choice when an unplanned passenger jeopardizes the lives of everyone on board. | 5.0469 |
| s1768 | Movie | The Impossible | J.A. Bayona | Spain, Thailand, United States | PG-13 | Tracking one family's harrowing experiences, this gripping drama depicts the chaos generated by the massive 2004 tsunami in Southeast Asia. | 3.1496503 |
| s7873 | TV Show | Rishta.com | NULL | India | TV-14 | Partners at an Indian matrimonial agency face endlessly challenging and often impossible demands as they help clients make the perfect match. | 3.131972 |
| s3242 | TV Show | Nailed It! Holiday! | NULL | United States | TV-PG | It's the "Nailed It!" holiday special you've been waiting for, with missing ingredients, impossible asks and desserts that look delightfully sad. | 3.1265335 |
| s1032 | Movie | Into the Beat | Stefan Westerwelle | Germany | TV-14 | A teen ballerina discovers hip-hop by chance and is faced with an impossible choice: Does she follow her parents' footsteps... or her newfound passion? | 2.9678147 |
| s1583 | Movie | Triple 9 | John Hillcoat | United States | R | A group of dirty Atlanta cops blackmailed by the Russian mob plan a seemingly impossible heist that sets off an explosive chain reaction of violence. | 2.9678147 |
| s848 | TV Show | Mad for Each Other | NULL | NULL | TV-MA | Bothered to realize they are next-door neighbors and share a psychiatrist, a man and a woman find it's impossible to stay out of each other's way. | 2.9385366 |
+---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
/* The query searches records where the combined title, director, and description
* fields phrase-prefix match 'award winning' with a relevance score of at least 10,
* and orders the results by their relevance score in descending order. */
SELECT show_id, type, title, country, rating, duration, _score
FROM "netflix_catalog"
WHERE MATCH(title_director_description_ft, 'award winning') USING phrase_prefix
AND _score >= 10
ORDER BY _score DESC;
+---------+----------+-----------------------------------------+-----------------+--------+------------+-----------+
| show_id | type | title | country | rating | duration | _score |
| ------- | -------- | ----------------------------------------| --------------- | ------ | ---------- | --------- |
| s2423 | TV Show | Queer Eye | United States | TV-14 | 5 Seasons | 56.835224 |
| s5930 | Movie | Print the Legend | United States | TV-14 | 100 min | 56.835224 |
| s849 | TV Show | Master of None | United States | TV-MA | 3 Seasons | 54.720444 |
| s7369 | TV Show | Mad Men | United States | TV-14 | 7 Seasons | 53.722008 |
| s4556 | Movie | The Meaning of Monty Python | United Kingdom | TV-MA | 60 min | 51.933487 |
| s6342 | TV Show | Blue Planet II | United Kingdom | TV-G | 1 Season | 51.933487 |
| s4162 | Movie | Surga Yang Tak Dirindukan 2 | Indonesia | TV-14 | 119 min | 50.937637 |
| s5186 | TV Show | Alias Grace | Canada | TV-MA | 1 Season | 41.05056 |
| s4184 | TV Show | The World's Most Extraordinary Homes | United Kingdom | TV-G | 3 Seasons | 39.506954 |
| s6111 | Movie | Alejandro Sanz: What I Was Is What I Am | Spain | TV-MA | 102 min | 38.77893 |
+---------+----------+-----------------------------------------+-----------------+--------+------------+-----------+
/* This query uses the composite index to match the term 'scence fction'
* (with a likely typo in 'science fiction'), allowing for some fuzziness in matching,
* using the English analyzer, requiring at least one term to match. */
SELECT show_id, title, country, description, _score
FROM "netflix_catalog"
WHERE MATCH(title_director_description_ft, 'scence fction')
USING best_fields
WITH (
fuzziness=1,
analyzer='english',
minimum_should_match=1,
operator='or'
)
ORDER BY _score DESC;
+---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| show_id | title | country | description | _score |
+---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| s1604 | Alien Worlds | United Kingdom | Applying the laws of life on Earth to the rest of the galaxy, this series blends science fact and fiction to imagine alien life on other planets. | 4.052918 |
| s2589 | Oh Yuck | Canada | Using a concoction of cartoons, comedy and live action, Dr. Yuck and his eccentric lab mates investigate the science behind the planet's ickiest things. | 3.8219523 |
| s7848 | Red vs. Blue | United States | This parody of first-person shooter games, military life and science-fiction films centers on a civil war fought in the middle of a desolate canyon. | 3.8219523 |
| s3054 | Border Security: America's Front Line | Canada | This reality series chronicles the behind-the-scenes action as U.S. Customs and Border Protection agents safeguard the nation's crossings and ports. | 3.5455647 |
| s5870 | Turbo FAST | United States | Join Turbo and his posse, the Fast Action Stunt Team. Filled with comedy, action and fun, this series amps it up to the extreme. | 2.695207 |
| s3574 | Our Planet - Behind The Scenes | United Kingdom | Years spent recording footage of creatures from every corner of the globe is bound to produce a bit of drama. Here's a behind-the-scenes look. | 2.5541513 |
| s8016 | Sid the Science Kid | United States | Armed with a healthy sense of humor and the help of his teacher, friends and family, curious kid Sid tackles questions youngsters have about science. | 2.5541513 |
| s4917 | Bill Nye: Science Guy | United States | The dynamic, bow-tied host behind the young adult science show faces climate-change skeptics as he demonstrates the importance of scientific evidence. | 2.49332 |
+---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
More examples of spatial 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 relational queries
/* Table that stores device reading information.
* Composite primary key defined on two columns. */
CREATE TABLE IF NOT EXISTS "doc"."devices_readings" (
"ts" TIMESTAMP WITH TIME ZONE,
"device_id" TEXT,
"battery" OBJECT(DYNAMIC) AS (
"level" BIGINT,
"status" TEXT,
"temperature" DOUBLE PRECISION
),
"cpu" OBJECT(DYNAMIC) AS (
"avg_1min" DOUBLE PRECISION,
"avg_5min" DOUBLE PRECISION,
"avg_15min" DOUBLE PRECISION
)
PRIMARY KEY (ts, device_id)
)
CLUSTERED INTO 6 SHARDS
/* The query returns 10 records from two joined tables:
* devices_readings and devices_info. */
SELECT r.device_id,
r.ts,
r.battery['level'],
r.battery['status'],
i.manufacturer,
i.os_name
FROM devices_readings r
JOIN devices_info i ON r.device_id = i.device_id
LIMIT 10;
+------------+---------------+------------------+-------------------+--------------+---------+
| device_id | ts | battery['level'] | battery['status'] | manufacturer | os_name |
+------------+---------------+------------------+-------------------+--------------+---------+
| demo000025 | 1479920640000 | 26 | discharging | iobeam | 6.0.1 |
| demo000025 | 1480209300000 | 52 | discharging | iobeam | 6.0.1 |
| demo000025 | 1479663270000 | 53 | charging | iobeam | 6.0.1 |
| demo000062 | 1479461430000 | 14 | discharging | iobeam | 6.0.1 |
| demo000067 | 1480606380000 | 42 | discharging | iobeam | 5.0.0 |
| demo000081 | 1479387000000 | 85 | discharging | iobeam | 6.0.1 |
| demo000081 | 1480166250000 | 50 | discharging | iobeam | 6.0.1 |
| demo000081 | 1479369300000 | 35 | discharging | iobeam | 6.0.1 |
| demo000087 | 1479885600000 | 72 | discharging | iobeam | 5.0.0 |
| demo000087 | 1480322430000 | 65 | discharging | iobeam | 5.0.0 |
+------------+---------------+------------------+-------------------+--------------+---------+
/*
* Based on sensor data, this query calculates:
* - time-buckets of 10 seconds
* - different aggregations per time-bucket and host group
*/
SELECT FLOOR(EXTRACT(epoch FROM m.timestamp) / 10) * 10 AS period,
h.host_group,
MIN(m.fields['usage_user']) AS "min",
AVG(m.fields['usage_user']) AS "avg",
MAX(m.fields['usage_user']) AS "max"
FROM telegraf.metrics m
LEFT JOIN telegraf.hosts h ON h.host_name = m.tags['host']
WHERE tags['cpu'] = 'cpu-total'
AND m.timestamp > NOW() - '150 seconds'::INTERVAL
GROUP BY 1, 2
ORDER BY 1 DESC;
+------------+---------+-------+--------------+
| device_id | battery | cpu | manufacturer |
+------------+---------+-------+--------------+
| demo002053 | 96.6 | 6.17 | iobeam |
| demo002380 | 91.7 | 8.71 | iobeam |
| demo000391 | 96.3 | 21.0 | iobeam |
| demo000472 | 90.4 | 8.23 | iobeam |
| demo000157 | 93.9 | 4.17 | iobeam |
| demo000307 | 89.2 | 6.05 | iobeam |
| demo002227 | 88.9 | 5.24 | iobeam |
| demo000703 | 93.2 | 4.17 | iobeam |
| demo001792 | 89.3 | 4.18 | iobeam |
| demo001780 | 87.9 | 4.95 | iobeam |
+------------+---------+-------+--------------+
/* The query provides a detailed analysis of the battery performance
* (both level and temperature) for devices with specific API versions,
* while focusing only on recent data. */
WITH
max_timestamp AS (
SELECT MAX(ts) AS max_ts
FROM doc.devices_readings
),
device_readings_agg AS (
SELECT
r.device_id,
AVG(r.battery['level']) AS avg_battery_level,
AVG(r.battery['temperature']) AS avg_battery_temperature
FROM devices_readings r, max_timestamp m
WHERE r.ts >= m.max_ts - INTERVAL '1 week'
GROUP BY r.device_id
),
device_model_info AS (
SELECT
device_id,
manufacturer,
model,
api_version
FROM devices_info
WHERE api_version BETWEEN 21 AND 25
)
SELECT
info.manufacturer,
info.model,
info.api_version,
AVG(read.avg_battery_level) AS model_avg_battery_level,
AVG(read.avg_battery_temperature) AS model_avg_battery_temperature,
COUNT(*) AS readings_count
FROM device_readings_agg read
JOIN device_model_info info
ON read.device_id = info.device_id
GROUP BY
info.manufacturer,
info.model,
info.api_version
ORDER BY
model_avg_battery_level DESC;
+--------------+---------+-------------+-------------------------+-------------------------------+----------------+
| manufacturer | model | api_version | model_avg_battery_level | model_avg_battery_temperature | readings_count |
+--------------+---------+-------------+-------------------------+-------------------------------+----------------+
| iobeam | focus | 22 | 51.812515496614985 | 90.95264431126454 | 37 |
| iobeam | pinto | 23 | 51.320575821166265 | 90.94536040662497 | 29 |
| iobeam | mustang | 21 | 51.12593768085889 | 90.84655787872552 | 57 |
| iobeam | mustang | 22 | 50.92397024012975 | 90.8132448654412 | 29 |
| iobeam | focus | 23 | 50.3493936780697 | 90.86938401742282 | 27 |
| iobeam | mustang | 23 | 50.226325680521306 | 90.82969886537008 | 28 |
| iobeam | pinto | 21 | 50.11437819309262 | 90.81538121980917 | 60 |
| iobeam | focus | 21 | 49.60331350481256 | 90.87625045994797 | 59 |
| iobeam | pinto | 22 | 49.54924258513566 | 90.84554594103246 | 29 |
+--------------+---------+-------------+-------------------------+-------------------------------+----------------+
More examples of JSON queries
INSERT INTO quotes (
title, quotation, protagonist
) VALUES (
'Alice in Wonderland',
{
"words": "Curiouser and curiouser!",
"length": 3
},
{
"surname": "Pleasance Liddell",
"first_name": "Alice",
"details": {
"age": 7,
"birthday": '1852-05-04T00:00Z'::TIMESTAMPTZ
}
}
);
SELECT
title AS title,
protagonist['first_name'] AS name,
date_format(
'%D %b %Y',
'GMT',
protagonist['details']['birthday']
) AS born,
quotation['words'] AS quote
FROM quotes limit 100;
INSERT INTO quotes (
title,
quotation,
protagonist
) VALUES
(
'Slaughterhouse-Five',
{
"words": "Everything was beautiful, and nothing hurt."
},
{
"surname": "Pilgrim",
"first_name": "Billy",
"details": {
"birthday": '1922-07-04T00:00Z'
}
}
),
(
'The Complete Tales of Winnie-the-Pooh',
{
"words": "How lucky I am to have something that makes saying goodbye so hard."
},
{
"first_name": "Winnie-the-Pooh",
"details": {
"birthday": '1926-10-14T00:00Z'
}
}
),
(
'The Complete Tales of Winnie-the-Pooh',
{
"words": "I am short, fat, and proud of that."
},
{
"first_name": "Winnie-the-Pooh",
"details": {
"birthday": '1926-10-14T00:00Z'
}
}
);
SELECT title, name, born, quote
FROM "doc"."general_information"
LIMIT 100;