# Написание 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-tiles) | Одно числовое значение | `SELECT COUNT(*) FROM schema.table` | | [Столбчатая диаграмма](#bar-charts) | Два столбца: категория, значение | `SELECT column1, COUNT(*) FROM schema.table GROUP BY column1` | | [Круговая диаграмма](#pie-charts) | Два столбца: метка, значение | `SELECT category, SUM(value) FROM schema.table GROUP BY category` | | [Таблица](#tables) | Любые столбцы | `SELECT column1, column2, column3 FROM schema.table` | | [Текст](#text-panels) | Запрос не требуется | Только содержимое Markdown |
Карточки-метрики Карточки-метрики отображают одно числовое значение. Операторы должны возвращать ровно одну строку с одним числовым столбцом: {% 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.
Столбчатые диаграммы Столбчатые диаграммы требуют ровно двух столбцов: категория (текст или дата) и значение (числовое). Первый столбец становится осью 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` для управления порядком столбцов. Сортируйте по значению для ранжированных сравнений или по категории для временных рядов.
Круговые диаграммы Круговые диаграммы требуют ровно двух столбцов: метка (текст) и значение (числовое). Первый столбец становится подписями секторов, второй определяет их размеры: {% 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 основными категориями.
Таблицы Таблицы принимают любое количество столбцов с любыми типами данных. Выберите столбцы, которые хотите отображать: {% 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)"`.
Текстовые панели Текстовые панели отображают одиночные текстовые значения или форматированные строки. Операторы должны возвращать один текстовый столбец: {% code title="Время последнего обновления данных" %} ```sql SELECT 'Last updated: ' || MAX(record_added_at)::text as value FROM bronze.tracking_data_core; ``` {% endcode %}
Запросы для отчетов подчиняются тем же структурным правилам, что и запросы визуализаций в панелях дашборда. Поскольку один оператор одновременно формирует таблицу данных, график и карту местоположений, вам может понадобиться объединить столбцы, которые в дашборде были бы написаны как отдельные запросы панелей. Например, запроса панели столбчатой диаграммы, возвращающего два столбца, недостаточно для отчета, которому для карты местоположений также нужны 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" %} Таблицы raw telematics хранят координаты и скорость в виде масштабированных целых чисел. Координаты делятся на 10 000 000 (10⁷), чтобы преобразовать их в десятичные градусы, а скорость делится на 100 (10²), чтобы преобразовать ее в км/ч. Применяйте эти преобразования в любом запросе, который читает из `raw_telematics_data` таблиц. {% endhint %} #### Как адаптировать запросы панелей дашборда для отчетов Любой запрос панели из дашборда является подходящей отправной точкой для отчета. Необходимая доработка зависит от того, какие компоненты вы хотите включить. Если запрос панели уже является визуализацией таблицы и возвращает несколько столбцов, он может уже содержать все необходимое. Добавьте столбцы координат, если требуется карта местоположений. Если запрос панели является запросом столбчатой диаграммы или карточки-метрики и возвращает агрегированные результаты, ему, вероятно, не хватает детализации по строкам, необходимой для таблицы данных и карты местоположений. В этом случае уберите агрегацию и работайте вместо этого с данными базового слоя Raw или Silver. [SQL Recipe Book](/docs/analytics/ru/example-queries.md) содержит готовые примеры запросов для распространенных аналитик автопарка. Рецепты из книги можно адаптировать для отчетов, добавив столбцы координат там, где нужна карта местоположений. Основная логика 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 для статистики по всему автопарку или многомерного анализа, который потребовал бы сложных JOIN с таблицами 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](/docs/analytics/ru/example-queries.md) содержит полные примеры для распространенных телематических анализов. Эти рецепты демонстрируют шаблоны для анализа поездок, расчетов посещений зон, выявления простоев и метрик автопарка. Каждый рецепт включает полный SQL-оператор, объяснение логики и примеры результатов. Адаптируйте примеры из Recipe Book для визуализаций, изменяя предложение SELECT в соответствии с требованиями визуализации. Рецепт, который возвращает подробные записи поездок, можно превратить в столбчатую диаграмму, добавив GROUP BY и агрегацию COUNT. Оператор, вычисляющий метрики по каждому транспортному средству, можно превратить в карточку-метрику, добавив SUM по всем транспортным средствам. Вам нужно только: 1. Скопировать примеры из [Recipe Book](/docs/analytics/ru/example-queries.md) в редактор Dashboard Studio. 2. Проверить их на ваших реальных данных. 3. Проверить результаты, затем изменить предложение SELECT для нужной визуализации. Основная логика WHERE и JOIN остается прежней; вы изменяете только структуру вывода. Сведения о схемах см. в [IoT Query Schema Overview](https://www.navixy.com/docs/analytics/iotquery/schema-overview). Этот справочник описывает доступные таблицы, определения столбцов и связи между слоями Raw data, Transformation и Insight. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://navixy.com/docs/analytics/ru/dashboard-studio/writing-sql-queries.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.