Написание SQL-запросов

Dashboard Studio использует SQL для извлечения данных из схем IoT Query. Вы пишете SQL в двух контекстах: в редакторах панелей, где запросы питают визуализации, и в автономном SQL Editor для исследования данных. На этой странице объясняется, как писать эффективный SQL для обоих контекстов, с акцентом на требования визуализаций, поскольку они имеют конкретные структурные ограничения.

Где используется SQL

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

Запросы для визуализаций питают отдельные панели в отчетах. Эти запросы вы пишете на вкладке SQL Query редактора панели. Каждая панель выполняет один запрос, который должен возвращать данные в определённой структуре, соответствующей типу визуализации. Эти запросы выполняются при загрузке или обновлении отчётов, поэтому производительность важна для пользовательского опыта. SQL для визуализаций не может изменять данные; все запросы выполняются как только чтение (SELECT) по схемам IoT Query.

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

SQL Editor поддерживает исследование данных и экспорт. Откройте SQL Editor в левой боковой панели в разделе Tools. Пишите любые SELECT-запросы для изучения структуры данных, проверки предположений или экспорта результатов в CSV. SQL Editor показывает полные таблицы результатов с возможностью сортировки столбцов и предоставляет метрики выполнения. Используйте его для тестирования логики перед добавлением SQL в панели визуализации или для разовых выгрузок данных, которые не требуют визуализации.

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

Как писать SQL для визуализаций

SQL для визуализаций должен возвращать определённое количество столбцов и типы данных. Dashboard Studio не сможет отобразить столбчатую диаграмму из трёх столбцов или информационный блок (stat tile) из текстовых данных. Перед написанием запроса проверьте раздел Dataset Requirements на вкладке SQL Query, чтобы точно узнать, что ожидает выбранная визуализация. В таблице ниже перечислены поддерживаемые типы визуализаций:

Визуализация	Требования к запросу	Пример
Stat tile	Одиночное числовое значение	SELECT COUNT(*) FROM schema.table
Bar chart	Два столбца: категория, значение	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Pie chart	Два столбца: метка, значение	SELECT category, SUM(value) FROM schema.table GROUP BY category
Table	Любые столбцы	SELECT column1, column2, column3 FROM schema.table
Text	Запрос не требуется	Только содержимое Markdown

Stat tiles

Stat tiles отображают одиночные числовые значения. Запросы должны возвращать ровно одну строку с одним числовым столбцом:

Общее количество поездок за текущий месяц

SELECT COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE);

Общее пройденное расстояние (км)

SELECT SUM(distance_km) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days';

Название столбца не имеет значения — важно, чтобы результат был одним числовым значением. Dashboard Studio отображает это значение с форматированием, которое вы настраиваете в Visualization Settings.

Bar charts

Столбчатые диаграммы требуют ровно два столбца: категория (текст или дата) и значение (число). Первый столбец становится осью X, второй — высотой столбцов:

Поездки по типу транспортного средства

SELECT 
  vehicle_type as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY vehicle_type
ORDER BY value DESC;

Ежедневное количество поездок

SELECT 
  DATE_TRUNC('day', start_time)::date as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY category;

Используйте ORDER BY чтобы управлять последовательностью столбцов. Сортируйте по значению для ранжированных сравнений или по категории для временных рядов.

Pie charts

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

Распределение поездок по зонам

SELECT 
  zone_name as label,
  COUNT(*) as value
FROM silver.zone_visits
WHERE enter_time >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY zone_name
ORDER BY value DESC
LIMIT 10;

Добавляйте LIMIT для категорий с большим количеством значений. Круговые диаграммы с 20+ слайсами становятся нечитаемыми; ограничивайте до верхних 10–15 категорий.

Tables

Таблицы принимают любое количество столбцов любых типов данных. Выберите столбцы, которые хотите отобразить:

Детали недавних поездок

SELECT 
  device_id,
  start_time,
  end_time,
  distance_km,
  duration_minutes,
  max_speed_kmh
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY start_time DESC
LIMIT 100;

Имена столбцов становятся заголовками таблицы. Используйте псевдонимы с пробелами для читабельных заголовков: distance_km as "Distance (km)".

Текстовые панели

Текстовые панели отображают одиночные текстовые значения или форматированные строки. Запросы должны возвращать один текстовый столбец:

Метка времени последнего обновления данных

SELECT 
  'Last updated: ' || MAX(record_added_at)::text as value
FROM bronze.tracking_data_core;

Запросы для отчётов следуют тем же структурным правилам, что и запросы визуализаций в панелях. Поскольку один запрос питает таблицу данных, диаграмму и карту местоположений одновременно, вам может понадобиться объединить столбцы, которые в дашборде были бы написаны как отдельные запросы для отдельных панелей. Например, запрос панели столбчатой диаграммы, возвращающий два столбца, недостаточен для отчёта, которому также нужны GPS-координаты для карты. Включите все требуемые столбцы для каждого компонента в один запрос. Базовая логика фильтрации и JOIN остаётся такой же, как в запросах панелей; необходимо расширить только SELECT.

Как писать SQL для отчётов

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

Требования к столбцам по компонентам

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

Компонент	Требуемые столбцы	Примечания
Таблица данных	Любые столбцы	Все возвращаемые столбцы отображаются как столбцы таблицы
Диаграмма	Как минимум один столбец времени или категории, как минимум один числовой столбец	Столбцы осей выбираются в настройках диаграммы
Карта местоположений	Широта и долгота в десятичных градусах	Dashboard Studio автоматически определяет столбцы с координатами

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

Комбинирование компонентов в одном запросе

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

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

SELECT
    t.device_id,
    o.object_label,
    t.device_time,
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed::float / 100 AS speed
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 >= NOW() - INTERVAL '24 hours'
ORDER BY t.device_time DESC
LIMIT 1000

В этом запросе device_time и speed служат для диаграммы, latitude и longitude служат для карты местоположений, и все столбцы отображаются в таблице данных.

Исходные таблицы телематики хранят координаты и скорость как масштабированные целые числа. Координаты делятся на 10 000 000 (10⁷) для конвертации в десятичные градусы, а скорость делится на 100 (10²) для конвертации в км/ч. Применяйте эти преобразования в любом запросе, который читает из raw_telematics_data таблиц.

Адаптация запросов панелей дашборда для отчётов

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

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

Если запрос панели — это запрос столбчатой диаграммы или stat tile, возвращающий агрегированные результаты, вероятно, ему не хватает детальных строковых данных, необходимых для таблицы и карты. В этом случае удалите агрегацию и работайте с исходными данными или данными слоя Silver.

SQL Recipe Book содержит готовые примеры запросов для типичных анализов автопарка. Рецепты из книги можно адаптировать для отчётов, добавив столбцы координат в местах, где нужна карта. Базовая логика WHERE и JOIN переносится напрямую; изменяйте только SELECT, чтобы охватить все необходимые компоненты.

Как использовать глобальные переменные

Глобальные переменные предоставляют повторно используемые значения в нескольких SQL-запросах. Определите переменные в Settings > Configuration > Global Variables, затем обращайтесь к ним с помощью ${variable_name} синтаксиса.

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

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

SELECT 
  DATE_TRUNC('day', start_time)::date as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= '${analysis_start_date}'::date
  AND start_time < '${analysis_end_date}'::date
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY category;

Переменные хранят текстовые значения. Приводите их к соответствующим типам в SQL: '${variable_name}'::date для дат, '${variable_name}'::integer для чисел.

Для параметров специфичных для запроса, которые часто меняются, можно использовать блоки параметров CTE в начале:

WITH params AS (
  SELECT 
    5 as min_idle_minutes,
    10 as max_idle_speed_kmh,
    '${analysis_start_date}'::date as date_from,
    '${analysis_end_date}'::date as date_to
)

SELECT 
  device_id,
  COUNT(*) as idle_count,
  SUM(duration_minutes) as total_idle_minutes
FROM silver.idle_events e
CROSS JOIN params p
WHERE e.event_time >= p.date_from
  AND e.event_time < p.date_to
  AND e.speed_kmh <= p.max_idle_speed_kmh
  AND e.duration_minutes >= p.min_idle_minutes
GROUP BY device_id
ORDER BY total_idle_minutes DESC;

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

Как получить доступ к схемам IoT Query

IoT Query организует данные в слоях Bronze, Silver и Gold. Понимание того, какой слой использовать, экономит время и повышает ясность SQL. Для полных сведений о схемах смотрите IoT Query Schema Overview.

Слой Bronze содержит необработанные точки отслеживания от устройств: bronze.tracking_data_core хранит каждую GPS-позицию с отметками времени, координатами и показаниями сенсоров. Используйте Bronze для анализа на уровне точек или когда нужны необработанные значения сенсоров, не обработанные в верхних слоях.

Слой Silver предоставляет обработанные сущности: silver.trips агрегирует точки отслеживания в записи поездок с временем начала/окончания, расстоянием и продолжительностью. silver.zone_visits записывает моменты входа и выхода устройств из геозон. silver.idle_events определяет периоды, когда транспортные средства остаются неподвижными с работающим двигателем. Используйте Silver для большинства нужд визуализации, так как он предоставляет структуры, готовые к анализу.

Слой Gold предлагает предварительно агрегированные метрики и размерные модели для сложной аналитики. Используйте Gold для статистики по всему парку или многомерного анализа, который потребовал бы сложных JOIN-ов к таблицам Silver.

Ссылайтесь на таблицы в формате schema.table : silver.trips, а не просто trips. Включайте фильтры по диапазонам дат в WHERE, чтобы ограничить объём просматриваемых данных:

Всегда фильтруйте по временным диапазонам

SELECT device_id, COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

Большинство SQL-запросов фильтруют по устройству, диапазону дат или по обоим параметрам. Добавляйте эти фильтры в начале WHERE, чтобы уменьшить объём обрабатываемых данных.

Как использовать SQL Editor

Откройте SQL Editor в левой боковой панели в разделе Tools. Используйте его для трёх основных задач: тестирования логики перед добавлением в панели, изучения схем данных для понимания доступных столбцов и экспорта данных, которые не требуют визуализации.

SQL Editor поддерживает несколько вкладок для разных запросов. Пишите SQL на вкладках, выполняйте нажатием кнопки "Execute Query" и просматривайте результаты в таблице ниже. Результаты показывают метрики выполнения (время выполнения, возвращенные строки) и поддерживают сортировку столбцов для быстрого анализа данных.

Экспортируйте результаты в CSV с помощью кнопки "Export CSV". Это полезно для разовых отчётов или выгрузок данных для внешнего анализа. В SQL Editor нет ограничения на количество строк результата, в отличие от SQL для визуализаций, который должен возвращать сфокусированные наборы данных.

Тестируйте SQL для визуализаций в SQL Editor перед добавлением в панели. Напишите запрос, убедитесь, что он возвращает ожидаемые столбцы и типы данных, затем скопируйте его на вкладку SQL Query редактора панели. Этот рабочий процесс позволяет выявить структурные проблемы до настройки параметров визуализации.

Шаблон исследования новых данных:

-- 1. Изучите структуру таблицы
SELECT * FROM silver.trips LIMIT 10;

-- 2. Проверьте покрытие диапазона дат
SELECT 
  MIN(start_time) as earliest,
  MAX(start_time) as latest,
  COUNT(*) as total_trips
FROM silver.trips;

-- 3. Протестируйте логику фильтрации
SELECT 
  device_id,
  start_time,
  distance_km
FROM silver.trips
WHERE start_time >= '2024-01-01'
  AND device_id = 12345
ORDER BY start_time;

-- 4. Адаптируйте для визуализации (2 столбца для столбчатой диаграммы)
SELECT 
  DATE_TRUNC('day', start_time)::date as day,
  COUNT(*) as trips
FROM silver.trips
WHERE start_time >= '2024-01-01'
  AND device_id = 12345
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY day;

Распространённые SQL-шаблоны

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

Подсчёты временных рядов для отслеживания трендов

SELECT 
  DATE_TRUNC('hour', start_time) as time_bucket,
  COUNT(*) as event_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', start_time)
ORDER BY time_bucket;

Ранжирование по категориям для сравнения групп

SELECT 
  category_column,
  COUNT(*) as count
FROM schema.table
WHERE filter_conditions
GROUP BY category_column
ORDER BY count DESC
LIMIT 15;

Вычисление метрик для агрегированной статистики

SELECT 
  SUM(distance_km) as total_distance,
  AVG(duration_minutes) as avg_duration,
  COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= DATE_TRUNC('week', CURRENT_DATE);

Фильтрованные сводки с несколькими условиями

SELECT 
  device_id,
  COUNT(*) as trips,
  SUM(distance_km) as total_km
FROM silver.trips
WHERE start_time >= '${period_start}'::date
  AND start_time < '${period_end}'::date
  AND distance_km >= 5
  AND duration_minutes >= 10
GROUP BY device_id
HAVING COUNT(*) >= 5
ORDER BY total_km DESC;

Что делать, когда SQL не выполняется

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

Несоответствие структуры столбцов

Возникает, когда результаты не соответствуют ожиданиям визуализации. Если вы выбрали столбчатую диаграмму, а ваш SQL возвращает три столбца, Dashboard Studio не сможет отобразить её. Проверьте Dataset Requirements на вкладке SQL Query. Для столбчатой диаграммы требуется ровно два столбца (категория, значение), поэтому скорректируйте SELECT:

-- Неправильно: три столбца
SELECT device_id, start_time, COUNT(*) FROM silver.trips GROUP BY device_id, start_time;

-- Правильно: два столбца
SELECT device_id, COUNT(*) as trips FROM silver.trips GROUP BY device_id;

Ошибки синтаксиса SQL

Показывают конкретные сообщения об ошибках. Частые проблемы включают отсутствие префиксов схем (trips вместо silver.trips), опечатки в именах столбцов или некорректное приведение дат. Тестируйте запросы в SQL Editor, чтобы увидеть подробные сообщения об ошибках с номерами строк.

Пустые результаты

Даже успешное выполнение может означать, что фильтры исключают все данные. Выполните SQL без WHERE в SQL Editor, чтобы убедиться, что таблица содержит данные, затем добавляйте фильтры по одному, чтобы определить, какое условие исключает ожидаемые результаты.

Проблемы с производительностью

Если запросы выполняются медленно или истекает время ожидания, добавьте фильтры по диапазону дат в WHERE. Операции, просматривающие целые таблицы, обрабатывают миллионы строк без необходимости:

-- Медленно: без фильтра по дате
SELECT device_id, COUNT(*) FROM silver.trips GROUP BY device_id;

-- Быстро: фильтр по диапазону дат
SELECT device_id, COUNT(*) 
FROM silver.trips 
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

Для дополнительных рекомендаций по производительности см. Как получить доступ к схемам IoT Query по лучшим практикам фильтрации и выбору схем.

Где найти примеры SQL

Раздел SQL Recipe Book предоставляет полные примеры для типичных телематических анализов. Эти рецепты демонстрируют шаблоны для анализа поездок, расчёта посещений зон, обнаружения простоя и метрик автопарка. Каждый рецепт включает полный SQL-запрос, объяснение логики и примеры результатов.

Адаптируйте примеры из Recipe Book для визуализаций, изменив SELECT, чтобы соответствовать требованиям визуализации. Рецепт, возвращающий детализированные записи поездок, можно превратить в столбчатую диаграмму, добавив GROUP BY и агрегирование COUNT. Запрос, вычисляющий метрики по автомобилю, может стать stat tile, если суммировать значения по всем транспортным средствам.

Вам нужно лишь:

1. Скопировать примеры из Recipe Book в Editor Dashboard Studio.

2. Тестировать на ваших реальных данных.

3. Проверить результаты, затем изменить SELECT для целевой визуализации.

Базовая логика WHERE и JOIN остаётся прежней; изменяйте только структуру вывода.

Для сведений о схемах смотрите IoT Query Schema Overview. Этот справочник объясняет доступные таблицы, определения столбцов и взаимосвязи между слоями Bronze, Silver и Gold.