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

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

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

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

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

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

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

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

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

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

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

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

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

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

Глобальные переменные обеспечивают повторно используемые значения в нескольких 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 layer содержит сырые трековые точки от устройств: bronze.tracking_data_core хранит каждую GPS-позицию с временными метками, координатами и показаниями датчиков. Используйте Bronze для анализа на уровне точек или когда требуются необработанные значения датчиков, не агрегированные в более высокие уровни.

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

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

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

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

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

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

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

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