SQL-параметры¶

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

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

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

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

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

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

Glarus BI прочитает переменную и прикрепит к запросу виджет фильтра, который пользователи смогут использовать для изменения значения, вставленного в переменную 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¶

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

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

Text: простое поле ввода.
Number: простое поле ввода.
Date: простой выбор даты. Если вам нужен более сложный выбор даты, например указание диапазона, вам нужно использовать фильтр полей.
Фильтр полей: различные виджеты фильтров в зависимости от отображаемого поля.

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

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

Необязательные выражения¶

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

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

В этом примере если для 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.

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

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

категория,
название сущности,
ключ объекта,
внешний ключ,
город,
состояние,
почтовый индекс.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

простое поле ввода;
поисковая строка;
список всех значений (также известный как выпадающее меню).

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

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

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

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

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

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

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

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

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

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

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

Однако, если в этом столбце слишком много разных значений для отображения в раскрывающемся меню, Glarus BI вместо этого просто отобразит окно поиска. Поэтому если у вас много адресов электронной почты, вы всё равно можете просто получить окно поиска. Виджеты раскрывающегося меню работают лучше, когда есть небольшой набор значений для выбора.

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

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

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

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

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

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

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

Ваш основной запрос должен «знать» обо всех таблицах, на которые указывает ваша переменная «Фильтр поля», иначе вы получите синтаксическую ошибку 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-запросе.