Зачем аналитикам данных знать SQL

Рассказывает Ирина Хомутова, эксперт и методолог курса SQL «Нетологии»

Что такое SQL?

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

И если мы хотим анализировать данные — нам нужно их откуда-то получать, а получаем мы их, как правило, из баз данных. Не всегда это видно невооружённым глазом, но читаем ли мы новости, переводим деньги со счёта на счёт, запрашиваем выписки по счёту, делаем покупки в интернет-магазине или просто знакомимся с ценами конкурентов — всё это мы делаем, обращаясь к различным базам данным.

Разные уровни и роли, работающие с SQL или результатами его применения

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

Кто такие аналитики данных и с чем они работают?

Аналитики данных — своего рода экспериментаторы, которые владеют инструментарием для соединения потоков данных из различных источников, а также выдвигают гипотезы и проверяют их. Вот тут-то им и нужны базы данных и язык, позволяющий точно формулировать к ним запрос. Правильное получение исходных данных — это искусство, гарантирующее высокую вероятность того, что в поведении исследуемых объектов установятся причинно-следственные связи. Именно качество данных позволяет выбирать наиболее подходящие гипотезы.

Помимо SQL, аналитику данных необходимо знать инструменты статистического анализа данных: это и узкоспециализированные пакеты — SPSS, Statistika, и различные языки программирования — SAS, R, Python, обладающие функциональностью для анализа и визуализации данных, и совсем легковесные решения типа Gretl. А самый популярный набор инструментов у аналитиков, это, пожалуй, Python + SQL.

Инструменты и методы, с которыми сталкивается аналитик данных

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

При этом в каждой индустрии свои стандарты анализа, зависящие от чувствительности данных: для госструктуры набор инструментов будет одним (исходя из жёстких ограничений доступов), для НКО — другим, для диджитал-стартапа — третьим.

Откуда брать данные и что делать с SQL командами?

В организациях есть свои хранилища данных и при необходимости к ним можно получить доступ. Это происходит и при разграничении прав пользователей, и при работах по разработке баз данных. С помощью Python, зная адрес сервера и данные для подключения к нему, можно импортировать нужные библиотеки и писать запросы уже внутри используемой программы.
Примеры библиотек: для ODBC — pyodbc, PostgreSQL — psycopg2, MySQL — mysql.connector и т. д.

Ваша программа-«получатель данных», в свою очередь, может находиться внутри облачного решения на основе jupiter notebook. А к полученным данным вы примените дальнейшие выкладки и/или построите графики (как минимум, библиотеки: pandas, numpy, matplotlib и т.д.).

Аналитические функции

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

Примеры:

  • вычислить частоту общения друзей друг с другом (например, необходимо определить максимально близкие контакты внутри выделенных кругов): тогда мы будем искать процентные соотношения длительности переговоров, частоты переписок и телефонных разговоров, количество географических пересечений, посещений общих ресурсов и т.д.,
  • сравнить зарплаты сотрудников внутри отделов, вычислить рейтинги,
  • узнать топ-N заказчиков в сечении по услугам или продуктам,
  • узнать загруженность соседних точек продаж и т. д.

На помощь приходят аналитические функции SQL — мощный инструмент, который помогает разгрузить клиента от большого объёма процедурного кода, СУБД — от сложных и порой малоэффективных запросов, уменьшить время на разработку и получить при этом желаемый результат.

Функции для статистических расчётов включают в себя как стандартные агрегирующие функции (используются для получения обобщающих значений), так и расширения, характерные для конкретных СУБД.

Рассмотрим примеры, основанные на классическом тренировочном датасете Postgres.

Мы хотим обслуживать без очереди пассажиров, входящих в топ-5 по суммарному чеку за перелёт. Для этого вычисляем и выводим определённый список, чтобы спланировать дальнейшую работу:

with flycnt as (
select book_ref,
          book_date, 
         total_amount/max(total_amount) over (partition by  date_trunc('day', book_date)) as pcnt
from bookings.bookings
)

select * 
from (
	select book_ref,
          book_date, 
          pcnt, 
         row_number() over(partition by  date_trunc('day', book_date) order by pcnt) as top
	from flycnt
) as topflyers
where top <= 5

На неделю получили примерно такой список:

book_refbook_datepcnttop
2102222016-09-01 01:11:00+070.505501040737436812371
1EC77E2016-09-01 18:54:00+070.550104073743681236992
E6CFAC2016-09-01 02:23:00+070.594707106749925661613
9B5DE32016-09-01 13:19:00+070.594707106749925661614
85A1402016-09-01 08:50:00+070.594707106749925661615
467FFA2016-09-02 11:11:00+070.282274802822748028231
F3DE412016-09-02 03:53:00+070.282274802822748028232
57FA092016-09-02 03:12:00+070.307181403071814030723
CA577F2016-09-02 06:27:00+070.307181403071814030724
B874012016-09-02 16:19:00+070.332088003320880033215
AD19302016-09-03 09:37:00+070.425425425425425425431
15C5CF2016-09-03 05:34:00+070.425425425425425425432
3BCE962016-09-03 07:05:00+070.462962962962962962963
4AE6282016-09-03 01:40:00+070.462962962962962962964
3BE3862016-09-03 22:50:00+070.462962962962962962965
7EBDF02016-09-04 03:46:00+070.464182662150294818721
6238CA2016-09-04 14:31:00+070.464182662150294818722
A1F7762016-09-04 13:58:00+070.552001003638188433073
B7D95A2016-09-04 13:09:00+070.752728641324802408734
6E4FCC2016-09-04 08:19:00+070.752728641324802408735
8589BD2016-09-05 20:41:00+070.345002536783358701171
6EBFDF2016-09-05 05:00:00+070.345002536783358701172
0537C42016-09-05 17:00:00+070.375443937087772704213
37300000002016-09-05 22:39:00+070.405885337392186707264
B7CD132016-09-05 19:23:00+070.446473871131405377985
C1894E2016-09-06 01:43:00+070.445375949698716269321
D7B76D2016-09-06 23:35:00+070.445375949698716269322
D17BFD2016-09-06 19:48:00+070.445375949698716269323
A057E62016-09-06 10:00:00+070.445375949698716269324
FBCCF62016-09-06 06:01:00+070.445375949698716269325
D4B21A2016-09-07 05:26:00+070.435395056985529517221
31600000002016-09-07 10:36:00+070.512229478806505314382
D774122016-09-07 17:16:00+070.512229478806505314383
D316762016-09-07 01:05:00+070.512229478806505314384
7947662016-09-07 22:47:00+070.768344218209757971575

Рассмотрим наш запрос внимательнее. Что тут есть:

  1. Предложение over — то, что как раз-таки синтаксически отличает оконную функцию от обычной или агрегатной функции.
  2. Внутри over-предложения partition by указывает, что строки нужно разделить по группам или разделам, объединяя в соответствии с указанным в нём выражением (подобно group by на уровне запроса, за исключением того, что его аргументы всегда являются просто выражениями, а не именами выходных столбцов или числами).
  3. Оконная функция вычисляется по строкам, попадающим в один раздел с текущей строкой.
  4. Указание order by определяет порядок, в котором оконная функция обрабатывает строки раздела.

Оконные функции общего назначения в большинстве СУБД позволяют узнать:

  • номер текущей строки в её разделе;
  • ранг: относительный/абсолютный, с пропусками или без;
  • значения: в первой/последней или n-ой строке;
  • значение соседа текущей строки со смещением вверх/вниз

Для Postgres оконные функции:

  • row_number() — номер текущей строки в её разделе, начиная с 1;
  • rank() — ранг текущей строки с пропусками; то же, что и row_number для первой родственной ей строки;
  • dense_rank() — ранг текущей строки без пропусков; эта функция считает группы родственных строк;
  • percent_rank() — относительный ранг текущей строки, вычисляется по формуле: percent_ rank = (rank - 1) / (Nстрок - 1);
  • cume_dist() — относительный ранг текущей строки: (число строк, предшествующих или родственных текущей) / (общее число строк);
  • ntile(число_групп) — ранжирование по целым числам от 1 до значения аргумента так, чтобы размеры групп были максимально близки;
  • lag(значение [, смещение [,значение_по_умолчанию]]) — значение для строки, положение которой задаётся смещением от текущей строки к началу раздела;
  • lead(значение [, смещение [,значение_по_умолчанию]]) — значение для строки, положение которой задаётся смещением от текущей строки к концу раздела;
  • first_value() — значение, вычисленное для первой строки в рамке окна;
  • last_value() — значение, вычисленное для последней строки в рамке окна;
  • nth_value() — значение, вычисленное для n-oй строки в рамке окна.

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

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