# Распространённые запросы

Подключившись к базе данных, вы сможете извлекать данные с помощью SQL-запросов. В этом разделе приведены примерные SQL-запросы, которые помогут вам начать работу с Private Telematics Lakehouse. Эти примеры демонстрируют, как получить доступ к данным и анализировать их из **Сырой слой данных** слоя, который содержит необработанные бизнес- и телематические данные с минимальной трансформацией.

{% hint style="danger" %}
Обратите внимание: поскольку база данных содержит огромное количество информации, убедитесь, что тестовые запросы выполняются с ограниченным количеством извлекаемых значений.
{% endhint %}

## Базовые запросы

### Получение основной информации об объектах

Этот запрос возвращает информацию об объектах (автомобилях/активах) в вашей системе:

```sql
SELECT
    o.object_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
ИЗ
    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
СОРТИРОВАТЬ ПО
    o.object_label;
```

### Получить последние местоположения устройств

Получите самые свежие данные о местоположении для всех ваших устройств:

```sql
SELECT
    t.device_id,
    o.object_label,
    -- Преобразовать масштабированные целочисленные координаты обратно в десятичный формат
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed,
    t.device_time
ИЗ
    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
СОРТИРОВАТЬ ПО
    t.device_id, t.device_time DESC;
```

{% hint style="info" %}
Значения координат хранятся как целые числа, масштабированные на 10^7 для повышения эффективности хранения в TimescaleDB. При выполнении запросов делите на 10000000, чтобы преобразовать обратно в стандартный десятичный формат.
{% endhint %}

## Соединение бизнес- и телематических данных

### Отчёт об активности транспортных средств

Этот запрос формирует ежедневную сводку активности, объединяя бизнес- и телематические данные:

```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
ИЗ
    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'
ГРУППИРОВАТЬ ПО
    o.object_label, v.vehicle_type, DATE(t.device_time)
СОРТИРОВАТЬ ПО
    o.object_label, DATE(t.device_time);
```

### Назначения водителей и история местоположений

Отслеживайте, какие сотрудники были назначены на какие транспортные средства и их историю местоположений:

```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
ИЗ 
    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
СОРТИРОВАТЬ ПО 
    assignment_time;
```

## Анализ данных датчиков

### Отслеживание уровня топлива

Этот запрос показывает, как анализировать данные датчика уровня топлива:

```sql
SELECT
    o.object_label AS vehicle,
    t.device_time,
    i.value::numeric AS fuel_level
ИЗ
    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')
СОРТИРОВАТЬ ПО
    o.object_label, t.device_time;
```

## Геопространственный анализ

### Транспортные средства в геозонах

Определите, какие транспортные средства въезжали в конкретные геозоны:

```sql
SELECT
    o.object_label AS vehicle,
    z.zone_label AS geozone,
    t.device_time AS entry_time
ИЗ
    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
        -- Вычислить, находится ли точка внутри круговой зоны
        -- Преобразовать координаты из масштабированных целых чисел в десятичные
        (
            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')
СОРТИРОВАТЬ ПО
    z.zone_label, o.object_label, t.device_time;
```

{% hint style="info" %}
В этом запросе используются пространственные функции PostGIS. Если вы сталкиваетесь с ошибками, проверьте, включено ли расширение PostGIS в вашей базе данных.
{% endhint %}

## Советы по оптимизации производительности

При работе с облачным хранилищем данных рассмотрите следующие методы оптимизации:

1. **Используйте фильтрацию по времени**: Всегда включайте временной фильтр по `device_time` или `record_added_at` столбцам, чтобы ограничить объём сканируемых данных.\
   **Хорошая практика**:

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

**Избегайте этого** (сканирует всю таблицу)

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

2. **Используйте индексы**: В базе данных имеются индексы по `(device_id, device_time)` парам. Стройте запросы таким образом, чтобы по возможности использовать эти индексы.
3. **Используйте JOIN выборочно**: Объединяйте таблицы только когда это необходимо и старайтесь фильтровать данные до объединения больших таблиц.
4. **Преобразование масштабированных целых чисел**: Помните, что данные координат хранятся как масштабированные целые числа. Преобразуйте их только в финальном SELECT, а не в WHERE-условиях.
5. **Ограничивайте наборы результатов**: Всегда используйте LIMIT для исследовательских запросов, чтобы избежать возвращения миллионов строк.

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

6. **Используйте иерархические связи**: Структурируйте сложные запросы с учётом иерархии сущностей (**dealer → client → user/device → object**) для более эффективных объединений и фильтрации.
7. **Правильно управляйте подключениями**: Закрывайте подключения к базе данных, когда они не используются, особенно в BI-инструментах или плановых скриптах, чтобы избежать блокировок ресурсов или проблем с таймаутом.

## Дальнейшие шаги

Эти примеры служат отправной точкой для работы с вашими данными. По мере того как вы лучше познакомитесь со схемой, вы сможете разрабатывать более сложные запросы для решения своих конкретных бизнес-задач.