Webinar: Building a Real-Time Platform for Millions of Smart Meter Events

Register Now
Skip to content
Start free
Start free
Explore

Try CrateDB Live: More Queries

Scenario: Industrial IoT
  • 1. Choose Scenario
  • 2. Get Ready
  • 3. Run CrateDB
  • 4. Import Data
  • 5. Explore Queries
  • 6. More Queries
  • 7. Connect
  • 8. Next Steps

B. Analytical Patterns

B1. CTE: Detect Devices With Rising Metric Values 

Compare each device's average reading across two consecutive 6-hour windows. Flags devices where the recent window is >10% higher than the prior window, which is the early-drift signature used in predictive maintenance workflows.

 
SELECT 
  device_id, 
  device_type, 
  plant_id, 
  ROUND(avg_prior, 2) AS avg_prior_6h, 
  ROUND(avg_recent, 2) AS avg_recent_6h, 
  ROUND(avg_recent - avg_prior, 2) AS absolute_delta, 
  ROUND(
    (avg_recent - avg_prior) / NULLIF(avg_prior, 0) * 100, 
    1
  ) AS pct_change 
FROM 
  (
    SELECT 
      tags['device_id'] AS device_id, 
      tags['device_type'] AS device_type, 
      tags['plant_id'] AS plant_id, 
      AVG(
        CASE WHEN "timestamp" >= TIMESTAMP '2025-09-07 06:00:00' 
        AND "timestamp" < TIMESTAMP '2025-09-07 12:00:00' THEN fields['metric_value'] END
      ) AS avg_recent, 
      AVG(
        CASE WHEN "timestamp" >= TIMESTAMP '2025-09-07 00:00:00' 
        AND "timestamp" < TIMESTAMP '2025-09-07 06:00:00' THEN fields['metric_value'] END
      ) AS avg_prior 
    FROM 
      rtia.iot_data 
    WHERE 
      "timestamp" >= TIMESTAMP '2025-09-07 00:00:00' 
      AND "timestamp" < TIMESTAMP '2025-09-07 12:00:00' 
    GROUP BY 
      tags['device_id'], 
      tags['device_type'], 
      tags['plant_id']
  ) windows 
WHERE 
  avg_prior IS NOT NULL 
  AND (avg_recent - avg_prior) / NULLIF(avg_prior, 0) > 0.10 
ORDER BY 
  pct_change DESC 
LIMIT 
  20;


 


 


 

B2. Sustained Fault Detection With Having

Find devices that breached warning threshold at least 10 times in a single day, distinguishing sustained degradation from transient noise spikes.

 
SELECT 
  device_id, 
  device_type, 
  plant_id, 
  ROUND(avg_prior, 2) AS avg_prior_6h, 
  ROUND(avg_recent, 2) AS avg_recent_6h, 
  ROUND(avg_recent - avg_prior, 2) AS absolute_delta, 
  ROUND(
    (avg_recent - avg_prior) / NULLIF(avg_prior, 0) * 100, 
    1
  ) AS pct_change 
FROM 
  (
    SELECT 
      tags['device_id'] AS device_id, 
      tags['device_type'] AS device_type, 
      tags['plant_id'] AS plant_id, 
      AVG(
        CASE WHEN "timestamp" >= TIMESTAMP '2025-09-07 06:00:00' 
        AND "timestamp" < TIMESTAMP '2025-09-07 12:00:00' THEN fields['metric_value'] END
      ) AS avg_recent, 
      AVG(
        CASE WHEN "timestamp" >= TIMESTAMP '2025-09-07 00:00:00' 
        AND "timestamp" < TIMESTAMP '2025-09-07 06:00:00' THEN fields['metric_value'] END
      ) AS avg_prior 
    FROM 
      rtia.iot_data 
    WHERE 
      "timestamp" >= TIMESTAMP '2025-09-07 00:00:00' 
      AND "timestamp" < TIMESTAMP '2025-09-07 12:00:00' 
    GROUP BY 
      tags['device_id'], 
      tags['device_type'], 
      tags['plant_id']
  ) windows 
WHERE 
  avg_prior IS NOT NULL 
  AND (avg_recent - avg_prior) / NULLIF(avg_prior, 0) > 0.10 
ORDER BY 
  pct_change DESC 
LIMIT 
  20;


 


 


 

B3. NULLIF: Quality Deviation As Percentage of Baseline

Compute how far each device's current quality score deviates from its own all-time average. NULLIF guards against division by zero on devices with no historical baseline.

 
SELECT 
  tags['device_id'] AS device_id, 
  tags['device_type'] AS device_type, 
  tags['plant_id'] AS plant_id, 
  ROUND(
    AVG(fields['quality_score']) FILTER (
      WHERE 
        "timestamp" < TIMESTAMP '2025-09-07 00:00:00'
    ), 
    1
  ) AS baseline_quality, 
  ROUND(
    AVG(fields['quality_score']) FILTER (
      WHERE 
        "timestamp" >= TIMESTAMP '2025-09-07 00:00:00'
    ), 
    1
  ) AS recent_quality, 
  ROUND(
    (
      AVG(fields['quality_score']) FILTER (
        WHERE 
          "timestamp" >= TIMESTAMP '2025-09-07 00:00:00'
      ) - AVG(fields[ 'quality_score' ]) FILTER (
        WHERE 
          "timestamp" < TIMESTAMP '2025-09-07 00:00:00'
      )
    ) / NULLIF(
      AVG(fields['quality_score']) FILTER (
        WHERE 
          "timestamp" < TIMESTAMP '2025-09-07 00:00:00'
      ), 
      0
    ) * 100, 
    1
  ) AS quality_change_pct 
FROM 
  rtia.iot_data 
GROUP BY 
  tags['device_id'], 
  tags['device_type'], 
  tags['plant_id'] 
HAVING 
  AVG(fields['quality_score']) FILTER (
    WHERE 
      "timestamp" < TIMESTAMP '2025-09-07 00:00:00'
  ) IS NOT NULL 
  AND AVG(fields['quality_score']) FILTER (
    WHERE 
      "timestamp" >= TIMESTAMP '2025-09-07 00:00:00'
  ) IS NOT NULL 
ORDER BY 
  quality_change_pct ASC 
LIMIT 
  20;


 


 


 

C. Full text search Queries – access Apache Lucene using SQL

Uses the FULLTEXT index on maintenance_log.notes (standard analyzer). MATCH() scores results by relevance; combine with filters to narrow the set.

C1. Full-text: Find Maintenance Records By Fault Keyword

Locate all work orders that mention calibration in the technician notes

 
SELECT 
  work_order_id, 
  device_id, 
  maintenance_type, 
  technician, 
  completed_date, 
  notes 
FROM 
  rtia.maintenance_log 
WHERE 
  MATCH(notes, 'calibration') 
ORDER BY 
  completed_date DESC;


 


 


 

C2. FULL-TEXT: Multi-term Search Across Corrective Jobs

Find corrective jobs where notes mention a sensor replacement

 
SELECT 
  work_order_id, 
  device_id, 
  notes, 
  cost_eur, 
  completed_date 
FROM 
  rtia.maintenance_log 
WHERE 
  MATCH(notes, 'sensor replaced') 
  AND maintenance_type = 'corrective' 
ORDER BY 
  cost_eur DESC;


 


 


 


 

C3. FULL-TEXT + JOIN: Fault Keyword Cost By Plant

Which plants have the highest spend on emergency shutdowns?

 
SELECT 
  p.plant_name, 
  p.industry_segment, 
  COUNT(*) AS matching_jobs, 
  ROUND(
    SUM(m.cost_eur), 
    0
  ) AS total_cost_eur 
FROM 
  rtia.maintenance_log m 
  JOIN rtia.plants p ON m.plant_id = p.plant_id 
WHERE 
  MATCH(m.notes, 'emergency shutdown') 
GROUP BY 
  p.plant_name, 
  p.industry_segment 
ORDER BY 
  total_cost_eur DESC;


 


 


 


 

C4. FULL-TEXT + JOIN: Devices With Recurring Signal Faults 

Identify assets with repeated signal or cable issues across all work orders

 
SELECT 
  m.device_id, 
  d.manufacturer, 
  d.device_type, 
  COUNT(*) AS matching_jobs, 
  ROUND(
    SUM(m.cost_eur), 
    0
  ) AS total_repair_cost 
FROM 
  rtia.maintenance_log m 
  JOIN rtia.devices d ON m.device_id = d.device_id 
WHERE 
  MATCH(
    m.notes, 'signal cable restored'
  ) 
  AND m.status = 'completed' 
GROUP BY 
  m.device_id, 
  d.manufacturer, 
  d.device_type 
ORDER BY 
  matching_jobs DESC 
LIMIT 
  10;


 


 


 

D. Geospatial

geo_location is a GEO_POINT GENERATED from fields['geo_lon'] / fields['geo_lat'] and stored as [longitude, latitude]. DISTANCE() returns meters. WITHIN() tests point-in-polygon.

D1. GEO: Distance From A Reference Coordinate

How far is each plant from Stuttgart HQ?

 
SELECT 
  plant_name, 
  city, 
  federal_state, 
  ROUND(
    DISTANCE(
      geo_location, 
      (
        SELECT 
          geo_location 
        FROM 
          rtia.locations 
        WHERE 
          location_name = 'Stuttgart'
      )
    ) / 1000, 
    0
  ) AS km_from_stuttgart 
FROM 
  rtia.plants 
ORDER BY 
  km_from_stuttgart;


 


 


 


 

D2. Geo: Critical Readings Within A Radius

All critical sensor events within 100 km of Frankfurt

 
SELECT 
  tags['device_id'] AS device_id, 
  tags['device_type'] AS device_type, 
  tags['plant_id'] AS plant_id, 
  fields['metric_value'] AS metric_value, 
  tags['metric_unit'] AS metric_unit, 
  "timestamp", 
  ROUND(
    DISTANCE(
      geo_location, 
      (
        SELECT 
          geo_location 
        FROM 
          rtia.locations 
        WHERE 
          location_name = 'Frankfurt'
      )
    ) / 1000, 
    1
  ) AS km_from_frankfurt 
FROM 
  rtia.iot_data 
WHERE 
  tags['status'] = 'critical' 
  AND DISTANCE(
    geo_location, 
    (
      SELECT 
        geo_location 
      FROM 
        rtia.locations 
      WHERE 
        location_name = 'Frankfurt'
    )
  ) < 150000 
ORDER BY 
  km_from_frankfurt 
LIMIT 
  20;


 


 


 


 

D3. Geo: Fault Density Within A Bounding Polygon

Critical alerts inside the Bavaria bounding box

 
SELECT 
  tags['device_type'] AS device_type, 
  COUNT(*) AS critical_count, 
  ROUND(
    AVG(fields[ 'quality_score' ]), 
    1
  ) AS avg_quality 
FROM 
  rtia.iot_data 
WHERE 
  tags['status'] = 'critical' 
  AND WITHIN(
    geo_location, 
    (
      SELECT 
        geo_area 
      FROM 
        rtia.locations 
      WHERE 
        location_name = 'Bavaria'
    )
  ) 
GROUP BY 
  tags['device_type'] 
ORDER BY 
  critical_count DESC;


 


 


 


 

D4. Geo + JOIN: Critical Alerts With Plant Distance From HQ

Combine sensor alerts with plant coordinates and distance from a central point

 
SELECT 
  tags['device_id'] AS device_id, 
  tags['device_type'] AS device_type, 
  tags['plant_id'] AS plant_id, 
  tags['status'] AS status, 
  fields['metric_value'] AS metric_value, 
  tags['metric_unit'] AS metric_unit, 
  geo_location, 
  ROUND(
    DISTANCE(
      geo_location, 
      (
        SELECT 
          geo_location 
        FROM 
          rtia.locations 
        WHERE 
          location_name = 'Munich'
      )
    ) / 1000, 
    2
  ) AS km_from_munich 
FROM 
  rtia.iot_data 
WHERE 
  tags['status'] IN ('warning', 'critical') 
ORDER BY 
  DISTANCE(
    geo_location, 
    (
      SELECT 
        geo_location 
      FROM 
        rtia.locations 
      WHERE 
        location_name = 'Munich'
    )
  ) 
LIMIT 
  10;


 


 


 


 

D5. Geo: Nearest Faulting Device To A Coordinate

Find the closest device currently in warning or critical state to Munich

 
SELECT
    tags['device_id'] as device_id,
    tags['device_type'] as device_type,
    tags['plant_id'] as plant_id,
    tags['status'] as status,
    fields['metric_value'] as metric_value,
    tags['metric_unit'] as metric_unit,
    geo_location,
    ROUND(DISTANCE(geo_location, [11.576, 48.137]) / 1000, 2) AS km_from_munich
FROM rtia.iot_data
WHERE tags['status'] IN ('warning', 'critical')
ORDER BY DISTANCE(geo_location, [11.576, 48.137])
LIMIT 10;



 


 


 


 

E. Vector Search

notes_embedding is a FLOAT_VECTOR(384) generated by sentence-transformers/all-MiniLM-L6-v2 from the maintenance_log.notes field.

To make life easier for ourselves we have a table called knn_searches that contains embeddings for the following phrases:

  • emergency shutdown thermal runaway critical breach
  • calibration drift sensor out of range recalibration
  • worn parts replaced mechanical failure vibration
  • signal cable damaged transmission restored
  • routine inspection preventive maintenance nominal

The query below uses the first phase, which we store as 'thermal event'. There is nothing stopping you adding your own phrases, but that's outside the scope of this scenario.

E1. VECTOR: Semantic Search — Emergency Shutdowns

Query vector encodes: "emergency shutdown thermal runaway critical breach". Returns the 5 work orders whose notes are semantically closest to a thermal emergency event, even if they use different words (e.g."critical fault", "unplanned stoppage", "thermal event contained").

 
SELECT 
  work_order_id, 
  device_id, 
  maintenance_type, 
  technician, 
  completed_date, 
  notes, 
  _score AS similarity 
FROM 
  rtia.maintenance_log 
WHERE 
  KNN_MATCH(
    notes_embedding, 
    (
      SELECT 
        embedding 
      FROM 
        rtia.knn_searches 
      WHERE 
        query_name = 'thermal_event'
    ), 
    5
  ) 
ORDER BY 
  _score DESC;


 


 


 


 

E2. VECTOR + FILTER: Emergency Shutdowns — Emergency Jobs Only

Same thermal-event vector, filtered to maintenance_type = 'emergency'. Demonstrates hybrid: KNN_MATCH narrows by semantic similarity, the SQL  predicate then restricts to only emergency work orders; one pass, no subquery. Query vector encodes: "emergency shutdown thermal runaway critical breach"

 
SELECT 
  work_order_id, 
  device_id, 
  notes, 
  cost_eur, 
  completed_date, 
  _score AS similarity 
FROM 
  rtia.maintenance_log 
WHERE 
  KNN_MATCH(
    notes_embedding, 
    (
      SELECT 
        embedding 
      FROM 
        rtia.knn_searches 
      WHERE 
        query_name = 'thermal_event'
    ), 
    10
  ) 
  AND maintenance_type = 'emergency' 
ORDER BY 
  _score DESC;


 


 


 


 

E3. VECTOR + JOIN: Calibration Faults By Plant

Query vector encodes: "calibration drift sensor out of range recalibration". Finds the top 50 semantically similar work orders and aggregates by plant — surfaces which facilities have the most calibration-related maintenance spend.

 
SELECT 
  p.plant_name, 
  p.industry_segment, 
  COUNT(*) AS similar_jobs, 
  ROUND(
    SUM(m.cost_eur), 
    0
  ) AS total_cost_eur, 
  ROUND(
    AVG(m.cost_eur), 
    0
  ) AS avg_cost_per_job 
FROM 
  rtia.maintenance_log m 
  JOIN rtia.plants p ON m.plant_id = p.plant_id 
WHERE 
  KNN_MATCH(
    m.notes_embedding, 
    (
      SELECT 
        embedding 
      FROM 
        rtia.knn_searches 
      WHERE 
        query_name = 'calibration_drift'
    ), 
    50
  ) 
GROUP BY 
  p.plant_name, 
  p.industry_segment 
ORDER BY 
  similar_jobs DESC;


 


 


 


 

E4. VECTOR + JOIN: Devices With Recurring Signal / Cable Faults

Query vector encodes: "signal cable damaged transmission restored". Finds assets that appear more than once in the top-100 KNN results — i.e. devices with a repeated signal/cable fault pattern across work orders. These are candidates for a hardware fix rather than another repair cycle.

 
SELECT 
  m.device_id, 
  d.manufacturer, 
  d.device_type, 
  d.asset_value_eur, 
  COUNT(*) AS similar_jobs, 
  ROUND(
    SUM(m.cost_eur), 
    0
  ) AS total_repair_cost, 
  MIN(m.completed_date) AS first_occurrence, 
  MAX(m.completed_date) AS last_occurrence 
FROM 
  rtia.maintenance_log m 
  JOIN rtia.devices d ON m.device_id = d.device_id 
WHERE 
  KNN_MATCH(
    m.notes_embedding, 
    (
      SELECT 
        embedding 
      FROM 
        rtia.knn_searches 
      WHERE 
        query_name = 'signal_cable'
    ), 
    100
  ) 
  AND m.status = 'completed' 
GROUP BY 
  m.device_id, 
  d.manufacturer, 
  d.device_type, 
  d.asset_value_eur 
HAVING 
  COUNT(*) > 1 
ORDER BY 
  similar_jobs DESC, 
  total_repair_cost DESC 
LIMIT 
  15;



 


 


 


 

F. Hybrid Search

F1. Hybrid Search: Vector + Full-text Combined Score

Combines KNN_MATCH (semantic similarity) and MATCH (keyword relevance) with OR. CrateDB merges both signals into a combined_score:

  • Records matching only the vector rank by cosine similarity alone.
  • Records matching only the keyword rank by BM25 relevance alone.
  • Records matching both receive a boosted combined score and surface first.

This is the key advantage over running two separate queries and merging results.

  • Vector encodes: "worn parts replaced mechanical failure vibration"
  • Keyword: 'vibration' explicitly boosts notes that name the symptom directly.
  • Together: finds all vibration-related work orders whether they use that exact word or describe the same concept differently ("excessive oscillation", "bearing noise", "abnormal movement").
 
SELECT
    work_order_id,
    device_id,
    maintenance_type,
    completed_date,
    notes,
    _score AS combined_score
FROM rtia.maintenance_log
WHERE KNN_MATCH(notes_embedding, (SELECT embedding FROM rtia.knn_searches WHERE query_name = 'mechanical_failure'), 20)
   OR MATCH(notes, 'vibration')
ORDER BY _score DESC
LIMIT 15;