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

Обложка: Зачем аналитикам данных знать 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_ref book_date pcnt top
210222 2016-09-01 01:11:00+07 0.50550104073743681237 1
1EC77E 2016-09-01 18:54:00+07 0.55010407374368123699 2
E6CFAC 2016-09-01 02:23:00+07 0.59470710674992566161 3
9B5DE3 2016-09-01 13:19:00+07 0.59470710674992566161 4
85A140 2016-09-01 08:50:00+07 0.59470710674992566161 5
467FFA 2016-09-02 11:11:00+07 0.28227480282274802823 1
F3DE41 2016-09-02 03:53:00+07 0.28227480282274802823 2
57FA09 2016-09-02 03:12:00+07 0.30718140307181403072 3
CA577F 2016-09-02 06:27:00+07 0.30718140307181403072 4
B87401 2016-09-02 16:19:00+07 0.33208800332088003321 5
AD1930 2016-09-03 09:37:00+07 0.42542542542542542543 1
15C5CF 2016-09-03 05:34:00+07 0.42542542542542542543 2
3BCE96 2016-09-03 07:05:00+07 0.46296296296296296296 3
4AE628 2016-09-03 01:40:00+07 0.46296296296296296296 4
3BE386 2016-09-03 22:50:00+07 0.46296296296296296296 5
7EBDF0 2016-09-04 03:46:00+07 0.46418266215029481872 1
6238CA 2016-09-04 14:31:00+07 0.46418266215029481872 2
A1F776 2016-09-04 13:58:00+07 0.55200100363818843307 3
B7D95A 2016-09-04 13:09:00+07 0.75272864132480240873 4
6E4FCC 2016-09-04 08:19:00+07 0.75272864132480240873 5
8589BD 2016-09-05 20:41:00+07 0.34500253678335870117 1
6EBFDF 2016-09-05 05:00:00+07 0.34500253678335870117 2
0537C4 2016-09-05 17:00:00+07 0.37544393708777270421 3
3730000000 2016-09-05 22:39:00+07 0.40588533739218670726 4
B7CD13 2016-09-05 19:23:00+07 0.44647387113140537798 5
C1894E 2016-09-06 01:43:00+07 0.44537594969871626932 1
D7B76D 2016-09-06 23:35:00+07 0.44537594969871626932 2
D17BFD 2016-09-06 19:48:00+07 0.44537594969871626932 3
A057E6 2016-09-06 10:00:00+07 0.44537594969871626932 4
FBCCF6 2016-09-06 06:01:00+07 0.44537594969871626932 5
D4B21A 2016-09-07 05:26:00+07 0.43539505698552951722 1
3160000000 2016-09-07 10:36:00+07 0.51222947880650531438 2
D77412 2016-09-07 17:16:00+07 0.51222947880650531438 3
D31676 2016-09-07 01:05:00+07 0.51222947880650531438 4
794766 2016-09-07 22:47:00+07 0.76834421820975797157 5

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

  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 для проверки продуктовых гипотез без привлечения специалистов по машинному обучению.