Data analysis - DA SQL

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).

Операторы ANY и ALL в SQL ANY, ALL (и синоним SOME, который эквивалентен ANY) в SQL - это операторы, которые позволяют сравнивать значение со множеством значений, получаемым из подзапроса или массива. Как пример использования ANY: SELECT name FROM products WHERE price > ANY (SELECT price FROM competitors) То же самое с ALL: SELECT name FROM products WHERE price > ALL (SELECT price FROM competitors) Нужны эти операторы для того, чтобы возвращать значения в случае, если выполняется хоть 1 условие из конструкции (ANY) или если выполняются все условия (ALL). Применительно к нашему примеру. В первом запросе подзапрос выводит просто массив чисел (цен). Допустим, это 5, 10, 15. И далее основной запрос выводит те продукты, у которых цена больше хотя бы одного числа (цены) из массива подзапроса. То есть если продукт имеет цену 9, то запрос выведет его название, потому что 9 > 5 (хотя бы одного числа). Во втором запросе всё то же самое, только выводятся те продукты, у которых цена больше ВСЕХ чисел (цен) из массива подзапроса. То есть если цена как раньше 9, то запрос не выведет ничего. Нужна минимально цена 16, чтобы это было больше и 5, и 10, и 15. 🟠Стоит отметить, что ALL в этом контексте и ALL, например, в UNION ALL - это совершенно разные вещи. В первом случае ALL используется просто как логический оператор (грубо говоря, наподобие AND и OR), во втором случае - это часть UNION, которая отвечает только за то, будут ли выведены дубликаты при объединении или нет, то есть тут нет логико-условной составляющей. 🟠По принципу работы ANY и ALL иногда похожи на MIN и MAX. Если посмотреть внимательнее на предыдущие примеры, то в первом случае выведутся просто те наименования продуктов, цена которых больше минимальной из массива. Во втором случае - больше максимальной. 🟠Также ANY может быть и полным аналогом IN. Например: SELECT * FROM orders WHERE customer_id = ANY (SELECT id FROM clients) то же самое, что и SELECT * FROM orders WHERE customer_id IN (SELECT id FROM clients) 🟠 Иногда еще используется как альтернатива EXISTS, чтобы просто проверить на существование. Например: SELECT 1 WHERE 10 < ANY (SELECT price FROM competitors) Если хоть одна цена из подзапроса больше 10, то запрос выведет 1. Если нет, то ничего.

Сегментация с помощью K-means на Python Предположим, что надо сегментировать банковские точки продаж по признакам: ▶️Число клиентов ▶️Доход (млн руб.) ▶️Конверсия в продукты ▶️Среднее время обслуживания (мин) ▶️Жалобы на 1000 клиентов ▶️Число конкурентов рядом Вообще, сегментация через k-means осуществляется по принципу "чем меньше расстояние между значениями, тем лучше". То есть этот метод не различает контекст и будет просто объединять максимально похожие друг на друга точки в одну группу. Это не скоринг, где точки будут отсортированы по рейтингу. И результаты сегментации надо будет интерпретировать уже вручную в зависимости от того, что получится. 🟠В нашем контексте это будет полезно для того, чтобы определить, какие паттерны вообще существуют и что делать. То есть может получиться образцовая группа, где высокий доход, низкие жалобы и высокая конверсия. Вторая группа может иметь много клиентов, но медленное обслуживание и высокие жалобы. И в этом случае для второй группы надо будет решать специфические проблемы, например, как эти точки разгрузить. А третья группа может иметь мало клиентов и мало конкурентов - это уже другой пул проблем и задач. 🟠Итак, приступим. Сначала как обычно импортируем библиотеки и подключаемся к источнику данных. Также нужно будет отдельно выделить признаки для сегментации в датафрейм: import pandas as pd from sklearn.preprocessing import StandardScaler from sklearn.cluster import KMeans ###### #ИСТОЧНИК ДАННЫХ + СОЗДАНИЕ DF ###### features = [ 'clients', 'income_mln', 'conversion_pct', 'service_time_min', 'complaints_per_1k', 'competitors_nearby' ] X = df[features] Затем важный шаг - нормализация данных. Она приводит все данные к одному масштабу. Это особенно важно в нашем случае, потому что все признаки у нас имеют разные единицы измерения. И чтобы не происходило смешивания, этот метод приводит каждое значение к "общему знаменателю", рассчитывая среднее и стандартное отклонение по каждому столбцу. scaler = StandardScaler() X_scaled = scaler.fit_transform(X) Наконец, запускаем саму сегментацию. Для этого нужно настроить сам алгоритм. То есть указать количество кластеров (пусть будет n_clusters=4), проставить random_state (чтобы алгоритм всегда выдавал один и тот же результат, начиная с одной точки) и указать число повторений (n_init=10) для бОльшей точности. И затем этот алгоритм применить уже к нормализованным данным (X_scaled): kmeans = KMeans(n_clusters=4, random_state=42, n_init=10) clusters = kmeans.fit_predict(X_scaled) df['cluster'] = clusters print(df.groupby('cluster')[features].mean().round(2)) В итоге получится 4 группы точек продаж, сгруппированных по ранее указанным признакам. Для каждой группы будет указано среднее значение каждого признака. Если захочется вывести id конкретных точек в каждой группе, то можно написать: for cluster_id in sorted(df['cluster'].unique()): ids = df[df['cluster'] == cluster_id]['branch_id'].tolist() print(f"Кластер {cluster_id}: {ids}") Выведет 4 списка (по 1 на группу), где будут указаны номера точек продаж.

Что такое Data Mesh? Data Mesh - относительно новое понятие в управлении данными, которое используется как альтернатива традиционным DWH. 🟠Главный акцент тут - на децентрализованности. В классическом подходе хранилища данных обслуживает какая-то конкретная специализированная на этом команда. Она, как правило, извлекает данные из источников, трансформирует их и загружает в DWH, которым потом пользуются разные отделы в компании. При таком подходе существуют свои недостатки. Например, при увеличении объема данных нагрузка на команду DWH так же увеличивается, что приводит либо к росту издержек, либо к снижению скорости обработки, либо к снижению качества и т.д. Также инженеры могут находиться в отрыве от всех бизнесовых контекстов и тонкостей и заниматься только техническими вопросами, что может приводить к неточностям. 🟠Подход Data Mesh решает эти недостатки тем, что смещает фокус обслуживания только лишь с 1 команды на все отделы в компании. Условно, продуктовые отделы, отделы маркетинга и финансов теперь сами отвечают за размещение и поддержку тех данных, которыми они пользуются или которые они производят. И хоть идея подхода звучит красиво, на деле имеет множество недостатков: Во-первых, чтобы это было оправданно, компания должна быть реально большой, иначе затраты и усилия по созданию и поддержке такой архитектуры будут слишком велики. Во-вторых, в разных отделах теперь будут нужны сотрудники, которые одновременно и технически подкованы, чтобы управлять данными своего отдела, и разбираются в бизнес-контексте своих данных. В-третьих, нужна отличная согласованность между разными отделами в компании, чтобы данные в итоге получались едиными и чтоб не выходило так, что у каждой команды свой формат данных. Ну и также, скорее всего, нужно будет возиться с доступами. И вероятнее всего могут возникнуть проблемы при миграции уже существующих данных с традиционного DWH в Data Mesh. 🟠Но а в целом, если решить все эти вопросы, то Data Mesh даст преимущества в виде: ▶️Масштабируемости, потому каждый новый домен добавляется автономно ▶️Качества, скорости и актуальности данных, потому что за каждыми данными будет стоять своя компетентная команда ▶️Гибкости, потому что под данные можно будет выбирать не только, например, традиционные СУБД, а перемешивать и NoSQL, и Data Lake, и колоночные СУБД и т.д. Главное, чтоб это было согласованно.

RFM-анализ на SQL Один из довольно часто встречающихся на практике кейсов - сегментация пользователей (или RFM-анализ). Цель анализа - разделить клиентов на группы (сегменты), чтобы понять, кто из них самый ценный, кто требует внимания, а кого, возможно, уже не вернуть. Осуществляется на основе: ▶️Recency — когда последний раз покупал? ▶️Frequency — сколько раз покупал? ▶️Monetary — сколько потратил? Базовый запрос выглядит так: WITH clients as ( SELECT customer_id, CURRENT_DATE - MAX(order_date) AS recency_days, COUNT(order_id) AS frequency, SUM(price) AS monetary_value FROM orders GROUP BY customer_id) Из таблицы заказов считается разница между сегодняшним числом (или отчетной датой) и датой последнего заказа, а также считается число заказов и их сумма в разрезе каждого клиента. Далее подключается скоринговая модель. Основной смысл таков: разбить клиентов на 5 квантилей и на основании этих 3 показателей присвоить им рейтинги. Для Recency ценятся наиболее свежие даты, а для Frequency и Monetary - чем больше, тем лучше. Используем оконную функцию NTILE(5), которая делит клиентов на 5 равных частей: SELECT customer_id, NTILE(5) OVER (ORDER BY recency_days) AS r_score, NTILE(5) OVER (ORDER BY frequency DESC) AS f_score, NTILE(5) OVER (ORDER BY monetary_value DESC) AS m_score FROM clients На выходе получится сгруппированная ранее таблица клиентов, к которой добавится 3 столбца, в каждом из которых будет число от 1 до 5 - это будет рейтинг клиента. С этими рейтингами можно поступать по-разному. Можно всё агрегировать в единый итоговый 15-балльный рейтинг, а можно оставить как есть и смотреть рейтинги в разрезе 3 показателей - тут зависит от целей.

Регрессия с контролем ковариат на Python Допустим, есть такие данные: ▶️customer_id - ID клиента ▶️avg_balance - Средний баланс за период ▶️total_spent - Суммарные траты за тот же период ▶️age - Возраст ▶️income - Доход (если есть) ▶️months_with_bank - Сколько клиент уже в банке (в месяцах) ▶️num_products - Количество продуктов в банке И есть гипотеза: "Чем больше средний баланс клиента, тем больше его суммарные расходы". Предположим, что мы провели корреляционный анализ и выяснили, что связь действительно есть. Теперь нужно выяснить причину этой связи: действительно ли баланс влияет на траты? Или же это траты влияют на баланс? Или есть какие-то иные факторы? И сейчас, продолжая тему причинно-следственных связей, построим регрессию с контролем ковариат, чтобы это выяснить. import pandas as pd import numpy as np import statsmodels.api as sm ###### #ИСТОЧНИК ДАННЫХ + СОЗДАНИЕ DF ###### X_simple = sm.add_constant(df[['avg_balance']]) y = df['total_spent'] model_simple = sm.OLS(y, X_simple).fit() print(model_simple.summary().tables[1]) Первым делом всё импортируем, создаём датафрейм и строим обычную линейную регрессию. Данные выведутся в виде таблицы, где нам важна ось Х. Таблица содержит несколько "технических" полей, которые в целом можно и проигнорировать. Можно оставить только самое важное: ▶️coef - коэффициент, показывающий, на сколько руб. вырастут суммарные траты при увеличении среднего баланса на 1 рубль. ▶️P>|t| - коэффициент стат значимости, показывает, насколько данным можно доверять. Если < 0.05, то всё хорошо. ▶️[0.025 0.975]. Доверительный интервал, который показывает "разброс" значений первого коэффициента coef. То есть траты не обязательно должны расти, например, на 0.4. Они могут вырасти и на 0.39, и на 0.41 - и это будет ок. 🟠Всё это может напоминать уже проведенный ранее корреляционный анализ, но всё же есть отличия. Корреляция Спирмена (которая использовалась ранее) отличается от линейной регрессии тем, что она даёт только лишь 1 число - коэффициент корреляции, который показывает лишь силу связи. Линейная регрессия же показывает уже конкретно с помощью coef, какой будет эффект. Также корр. Спирмена не учитывает причинность, тогда как линейная регрессия оценивает именно то, на сколько в среднем отличаются траты у клиентов с более высоким балансом при прочих равных. 🟠Еще стоит отметить, что это - первоначальные данные, которые просто описывают исходный датасет. Далее с этими данными мы будем сравнивать результаты ковариатов. 🟠Идем дальше: models = { 'age': ['age'], 'income': ['income'], 'tenure': ['months_with_bank'], 'products': ['num_products'] } for name, cov in models.items(): X = sm.add_constant(df[cov + ['avg_balance']]) coef = sm.OLS(y, X).fit().params['avg_balance'] print(f"С контролем {name:10}: {coef:.4f}") Этот скрипт построит регрессию с контролем ковариат (поля age, income, months_with_bank и num_products) и сравнит её с первоначальной. На выходе получим 4 значения coef при avg_balance - по одному для каждой модели с контролем одного фактора. Там, где эта разница больше всего (например, было 0.4, а стало 0.2 при факторе income), там и потенциально скрыто наибольшее влияние на траты. Следовательно, в дальнейшем анализе надо будет присмотреться не к avg_balance как к причине роста трат, а к income.

Про корреляционный анализ и причинно-следственные связи Вспомнил про недавно проведенный корреляционный анализ и подумал, что уместно было бы раскрыть тему причинно-следственных связей. Ведь на первый взгляд это всё взаимосвязано и одно вытекает из другого, но на деле не всё так просто. Та гипотеза звучала так: "Чем больше средний баланс клиента, тем больше его суммарные расходы". И действительно, проведя корреляционный анализ, мы могли обнаружить эту связь. Но по факту это просто 2 "сухих" показателя, которые могут двигаться в одном направлении с примерно одинаковой скоростью. И не понятно, действительно ли из-за роста баланса люди начинают тратить больше? Или увеличивающиеся траты - предвестник увеличения баланса? Или же вообще есть сторонние факторы, которые если исчезнут, то эта корреляционная связь пропадёт? 🟠И раз возникает столько вопросов, то зачем тогда нужен корреляционный анализ? ▶️Его в основном используют как один из этапов разведочного анализа, что бы показать, что связь в принципе существует и что надо копать глубже. ▶️Еще он бывает полезен при прогнозировании, потому что в прогнозах само наличие какой-то устойчивой связи ценится высоко. ▶️Бывает, что корреляции помогают замечать аномалии. Если вдруг связь нарушается, то это может означать какое-то неприятное событие: сбой, мошенничество, изменение поведения и т.д. 🟠Теперь о том, как проверить причинно-следственные связи. Несколько самых релевантных способов: ▶️Самый простой и интуитивный способ - это посмотреть на даты в датасете. Если баланс вырос до того, как увеличились траты - значит, баланс влияет на траты. Если наоборот - значит, траты влияют на баланс. Но есть одно но. Временная последовательность - это ещё не доказательство причинности. Оба события могли быть вызваны 3 фактором, который произошёл раньше. Например, баланс мог вырасти из-за увеличения дохода. То есть по вполне естественным причинам. И клиент стал больше тратить. Но стал больше тратить не из-за роста баланса, а из-за роста своего дохода. Если же убрать естественный фактор и заменить его искусственным, то есть просто начислить ни с того ни с сего бонусы на баланс, то увеличение баланса в этом случае может не гарантировать рост трат, потому что клиент может решить сэкономить деньги. В этом случае связь баланс - расходы нарушится, поэтому надо учитывать дополнительные факторы. ▶️И вот мы плавно подошли ко второму способу - эксперименты. Чтобы убедиться в том, что связь действительно устойчива и не зависит ни от чего еще, можно провести А/Б тесты - т.е. одной случайной группе бонусами баланс завысить, а другую оставить как есть, и сравнить результаты. Если первая группа будет больше тратить, то связь и правда есть и не зависит ни от чего больше. Если же нет, то надо искать другие факторы. ▶️Но бывает, когда нет возможности проводить такие эксперименты. В этой ситуации помогает метод Difference-in-Differences. Он может использоваться и внутри А/Б теста, и как отдельный подход. Суть в том, что сравниваются уже не случайные экспериментальные данные, а реальные кейсы. Например, одной группе клиентов банк по какой-то бизнесовой логике начислил бонусы на баланс (не из-за эксперимента), а другой - нет. И надо оценить, сравнив эти 2 группы, насколько это сильное влияние. ▶️Есть еще регрессия с контролем ковариат - это самый логичный шаг сразу после проведения корреляционного анализа. И хоть это не покажет причинность, но уже будет ближе к ней, чем простая корреляция. Идея в следующем. Вот мы нашли связь баланс - траты. После этого нужно взять клиентов с одинаковыми признаками (пол, возраст, город, доход...), но с разным балансом. И внутри этой группы посмотреть, действительно ли клиенты, которые схожи по всему, кроме баланса, имеют тенденцию к увеличению трат по мере его роста? Если связь между балансом и тратами еще есть, значит, баланс влияет на поведение. Если связь ослабла, значит, дело не в балансе, а в чем-то еще. Но есть минус - регрессия контролирует только те факторы, которые уже есть в данных.

Что такое реплики баз данных? Начнем с того, что реплика - это копия основной базы данных (или её части). Реплика используется для многих целей. Как пример: ▶️Для отказоустойчивости. Если с основной БД что-то случается, то реплика становится на её место и перенимает весь её функционал до тех пор, пока проблема не устранится. В тот момент, когда основная БД становится снова доступна, она, как правило, сама становится репликой. А старая реплика становится основной БД. ▶️Для снижения нагрузки на основную систему. Базы данных используются для очень многих процессов: туда и загружаются данные, и добавляются новые столбцы, и происходят обновления/удаления и т.д. и т.п. И еще из БД данные выгружаются. И чтобы не нагружать БД еще и разными выгрузками, делаются реплики в виде read-only (откуда данные можно только выгружать). Это делается для того, чтобы основная БД не использовалась для выгрузок и чтобы распределить лишнюю нагрузку. Также это помогает снижать риск случайной порчи данных из-за человеческого фактора или из-за ошибки системы. 🟠Выделяют основные виды реплик: ▶️Read-only replica, упоминалась чуть выше. Это та реплика, которая позволяет только читать данные, но не изменять их. ▶️Hot standby. По сути, это тоже реплика, которая принимает запросы только на выборку, но которая в случае сбоев основной БД может её заменить. ▶️Warm/cold standby. Это реплика, которая вообще никак не используется ни для каких запросов, которая просто существует. Но в случае сбоев основной БД принимает весь её функционал. 🟠Базовые этапы построения реплик выглядят так: 1️⃣Сначала делается полная копия БД на определенный момент времени, т.е. её снимок. Таким образом реплика создаётся, принимается ее начальное состояние. 2️⃣Одновременно эта позиция фиксируется с помощью логов в журнале транзакций, чтобы потом иметь понимание, с какого состояния эту реплику поддерживать 3️⃣Затем с помощью логов в журнале транзакций фиксируются изменения, которые происходят с БД 4️⃣В итоге эти изменения непрерывно передаются на реплику, чтобы она соответствовала актуальной версии БД. Таким образом реплика поддерживается. P.S. Репликация и CDC (про который писал ранее) основаны на одном и том же механизме - чтении журнала транзакций, но решают разные задачи.