# Написание SQL-запросов Dashboard Studio использует SQL для извлечения данных из схем IoT Query. Вы пишете SQL в двух контекстах: в редакторах панелей, где запросы обеспечивают визуализации, и в автономном SQL Editor для исследования данных. Эта страница объясняет, как писать эффективный SQL для обоих контекстов, с особым упором на требования к визуализации, поскольку они имеют специфические структурные ограничения. ### Где используется SQL Dashboard Studio предоставляет две SQL-среды для разных целей. Понимание, когда использовать каждую из них, помогает работать эффективнее. [**Запросы для визуализации**](#how-to-write-sql-for-visualizations) питают отдельные панели в отчетах. Эти выражения вы пишете во вкладке **SQL Query** редактора панели. Каждая панель выполняет одно выражение, которое должно возвращать данные в конкретной структуре, соответствующей типу визуализации. Эти выражения выполняются при загрузке или обновлении отчетов, поэтому производительность влияет на пользовательский опыт. SQL для визуализаций не может изменять данные; все выражения выполняются в режиме только для чтения как SELECT-операции против схем IoT Query. **Отчеты** используют тот же подход к SQL для визуализации, что и панели дашборда. Отчет выполняет один запрос, который одновременно питает три представления: таблицу данных, диаграмму и карту местоположений. Выражение должно возвращать все столбцы, необходимые для всех трех компонентов, поэтому включите координатные, временные и метрические столбцы вместе в один SELECT. [**SQL Editor**](#how-to-use-the-sql-editor) поддерживает исследование данных и экспорт. Откройте SQL Editor в левой боковой панели в разделе Tools. Пишите любые SELECT-выражения для изучения структуры данных, проверки предположений или экспорта результатов в CSV. SQL Editor показывает полные таблицы результатов с сортировкой столбцов и предоставляет метрики выполнения. Используйте его для тестирования логики перед добавлением SQL в панели визуализации или для разовых извлечений данных, которые не требуют визуализации. {% hint style="info" %} **Ключевое различие**: SQL для визуализаций должен соответствовать точной структуре столбцов, в то время как выражения в SQL Editor могут возвращать любой формат результатов. Сначала тестируйте сложную логику в SQL Editor, затем адаптируйте её для визуализаций. {% endhint %} ### Как писать SQL для визуализаций
SQL для визуализаций должен возвращать определенное количество столбцов и типы данных. Dashboard Studio не может отобразить столбчатую диаграмму из трех столбцов или статистический тайл из текстовых данных. Перед написанием запроса проверьте раздел Dataset Requirements на вкладке SQL Query, чтобы точно увидеть, чего ожидает выбранная визуализация. В таблице ниже перечислены поддерживаемые типы визуализаций: | Визуализация | Требование к запросу | Пример | | ------------------------ | -------------------------------- | ----------------------------------------------------------------- | | [Stat tile](#stat-tiles) | Одиночное числовое значение | `SELECT COUNT(*) FROM schema.table` | | [Bar chart](#bar-charts) | Два столбца: категория, значение | `SELECT column1, COUNT(*) FROM schema.table GROUP BY column1` | | [Pie chart](#pie-charts) | Два столбца: метка, значение | `SELECT category, SUM(value) FROM schema.table GROUP BY category` | | [Table](#tables) | Любые столбцы | `SELECT column1, column2, column3 FROM schema.table` | | [Text](#text-panels) | Запрос не требуется | Только содержимое в Markdown |
Статистические плитки Stat tiles отображают одиночные числовые значения. Запросы должны возвращать ровно одну строку с одним числовым столбцом: {% code title="Общее число поездок в текущем месяце" overflow="wrap" %} ```sql SELECT COUNT(*) as value FROM silver.trips WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE); ``` {% endcode %} {% code title="Общее пройденное расстояние (км)" overflow="wrap" %} ```sql SELECT SUM(distance_km) as value FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '7 days'; ``` {% endcode %} Название столбца не имеет значения, важно лишь то, что результат — одиночное числовое значение. Dashboard Studio отображает это значение с форматированием, которое вы настраиваете в Visualization Settings.
Столбчатые диаграммы Bar charts требуют ровно два столбца: категория (текст или дата) и значение (числовое). Первый столбец становится осью X, второй — высотой столбцов: {% code title="Поездки по типам транспортных средств" overflow="wrap" %} ```sql 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; ``` {% endcode %} {% code title="Ежедневное количество поездок" overflow="wrap" %} ```sql 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; ``` {% endcode %} Используйте `ORDER BY` чтобы контролировать порядок столбцов. Сортируйте по value для ранжированных сравнений или по category для временных рядов.
Круговые диаграммы Pie charts требуют ровно два столбца: метка (текст) и значение (числовое). Первый столбец становится подписью секций, второй определяет размеры секций: {% code title="Распределение поездок по зонам" %} ```sql 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; ``` {% endcode %} Добавляйте LIMIT для категорий с большим числом значений. Круговые диаграммы с 20+ секциями становятся нечитаемыми; ограничивайтесь топ-10–15 категориями.
Таблицы Tables принимают любое количество столбцов любых типов данных. Выберите столбцы, которые хотите отобразить: {% code title="Детали недавних поездок" %} ```sql 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; ``` {% endcode %} Имена столбцов становятся заголовками таблицы. Используйте псевдонимы с пробелами для удобочитаемых заголовков: `distance_km as "Distance (km)"`.
Текстовые панели Text panels отображают одиночные текстовые значения или форматированные строки. Запросы должны возвращать один текстовый столбец: {% code title="Отметка времени последнего обновления данных" %} ```sql SELECT 'Last updated: ' || MAX(record_added_at)::text as value FROM bronze.tracking_data_core; ``` {% endcode %}
Запросы для отчетов следуют тем же структурным правилам, что и SQL для визуализаций в панелях дашборда. Поскольку одно выражение питает таблицу данных, диаграмму и карту местоположений одновременно, вам может понадобиться объединить столбцы, которые в дашборде писались бы как отдельные запросы для панелей. Например, запрос панели столбчатой диаграммы, возвращающий два столбца, недостаточен для отчета, которому также нужны GPS-координаты для карты. Включите все требуемые столбцы для каждого компонента в одном выражении. Основная логика фильтрации и JOIN остается такой же, как в панельных запросах; изменяется только SELECT-часть, чтобы быть шире. ### Как писать SQL для отчетов Отчет выполняет один SQL-запрос, который одновременно питает три компонента: таблицу данных, диаграмму и карту местоположений. В отличие от панелей дашборда, где каждая панель имеет свой сфокусированный запрос, запрос отчета должен возвращать все столбцы, необходимые для каждого компонента, в одном SELECT-выражении. #### Требования к столбцам по компонентам Каждый компонент отчета имеет специфические требования к столбцам. Ваш запрос должен удовлетворять все включенные компоненты. | Компонент | Требуемые столбцы | Примечания | | -------------------- | ---------------------------------------------------------------------------------- | ---------------------------------------------------------------- | | Таблица данных | Любые столбцы | Все возвращаемые столбцы отображаются как столбцы таблицы | | Диаграмма | Как минимум один столбец времени или категории и как минимум один числовой столбец | Столбцы осей выбираются в настройках диаграммы | | Карта местоположений | Широта и долгота в десятичных градусах | Dashboard Studio автоматически определяет столбцы с координатами | Поскольку таблица данных принимает любые столбцы, дополнительных ограничений нет. Структурные решения в основном диктуют диаграмма и карта местоположений. #### Объединение компонентов в одном запросе Запрос, возвращающий только столбцы, необходимые для диаграммы (два столбца: категория и значение), не может одновременно питать карту местоположений. Вы должны включить все требуемые столбцы вместе. Следующий пример возвращает столбцы для всех трех компонентов: временной столбец и числовой столбец для диаграммы, координатные столбцы для карты местоположений и дополнительные атрибуты, которые появляются в таблице данных. ```sql 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` обслуживают карту местоположений, а все столбцы отображаются в таблице данных. {% hint style="info" %} Исходные телематические таблицы хранят координаты и скорость в виде масштабированных целых чисел. Координаты делятся на 10 000 000 (10⁷) для преобразования в десятичные градусы, а скорость делится на 100 (10²) для преобразования в км/ч. Применяйте эти преобразования в любом запросе, который читает из `raw_telematics_data` таблиц. {% endhint %} #### Адаптация панельных запросов дашборда для отчетов Любой панельный запрос из дашборда является корректной отправной точкой для отчета. Необходимая корректировка зависит от того, какие компоненты вы хотите включить. Если панельный запрос уже является таблицей и возвращает несколько столбцов, он может уже содержать всё необходимое. Добавьте столбцы с координатами, если требуется карта местоположений. Если панельный запрос — это запрос для столбчатой диаграммы или stat tile, возвращающий агрегированные результаты, ему, вероятно, не хватает детализированных строк, необходимых для таблицы данных и карты местоположений. В таком случае уберите агрегирование и работайте с исходными данными или данными уровня Silver. [SQL Recipe Book](https://www.navixy.com/docs/analytics/ru/example-queries) содержит готовые примеры запросов для типичных анализов автопарка. Рецепты из книги можно адаптировать для отчетов, добавляя столбцы с координатами там, где нужна карта местоположений. Основная логика WHERE и JOIN переносится напрямую; изменяйте только SELECT-часть, чтобы покрыть все требуемые компоненты. ### Как использовать глобальные переменные Глобальные переменные предоставляют повторно используемые значения для нескольких SQL-выражений. Определяйте переменные в **Settings > Configuration > Global Variables**, затем обращайтесь к ним с помощью `${variable_name}` синтаксиса.
Определяйте переменные для значений, которые периодически меняются, но остаются одинаковыми для нескольких панелей: диапазоны дат анализа, фильтры по типу транспортного средства или пороговые значения. Когда эти значения изменятся, обновите определение переменной один раз вместо редактирования отдельных SQL-выражений. {% code title="Использование переменных диапазона дат" %} ```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; ``` {% endcode %} Переменные хранят текстовые значения. Приводите их к соответствующим типам в SQL: `'${variable_name}'::date` для дат, `'${variable_name}'::integer` для чисел. Для параметров, специфичных для выражения, которые часто меняются, можно использовать блоки параметров CTE в начале: ```sql 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](https://www.navixy.com/docs/analytics/iotquery/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, чтобы ограничить объем сканируемых данных: {% code title="Всегда фильтруйте по временным диапазонам" %} ```sql SELECT device_id, COUNT(*) as trip_count FROM silver.trips WHERE start_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY device_id; ``` {% endcode %} Большинство SQL-выражений фильтруют по устройству, диапазону времени или по обоим критериям. Добавляйте эти фильтры в начало WHERE, чтобы уменьшить объем обрабатываемых данных. ### Как использовать SQL Editor Откройте SQL Editor в левой боковой панели в разделе Tools. Используйте его для трех основных целей: тестирования логики перед добавлением в панели, исследования схем данных, чтобы понять доступные столбцы, и экспорта данных, которые не нуждаются в визуализации.
SQL Editor поддерживает несколько вкладок для разных выражений. Пишите SQL во вкладках, выполняйте с помощью кнопки "Execute Query" и просматривайте результаты в таблице ниже. Результаты показывают метрики выполнения (время выполнения, число возвращенных строк) и поддерживают сортировку столбцов для быстрого изучения данных. Экспортируйте результаты в CSV с помощью кнопки "Export CSV". Это удобно для разовых отчетов или извлечений данных для внешнего анализа. SQL Editor не имеет ограничения на число строк результата, в отличие от SQL для визуализаций, который должен возвращать сфокусированные наборы данных. Тестируйте SQL для визуализаций в SQL Editor перед добавлением в панели. Напишите выражение, убедитесь, что оно возвращает ожидаемые столбцы и типы данных, затем скопируйте его во вкладку SQL Query редактора панели. Этот рабочий процесс обнаруживает структурные проблемы до настройки параметров визуализации. Паттерн исследования новых данных: {% code expandable="true" %} ```sql -- 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; ``` {% endcode %} ### Типичные SQL-паттерны Большинство 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; ```
Ранжирование категорий для сравнения групп ```sql SELECT category_column, COUNT(*) as count FROM schema.table WHERE filter_conditions GROUP BY category_column ORDER BY count DESC LIMIT 15; ```
Вычисление метрик для агрегированной статистики ```sql 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); ```
Отфильтрованные сводки с несколькими условиями ```sql 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: ```sql -- Неверно: три столбца 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. Операции, сканирующие целые таблицы, необоснованно обрабатывают миллионы строк: ```sql -- Медленно: без фильтра по дате 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](#how-to-access-iot-query-schemas) для лучших практик по фильтрации и выбору схем. ### Где найти примеры SQL The [SQL Recipe Book](https://www.navixy.com/docs/analytics/ru/example-queries) предоставляет полные примеры для распространенных телематических анализов. Эти рецепты демонстрируют шаблоны для анализа поездок, вычисления посещений зон, обнаружения простоя и метрик автопарка. Каждый рецепт включает полный SQL-запрос, объяснение логики и пример результатов. Адаптируйте примеры из Recipe Book для визуализаций, отрегулировав SELECT в соответствии с требованиями визуализации. Рецепт, возвращающий детализированные записи поездок, можно превратить в столбчатую диаграмму, добавив GROUP BY и COUNT-агрегацию. Запрос, вычисляющий метрики по каждому транспортному средству, можно превратить в stat tile, добавив SUM по всем транспортным средствам. Вам нужно всего лишь: 1. Скопировать примеры из [Recipe Book](https://www.navixy.com/docs/analytics/ru/example-queries) в редактор Dashboard Studio. 2. Тестировать на ваших реальных данных. 3. Проверить результаты, затем модифицировать SELECT для целевой визуализации. Основная логика WHERE и JOIN остается прежней; изменяйте только структуру вывода. Для деталей по схемам см. [IoT Query Schema Overview](https://www.navixy.com/docs/analytics/iotquery/schema-overview). В этом справочнике объясняются доступные таблицы, определения столбцов и взаимосвязи между слоями Raw data, Transformation и Insight.