Consultas comunes

Al conectarse a la base de datos, podrá recuperar datos mediante consultas SQL. Esta sección proporciona consultas SQL de ejemplo para ayudarle a comenzar a trabajar con Private Telematics Lakehouse. Estos ejemplos demuestran cómo acceder y analizar datos de la Capa de datos sin procesar layer, que contiene datos empresariales y telemáticos sin procesar con una transformación mínima.

Tenga en cuenta: debido a que la base de datos contiene una gran cantidad de información, asegúrese de realizar consultas de prueba basadas en un número limitado de valores recuperados.

Consultas básicas

Recuperar información básica del objeto

Esta consulta devuelve información sobre los objetos (vehículos/activos) de su sistema:

SELECT
    o.object_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
FROM
    raw_business_data.objects o
    LEFT JOIN raw_business_data.devices d ON o.device_id = d.device_id
    LEFT JOIN raw_business_data.groups g ON o.group_id = g.group_id
WHERE
    o.is_deleted = false
ORDER BY
    o.object_label;

Obtener las últimas ubicaciones del dispositivo

Recupere los datos de ubicación más recientes de todos sus dispositivos:

SELECT
    t.device_id,
    o.object_label,
    -- Convertir las coordenadas enteras escaladas de nuevo al formato decimal
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed,
    t.device_time
FROM
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
    AND t.latitude != 0
    AND t.longitude != 0
ORDER BY
    t.device_id, t.device_time DESC;

Los valores de las coordenadas se almacenan como enteros escalados por 10^7 para mejorar la eficiencia de almacenamiento en TimescaleDB. Al consultar, divida por 10000000 para convertirlos nuevamente al formato decimal estándar.

Unir datos empresariales y telemáticos

Informe de actividad del vehículo

Esta consulta genera un resumen diario de actividad al unir datos empresariales y telemáticos:

SELECT
    o.object_label AS vehicle,
    v.vehicle_type,
    DATE(t.device_time) AS date,
    COUNT(DISTINCT DATE_PART('hour', t.device_time)) AS active_hours,
    MAX(t.speed) AS max_speed,
    AVG(t.speed) AS avg_speed
FROM
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    LEFT JOIN raw_business_data.vehicles v ON o.object_id = v.object_id
WHERE
    t.device_time BETWEEN '2025-03-01' AND '2025-03-28'
GROUP BY
    o.object_label, v.vehicle_type, DATE(t.device_time)
ORDER BY
    o.object_label, DATE(t.device_time);

Asignaciones de conductores e historial de ubicación

Rastree qué empleados fueron asignados a qué vehículos y su historial de ubicación:

SELECT 
    o.object_label AS vehicle,
    new_row.changed_datetime AS assignment_time,
    e_new.first_name || ' ' || e_new.last_name AS new_driver_name,
    e_old.first_name || ' ' || e_old.last_name AS old_driver_name,
    new_row.address,
    new_row.latitude,
    new_row.longitude
FROM 
    raw_business_data.driver_history new_row
JOIN 
    raw_business_data.driver_history old_row
    ON new_row.changed_datetime = old_row.changed_datetime
    AND new_row.object_id = old_row.object_id
LEFT JOIN 
    raw_business_data.employees e_new ON new_row.new_employee_id = e_new.employee_id
LEFT JOIN 
    raw_business_data.employees e_old ON old_row.old_employee_id = e_old.employee_id
LEFT JOIN 
    raw_business_data.objects o ON new_row.object_id = o.object_id
ORDER BY 
    assignment_time;

Análisis de datos de sensores

Seguimiento del nivel de combustible

Esta consulta muestra cómo analizar datos del sensor de combustible:

SELECT
    o.object_label AS vehicle,
    t.device_time,
    i.value::numeric AS fuel_level
FROM
    raw_telematics_data.inputs i
    JOIN raw_business_data.objects o ON i.device_id = o.device_id
    JOIN raw_telematics_data.tracking_data_core t ON
        i.device_id = t.device_id AND
        i.device_time = t.device_time
WHERE
    i.sensor_name = 'fuel'
    AND t.device_time > (CURRENT_DATE - INTERVAL '7 days')
ORDER BY
    o.object_label, t.device_time;

Análisis geoespacial

Vehículos en geozonas

Identifique qué vehículos entraron en geozonas específicas:

SELECT
    o.object_label AS vehicle,
    z.zone_label AS geozone,
    t.device_time AS entry_time
FROM
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    JOIN raw_business_data.zones z ON
        -- Calcular si el punto está dentro de la zona circular
        -- Convertir coordenadas de enteros escalados a decimal
        (
            CASE
                WHEN z.zone_type = 'circle' THEN
                    ST_DWithin(
                        ST_MakePoint(t.longitude::float/10000000, t.latitude::float/10000000)::geography,
                        ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude)::geography,
                        z.radius
                    )
                ELSE false
            END
        )
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
ORDER BY
    z.zone_label, o.object_label, t.device_time;

Esta consulta utiliza funciones espaciales de PostGIS. Si encuentra errores, verifique que la extensión PostGIS esté habilitada en su base de datos.

Consejos para optimizar el rendimiento

Al trabajar con Cloud Data Warehouse, considere estas técnicas de optimización:

1. Use el filtrado basado en tiempo: Incluya siempre un filtro de tiempo en la device_time o record_added_at columns para limitar los datos escaneados. Buena práctica:

SELECT * FROM raw_telematics_data.tracking_data_core 
WHERE device_time > (CURRENT_DATE - INTERVAL '7 days');

Evite esto (escanea toda la tabla)

SELECT * FROM raw_telematics_data.tracking_data_core;

1. Aproveche los índices: La base de datos tiene índices en (device_id, device_time) pares. Estructure sus consultas para usar estos índices cuando sea posible.

2. Use uniones de forma selectiva: Una tablas solo cuando sea necesario e intente filtrar los datos antes de unir tablas grandes.

3. Conversión de enteros escalados: Recuerde que los datos de coordenadas se almacenan como enteros escalados. Conviértalos solo en el SELECT final, no en las cláusulas WHERE.

4. Limite los conjuntos de resultados: Use siempre LIMIT en consultas exploratorias para evitar devolver millones de filas.

SELECT * FROM raw_telematics_data.tracking_data_core 
WHERE device_time > (CURRENT_DATE - INTERVAL '1 day')
LIMIT 1000;

1. Utilice relaciones jerárquicas: Estructure consultas complejas siguiendo la jerarquía de entidades (dealer → client → user/device → object) para lograr uniones y filtrado más eficientes.

2. Gestione correctamente las conexiones: Cierre las conexiones de la base de datos cuando no estén en uso, especialmente en herramientas de BI o scripts programados, para evitar bloqueos de recursos o problemas de tiempo de espera.

Próximos pasos

Estos ejemplos proporcionan un punto de partida para trabajar con sus datos. A medida que se familiarice más con el esquema, podrá desarrollar consultas más complejas para satisfacer sus necesidades empresariales específicas.