Data analysis | Анализ данных | DA

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

Допустим, что активный пользователь - это тот, кто заходит в приложение 3 и более раз в неделю.

Также у нас есть заготовленный датасет (недельный срез) с колонками:

▶️user_id - id пользователя

▶️logins_per_week - число заходов в неделю

▶️converted - поле, принимающее значение 1 или 0. 1 - совершил целевое действие за неделю, 0 - нет

🟠Что с этим делать? Можно разделить пользователей на 2 группы: активные (3+ захода в неделю) и неактивные. Потом для каждой группы найти среднее значение поля converted (процент конверсии) и сравнить проценты конверсий между собой.

В коде это будет выглядеть так:

import pandas as pd

######
#ИСТОЧНИК ДАННЫХ + СОЗДАНИЕ DF
######

df['is_active'] = df['logins_per_week'] >= 3

conv_active = df[df['is_active']]['converted'].mean()
conv_inactive = df[~df['is_active']]['converted'].mean()

print(f"Конверсия активных: {conv_active:.1%}")
print(f"Конверсия неактивных: {conv_inactive:.1%}")
print(f"{conv_active/conv_inactive:.2f}")

Предположим, что конверсия активных пользователей = 10%, неактивных = 7.5%. Отношение = 10%/7.5% = 1.33

🟠Но что дальше делать с полученным числом? Какие выводы и бизнес рекомендации можно сформулировать? Достаточно ли проведенных расчетов, чтобы доказать гипотезу? Откуда брать эту границу в "3 и более раз в неделю"? Как подготовить данные для анализа, чтобы получился такой же удобный датасет?

На эти и многие другие вопросы на примере реальных кейсов я отвечаю в курсе "Практический анализ данных на SQL и Python" - https://stepik.org/a/269469

Проконсультирую по материалам курса, помогу решить и объяснить сложные задачи и отвечу на все вопросы. Можете связаться со мной через обратную связь на Stepik или же написать напрямую в тг - @connection_07

Data Vault на практике

Data Vault - это один из видов архитектуры базы данных (альтернатива "звездочке", "снежинке" и т.п.). Включает в себя такие элементы, как:

▶️Hub - таблицы, содержащие бизнес ключи. Здесь хранятся разные id и номера.

▶️Satellite - таблицы, содержащие основную информацию по атрибутам.

▶️Link - таблицы, показывающие, как id и номера из хабов связаны между собой.

🟠Теперь о том, как это всё работает на практике. Предположим, у нас есть данные о транзакциях, которые надо организовать в виде Data Vault. Данные содержат информацию о том, кто что покупал? У кого покупал? Сколько покупал? И т.п.

1️⃣Начать можно с hub-таблиц. Там будет информация конкретно по каждому id.

То есть отдельно создается таблица под customer_id, которая помимо customer_id будет содержать разную техническую информацию: hash, дату загрузки, источник и т.п. И такие таблицы создаются отдельно для каждого вида id или номера: transaction_id, merchant_id, account_number и т.д.

2️⃣В satellite-таблицах раскрывается вся полезная информация, которая описывает сами сущности.

То есть, если брать информацию о клиентах, то в таких таблицах вместе с customer_id будут содержаться данные о e-mail, телефонах, ФИО, адресе, сегменте и т.п. Также там можно хранить историю изменений, введя дополнительные поля с датами.

Как и с hub-таблицами, отдельно под каждую сущность (клиенты, счета, транзакции...), как правило, создается своя satellite-таблица.

3️⃣Link-таблицы нужны для связи айдишников между собой. Например, вполне реальна ситуация, когда клиент может владеть несколькими счетами. Для правильного отображения этих множественных связей создается таблица с полями customer_id и account_number.

Если же нужно связать номер счета с номером транзакции, то создается таблица с полями account_number и transaction_id, и далее по аналогии.

Изредка возможен еще вариант link-таблицы, в которой есть связь более чем 2 атрибутов. Например, customer_id, account_number и transaction_id можно объединить в одну таблицу, потому что клиент может владеть несколькими счетами, а с каждого отдельного счета, в свою очередь, может совершаться множество транзакций.

🟠Также стоит отметить, что, говоря про разные айди в satellite-таблицах и в link-таблицах, я немного упрощаю. Эти таблицы для соблюдения всех правил data vault должны обязательно содержать информацию о хэше этих id, которые в конечном итоге приведут к hub-таблицам.

То есть link-таблицы связывают между собой не id, а hub-таблицы. Так же как и satellite-таблицы: они детально раскрывают информацию не по абстрактным id, а по сущностям именно из хабов.

Data analysis | Анализ данных | DA pinned «Дорогие подписчики! Сделал на Stepik курс по тематике нашего канала, с уклоном именно в практическую аналитику. Он будет полезен тем, кто проходит собеседования и тем, кто уже работает аналитиком. Курс учит полному циклу анализа данных, хорошо систематизирует…»

Schema Evolution

Эволюция схемы - это способность системы адаптировать структуру данных (таблицы, поля, типы) под новые требования без потери работоспособности всего того, что зависит от бд.

Применяется в случаях, когда:

▶️Нужно добавить или удалить столбцы

▶️Нужно изменить тип данных

▶️Меняется допустимость наличия NULL в полях

▶️Меняется значение по умолчанию

▶️Изменяются или добавляются ключи

▶️При денормализации / нормализации таблиц

▶️Нужно переименовывать поля и т.д.

🟠В чем суть Schema Evolution?

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

Например, если мы собираемся удалить поле из таблицы, то его нужно отметить, затем разорвать с ним все зависимости (например, в скриптах), всё протестировать и уже только потом удалить.

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

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

🟠Технически эволюция схемы реализуется следующим образом:

1️⃣Сначала необходимо спроектировать изменения, а именно понять, какие запросы, процедуры, отчеты и т.п. затронет изменение, то есть выявить связи

2️⃣Затем непосредственно написать сам DML/DDL-скрипт с INSERT / UPDATE / ALTER / DELETE и т.п.

3️⃣Протестировать где-нибудь в безопасной среде, например, на реплике или в песочнице

4️⃣После теста уже применить скрипт для основных данных

5️⃣В конце нужно не забыть удалить временные объекты и обновить документацию

Как быстро и просто рассчитать динамику в SQL

Предположим, имеется таблица с банковскими транзакциями, в которой нас интересуют лишь 2 поля:

▶️Дата транзакции
▶️Сумма транзакции

И стоит задача: нужно понять, как из месяца в месяц менялся общий оборот по всем счетам (в процентах).

Сделать это можно одним небольшим запросом, даже без использования СТЕ:

SELECT
LAST_DAY(date),
sum(amount),
LAG(sum(amount)) 
OVER (ORDER BY LAST_DAY(date)) AS prev,

(sum(amount) - LAG(sum(amount)) 
OVER (ORDER BY LAST_DAY(date))) 
/ NULLIF(LAG(sum(amount)) 
OVER (ORDER BY LAST_DAY(date)), 0) 
AS pct_change

FROM transactions
group by LAST_DAY(date)

Запрос выведет 4 столбца:

1️⃣последний день месяца (стандартная практика, подразумеваются данные за весь месяц)

2️⃣общую сумму транзакций за месяц

3️⃣общую сумму транзакций за предыдущий месяц

4️⃣процентное изменение общей суммы транзакций к предыдущему месяцу

🟠Работает это следующим образом. Фактически 1 и 2 столбцы - это все транзакции, просто агрегированные и сгруппированные по last_day (по месяцам).

Но к ним добавляются с помощью оконных функций LAG дополнительные поля 3 и 4. Столбец 3 (prev), который имеет вид

LAG(sum(amount)) 
OVER (ORDER BY LAST_DAY(date)) AS prev

рассчитывается просто как обычная функция LAG, только с оговоркой на группировку, поэтому в нем присутствует и SUM, и LAST_DAY. И фактически он является вспомогательным для расчета столбца 4. И если в расчете столбца 4 для наглядности убрать все синтаксические нагромождения, то получится это:

(sum(amount) - prev) 
/ NULLIF (prev, 0) 
AS pct_change

То есть типичный расчет динамики: из суммы текущего месяца вычитается сумма за прошлый месяц, и эта разница делится на сумму за прошлый месяц (с проверкой на деление на 0).

Про корреляционный анализ и причинно-следственные связи

Вспомнил про недавно проведенный корреляционный анализ и подумал, что уместно было бы раскрыть тему причинно-следственных связей. Ведь на первый взгляд это всё взаимосвязано и одно вытекает из другого, но на деле не всё так просто.

Та гипотеза звучала так: "Чем больше средний баланс клиента, тем больше его суммарные расходы". И действительно, проведя корреляционный анализ, мы могли обнаружить эту связь. Но по факту это просто 2 "сухих" показателя, которые могут двигаться в одном направлении с примерно одинаковой скоростью.

И не понятно, действительно ли из-за роста баланса люди начинают тратить больше? Или увеличивающиеся траты - предвестник увеличения баланса? Или же вообще есть сторонние факторы, которые если исчезнут, то эта корреляционная связь пропадёт?

🟠И раз возникает столько вопросов, то зачем тогда нужен корреляционный анализ?

▶️Его в основном используют как один из этапов разведочного анализа, что бы показать, что связь в принципе существует и что надо копать глубже.

▶️Еще он бывает полезен при прогнозировании, потому что в прогнозах само наличие какой-то устойчивой связи ценится высоко.

▶️Бывает, что корреляции помогают замечать аномалии. Если вдруг связь нарушается, то это может означать какое-то неприятное событие: сбой, мошенничество, изменение поведения и т.д.

🟠Теперь о том, как проверить причинно-следственные связи. Несколько самых релевантных способов:

▶️Самый простой и интуитивный способ - это посмотреть на даты в датасете. Если баланс вырос до того, как увеличились траты - значит, баланс влияет на траты. Если наоборот - значит, траты влияют на баланс.

Но есть одно но. Временная последовательность - это ещё не доказательство причинности. Оба события могли быть вызваны 3 фактором, который произошёл раньше.

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

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

▶️И вот мы плавно подошли ко второму способу - эксперименты. Чтобы убедиться в том, что связь действительно устойчива и не зависит ни от чего еще, можно провести А/Б тесты - т.е. одной случайной группе бонусами баланс завысить, а другую оставить как есть, и сравнить результаты.

Если первая группа будет больше тратить, то связь и правда есть и не зависит ни от чего больше. Если же нет, то надо искать другие факторы.

▶️Но бывает, когда нет возможности проводить такие эксперименты. В этой ситуации помогает метод Difference-in-Differences. Он может использоваться и внутри А/Б теста, и как отдельный подход.

Суть в том, что сравниваются уже не случайные экспериментальные данные, а реальные кейсы. Например, одной группе клиентов банк по какой-то бизнесовой логике начислил бонусы на баланс (не из-за эксперимента), а другой - нет. И надо оценить, сравнив эти 2 группы, насколько это сильное влияние.

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

Идея в следующем. Вот мы нашли связь баланс - траты. После этого нужно взять клиентов с одинаковыми признаками (пол, возраст, город, доход...), но с разным балансом. И внутри этой группы посмотреть, действительно ли клиенты, которые схожи по всему, кроме баланса, имеют тенденцию к увеличению трат по мере его роста?

Если связь между балансом и тратами еще есть, значит, баланс влияет на поведение. Если связь ослабла, значит, дело не в балансе, а в чем-то еще.

Но есть минус - регрессия контролирует только те факторы, которые уже есть в данных.

Как хранить историю изменений в DWH

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

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

🟠На помощь приходит подход Slowly Changing Dimensions (SCD). Идея в том, чтобы трансформировать таблицу измерений в более информативную. Например, в таблице "Клиенты" помимо содержащихся там полей:

▶️id
▶️ФИО
▶️Сегмент и т.п.

добавить еще поля:

▶️Дата начала
▶️Дата окончания
▶️Флаг текущей версии
▶️id представить как 2 ключа: бизнес ключ (старый id, например номер договора) и суррогатный ключ

Отдельно прокомментирую разбивку на ключи. Это нужно для того, чтобы у строки был уникальный идентификатор. Потому что бизнес ключ остается одним и тем же - он определяет клиента Иванова И.И. как отдельного человека (например по уникальному номеру договора). Но суррогатный ключ, если можно так сказать - "технический" в рамках таблицы измерений, и постоянно меняется с каждой новой версией.

Допустим, у Иванова И.И. обновился сегмент. Как это будет выглядеть для ключей:

БК = 1; СК = 1; ФИО = Иванов И.И; Сегм = РБ
-------------------------------
БК = 1; СК = 2; ФИО = Иванов И.И; Сегм = МСБ

🟠На фоне этого возникает вопрос: как джойнить эту таблицу с фактами? Ведь до SCD таблицы соединялись по БК, который всегда был уникальным, а теперь он стал дублироваться. Решение простое - нужно теперь джойнить по СК, а для этого его необходимо добавить еще и в таблицу фактов как дополнительный атрибут.

Сделать это можно постфактум. Например, в таблице фактов отражено, что 1-ого числа была совершена продажа Иванову И. И. В клиентской таблице смотрим, какой СК был актуален у Иванова И. И. на 1-ое число, и просто заносим его в факты, предварительно добавив там новое поле.

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

Фреймы оконных функций в PL/SQL

Ранее в разборе скрипта я упомянул запись ROWS UNBOUNDED PRECEDING. Подобные записи называются фреймами. Рассмотрим подробнее, какие фреймы вообще бывают и для чего они нужны.

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

Все фреймы в PL/SQL имеют строго регламентированный вид записи и пишутся только лишь в контексте оконных функций, то есть внутри OVER().

Шаблон фреймов таков: сначала задается метод (ROWS или RANGE), а затем диапазон использования. То есть они имеют такой общий вид:

{ ROWS | RANGE }
[ BETWEEN
{ UNBOUNDED PRECEDING | N PRECEDING | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | N FOLLOWING | CURRENT ROW }
]

🟠Как уже упомянул ранее, сначала выбирается ROWS или RANGE. Отличие между ними в том, что ROWS считает по каждой строке, а RANGE - по каждому уникальному значению.

Если очень грубо, то для аналогии можно привести в пример ранжирующие функции: ROWS работает как row_number(), а RANGE как rank().

🟠Затем мы устанавливаем диапазон (границы), в рамках которого будет считаться оконная функция. Всегда обязательно задавать после ROWS | RANGE верхнюю границу диапазона, которая может принимать только лишь 1 из 3 значений:

▶️ UNBOUNDED PRECEDING - от самой первой строки в выборке, то есть оконная функция идет с самого начала, постепенно накапливая строки. Особенно популярно в расчете накопленной суммы.

▶️ N PRECEDING - оконная функция затрагивает строки, которые находятся в N позиций ДО текущей (включительно). Обработка идет со смещением. Наиболее часто используется при расчете скользящих средних.

▶️ CURRENT ROW - по сути то же самое, что и N PRECEDING, только N = 0 всегда. Функция просто возвращает текущую строку

Идем дальше. Задать только лишь нижнюю границу нельзя.

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

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

🟠Нижняя граница может принимать только лишь 1 из 3 значений:

▶️ UNBOUNDED FOLLOWING
▶️ CURRENT ROW
▶️ N FOLLOWING

Тут всё по аналогии с верхней границей, с той лишь разницей, что это - нижняя граница.

🟠Теперь на примере. Допустим, есть такая оконная функция:

SUM(Sales) OVER (ORDER BY Date)

В данном случае фрейм тут не указывается, и PL/SQL по умолчанию использует

RANGE UNBOUNDED PRECEDING

Но это используется только когда в OVER есть ORDER BY. В остальных случаях фреймы по умолчанию не применяются, что эквивалентно записи

ROWS BETWEEN UNBOUNDED PRECEDING 
AND UNBOUNDED FOLLOWING 

🟠Вообще самых разных фреймов можно составить не так уж и много, но наиболее часто используемые из них:

SUM(Sales) OVER (ORDER BY Date 
ROWS UNBOUNDED PRECEDING)

Используется в основном тогда, когда мы хотим посчитать накопленную сумму правильно, то есть учитывая каждую строку (ROWS), без пропуска одинаковых значений (RANGE).

Если вдруг захочется посчитать накопленную сумму только за последние N строк и потом ее постоянно обновлять (скользящая сумма), то запишем:

SUM(Sales) OVER (ORDER BY Date 
ROWS N PRECEDING)

В случае, если считаем по дням, то просто лучше заменить ROW на RANGE, т.к. продаж может быть больше 1 за день и они все будут суммироваться внутри одной даты. И результат будет выводиться уже за последние N дней, а не N строк.

Но вообще это часто используется при расчете скользящих средних.

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

SUM(Sales) OVER (ORDER BY Date 
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

Используется в основном при расчете каких-нибудь остатков.

Что такое реплики баз данных?

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

▶️Для отказоустойчивости.

Если с основной БД что-то случается, то реплика становится на её место и перенимает весь её функционал до тех пор, пока проблема не устранится.

В тот момент, когда основная БД становится снова доступна, она, как правило, сама становится репликой. А старая реплика становится основной БД.

▶️Для снижения нагрузки на основную систему.

Базы данных используются для очень многих процессов: туда и загружаются данные, и добавляются новые столбцы, и происходят обновления/удаления и т.д. и т.п.

И еще из БД данные выгружаются. И чтобы не нагружать БД еще и разными выгрузками, делаются реплики в виде read-only (откуда данные можно только выгружать). Это делается для того, чтобы основная БД не использовалась для выгрузок и чтобы распределить лишнюю нагрузку.

Также это помогает снижать риск случайной порчи данных из-за человеческого фактора или из-за ошибки системы.

🟠Выделяют основные виды реплик:

▶️Read-only replica, упоминалась чуть выше. Это та реплика, которая позволяет только читать данные, но не изменять их.

▶️Hot standby. По сути, это тоже реплика, которая принимает запросы только на выборку, но которая в случае сбоев основной БД может её заменить.

▶️Warm/cold standby. Это реплика, которая вообще никак не используется ни для каких запросов, которая просто существует. Но в случае сбоев основной БД принимает весь её функционал.

🟠Базовые этапы построения реплик выглядят так:

1️⃣Сначала делается полная копия БД на определенный момент времени, т.е. её снимок. Таким образом реплика создаётся, принимается ее начальное состояние.

2️⃣Одновременно эта позиция фиксируется с помощью логов в журнале транзакций, чтобы потом иметь понимание, с какого состояния эту реплику поддерживать

3️⃣Затем с помощью логов в журнале транзакций фиксируются изменения, которые происходят с БД

4️⃣В итоге эти изменения непрерывно передаются на реплику, чтобы она соответствовала актуальной версии БД. Таким образом реплика поддерживается.

P.S. Репликация и CDC (про который писал ранее) основаны на одном и том же механизме - чтении журнала транзакций, но решают разные задачи.

Аналитический кейс на SQL

Предположим, в базе данных есть таблица accounts с банковскими счетами. Основные столбцы:

▶️account_id - айди счета
▶️balance - размер средств на счете на текущий момент

Надо понять, на каком проценте счетов содержится большая часть средств (допустим, до 80% средств)? Это довольно популярная метрика, которая показывает степень диверсификации и зависимости от крупных клиентов. Плюсом было бы неплохо автоматизировать расчет этой метрики.

Здесь будем использовать СТЕ и оконные функции.

WITH ranked as (
SELECT account_id, 
balance, 
SUM(balance) OVER () AS total_balance,
SUM(balance) OVER (ORDER BY balance DESC 
ROWS UNBOUNDED PRECEDING) AS c_balance
FROM accounts
) 

SELECT 
ROUND(COUNT(*)
/MAX((SELECT COUNT(*) FROM accounts)), 2)
,MAX(ROUND(c_balance/total_balance, 2))
FROM ranked 
WHERE ROUND(c_balance/total_balance, 2) <= 0.8

В первой таблице - ranked - объявляются 2 оконные функции:

— total_balance будет всегда показывать одно и то же число - общий размер портфеля

— c_balance будет отражать накопленную сумму по балансам счетов, и начинать будет с самого крупного счета. Запись ROWS UNBOUNDED PRECEDING нужна для того, чтобы в случае, если баланс на 2+ счетах одинаков, оконная функция их не пропускала.

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

На выходе у нас получается таблица только лишь с 2 числами. Первое число будет отражать процент счетов, второе - долю средств на этих счетах в общем портфеле.