Что такое SQL?

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

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

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

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

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

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

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

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

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

PHP-программист

«ООО «ОЛКОН»», Самара, от 30 000 до 90 000 ₽

tproger.ru Вакансии на tproger.ru

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

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

Меняем схему базы данных в PostrgreSQL, не останавливая работу приложенияtproger.ru

Хинт для программистов: если зарегистрируетесь на соревнования Huawei Honor Cup, бесплатно получите доступ к онлайн-школе для участников. Можно прокачаться по разным навыкам и выиграть призы в самом соревновании.

Перейти к регистрации