SQL параметры

Вы можете создавать шаблоны SQL, добавляя переменные в ваши SQL-запросы в Native/SQL editor. Эти переменные будут создавать виджеты фильтров, которые можно использовать для изменения значения переменной в запросе. Вы также можете добавить параметры к URL-адресу вашего вопроса, чтобы установить значения фильтров, чтобы при загрузке вопроса эти значения были вставлены в переменные.

Переменные

Определение переменных

Набрав {% raw %}{{variable_name}}{% endraw %} в вашем собственном запросе, вы также автоматически создаете переменную с именем variable_name.

Фильтр для полей (особый тип фильтра) имеет немного другой синтаксис.

В этом примере определяется текстовая переменная с названием cat:

SELECT count(*)
FROM products
WHERE category = {% raw %}{{cat}}{% endraw %}

GlarusBI прочитает переменную и прикрепит к запросу виджет фильтра, который пользователи смогут использовать для изменения значения, вставленного в переменную cat с кавычками. Таким образом, если кто-то введет «Gizmo» в виджет фильтра, запрос будет выглядеть так:

SELECT count(*)
FROM products
WHERE category = 'Gizmo'

Если вы пишете нативный запрос MongoDB, он будет выглядеть примерно так: переменная cat будет определена внутри предложения match

{% raw %}[{ $match: { category: {{cat}} } }]{% endraw %}

Присвоение значений переменным SQL

Чтобы присвоить переменной SQL значение, вы можете:

  • ввести значение в виджет фильтра и повторно задать вопрос, или

  • добавить параметр к URL и перезагрузить страницу.

Чтобы добавить значение к URL-адресу, следуйте этому синтаксису:

?variable_name=value

Например, чтобы присвоить переменной в вопросе {% raw %}{{cat}}{%endraw%} значение “Gizmo”, ваш URL-адрес будет выглядеть примерно так:

https://glarusbi.example.com/question/42-eg-question?cat=Gizmo

Чтобы присвоить значения нескольким переменным, разделите параметры с помощью &:

https://glarusbi.example.com/question/42-eg-question?cat=Gizmo&maxprice=50

Типы переменных в SQL

Когда вы определяете переменную, появляется боковая панель Переменные. Вы можете установить тип для переменной, которая изменяет тип виджета фильтра, предоставляемого GlarusBI.

Существует четыре типа переменных:

  • Text: простое поле ввода.

  • Number: простое поле ввода.

  • Date: простой выбор даты. Если вам нужен более сложный выбор даты, например указание диапазона, вам нужно использовать фильтр полей.

  • Фильтр полей: различные виджеты фильтров, в зависимости от отображаемого поля.

Последний тип переменной, Фильтр полей, является особенным: он позволяет создавать «умные» виджеты фильтров, такие как окно поиска, раскрывающееся меню значений или динамическое средство выбора даты, которое позволяет указать диапазон дат.

Вы можете включить в запрос несколько переменных, и GlarusBI добавит к вопросу несколько виджетов фильтров. Если у вас есть несколько виджетов фильтров, вы можете щелкнуть виджет фильтра и, перетащив его, изменить порядок.

Необязательные классы

Вы можете сделать класс необязательным в запросе. Например, вы можете создать необязательное предложение WHERE, содержащее переменную SQL, так что, если переменной не будет присвоено значение (либо в фильтре, либо через URL-адрес), запрос все равно будет выполняться, как если бы вообще не было класса WHERE.

Чтобы сделать элемент необязательным в собственном запросе, введите [[скобки вокруг {% raw %}{{variable}}{% endraw %}]]. Если вы введете значение в виджете фильтра для variable, то текст целиком будет помещен в шаблон; в противном случае GlarusBI его просто проигнорирует.

В этом примере, если для cat не задано значение, запрос просто выберет все строки из таблицы products. Но если у cat действительно есть значение, например “Widget”, тогда запрос будет захватывать только продукты с типом категории Widget:

SELECT count(*)
FROM products
[[WHERE category = {% raw %}{{cat}}{% endraw %}]]

Чтобы использовать несколько необязательных классов, вы должны включить хотя бы одно обычное предложение WHERE, за которым следуют необязательные предложения, каждое из которых начинается с AND:

SELECT count(*)
FROM products
WHERE true
  [[AND id = {% raw %}{{id}}{% endraw %}]]
  [[AND {% raw %}{{category}}{% endraw %}]]

В этом последнем классе используется фильтр полей (обратите внимание на отсутствие столбца в предложении AND). При использовании фильтра поля необходимо исключить столбец из запроса; вам нужно сопоставить переменную на боковой панели.

Если вы используете MongoDB, вы можете сделать класс необязательным следующим образом:

{% raw %}
[
    [[{
        $match: {category: {{cat}}}
    },]]
    {
        $count: "Total"
    }
]
{% endraw %}

Или с несколькими дополнительными фильтрами:

{% raw %}
[
    [[{ $match: {{cat}} },]]
    [[{ $match: { price: { "$gt": {{minprice}} } } },]]
    {
        $count: "Total"
    }
]
{% endraw %}

Установка значения по умолчанию в виджете фильтра

На боковой панели вы можете установить значение по умолчанию для вашей переменной. Это значение будет вставлено в соответствующий виджет фильтра по умолчанию (даже если виджет фильтра пуст). Вам нужно будет вставить новое значение в виджет фильтра, чтобы переопределить значение по умолчанию.

Установка сложных значений по умолчанию в запросе

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

WHERE column = [[ {% raw %}{{ your_parameter }}{% endraw %} #]]your_default_value

Это полезно при определении сложных значений по умолчанию (например, если вашим значением по умолчанию является функция CURRENT_DATE).

Вот пример на PostgreSQL, который устанавливает значение по умолчанию для фильтра даты равным текущей дате с помощью CURRENT_DATE():

SELECT *
FROM accounts
{% raw %}WHERE DATE(created_at) = [[ {{dateOfCreation}} #]]CURRENT_DATE{% endraw %}

Обратите внимание, что хэш (#), используемый для комментирования текста, может потребоваться заменить синтаксисом комментария, характерным для используемой вами базы данных. Некоторые базы данных используют двойное тире (--) в качестве синтаксиса комментариев.

Тип переменной «Фильтр поля»

Установка для переменной типа «Фильтр поля» позволяет сопоставить переменную с полем в любой таблице в текущей базе данных и создать виджет «умного» фильтра, который имеет смысл для этого поля.

Переменные Field Filter следует использовать внутри класса WHERE в SQL или класса $match в MongoDB.

Типы, совместимые с фильтром поля

Фильтры полей работают ТОЛЬКО со следующими типами полей:

  • Категория

  • Имя сущности

  • Ключ объекта

  • Внешний ключ

  • Город

  • Состояние

  • Почтовый индекс

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

Когда вы устанавливаете Тип переменной на «Фильтр поля», GlarusBI предоставит возможность установить Поле для сопоставления, а также Тип виджета фильтра. Параметры, доступные для типа виджета «Фильтр», зависят от типа поля. Например, если вы сопоставляете поле типа «Категория», вы увидите варианты «Категория» или «Нет». Если вы сопоставляете поле даты, вы увидите варианты «Нет», «Месяц и год», «Квартал и год», «Одна дата», «Диапазон дат» или «Фильтр дат».

Если вы не видите возможность отображать виджет фильтра, убедитесь, что для сопоставленного поля задан один из указанных выше типов, а затем попробуйте вручную синхронизировать свою базу данных из раздела «Базы данных» панели администратора, чтобы заставить GlarusBI сканировать и кэшировать значения поля.

Если вы хотите сопоставить фильтр поля с полем, которое не относится к одному из совместимых типов, перечисленных выше, вам потребуется администратор, чтобы изменить тип поля для этого столбца. См. редактирование метаданных.

Синтаксис фильтра поля

Допустим, вы хотите создать фильтр полей, который фильтрует таблицу people по штату, и вы хотите, чтобы люди могли выбирать несколько штатов одновременно. Вот запрос:

Синтаксис для фильтров поля отличается от переменной Text, Number или Date.

SELECT *
FROM PEOPLE
WHERE {%raw%}{{state}}{%endraw%}

Затем на боковой панели выберите тип переменной «Фильтр поля» и выберите, какое поле сопоставить вашей переменной (в данном случае state).

Обратите внимание на отсутствие столбца и оператора (например, =). Причина, по которой вам нужно структурировать фильтры полей таким образом, заключается в том, чтобы обрабатывать случаи, когда GlarusBI генерирует код для вас. Например, для обработки случаев, когда кто-то выбирает несколько значений в виджете фильтра или диапазон дат. С помощью фильтров полей вы не можете контролировать сгенерированный SQL, поэтому, если вам нужен больший контроль, вы должны использовать одну (или несколько) переменную Text, Number или Date.

Пример нативного запроса MongoDB может выглядеть следующим образом:

{% raw %}[ {$match: {{date_var}} } ]{% endraw %}

Более подробное руководство см. в разделе Фильтры полей: создание виджетов смарт-фильтров для вопросов SQL.

Как создавать различные типы виджетов фильтров

Тип виджета фильтра, который GlarusBI отображает при создании виджета Field Filter, зависит от настройки этого поля в GlarusBI, которая называется Фильтрация этого поля. Администраторы могут установить для этого параметра поля следующие значения:

  • Простое поле ввода

  • Поисковая строка

  • Список всех значений (также известный как выпадающее меню)

Поля даты будут иметь либо простой фильтр даты (для переменных), либо динамическое средство выбора даты (для фильтров поля, сопоставленных с полем даты).

Если вы хотите изменить виджет фильтра для определенного поля, вам нужно попросить администратора обновить это поле в метаданных таблицы и установить опцию «Фильтрация этого поля».

Виджет фильтра с простым полем ввода

Создайте простую переменную Text или Number. Кроме того, вы можете использовать фильтр полей с полем, для которого значение Фильтрация этого поля установлено на «Обычное поле ввода».

Примечание. Для защиты от атак путем внедрения SQL-кода GlarusBI преобразует содержимое поля поиска в строку. Если вы хотите использовать подстановочные знаки, ознакомьтесь с нашей статьей Обучение.

Виджет фильтра с окном поиска

  • Включите переменную SQL в свой запрос.

  • Установите для параметра Тип переменной значение Фильтр поля.

  • Установите Поле для сопоставления с полем типа «Категория», для которого параметр Фильтрация этого поля установлен на «Поле поиска».

Виджет фильтра с выпадающим меню и поиском

Чтобы создать выпадающее меню с поиском и списком всех значений:

  • Включите переменную SQL в свой запрос.

  • Установите для параметра Тип переменной значение Фильтр поля.

  • Установите Поле для сопоставления с полем типа «Категория», для которого параметр Фильтрация этого поля установлен на «Список всех значений».

  • Установите Тип виджета фильтра на «Категория».

Если для поля, для которого вы хотите создать раскрывающийся список, не задан тип «Категория», а для параметра Фильтрация этого поля задано значение «Список всех значений», администратору потребуется обновить настройки для этого поля. Например, если вы хотите создать раскрывающееся меню для несовместимого типа поля, такого как поле электронной почты, администратору потребуется изменить тип этого поля на «Категория», установить для параметра Фильтрация этого поля значение Список всех значений, затем повторно загрузить значения для этого поля.

Однако, если в этом столбце слишком много разных значений для отображения в раскрывающемся меню, GlarusBI вместо этого просто отобразит окно поиска. Поэтому, если у вас много адресов электронной почты, вы все равно можете просто получить окно поиска. Виджеты раскрывающегося меню работают лучше, когда есть небольшой набор значений для выбора (например, пятьдесят штатов США).

Ограничения на фильтры полей

Некоторые вещи, которые могут помешать вам настроить переменную фильтра поля.

Псевдонимы таблиц

Вы не сможете выбирать значения из фильтров полей в запросах, которые используют псевдонимы таблиц для объединений или CTE.

Причина этого заключается в том, что фильтры полей генерируют SQL на основе сопоставленного поля; GlarusBI не анализирует SQL, поэтому он не может определить, на что ссылается псевдоним. У вас есть три варианта обходных путей, в зависимости от сложности вашего запроса.

  1. Использовать полное имя таблицы.

  2. Заменить CTE на подзапросы.

  3. Создать представление в вашей базе данных и использовать его в качестве основы для вашего запроса.

Включите зависимости в свой запрос

Ваш основной запрос должен знать обо всех таблицах, на которые указывает ваша переменная Фильтр Поля, иначе вы получите синтаксическую ошибку SQL. Например, предположим, что ваш основной запрос включает в себя такой фильтр полей:

SELECT *
FROM ORDERS
WHERE {% raw %}{{ product_category }}{% endraw %}

Допустим, переменная {% raw %}{{ product_category }}{% endraw %} относится к другому вопросу, который использует таблицу Products. Чтобы фильтр полей работал, вам необходимо включить соединение с Products в ваш основной запрос.

SELECT *
FROM ORDERS
JOIN PRODUCTS
ON ORDERS.product_id = PRODUCTS.id
WHERE {% raw %}{{ product_category }}{% endraw %}

Синтакс SQL

Убедитесь, что ваш SQL-диалект соответствует выбранной базе данных. Общие ошибки:

| База данных | Делайте так | Не делайте так | |————|—————————-|———————-| | BigQuery | FROM `dataset.table` | FROM dataset.table | | Oracle | FROM "schema"."table" | FROM schema.table |

Для дополнительной информации см. Устранение неполадок сообщений об ошибках SQL.

Подключение вопроса SQL к фильтру дашборда

Чтобы сохраненный вопрос SQL/собственный вопрос можно было использовать с фильтром дашборда, вопрос должен содержать хотя бы одну переменную.

Тип фильтра дашборда, который можно использовать с вопросом SQL, зависит от поля. Например, если у вас есть фильтр поля под названием {% raw %}{{var}}{% endraw %} и вы сопоставляете его с полем состояния, вы можете сопоставить фильтр дашборда местоположения со своим вопросом SQL. В этом примере вы должны создать новый дашборд (или перейти к существующему), щелкнуть значок карандаша, чтобы войти в режим редактирования дашборда, добавить вопрос SQL, содержащий переменную фильтра поля, добавить новый фильтр дашборда (или изменить существующий фильтр), затем щелкните раскрывающийся список на карточке вопроса SQL, чтобы увидеть фильтр поля.

Если вы добавите в вопрос переменную Дата, то можно будет использовать только параметр фильтра дашборда Одна дата. Поэтому, если вы пытаетесь использовать один из других параметров времени на дашборде, вам нужно изменить переменную на переменную Фильтр Поля и сопоставить ее с датой

Фильтр Полей

Больше о Фильтры Дашбордов.

Еще почитать?

Далее: Ссылки на сохраненные вопросы в запросах

Узнайте, как ссылаться на сохраненный вопрос в SQL-запросе.