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

Предыстория: аналитик создает отчёты в BI для директора компании «ABC». Аналитик не умеет программировать и подключается к данным по инструкции. Директор требует срочно добавить в отчёт данные из 3 источников: 

1. SAP – объем выручки от продаж;

2. HRLink – затраты на персонал;

3. Битрикс – время обработки заявок. 

Аналитик ставит задачу data инженеру на добавление новых данных в BI. Data инженер – единственный специалист по работе с данными в офисе из 400 человек. Он критически оценивает сроки выполнения задачи и объявляет: «Минимум – 2 недели». Такой срок директора не устраивает. Инженер предлагает аналитику альтернативное решение...

Что такое SQL?

SQL? DWH? Mart? Аналитик путается и пугается, но решает разобраться во всем по порядку. Задание директора выполнять придется, деваться некуда.

SQL (Structured Query Language) – язык структурированных запросов, используется для взаимодействия с базами данных и анализа данных.

Объем выручки от продаж содержится в таблице с названием order_revenue такого вида:

Важно: это учебный пример из 7 строк, которые хранятся в одной таблице. В базах данных могут быть миллионы строк в сотнях разных таблиц. В базах данных информация по клиентам обычно хранится в отдельной таблице (например, client_id, client_name, client_birth_date и пр.). Но язык SQL использует схожие команды для баз данных любых размеров.

Посчитаем сумму заказов (выручку от продаж) с помощью SQL. Сделаем это так:

/*выберем сумму (sum) всех заказов (sum_order)*/
select sum(order_sum)
/*из таблицы заказов (order_revenue)*/
from order_revenue

Результат: 58 850 рублей.

Зачем SQL нужен аналитикам?

Изначально аналитик использовал только BI, не погружался в работу с данными. Чтобы создать или дополнить отчёт, нужно обратиться к data инженеру. Он подготовит данные и объяснит аналитику, как к ним подключиться. Но теперь аналитик изучает, как устроена работа с данными в компании.

В компании «ABC» процесс работы с данными состоит из 5 этапов:

1. Source – источники данных, т.е. самостоятельные программы (например, SAP, HRLink, Битрикс). Обработка данных начинается с обращения к данным, которые в разных форматах содержатся в источниках.

2. ETL (Extract, Transform, Load) – извлечение, преобразование и загрузка данных из источников в единое хранилище данных. 

3. DWH (Data Warehouse) – единое хранилище обработанных и систематизированных данных. DWH содержит актуальные и исторические данные в виде таблиц, необходимых для анализа бизнеса. Здесь находится таблица order_revenue, сюда обращаются SQL.

4. OLAP (On-Line Analytical Processing) – программа для оперативного анализа данных, основанная на многомерной модели. Эта модель позволяет анализировать сотни метрик в сотнях разрезов, а элементы внутри модели связаны между собой.

5. BI (Business Intelligence) – программа для анализа, визуализации и представления данных. Она помогает бизнесу принимать решения на основе данных.

Благодаря знаниям SQL аналитик не ждет, пока инженер подготовит mart в DWH и OLAP с новыми данными. Аналитик может самостоятельно обратиться в DWH за данными и сделать отчет.

Важно: разграничение прав data инженеров и аналитиков устроено в компаниях по-разному. В этом примере обращение аналитика в DWH напрямую возможно, но это не всегда так.

Как устроен DWH?

Теперь аналитик выясняет, что такое mart и зачем он нужен. Аналитик пытается разобраться в устройстве хранилища и витрин данных.

В компании «ABC» DWH состоит из трех слоев, которые связаны между собой:

1. ODS (Operational Data Store) – тип базы данных, которая собирает данные из источников, обрабатывает и передает в собственно хранилище DWH. ODS хранит данные за небольшой период времени (например, час, сутки), поэтому и называется операционным или промежуточным хранилищем.

2. Обработанные данные попадают в DWH, где содержатся исторические данные и оперативные данные из ODS.

3. Mart (Data Mart) – витрина данных. Содержит срез данных из DWH по одной теме в виде, понятном пользователю.

Аналитику может создать витрину с нужными данными, чтобы потом использовать ее в BI. Для этого аналитику нужно написать ряд запросов SQL и сохранить их результаты в таблицу (view).

Что может пойти не так?

1. Ошибка: не учитывать специфику СУБД

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

Ошибка могла произойти, потому что аналитик использует разные СУБД. Например, на работе аналитик использует СУБД PostgreSQL, а в тренажере – СУБД MS SQL Server. В СУБД PostgreSQL работает код:

/*выберем 5 первых строк из таблицы заказов (order_revenue)*/
select *
from order_revenue
limit 5

Но в СУБД MS SQL Server этот код не сработает. Так происходит, потому что в СУБД используются разные функции. В СУБД MS SQL Server нет функции limit, зато есть похожая функция top. Такой код в СУБД MS SQL Server отработает верно:

/*выберем 5 первых строк из таблицы заказов (order_revenue)*/
select top 5 *
from order_revenue

БД (База Данных) – упорядоченный набор структурированной информации, который хранится в электронном виде в компьютерной системе. БД бывают реляционными, колончатыми, иерархическими и т.д.

СУБД (Система Управления Базами Данных) – программа, которая управляет структурой БД, контролируют доступ к данным и позволяет анализировать данные.

Чтобы обратиться к данным в базе используется СУБД, которая выступает посредником между пользователем и данными. Примеры: PostgreSQL, MS SQL Server, MySQL, MangoDB и т.д.

Ошибка

Аналитик не поинтересовался, какой СУБД пользуется для запросов. Это привело к ошибкам и неработающему коду.

Как исправить

Отличий в названиях и использовании функций в разных СУБД десятки. Поэтому перед написанием SQL запроса стоит выяснить, какая СУБД используется. А при поиске функции в интернете можно писать не просто «функции времени SQL», а «функции времени PostgreSQL». Так шансы найти нужный ответ повышаются.

2. Ошибка: неправильно ставить запятые

Далее все примеры будут написаны на PostgreSQL. В примерах от data инженера аналитик встречает код с запятой в начале строки:

select order_date
, order_sum
, client_name
from order_revenue

Аналитик считает, что это странно и неудобно. Поэтому помещает все поля запроса в одну строку:

select order_date, order_sum, client_name
from order_revenue

Код работает и визуально выглядит меньше. Но в 20% случаев при написании запроса в 20+ строк появляется ошибка:

SQL Error [42601]: ОШИБКА: ошибка синтаксиса (примерное положение: "from")
  Позиция: 66

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

select order_date, order_sum, 
from order_revenue
where ...

Ошибка

Аналитик пренебрёг опытом data инженера и не ставил запятую в начале строки.

Как исправить

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

3. Ошибка: не проверять округления

Аналитик решает задачу: узнать, какой процент от общей выручки приходится на каждого клиента. Он пишет код:

select distinct client_name
, sum(order_sum) as total_revenue
, sum(order_sum)*100 / (
	select sum(order_sum) 
	from public.order_revenue
	) as precent_of_revenue
from order_revenue
group by 1

Результат:

Аналитик приносит результат директору. Директор замечает, что сумма долей не равняется 100. Аналитик получает замечание за некорректность данных.

Аналитик сам проверяет сумму процентов по клиентам и получает 98% вместо 100%. Он ищет ошибку на протяжении часа и приходит к выводу: SQL неправильно округляет. При появлении дробной части SQL отсекает её, вопреки математическим правилам округления. Например:

select 5/3
  Результат: 1

select 1/3
  Результат: 0

Так происходит, потому для столбца order_sum был задан неподходящий тип данных.

Тип данных – свойство, определяющее, какого вида данные хранятся в столбце: целые числа, даты, текст и пр.

Тип данных INT используется для целых чисел, а DECIMAL(10,2) – для чисел с двумя цифрами после запятой. В нашем случае вместо DECIMAL(10,2) мог быть использован INT, поэтому SQL посчитал дробную часть необязательной и отсек её.

Ошибка

БД использует неподходящий тип данных, это приводит к ошибкам при работе с нецелыми числами.

Как исправить

Со стороны аналитика можно решить проблему на уровне SQL-запроса. Заменим умножение на 100 умножением на 100.0, то есть переведем число в нецелочисленный вид. Добавим округление до целого, получим код:

select distinct client_name
, sum(order_sum) as total_revenue
, round(sum(order_sum)*100.0 / (
	select sum(order_sum) 
	from public.order_revenue
	),0) as precent_of_revenue
from order_revenue
group by 1

Результат корректный, сумма процентов равно 100:

Data инженер со своей стороны может попробовать изменить тип данных столбца order_sum на более подходящий. Тогда в будущем похожих проблем не возникнет.

4. Ошибка: не связывать данные и реальность

Аналитик решает другую задачу: найти клиента с максимальной суммой заказа. Аналитик пишет код:

select client_name
, order_sum
from order_revenue
order by order_sum desc 
limit 1

  Результат: Petr 13 000

Аналитик радуется полученному результату, показывает данные директору. Директор поручает менеджеру связаться с Петром, чтобы повысить лояльность стратегического клиента и удержать его в компании «ABC». Петр получает поддержку в техническим вопросах от менеджера, ему нравится повышенное внимание компании. Поэтому клиент продолжает делать крупные заказы.

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

Ошибка

Задача предполагала, что клиент будет один, но данные этого не подтвердили. Аналитик не учел, что в реальной жизни клиентов с одной суммой заказа может быть несколько. Он не проверил данные, а сразу жестко отделил одного клиента с помощью limit.

Как исправить

Код: выбрать не одного клиента, а всех, у кого сумма заказа максимальна:

select client_name
, order_sum
from public.order_revenue
where order_sum = (
select max(order_sum) 
from public.order_revenue
)

  Результат: Petr 13 000, Maria 13 000

Логика: стоит связать данные и реальность. Могут ли быть одинаковые суммы заказов? Да. Могут ли быть одинаковые имена клиентов? Да. А почтовые адреса? Нет. До написания запроса SQL стоит обдумать, какими свойствами обладают клиенты, заказы, контакты в реальности. Это может помочь избежать ошибок.

5. Ошибка: не проверять результаты

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

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

/*добаляем результат запроса в таблицу percent_revenue*/
select distinct client_name
, sum(order_sum) as total_revenue
, sum(order_sum)*100 / (
	select sum(order_sum) 
	from public.order_revenue
	) as precent_of_revenue
into percent_revenue
from order_revenue
group by 1

/*проверяем сумму процентов*/
select sum(precent_of_revenue)
from percent_revenue

Код с подборными пояснениями находится тут.

Заключение

Мы разобрали 5 ошибок новичка при первом изучении SQL:

1. Не знать используемую СУБД => использовать не те функции и получать ошибки;

2. Не ставить запятые в начале строки => мучаться с синтаксисом и нечитаемым кодом;

3. Не проверять данные после округления => получать неправильные расчеты;

4. Не связывать данные с реальными объектами => получать логические ошибки;

5. Не проверять результаты вычислений.