Consultas comunes

Patrones SQL de ejemplo para información del objeto, ubicaciones de dispositivos, actividad del vehículo, asignaciones de conductor, datos del sensor y análisis de zonas geoespaciales

A medida que se conecte 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 el Private Telematics Lakehouse. Estos ejemplos demuestran cómo acceder y analizar datos del Datos brutos capa, que contiene datos brutos de negocio y telemáticos con una transformación mínima.

Tenga en cuenta: debido a que la base de datos contiene una enorme 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 (Gestión de vehículos/activos) de su sistema:

SELECT
    o.objeto_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
DE
    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.etiqueta_de_objeto;

Obtener las ubicaciones más recientes de los dispositivos

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

SELECT
    t.device_id,
    o.object_label,
    -- Convertir coordenadas enteras escaladas de vuelta al formato decimal
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitud,
    t.speed,
    t.device_time
DE
    raw_telematics_data.seguimiento_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, divídalos entre 10000000 para convertirlos de nuevo al formato decimal estándar.

Combinación de datos empresariales y telemáticos

Reporte de actividad del vehículo

Esta consulta genera un resumen diario de actividad al combinar datos de negocio y telemáticos:

SELECT
    o.object_label AS vehículo,
    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
DE
    raw_telematics_data.seguimiento_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'
AGRUPAR POR
    o.object_label, v.vehicle_type, DATE(t.device_time)
ORDER BY
    o.object_label, DATE(t.device_time);

Asignaciones del conductor e historial de ubicaciones

Haga un seguimiento de qué empleados fueron asignados a qué Gestión de vehículos y su historial de ubicación:

SELECT 
    o.object_label AS vehículo,
    new_row.changed_datetime AS assignment_time,
    e_new.first_name || ' ' || e_new.last_name AS nombre_del_nuevo_conductor,
    e_old.first_name || ' ' || e_old.last_name AS nombre_anterior_del_conductor,
    new_row.address,
    new_row.latitude,
    new_row.longitude
DE 
    raw_business_data.historial_de_Conductor nueva_fila
JOIN 
    raw_business_data.driver_history fila anterior
    ON new_row.changed_datetime = old_row.changed_datetime
    Y 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 
    tiempo de asignación;

Analizando Datos del sensor

Seguimiento del nivel de combustible

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

SELECT
    o.object_label AS vehículo,
    t.device_time,
    i.value::numeric AS nivel_de_combustible
DE
    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 = 'combustible'
    AND t.device_time > (CURRENT_DATE - INTERVAL '7 days')
ORDER BY
    o.etiqueta_de_objeto, t.hora_del_dispositivo;

Análisis geoespacial

Gestión de vehículos en geozonas

Identifique qué Gestión de vehículos ingresaron a zonas geográficas específicas:

SELECT
    o.object_label AS vehículo,
    z.zone_label AS geozone,
    t.device_time AS entry_time
DE
    raw_telematics_data.seguimiento_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    JOIN raw_business_data.zones z ON
        -- Calcule si el punto está dentro de la zona circular
        -- Convierta las coordenadas de enteros escalados a decimales
        (
            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 usa funciones espaciales de PostGIS. Si encuentra errores, verifique que la extensión de PostGIS esté activada en su base de datos.

Consejos para optimizar el rendimiento

Al trabajar con el almacén de datos en la nube, considere estas técnicas de optimización:

1. Usar el filtrado basado en el tiempo: Siempre incluya un filtro de tiempo en el device_time o record_added_at columnas para limitar los datos analizados. Buena práctica:

SELECT * FROM raw_telematics_data.seguimiento_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. Aprovechar í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 las 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. Convierta solo en el SELECT final, no en las cláusulas WHERE.

4. Limitar conjuntos de resultados: Utilice siempre LIMIT para las consultas exploratorias a fin de evitar devolver millones de filas.

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

1. Utilizar relaciones jerárquicas: Estructure consultas complejas siguiendo la jerarquía de entidades (distribuidor → cliente → usuario/dispositivo → Objeto) para lograr uniones y filtrado más eficientes.

2. Administrar correctamente las conexiones: Cierre las conexiones de base de datos cuando no las esté usando, 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 las necesidades específicas de su negocio.