Sql переменные в запросе: MS SQL Server и T-SQL
Содержание
Оператор Let в Power BI и Power Query
Оператор Let в Power BI и Power Query
Текст представляет собой адаптированный перевод статьи Chris Webb (Крис Вебб),
оригинал – Understanding Let Expressions In M For Power BI And Power Query.
Рассматриваются англоязычные версии ПО.
Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.
Оператор Let в Power BI и Power Query
Когда вы начинаете писать код загрузки данных в Power Query или Power BI, то первым делом открываете пункт Advanced Editor для запроса, уже созданного в пользовательском интерфейсе.
Открыв редактор кода, мы увидим пугающий кусок кода (отсутствие цветовой кодировки делает его ещё более пугающим), который хотелось бы понять. Первый шаг на пути к пониманию – разобраться как работает оператор let.
Каждый запрос, создаваемый в Power BI Desktop или Power Query, это одно выражение, которое возвращает одну переменную/значение. Обычно, хотя и не всегда, это таблица, которую можно загрузить в модель данных. В качестве иллюстрации откроем Power BI Desktop (интерфейс во многом похож на Power Query), нажмём кнопку Edit Queries чтобы вызвать окошко button Query Editor и выберем New Source > Blank Query и создать новый запрос.
Переходим на вкладку View, щелкаем Advanced Editor, чтобы вызвать диалоговое окно Advanced Editor:
На самом деле это не совсем пустой запрос, т.к. кое-какой код присутствует. Удалите всё из окна редактора и введите следующее выражение:
"Hello " & "World"
Нажмите кнопку Done, в результате запрос вернёт текст «Hello World»:
Обратите внимание на значок ABC слева от имени Query1.
Он указывает, что запрос возвращает текстовое значение. Поздравляем, вы написали на языке М печально знаменитую программу «Hello World»!
Возможно вы гадаете, как страшный кусок кода реального запроса в редакторе может быть одним выражением. Но это действительно так. Именно для этого и нужен оператор let. Он позволяет разбить одно выражение на несколько частей. Откройте Advanced Editor и введите:
let step1 = 3, step2 = 7, step3 = step1 * step2 in step3
Даже ничего не зная о языке М, нетрудно догадаться, что данный код возвращает число 21 (значок 123 рядом с именем запроса указывает на возвращаемый тип):
В языке М оператор let состоит из двух секций. После let идёт список переменных, каждая из которых имеет имя и соответствующее ей выражение. В нашем примере это три переменных: step1, step2 и step3. Переменные могут ссылаться на другие переменные, в данном случае step3 ссылается на две step1 и step2.
Переменные могут хранить данные любого типа: числа, текст, даты, или даже сложных типов, такие, как записи, списки, таблицы. Все три наши переменные числового типа. Обычно Query Editor показывает переменные как шаги в панели Applied Steps на правой части экрана.
Значение, возвращаемое let, передаётся в секцию in. У нас in возвращает переменную step3, равную 21.
Важно понимать, что in может ссылаться на любую из списка переменных или ни на одну из них. Также важно понимать, что, хотя список переменных выглядит как код процедуры, он остаётся списком, где переменные могут идти в любом порядке. Пользовательский интерфейс будет всегда генерировать код, в котором каждая переменная/шаг опирается на значение, возвращаемое предыдущей переменной/шагом. Но, когда вы сами пишете код, переменные могут идти в любом порядке. Следующий код так же вернёт значение 21:
let step3 = step1 * step2, step2 = 7, step1 = 3 in step3
Секция in возвращает значение переменной step3, для вычисления которой требуются переменные step2 и step1.
Порядок переменных «неправильный» (в Applied Steps не отражаются имена всех переменных). Что важно, так это цепочка зависимостей, которая может быть восстановлена из секции in.
В этом примере запрос вернёт 7:
let step3 = step1 * step2, step2 = 7, step1 = 3 in step2
В данном случае из всего выражения в let требуется вычислить только переменную step2. Аналогично, запрос
let step3 = step1 * step2, step2 = 7, step1 = 3 in "Hello" & " World"
… вернёт текстовое значение «Hello World» и не потребует вычисления переменных step1, step2 или step3.
Стоит отметить, что если имена переменных содержат пробел, то их следует заключать в двойные кавычки и ставить хэш-символ # перед ними. В следующем запросе все имена переменных содержат пробелы в именах, возвращает он, как нетрудно догадаться, значение 21:
let #"this is step 1" = 3, #"this is step 2" = 7, #"this is step 3" = #"this is step 1" * #"this is step 2" in #"this is step 3"
Как всё это применить к запросам, сгенерированным пользовательским интерфейсом? Вот код запроса, который подключается к серверу SQL и получает отфильтрованные данные из таблицы DimDate базы данных Adventure Works DW:
let Source = Sql.Database("localhost", "adventure works dw"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each ([DayNumberOfWeek] = 1)) in #"Filtered Rows"
Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_DimDate,
each ([DayNumberOfWeek] = 1))
in
#"Filtered Rows"Даже не разбираясь, что делает запрос, можно увидеть, что объявляются три переменные #»Filtered Rows», dbo_DimDate и Source, и возвращается значение переменной #»Filtered Rows». Также понятно, что для вычисления #»Filtered Rows», должна быть вычислена переменная dbo_DimDate, а для её вычисления нужно вычислить Source. Переменная Source подключается к базе данных Adventure Works DW на сервере SQL. Переменная dbo_DimDate получает данные из таблицы DimDate этой базы. А #»Filtered Rows» берёт таблицу, возвращаемую dbo_DimDate, и отфильтровывает строки, в которых значение столбца DayNumberOfWeek равно 1.
Это всё, что нужно знать об операторе let.
Данные > Источники данных > Запросы > Параметры
YouTube
|
Видеоурок по использованию переменной как SQL параметра в дизайнере отчетов.
|
При создании запроса есть возможность использовать объект Параметр (Parameter). Данный объект предназначен для передачи в запрос дополнительных условий для выборки данных. К примеру, если требуется, чтобы запрос использовал какое-либо значение вводимое пользователем при каждом выполнении запроса, то можно создать запрос с использованием параметров. Объект Параметр может быть использован только совместно с SQL источниками данных. Такие источники данных обычно имеют поле Текст запроса (Text Query). Для того, чтобы вставить параметр в запрос следует нажать кнопку Новый параметр (New Parameter).
После нажатия данной кнопки будет создан новый параметр, который будет отображен в закладке Параметры на панели перечня элементов.
Каждый параметр имеет свойства, с помощью которых можно изменить его настройки.
С помощью свойства Наименование (Name) можно изменить имя параметра.
Это свойство работает только для именованных параметров.
Для каждого параметра можно указать значение, которое используется для заполнения параметра. Значение может быть выражением, const, переменной и т.д. К примеру, x+y или variable.
Свойство Размер (Size) предоставляет возможность изменить размерность типа используемого в параметре. Следует помнить, что в базе данных каждый тип имеет свою размерность. Поэтому при использовании параметра в запросе, необходимо указывать корректную размерность типа. Для некоторых адаптеров баз данных размерность можно не указывать, но обычно если размерность не указана или указана неверно, то запросы с использованием таких параметров будут выполнены некорректно.
Для того, чтобы изменить тип параметра следует использовать значения свойства Тип (Type). Значения данного свойства находятся в выпадающем списке, и представляет собой перечисление типов используемых в параметрах для конкретной базы данных. Стоит учитывать, что список типов отличается в зависимости от базы данных.
Также параметр необходимо указать и в самом запросе. Ниже представлен пример схематичного расположения параметров в запросе:
Как правило, для указания параметра в запросе используется символ @. Символ @ используется с именованными параметрами, т.е. после символа @ следует имя параметра. Но в некоторых базах данных (к примеру в OleDB), символ @ может не восприниматься адаптером базы данных и запросы с параметрами работать не будут. В данном случае, можно использовать неименованные параметры. Для указания в запросе неименованных параметров используется символ ?. После символа ? не указывается имя параметра. В этом случае, важен порядок параметров в закладке Параметры. По мере указания символов ? в запросе, параметры будут браться последовательно из закладки Параметры в направлении «сверху-вниз». Рассмотрим на примере. Допустим есть три параметра, которые указываются в запросе:
Поскольку в данном случае используются неименованные параметры (обозначенные символом ?), то при выполнении запроса параметры будут браться из закладки Параметры последовательно «сверху-вниз».
На рисунке снизу схематично представлено сопоставление параметров из закладка Параметры к параметрам в запросе:
При этом, используемые параметры в данном примере, могут иметь имена, но при использовании символа ? они не играют роли. После того, как запрос с параметрами создан и выполнен, параметры также будут отображаться в Словаре данных, в созданном источнике данных, во вкладке Параметры. На рисунке снизу представлен пример панели Словаря данных и расположения в нем параметров:
Для того, чтобы отредактировать параметр отдельно от источника данных, следует выделить Параметр в словаре данных и нажать кнопку Редактировать (Edit) на панели инструментов в словаре или выбрать пункт Редактировать в контекстном меню выделенного параметра. После этого, пользователю будет отображено окно Редактирование параметра (Edit Parameter), в которым можно изменить настройки выделенного параметра.
В данном поле отображается используемое Наименование (Name) параметра, которое можно отредактировать;
B этом поле отображается используемый Тип (Type) параметра, который можно отредактировать;
Поле для ввода значений.
В зависимости от вкладки указывается выражение или выбирается переменная.
Кнопка Сохранить копию (Save a Copy) сохраняет копию редактируемого параметра, с присвоением постфикса Copy в имени параметра.
Вкладка Выражение (Expression). На этой вкладке в качестве значения параметра, указывается выражение, ссылка на колонку данных и т.д.
Вкладка Переменная (Variable). На этой вкладке в качестве значения параметра выбирается переменная.
Использование переменной как параметра
В параметре как значение можно указать переменную. В этом случае, значения переменной будут значениями параметра при запросе на выборку данных. Использовать переменную в запросе как параметр можно двумя способами:
Создать переменную в словаре данных. Открыть на редактирование источник данных. Создать параметр в источнике данных. Указать переменную как значение этого параметра. Прописать параметр в тексте запроса.
При создании или редактировании переменной, установить флажок Использовать как SQL параметр (Allow using as SQL parameter):
Прописать эту переменную в тексте запроса, используя специальный символ «@» перед именем переменной:
Нажмите кнопку ОК.
Теперь переменная присутствует в источнике данных, и используется как параметр в запросе.
sql — Объявить переменную для строки запроса
спросил
Изменено
4 года, 7 месяцев назад
Просмотрено
676 тысяч раз
Мне было интересно, есть ли способ сделать это в MS SQL Server 2005:
DECLARE @theDate varchar(60)
УСТАНОВИТЕ @theDate = '''2010-01-01'' И ''2010-08-31 23:59:59'''
ВЫБЕРИТЕ Код администратора,
SUM(Total) как Total,
SUM(WOD.Quantity) как количество,
AVG(Total) как avgTotal,
(ВЫБЕРИТЕ СУММУ(tblWOD.Amount)
ИЗ таблицы
ПРИСОЕДИНЯЙТЕСЬ к tblWO на tblWOD.OrderID = tblWO.ID
ГДЕ tblWO.Approved = '1'
И tblWO. AdministratorCode = tblWO.AdministratorCode
И tblWO.OrderDate МЕЖДУ @theDate
)
... и т. д
AdministratorCode = tblWO.AdministratorCode
И tblWO.OrderDate МЕЖДУ @theDate
)
... и т. д
Возможно ли это сделать?
- sql
- sql-сервер
- sql-сервер-2005
- tsql
- динамический-sql
1
Возможно, но требует использования динамического SQL.
Я рекомендую прочитать Проклятие и благословение динамического SQL, прежде чем продолжить…
DECLARE @theDate varchar(60)
УСТАНОВИТЕ @theDate = '''2010-01-01'' И ''2010-08-31 23:59:59'''
ОБЪЯВИТЬ @SQL VARCHAR(MAX)
SET @SQL = 'ВЫБЕРИТЕ код администратора,
SUM(Total) как Total,
SUM(WOD.Quantity) как количество,
AVG(Total) как avgTotal,
(ВЫБЕРИТЕ СУММУ(tblWOD.Amount)
ИЗ таблицы
ПРИСОЕДИНЯЙТЕСЬ к tblWO на tblWOD.OrderID = tblWO.ID
ГДЕ tblWO. Approved = ''1''
И tblWO.AdministratorCode = tblWO.AdministratorCode
И tblWO.OrderDate МЕЖДУ '+ @theDate +')'
EXEC(@SQL)
Approved = ''1''
И tblWO.AdministratorCode = tblWO.AdministratorCode
И tblWO.OrderDate МЕЖДУ '+ @theDate +')'
EXEC(@SQL)
Динамический SQL — это просто оператор SQL, составленный в виде строки перед выполнением. Таким образом, происходит обычная конкатенация строк. Динамический SQL требуется всякий раз, когда вы хотите сделать что-то в синтаксисе SQL, что не разрешено, например:
- один параметр для представления списка значений, разделенных запятыми, для предложения IN
- переменная для представления как значения, так и синтаксиса SQL (IE: приведенный вами пример)
EXEC sp_executesql позволяет вам использовать параметры bind/preparedstatement, поэтому вам не нужно беспокоиться об экранировании одинарных кавычек и т. д. для атак путем внедрения SQL.
1
DECLARE @theDate DATETIME УСТАНОВИТЕ @theDate = '2010-01-01'
Затем измените свой запрос, чтобы использовать эту логику:
И
(
tblWO. OrderDate > DATEADD(MILLISECOND, -1, @theDate)
И tblWO.OrderDate < DATEADD(DAY, 1, @theDate)
)
OrderDate > DATEADD(MILLISECOND, -1, @theDate)
И tblWO.OrderDate < DATEADD(DAY, 1, @theDate)
)
1
Использование EXEC
Вы можете использовать следующий пример для построения оператора SQL.
ОБЪЯВЛЕНИЕ @sqlCommand varchar(1000) ОБЪЯВИТЬ @columnList varchar(75) ОБЪЯВИТЬ @city varchar(75) SET @columnList = 'CustomerID, ContactName, City' SET @city = '''Лондон''' SET @sqlCommand = 'SELECT' + @columnList + ' FROM customers WHERE City = ' + @city EXEC (@sqlCommand)
Использование sp_executesql
Используя этот подход, вы можете гарантировать, что значения данных, передаваемые в запрос, являются правильными типами данных и избежать использования большего количества кавычек.
DECLARE @sqlCommand nvarchar(1000) ОБЪЯВИТЬ @columnList varchar(75) ОБЪЯВИТЬ @city varchar(75) SET @columnList = 'CustomerID, ContactName, City' SET @city = 'Лондон' SET @sqlCommand = 'SELECT' + @columnList + ' FROM customers WHERE City = @city' ВЫПОЛНИТЬ sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city
Ссылка
Я укажу, что в статье, связанной с ответом с самым высоким рейтингом «Проклятие и благословения динамического SQL», автор утверждает, что ответ заключается в том, чтобы не использовать динамический SQL.
Прокрутите почти до конца, чтобы увидеть это.
Из статьи: "Правильный метод - распаковать список в таблицу пользовательской функцией или хранимой процедурой."
Конечно, когда список находится в таблице, вы можете использовать соединение. Я не мог напрямую комментировать ответ с самым высоким рейтингом, поэтому просто добавил этот комментарий.
0
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя адрес электронной почты и пароль
Опубликовать как гость
Электронная почта
Требуется, но никогда не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Использование переменных в метабазе.
Возможно, вместе вы выучите продвинутый SQL… | by Chris So
Возможно, вы попутно выучите продвинутый SQL
Изображение: Сделано автором с большим трудом
Итак, вы используете Metabase. Во-первых, позвольте вас поздравить с этим выбором, т.к. кому вообще нужны Tableau и Power BI? В Metabase вы можете писать нативные SQL-запросы и визуализировать их в одном прогоне (это приложение с открытым исходным кодом, поэтому его можно использовать бесплатно)¹.
Теперь, как правило, у вас должна быть готовая к использованию область щелчком мыши, в которой лежат все ваши таблицы отчетов и которые могут быть запрошены выбранным вами инструментом BI. Вы знаете, всего несколько кликов, и эта диаграмма готова. 🖯
То же самое и с Metabase, но иногда даже самые мощные инструменты достигают своих пределов, и лучше всего подходит написанный вручную SQL-запрос. Но как только вы написали этот запрос, он стал статическим.
Допустим, к вам подходит коллега и спрашивает, может ли она получить данные о продажах за 2014 год для территории 1 и ее долю в общем объеме продаж.
К сожалению, Metabase не может справиться с этим с помощью встроенной логики, но благодаря собственным возможностям SQL вы можете использовать ее как IDE. Теперь запрос может выглядеть так:
ВЫБЕРИТЕ
СУММА(СЛУЧАЙ, КОГДА salesterritorykey = 1 THEN salesamount END) AS sales_territory_1
, SUM(СЛУЧАЙ, КОГДА salesterritorykey = 1 THEN salesamount END) / SUM(salesamount) AS ratio
FROM dbo.fact_internet_sales
ГДЕ ВЫДЕРЖКА (ГОД ОТ даты заказа: :DATE) = 2014
Хорошо. А что, если тот же вопрос задать для другого периода времени?
Ну, это очень просто, почти не неудобно.
Настройте предложение WHERE, и все готово. Но, возможно, ваш коллега хочет еще раз проверить данные и обращается к вам за каждым изменением в наборе результатов. Таким образом, вы меняете код снова и снова, что может привести к очень быстрому результату.²
Используя переменные, вы позволяете своим коллегам проводить собственные углубленные исследования и выполнять фильтрацию в вашем родном запросе, при этом им не нужно знать что-либо о SQL.
Да, кстати, база данных, которую я использую, — это образец базы данных AdventureWorksDW2019 с PostgreSQL. Заинтересованы в том, как я переместил эти данные из MS SQL Server в Postgres? Взгляните сюда. 👈
Официальную документацию можно найти здесь. Но вас здесь не было, если вы читали документы, верно? 😉 Итак, поясню.
Переменные — это простые заполнители, которые вставляют строки в ваш код SQL. В зависимости от типа данных, который вы выбираете для своего фильтра (текст, число или дата), поле ввода будет принимать строку, число или позволит вам выбрать дату из графического интерфейса. Что бы вы ни выбрали, это будет вставлено в ваш код, где находится ваша переменная.
Шаг 1: Инициация
Переменные инициируются фигурными скобками: {{}}. Введите какое-нибудь имя внутри, и Metabase сделает две вещи: Сначала создаст виджет фильтра выше и секунд , откроется контекстное меню справа. Здесь вы можете выбрать тип данных³, указать правильное имя, которое будет отображаться в виджете фильтра, и указать, требуется ли эта переменная.
Теперь давайте перепишем предложение WHERE из SQL-кода выше с переменной:
WHERE EXTRACT(YEAR FROM orderdate::DATE) = {{var_businessyear}} Довольно прямолинейно, верно?
Я включил «требуемый» переключатель, чтобы установить значение по умолчанию. В противном случае код SQL завершится ошибкой, потому что за этим знаком равенства ничего не будет.
Но теперь мы можем ввести любое число и в конечном итоге получим результаты, если есть совпадение (которое здесь будет между значениями 2010 и 2014).
Шаг 2: Дополнительно
Итак, теперь, когда мы знаем, как добавить переменную, давайте сделаем еще один шаг и добавим квадратные скобки: [[]]. Что они делают?
Все, что заключено в эти скобки, будет невидимо для исходного кода. Как только переменная будет введена, выражение будет видно для машины и оценено в запросе. Имейте в виду, что они работают только тогда, когда внутри есть переменная. В противном случае метабаза выдаст ошибку.
Это также означает, что вы не сможете написать собственный SQL с этими скобками так же просто, как это вызовет проблемы с синтаксисом SQL Server.⁴
Хорошо, давайте сделаем рабочий год необязательным, и код будет выглядеть так:
ВЫБЕРИТЕ
СУММА (СЛУЧАЙ, КОГДА salesterritorykey = 1 THEN salesamount END)
AS sales_territory_1
, SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END) 9 0031 / SUM(salesamount) AS ratio
FROM dbo.fact_internet_sales
WHERE 1 = 1
[[AND EXTRACT(YEAR FROM orderdate::DATE) = {{var_businessyear}}]]
При использовании необязательных переменных вы должны запустить блок фильтра с WHERE 1 = 1. Поскольку это допустимое выражение WHERE, оно не вызовет ошибки и вводит предложение WHERE в ваш запрос. При этом вы можете легко добавить дополнительные фильтры, добавив [[И…]].
Влияние на наш пример запроса незначительное — по крайней мере, мы можем деактивировать «требуемую» кнопку, не нарушая наш код. Но истинные преимущества опциональности проявляются при использовании нескольких фильтров.
Шаг 3: Несколько фильтров
Как и в любом предложении WHERE, вы можете комбинировать множество операторов. В более крупных запросах вы можете в конечном итоге накапливать И и ИЛИ, как потребители делают с телевизором в Черную пятницу. Однако, чтобы конечный пользователь мог делать то же самое и фильтровать более одного поля, вам просто нужно заключить каждое выражение в квадратные скобки, начинающиеся с оператора.
Итак, чтобы заставить работать несколько фильтров, мы должны переписать наш запрос сверху. Это гарантирует, что ни один результат не будет исключен перед фильтрацией с помощью переменной. Взгляните на это:
С продажами КАК (
ВЫБРАТЬ
salesterritorykey
, ВЫДЕРЖАТЬ (ГОД ОТ даты заказа:: ДАТА) КАК бизнес-год
, СУММА (продажа) КАК объем продаж
ИЗ dbo.fact_internet_sales
ГДЕ 1 = 1 9 0031 [[ AND EXTRACT(YEAR FROM orderdate::DATE) = {{var_businessyear}}]]
[[AND salesterritorykey = {{var_territory}}]]
[[AND orderdate::DATE >= {{var_orderdate_min}}]]
[[AND orderdate::DATE <= {{var_orderdate_max}}]]
GROUP BY 1,2
)SELECT
businessyear
, salesterritorykey
, salesamount
, salesamount / SUM(salesamount) OVER(PARTITION BY businessyear) AS total_sales
FROM sales
ORDER BY 1,2
Я создал CTE для предварительной обработки данных, что упрощает вычисление наших чисел.
Функция окна (OVER(…)) помогает нам получить общую сумму за каждый финансовый год и рассчитать коэффициент для каждой территории в этом году.
И вот конечный результат:
Вы знаете, в наше время мы привыкли исправлять результаты поиска, даже если мы ошибаемся в ключевых словах поиска. А благодаря Google нам даже не нужно заканчивать поисковый текст благодаря автозаполнению. Поэтому опечатки или незаконченный текст необходимо учитывать при построении запроса с переменными.
Я думаю, что будет лучше, если вы выполните некоторые манипуляции с текстом с помощью функций SQL, которые отлавливают такие случаи. Комбинация
- сопоставление без учета регистра (ILIKE)
- объединение строк с использованием оператора подстановки (|| ‘%’),
- удаление начальных и конечных пробелов (TRIM)
должно подойти для начала. В зависимости от пользователя вы можете добавить дополнительные функции для работы со строками и сопоставления с образцом.
Собственно, в следующем абзаце я покажу вам, как все это может работать в этой настройке.
Наконец-то у нас есть все, что нужно, чтобы по-настоящему пофантазировать с этими переменными. Наш последний небольшой проект — создание фильтра, который принимает несколько ключевых слов (в этом примере они разделены запятыми): 9\%$', '')
)]]
GROUP BY 1,2
ORDER BY 1,2
Я думаю, что это связано с расширенными операциями с текстом в SQL. Но не беспокойтесь, это намного проще, чем вы думаете, и мы получили большинство этих функций, описанных в предыдущем абзаце. Итак, что мы здесь делаем?
Если вы введете эту строку «g, Can», ваш результирующий набор будет содержать регионы Германия и Канада . Давайте рассмотрим первую часть наших вложенных функций:
ILIKE ... TRIM(SPLIT_PART({{var_region}}, ',',1)) || '%' Сначала функция SPLIT_PART разбивает строку по запятой и возвращает первую ее часть (или вторую, или третью, или четвертую). Это будет «г». Затем TRIM удаляет начальные и конечные пробелы. Это возвращает «г».
\%$’, »)
Что это? Это дополнительный уровень обнаружения ошибок. Regex-функция заменяет текст пустой строкой, если она содержит только «%», т.е. не содержит никакого текста. Это может произойти и произойдет, если вы разрешите использовать больше ключевых слов, чем на самом деле вводит конечный пользователь. В этом примере пользователь может отфильтровать до 4 слов, разделенных запятыми, но если ввести только 1 слово, оставшиеся 3 пробела будут пустыми.
Конечный результат нашего фильтра, который принимает несколько ключевых слов.
У этого подхода есть один недостаток: код по-прежнему статичен. 9\%$’, »)
Это можно сделать не одним способом, а в основном сводится к полю, которое может принимать две формы (0 или 1, «Да» или «Нет», «Эрни» или «Берт») и что этот будет оцениваться с учетом входных данных, которые вы даете своим фильтром. Таким образом, любая строка, введенная в этот фильтр, будет вставлена в предложение WHERE и должна быть доступна для чтения SQL.
В конце концов, он должен быть преобразован таким образом, чтобы это имело смысл в ситуации ВКЛ-ВЫКЛ. Причина, по которой мы идем по этому пути, заключается в ограничении типов данных с переменными. Metabase (пока) не предлагает бинарный тип, который добавляет интерактивный виджет-переключатель (так сказать, свиджет 😝).
Вот моя интерпретация. См. объяснение ниже.
WITH sales_predefined AS (
SELECT
orderdatekey
, EXTRACT(YEAR FROM orderdate::DATE) AS "Год заказа"
, salesamount
, CASE
WHEN EXTRACT(YEAR FROM orderdate::DATE) = (SELECT MAX(EXTRACT( ГОД ОТ orderdate::DATE)) FROM adventureworksdw2019.dbo.fact_internet_sales) THEN 'Да'
END AS is_latest_year
FROM adventureworksdw2019.dbo.fact_internet_sales
)SELECT
"Год заказа"
, SUM(salesamount) КАК "Общий объем продаж"
FROM sales_predefined
ГДЕ 1 = 1
[[И is_latest_year = REPLACE({{var_latest_year}}, {{var_latest_year}}, 'Да')]]
GROUP BY 1
ORDER BY 1
Поскольку мы не изменяем никакую таблицу и это более удобно, чем определение представления, мы начинаем этот запрос с CTE.
Это создает новое поле, которое содержит «Да», если год даты заказа равен максимальному году даты заказа (то есть 2014), в противном случае NULL (нам не нужно явно указывать это в нашем операторе CASE). Это поле будет иметь отношение к нашему переключателю включения/выключения.
Далее мы запрашиваем этот CTE, суммируя суммы продаж и группируя их по году заказа. Пока ничего особенного.
Настоящее волшебство происходит внутри квадратных скобок. Мы уже узнали, что они определяют необязательное предложение фильтра с переменными.
Теперь все, что введено в этот фильтр, будет преобразовано в «Да», и наш набор результатов будет содержать общий объем продаж за 2014 год.
Любая строка любой длины заменяется на «Да». И самое интересное: он защищен от SQL-инъекций. 🤙
В сегодняшней статье я познакомил вас с переменными в собственном SQL-запросе в Metabase. Они позволяют выполнять динамическую фильтрацию в статических SQL-запросах, вставляя любой текст (например, f-строку в Python 3).
Мы начали с простого варианта использования и добавляли уровень сложности на каждом шагу. Вы даже можете пойти дальше и использовать всю концепцию, вставив переменные не только в ваше предложение WHERE, но также в SELECT и ORDER BY (они не были рассмотрены в этом посте, но если вам это интересно, дайте мне знать !). Это просто текст, так что вариантов много.
Однако не забывайте о конечном пользователе! Имейте в виду, что некоторые входные данные могут возвращать ошибки в контексте вашего запроса. Помните о возможных опечатках или незаконченных словах. Выявляйте такие случаи с помощью дополнительных слоев SQL-команд, особенно манипуляций со строками и сопоставления с образцом.
Ну вот и все. Я надеюсь, вам понравилось чтение, и если вы хотите узнать больше о Metabase или каких-нибудь крутых трюках с SQL, подписывайтесь на меня (иногда я публикую посты). 👍
[1] Это может вас удивить, но я не связан с Metabase или программистами, стоящими за ней. Мне просто нравятся возможности, которые он предлагает, и я нахожу удивительным, что так много компаний, особенно здесь, в Германии, до сих пор используют дорогие и абсолютно неинтуитивные инструменты, такие как Tableau (« yikes!») или Power BI (« по крайней мере, это не Tableau» ).