Skip to content
Login
Try for free
Login
Try for free
Product

Query examples

 

Examples of SQL queries

        
        

/* Based on device data, this query returns the average
 * of the battery level for every hour for each device_id
 */
WITH avg_metrics AS (
    SELECT device_id,
       DATE_BIN('1 hour'::INTERVAL, time, 0) AS period,
       AVG(battery_level) AS avg_battery_level
    FROM devices.readings
    GROUP BY 1, 2 
    ORDER BY 1, 2
)
SELECT period,
       t.device_id,
       manufacturer,
       avg_battery_level  
FROM avg_metrics t, devices.info i
WHERE t.device_id = i.device_id 
      AND model = 'mustang'
LIMIT 10;
        
        

+---------------+------------+--------------+-------------------+
|    period     |  device_id | manufacturer | avg_battery_level |
+---------------+------------+--------------+-------------------+
| 1480802400000 | demo000001 |    iobeam    | 49.25757575757576 |
| 1480806000000 | demo000001 |    iobeam    | 47.375            |
| 1480802400000 | demo000007 |    iobeam    | 25.53030303030303 |
| 1480806000000 | demo000007 |    iobeam    | 58.5              |
| 1480802400000 | demo000010 |    iobeam    | 34.90909090909091 |
| 1480806000000 | demo000010 |    iobeam    | 32.4              |
| 1480802400000 | demo000016 |    iobeam    | 36.06060606060606 |
| 1480806000000 | demo000016 |    iobeam    | 35.45             |
| 1480802400000 | demo000025 |    iobeam    | 12                |
| 1480806000000 | demo000025 |    iobeam    | 16.475            |
+---------------+------------+--------------+-------------------+
        
        

/* Based on reports from IoT devices, this query returns
 * the voltage corresponding to the maximum
 * global active power for each meter_id
 */

SELECT meter_id,
       MAX_BY("Voltage", "Global_active_power") AS voltage_max_global_power
FROM iot.power_consumption 
GROUP BY 1 
LIMIT 10;
        
        

+------------+--------------------------+
|   meter_id | voltage_max_global_power |
+------------+--------------------------+
| 840073190N |          233.57          |
| 840072401F |          233.53          |
| 840072655G |          234.1           |
| 840071893D |          234.47          |
| 840073950P |          231.73          |
| 840075260N |          235.51          | 
| 840076398A |          234.56          |
| 84007B071E |          231.94          |
| 840075959Y |          237.21          |
| 840072534A |          231.96          |
+------------+--------------------------+
        
        

/* Based on the location of the International Space Station, 
 * this query returns the 10 closest capital cities from 
 * the last known position 
 */
SELECT city as "City Name",
       country as "Country",
       DISTANCE(i.position, c.location)::LONG / 1000 AS "Distance [km]"
FROM demo.iss i
CROSS JOIN demo.world_cities c
WHERE capital = 'primary'
      AND ts = (SELECT MAX(ts) FROM demo.iss)
ORDER BY 3 ASC
LIMIT 10;
        
        

+--------------+-----------------------------------+---------------+
|  City Name   |             Country               | Distance [km] |
+--------------+-----------------------------------+---------------+
|    Papeete   |         French Polynesia          |      3386     |
|    Avarua    |           Cook Islands            |      3708     |
|  Wellington  |            New Zealand            |      4565     |
|     Alofi    |                Niue               |      4628     |
|  Nuku‘alofa  |               Tonga               |      4887     |
|  Pago Pago   |          American Samoa           |      5063     |
|   Santiago   |               Chile               |      5112     |
|     Apia     |               Samoa               |      5182     |
|    Stanley   | Falkland Islands (Islas Malvinas) |      5266     |
|     Suva     |               Fiji                |      5611     |
+--------------+-----------------------------------+---------------+
        
        

/*
 * Based on system event logs, this query calculates:
 * - a filter for specific messages using a full-text index
 * - the number of entries per minute
 * - the average scoring ratio for each matched row
 */
SELECT DATE_TRUNC('minute', receivedat) AS event_time,
       COUNT(*) AS entries,
       AVG(_score) AS avg_score
FROM "syslog"."systemevents"
WHERE MATCH(message, 'authentication failure') 
USING most_fields WITH (analyzer = 'whitespace')
   AND MATCH(syslogtag, 'sshd')
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
        
        

+---------------+---------+--------------------+
|    event_time | entries |          avg_score |
+---------------+---------+--------------------+
| 1620220260000 |       4 | 1.5798743814229965 |
| 1620220200000 |       8 | 1.7750384211540222 |
| 1620220140000 |      10 | 1.6113891124725341 |
| 1620220080000 |       9 | 1.676726798216502  |
| 1620220020000 |       8 | 1.6908064410090446 |
| 1620219960000 |       8 | 1.690401442348957  |
| 1620219900000 |       7 | 1.7646006005150932 |
| 1620219840000 |       7 | 1.7795820917401994 |
| 1620219780000 |      10 | 1.5844267368316651 |
| 1620219720000 |      13 | 1.5637413492569556 |
+---------------+---------+--------------------+
 

More examples of time-series queries

 

        
        

/* To identify gaps on the readings, the following queries generates a series
 * and by joining it with the original data, you can spot any gap */
with avg_battery AS (
  SELECT battery_level, time 
  FROM devices.readings
  WHERE device_id = 'demo000007' 
  AND time > 1480118400000 
  AND time < 1480301200000
  ORDER BY 2
),

all_hours AS (
  SELECT generate_series(1480118430000,1480301200000,'30 second'::interval) AS generated_hours
)

SELECT time, generated_hours, battery_level
FROM all_hours 
LEFT JOIN avg_battery ON generated_hours = time
ORDER BY 2
LIMIT 20;
        
        

+---------------+---------------+---------------+
|          time |         hours | battery_level |
+---------------+---------------+---------------+
| 1480118430000 | 1480118430000 |            67 |
| 1480118460000 | 1480118460000 |            66 |
| 1480118490000 | 1480118490000 |            66 |
| 1480118520000 | 1480118520000 |            66 |
| 1480118550000 | 1480118550000 |            66 |
| 1480118580000 | 1480118580000 |            66 |
| 1480118610000 | 1480118610000 |            65 |
| 1480118640000 | 1480118640000 |          NULL |
| 1480118670000 | 1480118670000 |            65 |
| 1480118700000 | 1480118700000 |            65 |
| 1480118730000 | 1480118730000 |            65 |
| 1480118760000 | 1480118760000 |            65 |
| 1480118790000 | 1480118790000 |            65 |
| 1480118820000 | 1480118820000 |            65 |
| 1480118850000 | 1480118850000 |            65 |
| 1480118880000 | 1480118880000 |            65 |
| 1480118910000 | 1480118910000 |            65 |
| 1480118940000 | 1480118940000 |            65 |
| 1480118970000 | 1480118970000 |          NULL |
| 1480119000000 | 1480119000000 |          NULL |
+---------------+---------------+---------------+
        
        

/* Based on device data, this query returns the number of battery charges 
 * per day for a given device_id */
WITH aux_charging AS (
    SELECT time, 
      DATE_BIN('P1D'::INTERVAL,time,0) AS day, 
      battery_status, 
      LAG(battery_status) OVER (PARTITION BY device_id ORDER BY time) AS prev_battery_status
    FROM devices.readings
    WHERE device_id = 'demo000001'
    ORDER BY time
  ),

count_start_charging AS (
    SELECT day, (case when battery_status <> prev_battery_status then 1 else 0 end) AS start_charging
    FROM aux_charging
    ORDER BY 1
  )

SELECT day, sum(start_charging) as charges_number
FROM count_start_charging
GROUP BY 1
ORDER BY 1;
        
        

+---------------+---------------+
| count_charges |           day |
+---------------+---------------+
|             2 | 1479168000000 |
|             4 | 1479254400000 |
|             2 | 1479340800000 |
|            10 | 1479427200000 |
|             7 | 1479600000000 |
|             8 | 1479686400000 |
|             6 | 1479772800000 |
|            11 | 1479859200000 |
|             5 | 1480032000000 |
|             7 | 1480118400000 |
|             6 | 1480204800000 |
|            10 | 1480291200000 |
|             3 | 1480464000000 |
|             3 | 1480550400000 |
|             7 | 1480636800000 |
|             2 | 1480723200000 |
+---------------+---------------+
        
        

/* Based on device data, this query returns the average of the battery temperature
 * for each OS version */
SELECT device_info['os_name'], avg(battery_temperature)
FROM "devices"."readings"
GROUP BY 1
LIMIT 100;
        
        
 
+---------+--------------------------+
| os_name | avg(battery_temperature) |
+---------+--------------------------+
| 4.4.4   |        90.85937893039049 |
| 5.1.0   |        90.86754559738132 |
| 6.0.1   |        90.84230101265824 |
| 5.0.0   |        90.8574802739726  |
+---------+--------------------------+

More examples of IoT queries

 

        
        

/* Based on IoT devices reports, this query returns the voltage variation over time
for a given meter_id */ WITH avg_voltage_all AS ( SELECT meter_id, avg("Voltage") AS avg_voltage, date_bin('1 hour'::INTERVAL, ts, 0) AS time FROM iot.power_consumption WHERE meter_id = '840072572S' GROUP BY 1, 3 ORDER BY 3 ) SELECT time, (avg_voltage - lag(avg_voltage) over (PARTITION BY meter_id ORDER BY time)) AS var_voltage FROM avg_voltage_all LIMIT 10;
        
        

+---------------+-----------------------+
|          time |           var_voltage |
+---------------+-----------------------+
| 1166338800000 | NULL                  |
| 1166479200000 |   -2.30999755859375   |
| 1166529600000 |    4.17999267578125   |
| 1166576400000 |   -0.3699951171875    |
| 1166734800000 |   -3.7100067138671875 |
| 1166785200000 |   -1.5399932861328125 |
| 1166893200000 |   -3.839996337890625  |
| 1166997600000 |    9.25               |
| 1167044400000 |    0.4499969482421875 |
| 1167174000000 |    3.220001220703125  |
+---------------+-----------------------+
        
        

/* Based on IoT devices reports, this query returns the voltage corresponding to
the maximum global active power for each meter_id */ SELECT meter_id, max_by("Voltage", "Global_active_power") AS voltage_max_global_power FROM iot.power_consumption GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
        
        

+------------+--------------------------+
| meter_id   | voltage_max_global_power |
+------------+--------------------------+
| 840070437W |                   246.77 |
| 840073628P |                   246.69 |
| 840074265G |                   246.54 |
| 840070238E |                   246.35 |
| 840070335K |                   246.34 |
| 840075190M |                   245.15 |
| 840072876X |                   244.81 |
| 840070636M |                   242.98 |
| 84007B113A |                   242.93 |
| 840073250D |                   242.28 |
+------------+--------------------------+
 

More examples of geospatial queries

        
        

/* Based on NYC taxi trips, this query lists the latest 5 trips starting 
   within 500m of the Times Square */
SELECT pickup_datetime AS "time",
       trip_distance,
       latitude(pickup_location) latitude,
       longitude(pickup_location) longitude
FROM nyc_taxi.trips
WHERE distance('POINT(-73.985130 40.758896)'::GEO_POINT, pickup_location) < 500
      AND pickup_datetime BETWEEN 1451602800000 AND 1483138800000
ORDER BY time DESC
LIMIT 5;
        
        

+---------------+---------------+--------------------+--------------------+
|          time | trip_distance |           latitude |          longitude |
+---------------+---------------+--------------------+--------------------+
| 1467331195000 |          3.0  | 40.76302719116211  | -73.98388671875    |
| 1467331192000 |          1.8  | 40.761539459228516 | -73.98821258544922 |
| 1467331192000 |          4.59 | 40.75526809692383  | -73.98706817626953 |
| 1467331191000 |          3.6  | 40.75738525390625  | -73.99018096923828 |
| 1467331183000 |          1.7  | 40.75679016113281  | -73.98326110839844 |
+---------------+---------------+--------------------+--------------------+
        
        

/*
 * Based on NYC taxi trips, this query calculates:
 * - the distance of a given dropoff location to a fixed point of interest,
 *   partitioned in buckets of 50 meters each
 * - the average trip distance in kilometers within that partition
 * - the number of trips within that partition
 */
SELECT floor(
         distance(t.dropoff_location, 'POINT(-73.984 40.758)') / 50
       ) * 50 AS point_distance,
       avg(
         distance(t.pickup_location, t.dropoff_location) / 1000.0
       ) AS trip_distance,
       count(*) AS trips
FROM nyc_taxi.trips t
WHERE t.pickup_month = 1451606400000
GROUP BY 1
ORDER BY 1 ASC;
        
        

+----------------+---------------------------+--------+
| point_distance |             trip_distance |  trips |
+----------------+---------------------------+--------+
|              0 |    4.183248580260856      |   2258 |
|             50 |    3.5389173023149585     |  10891 |
|            100 |    2.973617177658283      |  23798 |
|            150 |    2.8757472450827946     |  41841 |
|            200 |    2.936262490313057      |  44880 |
|            250 |    2.901378357605571      |  44918 |
|            300 |    2.7757565505864257     |  49662 |
|            350 |    2.6239797352885708     |  57803 |
|            400 |    2.822579512102226      |  73603 |
|            450 |    2.5346381484458105     |  79051 |
|            500 |    2.4982467856661392     |  88705 |
|            550 |    2.613586563537282      |  93082 |
|            600 |    2.6103083633519786     |  98527 |
|            650 |    2.5381838408225286     | 113023 |
|            700 |    2.537684333523469      | 125640 |
|            750 |    2.5883437771402007     | 127099 |
|            800 |    2.617847584473263      | 119027 |
|            850 |    2.554740996422641      | 132532 |
|            900 |    2.499970010565691      | 164115 |
|            950 |    2.612569696294771      | 181091 |
|           1000 |    2.5512554347843093     | 152102 |
+----------------+---------------------------+--------+
        
        

/* Based on NYC taxi trips, this query returns the number 
   of trips per day with the region of the New York 
   Central Park as destination */
SELECT date_bin('24 hour'::interval, pickup_datetime, 0) AS day, 
       count(*) AS trips
FROM nyc_taxi.trips
WHERE within(
        dropoff_location, 
        'POLYGON(
          (-73.97308900174315 40.764422448981996,
           -73.98192956265623 40.76812781417226,
           -73.9584064734938  40.80087951931638,
           -73.94982340464614 40.797240957024385,
           -73.97308900174315 40.764422448981996)
         )'
      ) 
    AND pickup_datetime BETWEEN 1417392000000 AND 1420070400000 
GROUP BY 1
ORDER BY 1;
        
        

+---------------+-------+
|           day | trips |
+---------------+-------+
| 1417392000000 |  5094 |
| 1417478400000 |  5580 |
| 1417564800000 |  5760 |
| 1417651200000 |  5970 |
| 1417737600000 |  5850 |
| 1417824000000 |  5472 |
| 1417910400000 |  5335 |
| 1417996800000 |  5573 |
| 1418083200000 |  5382 |
| 1418169600000 |  5484 |
| 1418256000000 |  6291 |
| 1418342400000 |  5953 |
| 1418428800000 |  5846 |
| 1418515200000 |  5177 |
| 1418601600000 |  5446 |
| 1418688000000 |  5639 |
| 1418774400000 |  6051 |
| 1418860800000 |  6161 |
| 1418947200000 |  6052 |
| 1419033600000 |  5695 |
| 1419120000000 |  4891 |
| 1419206400000 |  5050 |
| 1419292800000 |  4786 |
| 1419379200000 |  4637 |
| 1419465600000 |  2721 |
| 1419552000000 |  3789 |
| 1419638400000 |  4343 |
| 1419724800000 |  4048 |
| 1419811200000 |  4769 |
| 1419897600000 |  5126 |
| 1419984000000 |  5128 |
+---------------+-------+

More examples of log analysis queries

        
        

/*
 * Based on system event logs, this query calculates:
 * - a filter for specific messages using a full-text index
 * - the number of entries per minute
 * - the average scoring ratio for each matched row
 */
SELECT DATE_TRUNC('minute', receivedat) AS event_time,
       COUNT(*) AS entries,
       AVG(_score) AS avg_score
FROM "syslog"."systemevents"
WHERE MATCH(message, 'authentication failure') 
USING most_fields WITH (analyzer = 'whitespace')
   AND MATCH(syslogtag, 'sshd')
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
        
        

+---------------+---------+--------------------+
|    event_time | entries |          avg_score |
+---------------+---------+--------------------+
| 1620220260000 |       4 | 1.5798743814229965 |
| 1620220200000 |       8 | 1.7750384211540222 |
| 1620220140000 |      10 | 1.6113891124725341 |
| 1620220080000 |       9 | 1.676726798216502  |
| 1620220020000 |       8 | 1.6908064410090446 |
| 1620219960000 |       8 | 1.690401442348957  |
| 1620219900000 |       7 | 1.7646006005150932 |
| 1620219840000 |       7 | 1.7795820917401994 |
| 1620219780000 |      10 | 1.5844267368316651 |
| 1620219720000 |      13 | 1.5637413492569556 |
+---------------+---------+--------------------+

Interested in learning more?