Experience Your First Query

The following is a typical query that comprehensively demonstrates KWDB's multi-modal fusion capabilities, showing how to correlate and analyze data across multiple schemas (time-series and relational).

Create Database and Tables

Time-Series Database and Table

• Created a time-series database named factory_iot
• Created a time-series table machine_sensors, containing:
  • Timestamp field ts (TIMESTAMPTZ, NOT NULL)
  • Three measurement fields: vibration, temperature, power_consumption
  • Three tag fields: machine_id, production_line, machine_type
  • Specified machine_id as the PRIMARY TAG

-- Create time-series database
CREATE TS DATABASE factory_iot;

-- Create time-series table with tags
CREATE TABLE factory_iot.machine_sensors (
    ts TIMESTAMPTZ NOT NULL,
    vibration FLOAT,
    temperature FLOAT,
    power_consumption FLOAT
) TAGS (
    machine_id INT NOT NULL,
    production_line VARCHAR(50),
    machine_type VARCHAR(50)
) PRIMARY TAGS(machine_id);

Relational Database and Table

• Created a relational database named factory_management
• Created a relational table production_orders, containing:
  • order_id (primary key)
  • machine_id (logically references machine_id from the time-series table)
  • product_code
  • start_time, end_time
  • quality_rating

-- Create relational database
CREATE DATABASE factory_management;

-- Create relational table
CREATE TABLE factory_management.production_orders (
    order_id VARCHAR(20) PRIMARY KEY,
    machine_id INT,  -- logically references machine_sensors.machine_id
    product_code VARCHAR(20),
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ,
    quality_rating FLOAT
);

Insert Data

Insert Time-Series Data

-- Insert time-series data (device sensor data)
INSERT INTO factory_iot.machine_sensors VALUES
('2023-08-01 08:00:00', 2.3, 65.2, 1200, 101, 'LineA', 'CNC'),
('2023-08-01 09:15:00', 2.5, 66.1, 1250, 101, 'LineA', 'CNC'),
('2023-08-01 10:30:00', 2.7, 67.0, 1300, 101, 'LineA', 'CNC'),
('2023-08-01 11:45:00', 2.6, 66.5, 1280, 102, 'LineB', 'CNC'),
('2023-08-01 13:00:00', 2.8, 67.2, 1320, 102, 'LineB', 'CNC');

Insert Relational Data

-- Insert relational data (production order data)
INSERT INTO factory_management.production_orders VALUES
('ORD-2023-101', 101, 'PROD-A100', NOW() - INTERVAL '8 hours', NOW() - INTERVAL '2 hours', 0.98),
('ORD-2023-102', 101, 'PROD-A200', NOW() - INTERVAL '7 hours 30 minutes', NOW() - INTERVAL '1 hour', 0.95),
('ORD-2023-103', 102, 'PROD-B100', NOW() - INTERVAL '8 hours', NOW() - INTERVAL '3 hours', 0.92),
('ORD-2023-104', 102, 'PROD-B200', NOW() - INTERVAL '7 hours', NOW() - INTERVAL '2 hours 30 minutes', 0.90);

Execute Query

Query analysis:

1. Data correlation: Correlate production orders and device sensor data through machine_id
2. Time filtering: Only query sensor data for 2023-08-01
3. Aggregation: Calculate average temperature, vibration, and power consumption for each order period
4. Grouping: Group by order, product, device, and other dimensions
5. Sorting: Sort by order start time in descending order

Expected results:

• Display basic information for each order and corresponding average device operating status
• Analyze the relationship between device operating parameters and product quality
• Compare production performance across different production lines or device types

-- Execute cross-modal analysis query
SELECT
    po.order_id,
    po.product_code,
    ms.machine_id,
    ms.production_line,
    ms.machine_type,
    po.start_time,
    po.end_time,
    po.quality_rating,
    AVG(ms.temperature) AS avg_temperature,
    AVG(ms.vibration) AS avg_vibration,
    AVG(ms.power_consumption) AS avg_power
FROM
    factory_management.production_orders AS po
JOIN
    factory_iot.machine_sensors AS ms
    ON po.machine_id = ms.machine_id
WHERE
    ms.ts BETWEEN '2023-08-01 00:00:00' AND '2023-08-02 00:00:00'
GROUP BY
    po.order_id,
    po.product_code,
    ms.machine_id,
    ms.production_line,
    ms.machine_type,
    po.start_time,
    po.end_time,
    po.quality_rating
ORDER BY
    po.start_time DESC;

Query Results

    order_id   | product_code | machine_id | production_line | machine_type |            start_time            |             end_time             | quality_rating |  avg_temperature  | avg_vibration | avg_power
---------------+--------------+------------+-----------------+--------------+----------------------------------+----------------------------------+----------------+-------------------+---------------+------------
  ORD-2023-104 | PROD-B200    |        102 | LineB           | CNC          | 2025-09-02 01:05:41.060688+00:00 | 2025-09-02 05:35:41.060688+00:00 |            0.9 |             66.85 |           2.7 |      1300
  ORD-2023-102 | PROD-A200    |        101 | LineA           | CNC          | 2025-09-02 00:35:41.060688+00:00 | 2025-09-02 07:05:41.060688+00:00 |           0.95 | 66.10000000000001 |           2.5 |      1250
  ORD-2023-101 | PROD-A100    |        101 | LineA           | CNC          | 2025-09-02 00:05:41.060688+00:00 | 2025-09-02 06:05:41.060688+00:00 |           0.98 | 66.10000000000001 |           2.5 |      1250
  ORD-2023-103 | PROD-B100    |        102 | LineB           | CNC          | 2025-09-02 00:05:41.060688+00:00 | 2025-09-02 05:05:41.060688+00:00 |           0.92 |             66.85 |           2.7 |      1300
(4 rows)

Time: 15.168875ms