Features
SQL Syntax
Write SQL queries across relational, time-series, JSON, full-text, geospatial, and vector data using a single, consistent syntax.
Time series query examples
Sub-second results across billions of records.
/* Most time-series databases require you to pre-aggregate
* hourly data at ingestion time. CrateDB computes it
* at query time across any dimension — no pre-processing pipeline needed. */
/* 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 |
+---------------+------------+--------------+-------------------+
/* Detecting missing readings in a continuous stream requires
* generating a reference series and left-joining against it.
* CrateDB handles this in standard SQL with generate_series. */
/* 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 |
+---------------+---------------+---------------+
/* Counting state transitions — like battery charge cycles —
* across a time window is a common operational analytics query.
* This runs in milliseconds on billions of rows. */
/* 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 |
+---------------+---------------+
/* Joining device readings with device metadata at query time, without pre-flattening.
* This is the high-cardinality pattern CrateDB is built for. */
/* 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 query examples
Note the bracket notation for nested field access.
/* Insert a JSON document with nested objects and arrays directly
into CrateDB using standard SQL. No schema definition required
for the dynamic fields — CrateDB adapts automatically. */
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
)
/* Access nested keys using bracket notation.
Slice arrays, filter on dynamic attributes, and project any field
including deeply nested ones without unpacking the document first.
The query runs in milliseconds even across millions of records. */
/* 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 |
+---------+-----------+------------+-----------------+
/* Two documents with different structures inserted in the same statement.
CrateDB's dynamic schema handles both without a migration,
a downtime window, or a schema redesign. */
/* 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 search examples
knn_match in standard SQL. No proprietary SDK, no separate query interface, no client-side result merging.
/* Find the nearest vectors to a given query embedding using knn_match.
Results include a relevance score. This runs on the same distributed SQL engine
as your analytical queries — no separate search index, no separate request. */
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|
|------------------------|--------|
/* Use a stored embedding as the query target rather than an inline vector.
The subquery retrieves the reference embedding and the outer query finds similar ones.
This pattern is common in RAG pipelines where context is retrieved
by similarity to a stored document embedding. */
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 search examples
All use the standard SQL MATCH function.
No Elasticsearch DSL, no separate query language, no additional client library.
/* Create a full-text index across multiple columns with a language-specific analyzer
at table creation time. CrateDB handles tokenization automatically.
The index is available for querying from the moment the first document is inserted. */
/* 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')
);
/* Weight specific fields more heavily in relevance scoring using the caret notation.
Title matches score higher than description matches.
This is standard BM25 relevance ranking, the same algorithm Elasticsearch uses. */
/* The query retuns data from the "netflix_catalog" table, specifically searching
* for movies where the 'title', 'director', or 'description' fields best match the
* term 'Friday', with a higher emphasis on matches in the title, and orders the results
* by their relevance score in descending order. */
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 |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
/* Apply per-column boost factors to tune relevance across multiple fields in a single query.
Useful when different fields have different importance for a given search context. */
/* 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 |
+---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
/* Search for specific phrases and filter results by minimum relevance score.
Only documents scoring above the threshold are returned,
giving precise control over result quality. */
/* 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 |
+---------+----------+-----------------------------------------+-----------------+--------+------------+-----------+
/* Handle spelling variations and typos using configurable fuzziness parameters.
CrateDB returns relevant results even when search terms contain errors,
using the same fuzzy matching algorithm as Elasticsearch. */
/* 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 |
+---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
Geospatial query examples
Native SQL with no proprietary GIS language and no external spatial processing library.
/* Find records within a given radius of a coordinate.
The query joins trip data with community area metadata at query time
No pre-joining, no pre-flattening.
Returns the five longest trips originating within 1km of the Shedd Aquarium. */
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 |
+------------------------------------------+-------+--------------+
/* Test whether points fall inside a polygon boundary defined in WKT format.
This query identifies trips starting inside O'Hare airport's boundary
that exceeded 20 miles, grouped by day.
Common pattern for geofencing and zone-based analytics. */
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 |
+---------------+-------+
/* Test whether a line or shape overlaps a polygon.
This query finds community areas with populations over 30,000 that a flight path
between O'Hare and Midway passes over, demonstrating that CrateDB can combine
spatial intersection with structured filters in a single statement. */
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 |
+------------------------+------------+
Standard query examples
Standard SQL. Queries executing in parallel across a distributed cluster, not on a single node.
/* Define a relational table with a composite primary key,
OBJECT columns for nested data, and explicit sharding configuration.
CrateDB's DDL is standard SQL with distributed-system extensions
for shard count and replication. */
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
/* A standard JOIN between a readings table and a device info table.
CrateDB distributes this join across nodes automatically
using its distributed query engine.
No query hints, no manual optimization, no denormalization required. */
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 |
+------------+---------------+------------------+-------------------+--------------+---------+
/* Window functions for rolling average, minimum, and maximum battery temperature
over the last 100 readings per device.
The same syntax as PostgreSQL, executing in parallel across the cluster. */
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 |
+------------+-----------------------+-----------------+-----------------+
/* A time-bounded aggregation across joined tables with a recent data filter.
This is the pattern behind operational dashboards — fresh data,
filtered by time window, enriched with device metadata at query time. */
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 |
+------------+---------+-------+--------------+
/* A three-stage CTE aggregating average battery level
and temperature across device models and API versions over the last week.
This query would timeout or require pre-aggregation in most time-series databases.
In CrateDB it runs on fresh data without pre-processing. */
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 |
+--------------+---------+-------------+-------------------------+-------------------------------+----------------+