Product > Database
SQL Examples
Time-series data
/* 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 |
+---------+--------------------------+
JSON data
INSERT INTO community_areas(areanumber, name, details) VALUES (
35,
'DOUGLAS',
'{
"description": "The Douglas community area stretches...",
"population": 20291,
"zipcodes": [ "60609", "60616", "60653" ],
"notableresident": {
"name": "Ida B. Wells",
"bio": "An American investigative journalist, educator and..."
}
}'::OBJECT
)
/* Retrieve fields from objects and slice arrays. */
SELECT name,
array_slice(details['zipcodes'], 1, 1) as first_zip,
details['population'] AS population,
details['notableresident']['name'] AS famous_resident
FROM community_areas
WHERE areanumber = 35;
+---------+-----------+------------+-----------------+
| name | first_zip | population | famous_resident |
+---------+-----------+------------+-----------------+
| DOUGLAS | ["60609"] | 20291 | Ida B. Wells |
+---------+-----------+------------+-----------------+
/* Insert documents for two Chicago community areas. Dynamic
object schemas allow these two documents to have different
structures. */
INSERT INTO community_areas(areanumber, name, details) VALUES
(
35,
'DOUGLAS',
'{
"description": "The Douglas community area stretches...",
"population": 20291,
"zipcodes": [ "60609", "60616", "60653" ],
"notableresident": {
"name": "Ida B. Wells",
"bio": "An American investigative journalist, educator and..."
}
}'::OBJECT
),
(
41,
'HYDE PARK',
'{
"description": "This community area is home to the Museum of Science...",
"population": 29456,
"notableresident": {
"name": "Muhammad Ali",
"born": 1942
}
}'::OBJECT
);
Vector data
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|
|------------------------|--------|
CrateDB Academy – Free Courses
Enroll for free, Learn, and Get Certified
Full-text data
/* 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 |
+---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
Spatial data
/* Based on Chicago taxi trips, this query lists the longest 5 trips
starting within 1km of the Shedd Aquarium and tells us the name
of the community area that the trip ended within. */
SELECT tripid,
miles,
c.name as dropoff_area
FROM taxi_rides t, community_areas c
WHERE distance('POINT(-87.6118581 41.8672843)'::GEO_POINT, pickupcentroidlocation) < 1000
AND dropoffcommunityarea = c.areanumber
ORDER BY miles DESC
LIMIT 5;
+------------------------------------------+-------+--------------+
| tripid | miles | dropoff_area |
+------------------------------------------+-------+--------------+
| e6d27fb09f7b17695a71666654c40fd56b79f472 | 46.5 | LOOP |
| eb8dbd2d81faa21f8f6d80117748a8630c55d55c | 37.27 | OHARE |
| 6aaa0d2aed0423f1321ff5df949afef0e3583d58 | 37.16 | OHARE |
| e9ab078e5e6ed4d29cfbd310ee75d47236d3afbb | 32.03 | OHARE |
| 329b4e6980070cab4c2af19ddbc7b73c1497a669 | 31.67 | OHARE |
+------------------------------------------+-------+--------------+
/* Based on Chicago taxi trips, this query retuern the number
of trips per day that started at O'Hare airport and were
over 20 miles long. */
SELECT date_bin('24 hour'::interval, starttime, 0) AS day,
count(*) as trips
FROM taxi_rides
WHERE within(
pickupcentroidlocation,
'POLYGON (
(-87.92151405039768 42.008992106324456,
-87.93962405297475 42.00147632076016,
-87.94067626623455 41.95592273798644,
-87.89918645000783 41.9491763875842,
-87.88992003878232 41.95716729007498,
-87.8821237584836 41.95716462062882,
-87.87074008603852 41.967968085787476,
-87.87727540517479 41.99772302560805,
-87.89707897269737 42.009775581434354,
-87.92151405039768 42.008992106324456)
)'
)
AND miles > 20
GROUP BY day
ORDER BY day DESC;
+---------------+-------+
| day | trips |
+---------------+-------+
| 1714521600000 | 24 |
| 1714435200000 | 110 |
| 1714348800000 | 109 |
| 1714262400000 | 77 |
| 1714176000000 | 52 |
| 1714089600000 | 98 |
| 1714003200000 | 113 |
| 1713916800000 | 82 |
| 1713830400000 | 59 |
| 1713744000000 | 91 |
| 1713657600000 | 69 |
| 1713571200000 | 48 |
| 1713484800000 | 82 |
| 1713398400000 | 75 |
| 1713312000000 | 80 |
| 1713225600000 | 98 |
| 1713139200000 | 104 |
| 1713052800000 | 63 |
| 1712966400000 | 75 |
| 1712880000000 | 142 |
| 1712793600000 | 169 |
| 1712707200000 | 103 |
| 1712620800000 | 154 |
| 1712534400000 | 203 |
| 1712448000000 | 98 |
| 1712361600000 | 71 |
| 1712275200000 | 80 |
| 1712188800000 | 79 |
| 1712102400000 | 72 |
| 1712016000000 | 91 |
| 1711929600000 | 39 |
+---------------+-------+
/* Which communities of more than 30,000 people does a flight from O'Hare to
Midway airport pass over? */
SELECT name,
details['population'] AS population
FROM community_areas
WHERE
intersects (
'LINESTRING (-87.90753846894022 41.9807787186139, -87.72939445102593 41.97508268795004, -87.55763552335945 41.97982941555023, -87.47846040428642 41.92427055787562, -87.51102436455034 41.837749425166244, -87.59225264192574 41.80057450627759, -87.64801197528328 41.770600684095974, -87.69988112259261 41.7819633786835, -87.7530469985847 41.78583107072223)',
boundaries
)
AND details['population'] > 30000
ORDER BY population DESC;
+------------------------+------------+
| name | population |
+------------------------+------------+
| UPTOWN | 57182 |
| EDGEWATER | 56296 |
| CHICAGO LAWN | 55931 |
| ALBANY PARK | 48396 |
| LINCOLN SQUARE | 40494 |
| NORWOOD PARK | 38303 |
| GARFIELD RIDGE | 35439 |
| WEST LAWN | 33662 |
| GREATER GRAND CROSSING | 31471 |
+------------------------+------------+
Table data
/* 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 |
+--------------+---------+-------------+-------------------------+-------------------------------+----------------+