Многие аналитики работающие с SQL и занимающиеся анализом данных, никогда не сталкивались с программированием на этом языке, потому что SQL в первую очередь язык структурированных запросов, а не полноценный язык программирования. Однако, программные возможности в нем есть, хотя и весьма скромные.
В тот момент, когда при написании запроса мы сталкиваемся с какими-либо ограничениями, например синтаксическими, нам может пригодиться программирование. Ниже я расскажу о программных конструкциях и модулях существующих в SQL и чем они могут помочь аналитику.
В SQL Azure, Oracle Database, PostgreSQL и прочих реляционных базах синтаксис может отличаться, но принцип остается тем же.
Программные конструкции
Программная конструкция — это часть программы, отвечающая за определенное действие и объясняющая компьютеру как решить задачу. То есть небольшой строительный блок из которых и состоит программный код.
Переменные
Изучение любого из языков программирования, практически всегда начинается со знакомства с переменными.
Переменная — это ячейка в памяти компьютера, используемая для временного хранения какой-либо информации (чисел, строк, дат и т.п.).
У переменной есть три важных атрибута:
- Имя (чтобы можно было к ней обратиться);
- Тип данных (чтобы понимать, что с этой переменной можно делать);
- Область видимости (чтобы понимать, где она будет использоваться).
Также в SQL у переменной есть три фазы:
- Объявление переменной при помощи инструкции
DECLARE
; - Инициализация при помощи
SET
илиSELECT
; - Использование переменной при помощи подстановки ее имени в нужное место вашего скрипта.
Давайте создадим переменную:
-- Объявляем переменную с именем 'StartDate' и задаем тип данных date DECLARE @StartDate date -- Инициализируем переменную и присваиваем ей значение '2019-09-30' -- Если нужно объявить сразу несколько переменных, то можно воспользоваться инструкцией SELECT (пример будет ниже) SET @StartDate = '2019-09-30' -- Используем переменную в запросе SELECT * FROM Orders WHERE OrderDate = @StartDate
Как переменные могут помочь аналитику?
Первое что приходит на ум — это создание отчета с динамическими периодами, чтобы не редактировать каждый раз даты отчета в условии WHERE
:
-- Объявляем переменные с именем 'StartDate' и 'EndDate' DECLARE @StartDate date , @EndDate date -- Инициализируем переменные и присваиваем для 'StartDate' значение '2019-09-30', а для @EndDate используем специальную функцию возвращающую текущую дату GETDATE() SELECT @StartDate = '2019-09-30', @EndDate = GETDATE() -- Используем переменную в запросе SELECT * FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate
Что касается области видимости, то нужно запомнить, что переменная видима с момента ее объявления до конца скрипта, после чего она уничтожается.
Условные операторы
Практически все языки программирования предоставляют возможность использовать условные операторы и SQL не исключение.
Условный оператор приходит на помощь в тот момент, когда вам нужно выполнить либо одно, либо другое действие в зависимости от заданного условия.
Синтаксис:
- Ключевое слово
IF
; - Условие, в соответствии с которым будет происходить проверка;
- Необязательное ключевое слово
ELSE
, указывающее на то, что делать если условие не выполнилось.
Давайте разберем на примере:
-- Объявляем переменную с именем @var и сразу присваиваем ей значение (так тоже можно) DECLARE @var int = 4 -- Простая форма, если условие не выполняется, то ничего не происходит IF 2 + 2 = @var SELECT 'Верно' -- Сложная форма, если условие не выполняется, говорим что делать IF 2 + 2 = @var SELECT 'Верно' ELSE SELECT 'Ошибка'
Чаще всего на практике требуются более сложные составные условия, содержащие различные логические связки.
Зачем это аналитику?
Логический оператор IF
, сам по себе нельзя использовать в обычном SELECT
-е, однако он активно применяется при создании программных модулей, таких как функции и хранимые процедуры о которых мы поговорим ниже.
Но есть логический оператор, который можно использовать внутри запроса и речь идет о операторе CASE
он же оператор множественного выбора.
Оператор множественного выбора в зависимости от указанных условий позволяет вернуть одно из множества возможных значений, то есть CASE
работает как несколько IF
-ов.
Синтаксис:
- Ключевое слово
CASE
; - Ключевое слово
WHEN
, после которого идет условие проверки; - Ключевое слово
THEN
, после которого идет действие к выполнению; - Необязательное ключевое слово
ELSE
, указывающее на то, что делать если ни одно из условий не выполнилось; - Ключевое слово
END
.
На практике все гораздо проще:
-- CASE работает только внутри SELECT SELECT -- При помощи CASE разобьем все товары на 4 группы в зависимости от цены CASE WHEN price <= 99 THEN 'Дешевые товары' WHEN price BETWEEN 100 AND 500 THEN 'Средняя ценовая группа' WHEN price > 500 THEN 'Дорогие товары' ELSE 'Бесплатно' END FROM Orders
Для аналитика польза очевидна, так как при помощи CASE
, можно создавать новые параметры отчетах, а также в зависимости от условий рассчитывать показатели.
Для примера давайте создадим новую группу каналов, ведь часто в отчетах бизнес-пользователи хотят видеть более понятные названия вместо smm_cpc
, cpc
или organic
.
SELECT CASE WHEN medium = 'smm_cpc' THEN 'Таргетированная реклама' WHEN medium = 'cpc' THEN 'Контекстная реклама' WHEN medium = 'organic' THEN 'Органический поиск' WHEN medium = 'referral' THEN 'Переходы по ссылкам' ELSE 'Прочее' END FROM Orders
Циклы
Ни один уважающий себя язык программирования не обходится без циклов.
Цикл — это инструкция, позволяющая выполнять один и тот же запрос несколько раз, пока условие истинно.
Синтаксис:
- Ключевое слово
WHILE
; - Условие в соответствии с которым будет выполняться цикл.
Рассмотрим пример:
-- Объявляем переменную с именем @var DECLARE @var int = 4 -- Создаем цикл и рядом пишем условие в соответствии с которым, цикл будет выполняться до тех пор, пока @var не будет равно 0 WHILE @var > 0 -- Заключаем тело цикла в скобки, в SQL они обозначаются как BEGIN ... END BEGIN -- В SELECT выводим текущее значение переменной, а в SET устанавливаем для нее новое значение минус 1 SELECT @var SET @var = @var - 1 END
В результате запрос, находящийся в операторных скобках, будет выполнен 4 раза:
В чем же польза для аналитика?
Так же как и в случае с условным оператором IF
, цикл нельзя вставить внутрь запроса и он нужен для создания программных модулей о которых мы сейчас и поговорим.
Программные модули
Программный модуль — это запрос или скрипт сохраненный на сервере и оформленный в виде объектов.
Главная фишка использования программных модулей состоит в том, что они позволяют собирать сложные решения из простых составных частей. То есть позволяют декомпозировать большую задачу на несколько мелких.
Представление
При решении какой-либо задачи, наши запросы могут стать достаточно большими и их контекст сложно постоянно держать в голове. И как только такая ситуация возникнет — лучшим решением будет использование функционала представлений.
Представление — это сохраненный в базе запрос с которым можно работать так, как будто бы это готовая таблица.
У представления есть несколько ограничений:
- В качестве представления можно сохранить не любой запрос, а только
SELECT
; - В запросе не должно быть сортировки;
- Все столбцы должны иметь имена.
Синтаксис:
- Ключевое слово
CREATE VIEW
использующееся для создания представления; - Ключевое слово
AS
, после которого должен идти текст запроса.
При создании представления сервер не выполняет запрос, а сохраняет его в базе для последующего использования.
Давайте разберем на примере:
-- Создаем представление с именем SalesReport CREATE VIEW SalesReport AS -- Далее указываем наш большой и сложный запрос, текст которого мы хотим сохранить на сервере SELECT [date] , cost , impressions , clicks , sessions , orders , revenue FROM Orders WHERE medium = 'cpc'
И теперь, чтобы получить результат выполнения запроса, нам достаточно выполнить следующий код:
SELECT * FROM SalesReport
Чем это полезно аналитику?
Использование представлений позволяет абстрагироваться от сложностей появляющихся при написании запроса, что крайне актуально, когда ваш запрос не помещается в один экран.
И теперь можно дальше усложнять запрос, используя представления как строительные блоки для конечного отчета.
Табличное выражение
Чтобы создавать представления, у вас должны быть права администратора к базе данных. Но что делать когда таких прав нет, но возможности представлений использовать хочется?
Специально для этого был придуман функционал табличного выражения.
Табличное выражение — это представление которое не сохраняется в базе, а существует только в момент выполнения SELECT
-а и после самоликвидируется.
Синтаксис:
- Ключевое слово
WITH
использующееся для создания табличного выражения; - Ключевое слово
AS
, после которого должен идти псевдоним; - В скобочках
()
указывается текст запроса, который будет выполняться при обращении к псевдониму; - После табличного выражения должен идти одиночный
SELECT
.
Пример:
-- Создаем табличное выражение с именем Cost WITH Cost AS ( -- Далее в скобках указываем наш запрос SELECT [date] , sourceMedium , campaign , cost , impressions , clicks FROM Advertising ) -- Выводим результат выполнения запроса SELECT * FROM Cost
Пока не очень понятно в чем польза и чем это отличается от представления?
Давайте разберем пример из практики практически любого digital-аналитика:
- Перед вами поставили задачу — создать отчет для отдела контекстной рекламы с доходом и расходом по рекламным кампаниям.
- Данные по расходам хранятся в одной базе, а данные по заказам в другой.
- Ключами позволяющими связать данные между собой являются UTM-метки.
Для нас теперь это раз плюнуть, вооружимся табличными выражениями и напишем запрос:
-- Запрашиваем расходы WITH Cost AS ( SELECT [date] , sourceMedium , campaign , cost , impressions , clicks FROM Advertising ) -- Запрашиваем заказы , Orders AS ( SELECT [date] , sourceMedium , campaign , orders , revenue FROM Crm ) -- Создаем итоговый отчет , Report AS ( SELECT Cost.[date] , Cost.sourceMedium , Cost.campaign , SUM(Cost.cost) AS 'cost' , SUM(Cost.impressions) AS 'impressions' , SUM(Cost.clicks) AS 'clicks' , SUM(Orders.orders) AS 'orders' , SUM(Orders.revenue) AS 'revenue' FROM Cost -- Соединяем данные между собой по дате и UTM-меткам LEFT JOIN Orders ON Cost.[date] = Orders.[date] AND Cost.sourceMedium = Orders.sourceMedium AND Cost.campaign = Orders.campaign GROUP BY Cost.[date], Cost.sourceMedium, Cost.campaign ) -- Выводим результат SELECT * FROM Report
Да, конечно, эту задачу можно было решить гораздо меньшим количеством кода.
Но моей целью было показать вам суть табличных выражений — они помогают разбить задачу на маленькие подзадачи, структурировать и упорядочить ваш код, что особенно актуально при написании больших и сложных отчетов.
Процедуры
Как и в случае представления, процедура представляет собой сохраненный в базе запрос, но процедура не является таблицей и поэтому на нее не накладываются такие строгие ограничения, как в представлении.
Основное назначение процедур — это сохранение программных сценариев для выполнения определенных действий в базе данных (UPDATE
, DELETE
, INSERT
и тп.), в том числе для вызова других процедур и функций.
Но у процедуры есть ограничения:
- Процедуру нельзя использовать в
SELECT
-е; - Процедуру нельзя усложнять «снаружи», то есть ее нельзя
JOIN
-ть, отфильтровать, группировать и тп.
Синтаксис:
- Ключевое слово
CREATE PROCEDURE
использующееся для создания процедуры; - В процедурах можно использовать параметры, название которых указывается после знака
@
; - Ключевое слово
AS
, после которого должен идти программный код; - Для вызова процедуры используется ключевое слово
EXECUTE
вместе с ее названием.
Пример:
-- Создаем процедуру с именем HumanResourcesProc CREATE PROCEDURE HumanResourcesProc -- Указываем параметры процедуры, которые будут выступать в качестве переменных @FirstName nvarchar(50), @LastName nvarchar(50) AS -- Далее идет текст скрипта SELECT FirstName , LastName , Department FROM HumanResources WHERE FirstName = @FirstName AND LastName = @LastName
И теперь, чтобы вызвать процедуру, достаточно выполнить следующий код:
-- Запускаем выполнение скрипта процедуры для вывода информации по определенному сотруднику EXECUTE HumanResourcesProc 'Иван', 'Иванов'
Чем процедуры могут помочь аналитику?
Функционал процедур можно задействовать для построения на основе вашей базы данных полноценного ETL-решения — то есть процесса по извлечению данных из внешних источников, их трансформации и очистке, а также загрузке в хранилище.
А подключая к хранилищу Python или R-скрипты, вы сможете обучать, оценивать и развертывать модели машинного обучения прямо в базе данных.
Функции
Зачастую нам нужно повторять одно и то же действие во многих частях программы или даже в разных отчетах. И чтобы не писать один и то же код в разных местах были придуманы функции.
Функции — это конструкции, содержащие исполняемый код и являющиеся основными строительными блоками программы.
В некоторых языках программирования нет деления на функции и процедуры, так в чем же отличия?
Их несколько:
- Функция может быть усложнена как «внутри», так и «снаружи», то есть она поддается сортировке, группировке и
JOIN
-у. - Функция всегда возвращает результат определенного типа, например, встроенная функция
SUM
— всегда вернет число. Процедура же может выполниться и ничего не вернуть или вернуть число, строку, дату и даже таблицу в зависимости от того, как она запрограммирована.
И как тогда определиться что использовать? Все просто.
Процедуру стоит использовать, если вы собираетесь менять базу или для оформления конечного результата, не требующего дополнительной обработки, во всех остальных случаях используйте функции.
Синтаксис:
- Ключевое слово
CREATE FUNCTION
использующееся для создания функции; - Параметры функции указываются в скобках
()
и после знака@
; - Ключевое слово
RETURNS
указывает на тип возвращаемых функцией данных; - Ключевое слово
AS
, после которого должен идти программный код; - Ключевое слово
RETURN
, сообщающее функции, что нужно вернуть значение.
Давайте переделаем процедуру из предыдущего примера в функцию:
-- Создаем функцию с именем HumanResourcesFunc CREATE FUNCTION HumanResourcesFunc ( -- В скобочках указываем параметры функции, которые будут выступать в качестве переменных @FirstName nvarchar(50), @LastName nvarchar(50) ) -- Указываем что в данном случае функция должна вернуть нам таблицу RETURNS TABLE AS RETURN -- Далее идет текст скрипта SELECT FirstName , LastName , Department FROM HumanResources WHERE FirstName = @FirstName AND LastName = @LastName
Для вызова созданной функции используем следующий код:
-- Запускаем выполнение скрипта функции для вывода информации по определенному сотруднику SELECT * FROM HumanResourcesFunc ('Иван', 'Иванов')
Как функции могут облегчить жизнь аналитику?
Помните, чуть выше в блоке про оператор множественного выбора, мы разбирали пример с новой группой каналов?
А теперь, представьте, что это действие — присваивание понятного названия канала, нам нужно сделать не один раз в одном месте, а много и в нескольких отчетах.
Чтобы не повторять один и тот же код, можно написать функцию, которая заменит CASE
:
-- Создаем функцию с именем ChannelFunc CREATE FUNCTION ChannelFunc ( -- В скобочках указываем параметр функции, который будет выступать в качестве переменной @medium nvarchar(50) ) -- Указываем, что в данном случае функция должна вернуть нам одно значение в виде строки RETURNS nvarchar (100) AS -- В операторных скобкам описываем логику при помощи IF BEGIN IF @medium = 'smm_cpc' RETURN 'Таргетированная реклама' IF @medium = 'cpc' RETURN 'Контекстная реклама' IF @medium = 'organic' RETURN 'Органический поиск' IF @medium = 'referral' RETURN 'Переходы по ссылкам' RETURN 'Прочее' END
И теперь, если мы захотим в каком-нибудь отчете заменить стандартные каналы на их русские названия, то просто применим функцию:
SELECT [date] -- Вместо CASE вызываем функцию ChannelFunc с параметром medium , ChannelFunc([medium]) AS 'newChannel' , campaign , cost , impressions , clicks FROM Advertising
Что дальше?
В следующей статье я расскажу как, используя программные возможности языка SQL, можно создать автообновляемый отчет на основе стека технологий MS SQL Server и Power BI.
Документация Microsoft (Transact-SQL):
- Типы данных;
- Переменные;
- Оператор IF…ELSE;
- Оператор CASE;
- Цикл WHILE;
- Представления;
- Табличное выражение;
- Процедуры;
- Функции.
Полезные ссылки: