The 2024 CrateDB architecture guide covering all key concepts is out.

Download now
Skip to content
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;
        
        
        

Interested in learning more?