Зачем аналитику нужно программирование на SQL?

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

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

Так как работаю я в основном с Microsoft SQL Server, то в тексте речь пойдет о программных возможностях баз данных построенных именно на этой системе.
В 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):

Полезные ссылки:

Об авторе
Свежие записи

Роман Романчук

Эксперт по маркетинговой и продуктовой аналитике, ex-директор по аналитике Сравни.

6 replies on “ Зачем аналитику нужно программирование на SQL? ”

nexter83:

22.10.2019 в 18:22

ОТличная статья!

Ответить
one43:

22.10.2019 в 18:35

Спасибо!)

Ответить
Олег Кащеенко:

04.11.2019 в 10:24

Ещё материал — простые шаги к полному пониманию SQL

Ответить
1. Roman Romanchuk:
  
  06.11.2019 в 23:56
  
  Не понял вас.
  
  Ответить
  1. beratov:
    
    02.12.2019 в 02:39
    
    — Доктор, меня все игнорируют!
    — Следующий!
    
    Ответить
Уведомление: Автоматизация отчетности при помощи SQL и Power BI – This is Data

Программные конструкции

Переменные

Условные операторы

Циклы

Программные модули

Представление

Табличное выражение

Процедуры

Функции

Что дальше?

Роман Романчук

6 replies on “ Зачем аналитику нужно программирование на SQL? ”

Добавить комментарий Отменить ответ