# Consultas comunes

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

{% hint style="danger" %}
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.
{% endhint %}

## Consultas básicas

### Recuperar información básica de objetos

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

```sql
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 ubicaciones más recientes de los dispositivos

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

```sql
SELECT
    t.device_id,
    o.object_label,
    -- Convertir coordenadas enteras escaladas de nuevo a 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;
```

{% hint style="info" %}
Los valores de coordenadas se almacenan como enteros escalados por 10^7 para mejorar la eficiencia de almacenamiento en TimescaleDB. Al consultar, divida por 10000000 para convertir de nuevo al formato decimal estándar.
{% endhint %}

## 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:

```sql
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 ubicaciones

Rastree qué empleados fueron asignados a qué vehículos y su historial de ubicaciones:

```sql
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 los datos del sensor de combustible:

```sql
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 ingresaron a geozonas específicas:

```sql
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 una 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;
```

{% hint style="info" %}
Esta consulta utiliza funciones espaciales de PostGIS. Si encuentra errores, verifique que la extensión PostGIS esté habilitada en su base de datos.
{% endhint %}

## Consejos para optimizar el rendimiento

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

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

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

**Evite esto** (escanea toda la tabla)

```sql
SELECT * FROM raw_telematics_data.tracking_data_core;
```

2. **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.
3. **Use joins selectivamente**: Una las tablas solo cuando sea necesario y trate de filtrar los datos antes de unir tablas grandes.
4. **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.
5. **Limite los conjuntos de resultados**: Use siempre LIMIT para consultas exploratorias para evitar devolver millones de filas.

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

6. **Utilice relaciones jerárquicas**: Estructure consultas complejas siguiendo la jerarquía de entidades (**dealer → client → user/device → object**) para uniones y filtrados más eficientes.
7. **Gestione las conexiones correctamente**: Cierre las conexiones a la base de datos cuando no estén en uso, especialmente en herramientas de BI o scripts programados, para evitar bloqueo 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 comerciales específicas.