SQL-параметры

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

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

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

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

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

SELECT
  count(*)
FROM
  products
WHERE
  category = {{category}}

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

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

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

[{ $match: { category: {{cat}} } }]

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

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

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

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

?variable_name=value

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT
  *
FROM
  PEOPLE
WHERE
  {{state}}

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

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

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

[ {$match: {{date_var}} } ]

Более подробное руководство см. в разделе Фильтры полей: создание "умных" виджетов-фильтров для запросов SQL (документация Metabase, англ.).

Фильтры полей в BigQuery и Oracle

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

База данных	Особенность диалекта	Пример
BigQuery	Схемы и таблицы должны быть в обратных кавычках	FROM `dataset.table`
Oracle	Схемы и таблицы должны быть в двойных кавычках	FROM "schema"."table"

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

Настройка значений выпадающих списков и окон поиска

С помощью переменных Text и Field filter вы можете указать Glarus BI, какие значения люди могут выбирать при использовании фильтра с выпадающим списком или окном поиска.

1. В редакторе прямых запросов добавьте {{переменную}} в двойных фигурных скобках.
2. Если боковая панель не открывается, вы можете щелкнуть на иконку {x} справа, чтобы открыть боковую панель Переменные.
3. На вкладке Настройки установите Тип переменной на "Text" или "Фильтр поля".
4. В боковой панели перейдите к Как пользователи должны фильтровать по этой переменной? Выберите либо Выпадающий список, либо Окно поиска.
5. Рядом с выбранной опцией нажмите Редактировать.
6. Glarus BI откроет модальное окно, где вы можете выбрать Откуда должны браться значения.

Вы можете выбрать:

• Из связанных полей. Если вы выбрали тип переменной "Фильтр поля", у вас также будет возможность использовать связанное поле.
• Из другой модели или запроса. Если вы выберете эту опцию, вам нужно будет выбрать модель или запрос, затем поле из этой модели или запроса, которое Glarus BI будет использовать для предоставления значений для этого выпадающего списка или окна поиска. Например, если вы хотите, чтобы в выпадающем списке были перечислены различные планы, на которых может находиться учетная запись, вы можете выбрать созданную вами модель "Учетная запись" и выбрать поле "План" для заполнения этого выпадающего списка. В выпадающем списке будут перечислены все уникальные варианты планов, которые появляются в столбце "План" в модели "Учетные записи".
• Пользовательский список. Введите каждый элемент в отдельной строке. Вы можете ввести любые строковые значения, которые вам нравятся.

Вы также можете изменить выбираемые значения фильтра дашборда.

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

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

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

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

WHERE column = [[ {{ your_parameter }} --]] your_default_value

Комментарий будет "активирован" всякий раз, когда вы передаете значение в your_parameter.

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

SELECT
  *
FROM
  orders
WHERE
  DATE(created_at) = [[ {{dateOfCreation}} --]] CURRENT_DATE

Если вы передаете значение переменной, предложение WHERE выполняется, включая синтаксис комментария, который комментирует функцию по умолчанию CURRENT_DATE.

Обратите внимание, что дефисы (--), используемые для комментирования текста, могут потребовать замены на синтаксис комментариев, специфичный для используемой вами СУБД.

Требование значения для виджета фильтра

На боковой панели Переменные вы можете переключить опцию Всегда требовать значение. Если включите её:

• вы должны ввести значение по умолчанию;
• значение по умолчанию переопределит любой необязательный синтаксис в вашем коде (например, необязательное предложение WHERE). Если значение не передано в фильтр, Glarus BI выполнит запрос, используя значение по умолчанию. Нажмите на значок Глаз в редакторе, чтобы просмотреть SQL, который запустит Glarus BI.

Делаем выражения и переменные необязательными

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

Чтобы сделать переменную необязательной в вашем прямом запросе, добавьте [[ .. ]] скобки вокруг всего выражения, содержащего {{переменную}}. Если кто-то вводит значение в виджете фильтра для переменной, Glarus BI поместит предложение в шаблон; в противном случае Glarus BI проигнорирует предложение и выполнит запрос, как будто предложения не существует.

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

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

Ваш SQL также должен быть способен выполняться без необязательного предложения в [[ ]]

Вам необходимо убедиться, что ваш SQL всё ещё действителен, когда значение не передается переменной в предложении в скобках.

Например, исключение ключевого слова WHERE из предложения в скобках вызовет ошибку, если значение не задано для cat:

-- это вызовет ошибку:
SELECT
  count(*)
FROM
  products
WHERE
  [[category = {{cat}}]]

Это происходит потому, что когда значение не задано для cat, Glarus BI попытается выполнить SQL, как если бы предложения в [[ ]] не существовало:

SELECT
  count(*)
FROM
  products
WHERE

что не является допустимым SQL-запросом.

Вместо этого поместите все предложение WHERE в [[ ]]:

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

Когда значение не задано для cat, Glarus BI просто выполнит:

SELECT
  count(*)
FROM
  products

что всё ещё является допустимым запросом.

Вам нужно хотя бы одно WHERE при использовании нескольких необязательных предложений

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

SELECT
  count(*)
FROM
  products
WHERE
  TRUE
  [[AND id = {{id}}]]
  [[AND {{category}}]]

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

Необязательные переменные в MongoDB

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

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

Или с несколькими необязательными фильтрами:

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

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

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

Фильтры полей не работают с псевдонимами таблиц

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

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

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

Фильтры полей должны быть подключены к полям, включённым в запрос

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

SELECT *
FROM ORDERS
WHERE {{ product_category }}

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

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

Синтаксис SQL

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

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

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

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

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

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

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

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

Дополнительная информация

• Создать виджеты-фильтры для дашбордов, используя переменные SQL. Документация Metabase (англ.)
• Фильтры полей: создавайте «умные» виджеты-фильтры для запросов SQL. Документация Metabase (англ.)
• Устранение неполадок SQL
• Устранение неполадок в фильтрах
• Фильтры дашборда
• Базовый ввод. Документация Metabase (англ.)

---

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

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