Написание 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 не может отобразить столбчатую диаграмму из трех столбцов или статистический тайл из текстовых данных. Перед написанием запроса проверьте раздел 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 отображают одиночные числовые значения. Запросы должны возвращать ровно одну строку с одним числовым столбцом:

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

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 чтобы контролировать порядок столбцов. Сортируйте по value для ранжированных сравнений или по category для временных рядов.

Круговые диаграммы

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)".

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

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

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

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

Запросы для отчетов следуют тем же структурным правилам, что и SQL для визуализаций в панелях дашборда. Поскольку одно выражение питает таблицу данных, диаграмму и карту местоположений одновременно, вам может понадобиться объединить столбцы, которые в дашборде писались бы как отдельные запросы для панелей. Например, запрос панели столбчатой диаграммы, возвращающий два столбца, недостаточен для отчета, которому также нужны 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 организует данные в слоя Raw data, Transformation и Insight. Понимание того, какой слой использовать, экономит время и улучшает ясность SQL. Для полного описания схем см. IoT Query Schema Overview.

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

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

Слой Insight предлагает предварительно агрегированные метрики и размерные модели для сложной аналитики. Используйте Insight для статистики по всему парку или многомерного анализа, который потребовал бы сложных соединений со 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

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

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

Вам нужно всего лишь:

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

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

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

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

Для деталей по схемам см. IoT Query Schema Overview. В этом справочнике объясняются доступные таблицы, определения столбцов и взаимосвязи между слоями Raw data, Transformation и Insight.