Silver слой

Скоро будет доступно!

Архитектура уровня Silver, описанная в этом документе, в настоящее время находится в разработке. В то время как основные возможности трансформации работают, система конфигурации и детали реализации могут измениться до окончательного релиза. Если вы заинтересованы в раннем доступе или у вас есть вопросы по этой функциональности, пожалуйста, свяжитесь с [email protected].

Уровень Silver преобразует необработанные телематические данные и бизнес-информацию в нормализованные, готовые к запросам сущности с предопределёнными метриками и структурами. Уровень Bronze содержит всё, что захвачено с устройств и систем — отдельные точки, события и значения полей, удобные для проверки и устранения неполадок. Уровень Silver обрабатывает эти сырые данные в осмысленные сущности, такие как поездки, посещения зон и операционные состояния, посредством настраиваемых преобразований, которые очищают, стандартизируют и агрегируют данные в понятные аналитические объекты.

💡 Кратко о уровне Silver: Bronze — это всё собранное, Silver — то, с чем вы можете работать.

Этот промежуточный уровень устраняет повторяющуюся ручную работу ETL и подготавливает данные для практической аналитики. Операторы автопарка получают ответы на распространённые вопросы без масштабной обработки данных, а интеграторы — стабильную основу для построения масштабируемой функциональности.

Архитектура и возможности

Уровень Silver организует обработанные данные в две отдельные схемы, которые отражают разные источники преобразований и шаблоны доступа. Обе схемы функционируют на уровне Silver медальонной архитектуры, расположенном выше схем уровня Bronze (raw_business_data и raw_telematics_data) и ниже уровня Gold.

Структура схемы

Уровень Silver использует подход динамической схемы, где структуры базы данных формируются автоматически на основе активных преобразований. В отличие от уровня Bronze с его фиксированными определениями схем, схемы уровня Silver содержат только таблицы, соответствующие настроенным и развернутым преобразованиям. Это означает, что доступные таблицы и их структуры зависят от того, какие преобразования в данный момент активны в вашем IoT Query инстансу.

Данные уровня Silver организованы в две схемы PostgreSQL:

• processed_common_data: Содержит преобразования, разработанные и поддерживаемые Navixy. Эта схема используется всеми клиентами и предоставляет стандартизованные аналитические сущности, которые охватывают общие сценарии телематики. Таблицы появляются в этой схеме по мере того, как Navixy разрабатывает и развёртывает новые преобразования для решения широко применимых аналитических задач.

• processed_custom_data: Содержит преобразования, специфичные для клиента, созданные для удовлетворения уникальных бизнес-требований. У каждого клиента есть изолированный экземпляр этой схемы, что позволяет создавать пользовательские аналитические сущности без влияния на других клиентов. Таблицы в этой схеме соответствуют преобразованиям, настроенным специально для вашей организации.

Обе схемы работают через конфигурации преобразований в формате JSON. Когда преобразование настраивается и активируется, система автоматически создаёт соответствующую структуру таблицы в соответствующей схеме. Когда преобразования удаляются или деактивируются, их таблицы могут быть заархивированы или удалены в соответствии с политиками хранения данных.

Эта динамическая формация объясняет, почему документация по уровню Silver не содержит фиксированных описаний схем, как это сделано для уровня Bronze. Вместо этого доступные таблицы и их структуры отражают конкретные преобразования, настроенные для вашего экземпляра IoT Query. Чтобы понять, какие данные доступны в вашем уровне Silver, просмотрите документацию по преобразованиям для сущностей, которые были развернуты в вашем экземпляре.

Архитектура обработки

Преобразования уровня Silver работают через архитектуру, управляемую конфигурациями, которая отделяет бизнес-логику от оркестрации. Каждое преобразование определяется JSON-конфигурацией, которая задаёт SQL-логику обработки, параметры, расписание и поведение при перерасчёте. Apache Airflow управляет жизненным циклом выполнения, применяя эти конфигурации для преобразования данных уровня Bronze в сущности уровня Silver.

Структура JSON-конфигурации остаётся идентичной как для общих, так и для пользовательских преобразований, обеспечивая единообразные шаблоны обработки для всех сущностей уровня Silver. Такой унифицированный подход к конфигурированию позволяет гибко развёртывать преобразования при сохранении стандартизированного исполнения и контроля версий.

Для подробной информации о системе JSON-конфигураций см. Configuration JSON раздел.

Актуальность данных

Сущности уровня Silver поддерживаются автоматически через запланированные процессы, определённые в конфигурациях преобразований. При выполнении запросов к данным уровня Silver учтите следующие характеристики обработки:

• Плановые обновления: Каждое преобразование обрабатывает новые данные уровня Bronze в соответствии со своим настроенным расписанием. Обновления обычно выполняются ежечасно или каждые несколько часов в зависимости от сложности преобразования.

• Окна обработки: Преобразования работают с временными окнами, чтобы эффективно обрабатывать управляемые сегменты данных, а не целые наборы данных.

• Влияние перерасчёта: Когда изменения конфигурации запускают перерасчёт, недавние данные могут показывать кратковременные несоответствия в пределах окон обработки.

• Поведение, специфичное для схемы: Преобразования в processed_common_data обновляются одновременно для всех клиентов, разделяющих эту схему. Преобразования в processed_custom_data выполняются независимо для каждого клиента, что позволяет настраивать расписание и логику обработки.

Configuration JSON

В этом разделе описывается архитектура конфигураций, которая в настоящее время находится в разработке. Структура JSON и спецификации параметров, показанные здесь, представляют планируемый подход к реализации. Конечные детали реализации могут отличаться по мере продвижения разработки.

Уровень Silver работает на архитектуре, управляемой конфигурациями, где преобразования определяются спецификациями в формате JSON. Каждая конфигурация содержит логику обработки, параметры преобразования, правила планирования и политики перерасчёта, которые определяют, как данные уровня Bronze становятся сущностями уровня Silver.

Структура JSON

Конфигурация преобразования состоит из четырёх разделов:

• version (string): Версия конфигурации в соответствии с семантическим версионированием

• metadata (object): Базовая информация, включая имя, описание, временную метку создания и идентификатор создателя

• sql_template (object): Спецификация логики обработки, включая пути к SQL-файлам, определения целевых таблиц и параметры преобразования

• target (object): Место вывода, указывающее схему и таблицу

• scheduler (object): Управление выполнением, включая cron-расписание, статус включения и конфигурацию бэкфилла

Схема конфигурации

{
  "version": "<semantic_version>",
  "metadata": {
    "name": "<transformation_name>",
    "description": "<transformation_description>",
    "created_at": "<iso_8601_timestamp>",
    "created_by": "<creator_identifier>"
  },
  "sql_template": {
    "sql_file": "<path_to_primary_sql_script>",
    "sql_file_1": "<path_to_additional_sql_script>",
    "table_name": "<schema.table_for_source_data>",
    "parameters": {
      "<parameter_name_1>": "<parameter_value_1>",
      "<parameter_name_2>": "<parameter_value_2>"
    }
  },
  "target": {
    "schema": "<output_schema>",
    "table": "<output_table>"
  },
  "scheduler": {
    "cron_expression": "<cron_schedule>",
    "enabled": <boolean>,
    "backfill": {
      "backfill_allowed": <boolean>,
      "start_date": "<iso_8601_date>",
      "end_date": "<iso_8601_date>"
    }
  }
}

Параметризация SQL-скриптов

SQL-скрипты преобразований ссылаются на параметры конфигурации с использованием заполнителей, начинающихся с двоеточия. Система подставляет фактические значения из конфигурации при выполнении скриптов и автоматически предоставляет стандартные параметры временного окна:

• :window_start - Начало временного окна обработки (метка времени в формате ISO-8601)

• :window_end - Конец временного окна обработки (метка времени в формате ISO-8601)

Пользовательские параметры определяются в разделе sql_template.parameters и контролируют логику, специфичную для преобразования, такую как пороги качества, бизнес-правила и методы расчёта.

Пример SQL с параметрами:

SELECT device_id, device_time, speed
FROM raw_telematics_data.tracking_data_core
WHERE device_time >= :window_start
  AND device_time < :window_end
  AND satellites >= :min_satellites
  AND speed >= :min_speed_kmh;

Версионирование конфигураций

Когда любой параметр конфигурации изменяется, создаётся новая версия. Каждая версия представляет собой конкретный набор правил обработки, которые были активны в течение определённого периода, что позволяет отслеживать эволюцию логики преобразований.

Триггеры создания версии:

• Любой параметр в sql_template.parameters изменяется

• Пути к файлам SQL-скриптов модифицируются

• Изменяются целевая схема или таблица

• Настройки планировщика или бэкфилла корректируются

Применение версии: Когда создаётся и применяется новая версия конфигурации, система обрабатывает данные на основе выбранного режима перерасчёта.

Режимы перерасчёта

Система конфигурации поддерживает три режима перерасчёта, которые контролируют, как изменения параметров влияют на исторические и будущие данные. Эти режимы обеспечивают гибкость при балансировке требований согласованности данных и эффективности обработки.

Перерасчёт только вперёд

Режим «только вперёд» применяет новые параметры конфигурации только к данным, обработанным после изменения версии. Исторические данные остаются неизменными, сохраняя значения, вычисленные с предыдущими параметрами.

Когда использовать: Незначительные корректировки параметров, которые не меняют фундаментально определения сущностей, тестирование новых параметров перед полным перерасчётом или управление вычислительными расходами путём избегания переработки истории.

Поведение: Если вы измените min_speed_kmh с 3 на 5 8 декабря, только поездки, обработанные с 8 декабря и далее, будут использовать новый порог. Поездки, рассчитанные до 8 декабря, сохранят свои оригинальные значения.

Полный перерасчёт истории

Режим полного перерасчёта обрабатывает все исторические данные в пределах настроенного диапазона бэкфилла с использованием новых параметров. Система заменяет все существующие сущности вновь вычисленными значениями.

Когда использовать: Фундаментальные изменения в определениях сущностей или алгоритмах обнаружения, исправление систематических ошибок в предыдущих расчётах или стандартизация всей исторической информации в соответствии с текущими бизнес-правилами.

Поведение: Изменение логики обнаружения поездок требует перерасчёта всех поездок для обеспечения единообразных определений сущностей во всём временном диапазоне.

Частичный перерасчёт

Режим частичного перерасчёта обрабатывает ограниченное временное окно исторических данных, как правило, за последние дни или недели.

Когда использовать: Исправление проблем качества недавних данных, обновление параметров, которые в основном влияют на недавние операционные паттерны, или внедрение изменений с ограниченным историческим воздействием.

Конфигурация: Укажите параметр backfill_days (например, 7 для последней недели) либо в конфигурации, либо при ручном запуске перерасчёта. Система обновит существующие записи в пределах указанного временного окна.

Доступные преобразования

В настоящее время уровень Silver предоставляет две группы преобразований, которые демонстрируют подход, управляемый конфигурациями, и служат шаблонами для разработки пользовательских сущностей.

Поездки

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

Краткая справка:

• Назначение: Преобразование данных уровня точек в аналитику уровня поездки

• Основная таблица: business_data.tracks

• Ключевые метрики: Расстояние, продолжительность, статистика скорости, географические границы

• Исходные данные: raw_telematics_data.tracking_data_core, raw_telematics_data.states

Таблица: business_data.tracks

Таблица tracks хранит агрегированную информацию о непрерывных сегментах движения с предварительно вычисленными метриками и географическим контекстом.

Первичный ключ: track_id (уникальный идентификатор с автоинкрементом)

Описание полей:

Схема track_id поле однозначно идентифицирует каждый сегмент поездки. Поле device_id ссылается на устройство отслеживания из уровня Bronze. Поля track_start_time и track_end_time определяют временные границы поездки. Поле track_duration предоставляет человеко-читаемый формат продолжительности, в то время как track_duration_seconds позволяет выполнять числовые вычисления. Поле track_distance_meters содержит суммарное пройденное расстояние. Поля скорости (avg_speed, max_speed, min_speed) дают статистическое сведение в километрах в час. Начальные координаты (latitude_start, longitude_start, altitude_start) и конечные координаты (latitude_end, longitude_end, altitude_end) определяют географические границы. Поле points_in_track показывает качество данных через количество точек. Поля start_zone и end_zone связывают поездки с данными справочника зон, когда поездки начинаются или заканчиваются в определённых зонах.

Связи данных:

-- Присоединение к информации об устройстве
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id

-- Присоединение к данным о транспортном средстве
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id

Алгоритм обнаружения поездок

Преобразование выявляет поездки с помощью детекции движения, которая анализирует скорость, расстояние и временные паттерны. Поездка представляет собой непрерывный сегмент движения, отделённый от других поездок периодами стоянки или разрывами данных.

Система начинает новую поездку, когда появляется первая точка трекинга для устройства, когда движение начинается после стоянки, превышающей настроенный порог, когда движение возобновляется после разрыва данных, превышающего настроенное время ожидания, или когда фиксируется одиночная LBS (базовая станция) точка. Система завершает поездку, когда движение останавливается и длительность стоянки достигает настроенного порога, либо когда возникает разрыв данных, превышающий таймаут.

Классификация движения:

• Движение: Скорость ≥ настроенного порога

• Стоянка: Скорость < порога И продолжительность ≥ настроенной длительности стоянки

• Разрыв данных: Время между точками ≥ настроенного таймаута разбиения

Проверка качества: Сгенерированные поездки должны соответствовать настраиваемым порогам качества, чтобы быть включёнными — минимум 2 точки трекинга, максимальная скорость ≥ настроенного порога, суммарное расстояние ≥ настроенного порога и определённые время начала и конца. Система фильтрует аномальные данные, включая нереалистичные скорости для LBS-точек, нулевые координаты и недостаточное покрытие спутниками.

Вычисление метрик: Метрики поездки вычисляются из проверенных точек трекинга. Расстояние представляет собой суммарную геометрическую длину. Статистика скорости включает средние, максимальные и минимальные значения скоростей точек. Продолжительность — это разница во времени между концом и началом. Географические границы фиксируют координаты первой и последней точки. Привязка к зоне сопоставляет стартовую и конечную зоны из справочных данных, когда поездки начинаются или заканчиваются в определённых зонах.

Параметры конфигурации

Параметр	Описание	Единица
min_parking_seconds	Порог длительности для определения стоянки	секунды
tracks_split_timeout_seconds	Максимальный разрыв между точками перед разбиением поездок	секунды
min_distance_meters	Минимальная дистанция поездки для проверки качества	метры
min_speed_kmh	Минимальный порог скорости для детекции движения	км/ч
max_lbs_speed_kmh	Максимальная реалистичная скорость для LBS-точек	км/ч
min_satellites	Минимальное количество спутников для качества GPS	шт.

Пример конфигурации

{
  "version": "1.0.0",
  "metadata": {
    "name": "Movement Tracking Transformation",
    "description": "Processes raw tracking data into trip entities with quality validation and metric calculation",
    "created_at": "2025-01-15T00:00:00Z",
    "created_by": "system"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/tracks.sql",
    "table_name": "business_data.tracks",
    "parameters": {
      "min_parking_seconds": 300,
      "tracks_split_timeout_seconds": 1200,
      "min_distance_meters": 100,
      "min_speed_kmh": 3,
      "max_lbs_speed_kmh": 200,
      "min_satellites": 4
    }
  },
  "target": {
    "schema": "business_data",
    "table": "tracks"
  },
  "scheduler": {
    "cron_expression": "0 */8 * * *",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "2024-01-01",
      "end_date": "2025-12-31"
    }
  }
}

Пример SQL-скрипта

Следующий упрощённый SQL демонстрирует использование параметров в логике преобразования:

-- Обнаружение поездок с настраиваемыми параметрами
WITH tracking_with_gaps AS (
    SELECT 
        device_id,
        device_time,
        latitude,
        longitude,
        speed,
        satellites,
        LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) as prev_time,
        LAG(speed) OVER (PARTITION BY device_id ORDER BY device_time) as prev_speed
    FROM raw_telematics_data.tracking_data_core
    WHERE device_time >= :window_start
      AND device_time < :window_end
      AND satellites >= :min_satellites
      AND latitude != 0 AND longitude != 0
),
trip_boundaries AS (
    SELECT *,
        CASE 
            WHEN prev_speed < :min_speed_kmh 
                 AND EXTRACT(EPOCH FROM (device_time - prev_time)) >= :min_parking_seconds
                 AND speed >= :min_speed_kmh
            THEN true
            WHEN EXTRACT(EPOCH FROM (device_time - prev_time)) >= :tracks_split_timeout_seconds
            THEN true
            ELSE false
        END as is_trip_start
    FROM tracking_with_gaps
)
INSERT INTO business_data.tracks (device_id, track_start_time, track_end_time)
SELECT device_id, MIN(device_time), MAX(device_time)
FROM trip_boundaries
WHERE speed >= :min_speed_kmh
GROUP BY device_id, SUM(is_trip_start::int) OVER (PARTITION BY device_id ORDER BY device_time);

Примеры запросов

Получить все поездки для конкретного устройства:

SELECT 
    track_id,
    track_start_time,
    track_end_time,
    track_duration,
    track_distance_meters,
    avg_speed,
    start_zone,
    end_zone
FROM business_data.tracks
WHERE device_id = 12345
ORDER BY track_start_time DESC;

Вычислить суточную сводку по пройденному расстоянию:

SELECT 
    DATE(track_start_time) as trip_date,
    COUNT(*) as total_trips,
    SUM(track_distance_meters) / 1000.0 as total_km,
    AVG(avg_speed) as avg_speed_kmh,
    SUM(track_duration_seconds) / 3600.0 as total_hours
FROM business_data.tracks
WHERE device_id = 12345
  AND track_start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(track_start_time)
ORDER BY trip_date DESC;

Найти поездки между определёнными зонами:

SELECT 
    t.track_id,
    t.track_start_time,
    t.track_duration,
    t.track_distance_meters / 1000.0 as distance_km,
    t.start_zone,
    t.end_zone,
    v.license_plate
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE t.start_zone = 'Distribution Center'
  AND t.end_zone = 'Customer Site A'
  AND t.track_start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY t.track_start_time DESC;

Геозоны

Преобразование Geofences предварительно вычисляет географические границы как геометрии PostGIS и отслеживает, когда устройства входят, находятся внутри и покидают эти определённые области. Эта обработка исключает необходимость вычислений геометрии в реальном времени при запросах, что существенно повышает производительность аналитики, основанной на местоположении.

Это преобразование демонстрирует обработку пространственных данных и обнаружение событий из непрерывных потоков местоположений.

Краткая справка:

• Назначение: Предварительно вычислять геометрии геозон и отслеживать присутствие устройств в географических областях

• Основные таблицы: business_data.geofence_geometries, business_data.geofence_visits

• Ключевые метрики: Длительность посещения, времена входа/выхода, использование геозоны

• Преимущество по производительности: 10–100× быстрее пространственные запросы по сравнению с вычислением геометрии на лету

• Исходные данные: raw_business_data.zones, raw_business_data.geofence_points, raw_telematics_data.tracking_data_core

Таблица: business_data.geofence_geometries

Таблица geofence_geometries хранит оптимизированные геометрические представления геозон для эффективных пространственных запросов.

Первичный ключ: geofence_id

Описание полей:

Схема geofence_id поле однозначно идентифицирует каждую геозону и ссылается на raw_business_data.zones.zone_id. Поле geofence_type указывает форму геозоны (круг, полигон или маршрут). Поле geofence_label содержит название геозоны для отображения и справки. Поле address сохраняет описание местоположения геозоны. Поле color хранит HEX-код цвета для визуализации. Поле geofence_geom содержит географическое представление для пространственных операций. Поля created_at и updated_at отслеживают временные изменения.

Спецификации типов геозон:

• Круг: Определяется центральной точкой и радиусом

• Полигон: Упорядоченные точки, формирующие замкнутую фигуру

• Маршрут: Линейный путь с буферным радиусом

Поведение синхронизации: Таблица автоматически синхронизируется при изменении исходных данных геозон в слое Bronze.

Таблица: business_data.geofence_visits

Таблица geofence_visits фиксирует историческое присутствие устройств в геозонах, включая время входа, время выхода и длительность посещения.

Первичный ключ: Составной ключ по (device_id, geofence_id, enter_time)

Описание полей:

Схема device_id поле ссылается на устройство отслеживания. Поле geofence_id ссылается на геозону из geofence_geometries. Поле enter_time отмечает момент, когда устройство вошло в геозону. Поле exit_time отмечает момент, когда устройство вышло (NULL для текущих посещений). Поле duration содержит вычисленную длину посещения.

Связи данных:

-- Присоединение к деталям геозоны
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id

-- Присоединение к информации об устройстве и транспортном средстве
FROM business_data.geofence_visits AS gv
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id

Алгоритм обнаружения посещений

Преобразование отслеживает присутствие устройств в геозонах, сравнивая точки трекинга с геометриями геозон. Записи о посещениях фиксируют, когда устройства входят, находятся внутри и покидают определённые геозоны.

Обнаружение входа: Система обнаруживает вход, когда точка трекинга устройства попадает внутрь геометрии геозоны, а предыдущая точка была вне этой геозоны или предыдущей точки не существовало.

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

Группировка посещений: Последовательные пары вход-выход образуют одну запись посещения. Открытые посещения (выход не обнаружен) показывают NULL в exit_time и обновляются при фиксации выхода в последующих циклах обработки.

Вычисление длительности: Длительность посещения вычисляется как разница во времени между событиями входа и выхода. Открытые посещения показывают NULL в поле длительности до обнаружения выхода.

Параметры конфигурации

Параметр	Описание	Единица
spatial_buffer_meters	Расстояние буфера для обнаружения границы геозоны	метры
min_visit_duration_seconds	Минимальная длительность посещения для записи	секунды
max_visit_gap_seconds	Максимальный временной разрыв до признания посещения завершённым	секунды

Пример конфигурации

{
  "version": "1.0.0",
  "metadata": {
    "name": "Geofences Transformation",
    "description": "Pre-computes geofence geometries and tracks device presence within defined geographic areas",
    "created_at": "2025-01-15T00:00:00Z",
    "created_by": "system"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/geofence_visits.sql",
    "table_name": "business_data.geofence_visits",
    "parameters": {
      "spatial_buffer_meters": 50,
      "min_visit_duration_seconds": 60,
      "max_visit_gap_seconds": 1800
    }
  },
  "target": {
    "schema": "business_data",
    "table": "geofence_visits"
  },
  "scheduler": {
    "cron_expression": "0 * * * *",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "2024-01-01",
      "end_date": "2025-12-31"
    }
  }
}

Примеры запросов

Получить все посещения конкретной геозоны:

SELECT 
    gv.device_id,
    gv.enter_time,
    gv.exit_time,
    gv.duration,
    v.license_plate,
    gg.geofence_label
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gg.geofence_label = 'Main Warehouse'
  AND gv.enter_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY gv.enter_time DESC;

Вычислить статистику использования геозон:

SELECT 
    gg.geofence_label,
    COUNT(*) as total_visits,
    COUNT(DISTINCT gv.device_id) as unique_devices,
    AVG(EXTRACT(EPOCH FROM gv.duration) / 60) as avg_duration_minutes,
    MAX(EXTRACT(EPOCH FROM gv.duration) / 60) as max_duration_minutes
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
WHERE gv.enter_time >= CURRENT_DATE - INTERVAL '30 days'
  AND gv.exit_time IS NOT NULL
GROUP BY gg.geofence_label
ORDER BY total_visits DESC;

Найти устройства, находящиеся в данный момент:

SELECT 
    gg.geofence_label,
    gv.device_id,
    v.license_plate,
    gv.enter_time,
    NOW() - gv.enter_time as time_in_geofence
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gv.exit_time IS NULL
ORDER BY gg.geofence_label, gv.enter_time;

Анализировать паттерны входов/выходов по часам:

SELECT 
    gg.geofence_label,
    EXTRACT(HOUR FROM gv.enter_time) as entry_hour,
    COUNT(*) as entries,
    AVG(EXTRACT(EPOCH FROM gv.duration) / 60) as avg_visit_minutes
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
WHERE gv.enter_time >= CURRENT_DATE - INTERVAL '30 days'
  AND gv.exit_time IS NOT NULL
GROUP BY gg.geofence_label, EXTRACT(HOUR FROM gv.enter_time)
ORDER BY gg.geofence_label, entry_hour;

Определить устройства с наибольшим временем пребывания:

SELECT 
    gg.geofence_label,
    v.license_plate,
    gv.enter_time,
    gv.exit_time,
    gv.duration,
    EXTRACT(EPOCH FROM gv.duration) / 3600 as duration_hours
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gv.exit_time IS NOT NULL
  AND gv.enter_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY gv.duration DESC
LIMIT 20;

Разработка пользовательских сущностей

Уровень Silver демонстрирует шаблоны преобразований через доступные преобразования, которые служат шаблонами для разработки пользовательских аналитических сущностей. Используя данные уровня Bronze, возможности SQL и архитектуру конфигураций, можно разработать пользовательские сущности для решения конкретных бизнес-требований.

Подход к разработке

Пользовательские сущности уровня Silver следуют архитектуре, управляемой конфигурациями, описанной в этом документе. Подход включает определение логики преобразования в SQL-скриптах и создание JSON-конфигураций, которые задают параметры и расписания для автоматического выполнения.

Ключевые возможности: Агрегировать множество сырых точек в единые аналитические объекты, применять бизнес-логику и правила валидации, предварительно вычислять метрики для ускорения запросов, поддерживать временную точность через плановую обработку и интегрировать пространственные операции с бизнес-контекстом.

Потенциальные типы пользовательских сущностей

• Операционные сущности: Состояния и рабочие режимы, специфичные для компании, шаблоны смен и отслеживание рабочего цикла, метрики использования активов, обнаружение окон обслуживания

• Поведенческие сущности: Пользовательская оценка риска на основе нескольких факторов, анализ и классификация стиля вождения, мониторинг соответствия с настраиваемыми порогами, агрегирование индикаторов безопасности

• Сущности производительности: Отраслевые метрики и KPI, расчёты эффективности с использованием пользовательских формул, показатели оптимизации ресурсов, отслеживание выполнения уровня сервиса

• Событийные сущности: Пользовательское обнаружение событий с комплексными условиями, агрегация оповещений и распознавание паттернов, выявление аномалий с помощью статистических методов, отслеживание нарушений порогов

Шаблон конфигурации

{
  "version": "1.0.0",
  "metadata": {
    "name": "<custom_entity_name>",
    "description": "<transformation_purpose_and_business_value>",
    "created_at": "<iso_8601_timestamp>",
    "created_by": "<developer_identifier>"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/<custom_entity>.sql",
    "table_name": "business_data.<custom_entity>",
    "parameters": {
      "<threshold_parameter>": <numeric_value>,
      "<time_window_parameter>": <numeric_value>,
      "<filter_enabled_parameter>": <boolean_value>
    }
  },
  "target": {
    "schema": "business_data",
    "table": "<custom_entity>"
  },
  "scheduler": {
    "cron_expression": "<cron_schedule>",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "<iso_8601_date>",
      "end_date": "<iso_8601_date>"
    }
  }
}

Рекомендации по написанию SQL-скриптов

Используйте параметризованные значения:

WHERE value >= :custom_threshold
  AND duration_seconds <= :time_window_seconds
  AND (:quality_filter_enabled = false OR quality_score >= 0.8)

Используйте стандартные временные интервалы:

FROM raw_telematics_data.tracking_data_core
WHERE device_time >= :window_start
  AND device_time < :window_end

Структурируйте обработку по этапам:

WITH raw_data AS (
    -- Извлечь и отфильтровать исходные данные
),
enriched_data AS (
    -- Выполнить объединение с эталонными данными и вычислить промежуточные значения
),
aggregated_entities AS (
    -- Агрегировать в итоговую структуру сущности
)
INSERT INTO business_data.custom_entity (...)
SELECT ... FROM aggregated_entities;

Дополнительные ресурсы

Для подробных шаблонов запросов и работы с данными уровня Silver обратитесь к IoT Query SQL Recipe Book.

Если вы заинтересованы в раннем доступе или у вас есть вопросы по этой функциональности, пожалуйста, свяжитесь с [email protected].