Data analysis - DA SQL

INTERSECT и EXCEPT/MINUS в SQL Все мы знаем такие операторы, как UNION и UNION ALL, которые объединяют результаты двух и более SELECT в одну таблицу (без дубликатов и с дубликатами соответственно). Но существуют еще менее популярные, но иногда полезные аналоги традиционных UNION: INTERSECT и EXCEPT (или же MINUS в Oracle). Все эти команды используются в одном и том же месте в запросах - между SELECT: SELECT col1, col2... FROM table1... [UNION | UNION ALL | INTERSECT | EXCEPT (MINUS)] SELECT col1, col2... FROM table2... При этом для всего этого действуют такие же общие правила: количество столбцов в запросах должно быть одинаковым; типы данных должны быть сопоставимы; наличие 2 и более NULL считается дубликатами и т.д. ▶️INTERSECT нужен для того, чтобы выводить строки, которые присутствуют в обоих SELECT, то есть этот оператор отвечает за пересечение. При этом, что логично, эта функция удаляет дубликаты в итоговом наборе данных, выводя только одно значение. ▶️EXCEPT (MS SQL) / MINUS (Oracle) работают иначе. Эти операторы возвращают те строки, которые есть в первой таблице, но которых нет во второй таблице. Грубо говоря, это как из первой таблицы вычесть вторую. Допустим, есть такие данные: Табл. 1: [1, 2, 3, 4, 5] Табл. 2: [2, 3, 4, 6] В случае, если запрос имеет такую логику: Табл. 1 EXCEPT / MINUS Табл. 2 то выведутся числа 1 и 5. Если же таблицы поменяются местами, то выведется просто число 6. Точно так же, как и с INTERSECT, удаляются дубликаты. При этом, если в одной таблице есть дубликаты с одним значением, а в другой таблице такого значения нет вовсе, то считается, что пересечения нет, следовательно, выведется значение из первой таблицы без дубликатов. 🟠Какова же логика работы этих операторов в случаях, когда нужно объединять 3 и более SELECT? ▶️В случае с INTERSECT всё работает последовательно: таблица А соотносится с таблицей В, получается результат, и потом этот результат соотносится с таблицей С, и т.д. Но из-за математических свойств пересечения множеств, по факту, нет никакой разницы, в каком порядке идут таблицы, хоть все одновременно. Результат будет один и тот же. ▶️EXCEPT (MS SQL) / MINUS (Oracle) тоже работают последовательно, точно так же как и INTERSECT. Но тут нужно обращать особое внимание на то, в каком порядке расположены таблицы, и также не забывать про то, какими будут промежуточные результаты.

GeoPandas в Python GeoPandas - это надстройка над Pandas в Python, которая используется для географического анализа. Главная её особенность - поддержка географических объектов с помощью GeoDataFrame и GeoSeries. ▶️GeoDataFrame - это, по сути, тот же DataFrame, только с поддержкой географических объектов в столбце geometry. Создается он следующим образом: import geopandas as gpd import pandas as pd #data = Данные с координатами df = pd.DataFrame(data) gdf = gpd.GeoDataFrame( df, geometry=gpd.points_from_xy(df['lon'], df['lat']), crs="EPSG:4326" ) Получится та же самая таблица, что и при обычном df, только координаты (широта и долгота) будут записаны в специальном формате в отдельном столбце geometry в виде point(). Позже их можно будет использовать для разных пространственных расчетов: нахождение площади, расстояния, построение границ и т.д. ▶️GeoSeries - это составная часть GeoDataFrame, которая представляет собой только лишь географические объекты в специальных форматах, в т.ч. и в формате point(). Может быть как и отдельной точкой, так и массивом. К примеру, отдельную точку можно создать так: import geopandas as gpd #координаты: target_lon, target_lat point = gpd.GeoSeries( gpd.points_from_xy([target_lon], [target_lat]), crs="EPSG:4326" ) 🟠Теперь о том, как это использовать на практике. Допустим, по городу расставлено множество точек продаж. И нам нужен алгоритм, который будет выдавать точки в радиусе 1 км от заданной. Итак, мы имеем уже созданный GeoDataFrame, который содержит координаты всех наших точек, и созданный GeoSeries, где мы будем задавать исходную точку. Сначала надо перевести всё в метрическую систему UTM, чтобы получить результаты в виде метров, а не градусов (по сути шаблонный код): import geopandas as gpd zone = int((target_lon + 180) // 6) + 1 epsg_utm = 32600 + zone if target_lat >= 0 else 32700 + zone gdf_utm = gdf.to_crs(epsg=epsg_utm) point_utm = point.to_crs(epsg=epsg_utm) И затем уже рассчитать расстояние между заданной точкой и точками в GeoDataFrame и вывести те, у которых расстояние менее 1000: gdf_utm['distance_m'] = gdf_utm.geometry.distance(point_utm.iloc[0]) nearby = gdf_utm[gdf_utm['distance_m']

Якорная модель (anchor modeling) Якорное моделирование - это метод моделирования хранилища данных. Считается, что это более усовершенствованная версия Data Vault. В anchor modeling используются такие элементы, как: ▶️Якоря. Это сами сущности, В этих таблицах хранятся только уникальные идентификаторы и еще, возможно, суррогатные ключи и метаданные загрузки. ▶️Атрибуты. В этих таблицах хранятся описания свойств сущностей, а еще и ссылки на якоря, временные промежутки (дата начала и окончания) и источник самих данных (метаданные). ▶️Связи. В случае, если таблицы-якоря связаны между собой сложнее принципа "один к одному", создаются таблицы-связи, где напротив каждого id записан другой id, который с ним связан. Иными словами, это полный аналог link-таблиц из Data Vault. ▶️Узлы. Можно сказать, это вспомогательные таблицы для атрибутов. Вместо того, чтобы каждый раз записывать в атрибутах значения, эти значения представить в виде id, а сами значения вынести уже в таблицы-узлы. Но можно этого и не делать, это необязательно. 🟠Отличие от Data Vault заключается в том, что Data Vault делит данные на хабы, линки и сателлиты. Якорная модель просто продолжает эти идеи, но с некоторыми отличиями: вместо больших таблиц-сателлитов, которые содержали в себе много атрибутов, создаются отдельные мини-таблицы под каждый атрибут. Также вместо хаба теперь якорь - по сути, то же самое, только чуть более минималистично. Link-таблицы полностью сохранены. И еще дополнительно добавлены узлы. То есть якорная модель более нормализована, чем Data Vault. И еще одна ключевая особенность - якорная модель хранит историю изменений в DWH. Реализовано это с помощью тех самых дат начала и конца, которые указываются в атрибутах. И каждый атрибут в этой модели должен иметь эти записанные интервалы. 🟠На практике всё может выглядеть следующим образом. Предположим, есть типичная БД с данными о клиентах, товарах и заказах. Сначала создаются 2 таблицы-якоря: одна с клиентскими данными (client_id, суррогатный ключ и метаданные загрузки), другая - с данными о товарах (prod_id, суррогатный ключ и метаданные). Затем создаётся таблица связей - она же таблица с заказами. В ней указывается, какие client_id с какими prod_id соотносятся. Но в более сложных моделях можно вынести номер транзакции в отдельный, третий якорь. Далее создаются таблицы атрибутов. Создается отдельная таблица для имени клиента, которая включает в себя суррогатный ключ, имя, даты начала и конца и источник. Затем создается отдельная таблица для города клиента, которая аналогична предыдущей таблице. И так далее для всех атрибутов, в том числе и товарных. Также в атрибутах указываются еще и данные о заказах: сумма заказа, количество, статус и т.д. Также для оптимизации могут создаваться и узлы. Например, в таблице с городами скорее всего будет много значений, которые еще и будут дублироваться. И в этом случае названия городов в таблице с атрибутами можно заменить на id, а сами названия городов вынести в отдельную таблицу-справочник (узел). Это не только уменьшит вес таблицы, но еще и защитит данные в случае, если город вдруг сменит своё название.

GROUPING SETS в SQL GROUPING SETS, или расширенная группировка, используется тогда, когда нужно получить несколько вариантов группировки в одном запросе (аналог выполнения нескольких GROUP BY с UNION ALL). В скриптах это выглядит следующим образом: SELECT department_id, job_id, SUM(salary) as total_sal FROM employees GROUP BY GROUPING SETS ( (department_id, job_id), (department_id), (job_id), () ) Основная цель - просуммировать поле salary и вывести эту сумму в разрезе department_id и job_id в разных вариациях. И для этого в GROUPING SETS задаются несколько вариантов группировок: 1️⃣По department_id и job_id 2️⃣По department_id 3️⃣По job_id 4️⃣Общая сумма 🟠В результате этого запроса выведутся 4 варианта группировки, наложенные друг на друга как при UNION. Также в конце запроса рекомендуется применять ORDER BY, чтобы было можно явно отделить варианты друг от друга. При 1 варианте группировки будет сумма в разрезе department_id + job_id. Во 2 варианте будет сумма в разрезе только лишь department_id, а поле job_id примет значение NULL. 3 вариант будет аналогичен 2, только с другим полем. 4 вариант выведет просто общую сумму, а поля department_id и job_id будут NULL. 🟠Еще бывают ситуации, когда NULL является не итоговым результатом, а частью данных в столбцах. И чтобы не запутаться в этом, используют функцию GROUPING(). Её прописывают в SELECT, выглядит это так: SELECT department_id, job_id, SUM(salary) as total_sal, GROUPING(department_id), GROUPING(job_id) FROM employees ... На выходе получатся уже 5 столбцов. Столбцы, сформированные с помощью GROUPING, будут иметь значение 0 либо 1, где 0 - это реальное значение строки, а 1 - это значение, сформированное агрегатором (итоги).

Предположим, что у нас есть гипотеза: "Активные пользователи приложения чаще совершают покупки". Допустим, что активный пользователь - это тот, кто заходит в приложение 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" - Ссылка скрыта Проконсультирую по материалам курса, помогу решить и объяснить сложные задачи и отвечу на все вопросы. Можете связаться со мной через обратную связь на 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, а по сущностям именно из хабов.

Дорогие подписчики! Сделал на Stepik курс по тематике нашего канала, с уклоном именно в практическую аналитику. Он будет полезен тем, кто проходит собеседования и тем, кто уже работает аналитиком. Курс учит полному циклу анализа данных, хорошо систематизирует знания и даёт понимание, в каких ситуациях какие инструменты и методы применять. За основу брал реальные кейсы, с которыми приходилось сталкиваться на работе. Плюс ещё добавил туда задания с собеседований. Курс довольно необычный и интересный. Он состоит из 3 модулей, которые включают в себя 18 уроков, 87 тестов и 46 задач на написание скриптов. Каждый урок связан друг с другом и представляет собой последовательный этап в рамках одного анализа 1. Анализ данных на SQL 2. Анализ данных на Python 3. Задачи с собеседований 1️⃣Анализ данных на SQL Фишка в том, что изначально мы будем работать с 3 таблицами на протяжении всего модуля. И для этих 3 таблиц будет раскрыт полный цикл анализа данных: — Формулирование гипотезы — Построение аналитической витрины — Проверка данных на качество — Описательный анализ — Сравнительный анализ — Интерпретация полученных результатов и формирование бизнес рекомендаций (выводы) 2️⃣Анализ данных на Python Идея та же самая: 3 датасета (но уже других) и полный цикл анализа: — Формулирование гипотезы — Загрузка и первичная подготовка данных — Анализ распределений — Ассоциативные метрики — Аномалии в данных — Корреляционный анализ — Статистическая значимость — Анализ ассоциаций — Интерпретация полученных результатов и формирование бизнес рекомендаций (выводы) В этом модуле мы усложняем аналитику с помощью Python и еще учимся выбирать, под какие задачи лучше подходит SQL, а под какие - Python. 3️⃣Задачи с собеседований Сюда включил много реальных задач и разделил их на 3 категории: — устные задачи (которые можно решить в уме) — задачи из лайф-кодинга — проектные задачи, которые надо решать дома к определенному дедлайну. Ссылка - Ссылка скрыта

FILTER в SQL В SQL конструкция FILTER используется для условной агрегации. Она позволяет применять агрегатные функции (такие как SUM, COUNT, AVG, MAX и т.д.) только к тем строкам, которые удовлетворяют определенному условию, не затрагивая остальные строки в выборке. И тут сразу вспоминаются функции HAVING и WHERE, которые на первый взгляд могут делать то же самое. Но всё же между ними есть отличия: ▶️WHERE: Фильтрует строки до группировки и агрегации. Если строка не прошла WHERE, она не участвует ни в одной агрегации в этом запросе. ▶️HAVING: Фильтрует результаты группировки. Используется после GROUP BY. ▶️FILTER: Фильтрует строки только для конкретной агрегатной функции. Другие агрегатные функции в том же SELECT видят все строки. 🟠Вообще, FILTER используется как более лаконичная и читаемая замена конструкции CASE WHEN. Для примера представим, что необходимо посчитать общую сумму продаж и сумму только оплаченных заказов: SELECT user_id, SUM(amount) AS total_amount, SUM(amount) FILTER (WHERE status = 'paid') AS paid_amount FROM orders GROUP BY user_id Это то же самое, что и: SELECT user_id, SUM(amount) AS total_amount, SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount FROM orders GROUP BY user_id То есть для обоих случаев выведутся 2 столбца в разрезе user_id: в первом будет содержаться общая сумма всех заказов, во втором - сумма заказов только со статусом 'paid'. 🟠В заключение стоит отметить, что FILTER поддерживается в PostgreSQL и постепенно начинает появляться в новых версиях в Microsoft и Oracle (но в MySQL её еще нет).

Метод Монте-Карло на Python Метод Монте-Карло используется тогда, когда нужно численно изучать случайные процессы. Представляет собой генерацию случайных величин для достижения определенных аналитических целей. Довольно часто применяется в практической аналитике при оценке рисков, финансовом моделировании и прогнозировании. 🟠Допустим, нужно оценить квартальную выручку. У нас есть три фактора с неопределённостью: количество клиентов, процент конверсии и средний чек. Самый простой и наглядный вариант рандомизации будет выглядеть так: import numpy as np np.random.seed(42) N_SIM = 100000 traffic = np.random.normal(loc=100000, scale=15000, size=N_SIM) conversion = np.random.normal(loc=0.02, scale=0.005, size=N_SIM) avg_check = np.random.normal(loc=2000, scale=300, size=N_SIM) revenue = traffic * conversion * avg_check print(f"Прогноз выручки: {revenue.mean():,.0f} ₽") То есть мы ожидаем, что клиентов будет в среднем 100 тыс., конверсия будет в среднем 2%, а чек будет в среднем 2 тыс. (параметр loc). Это всё устанавливается вручную, так же как и величина разброса от среднего (scale) и количество повторений (N_SIM = 100000). Также в этом варианте задаётся, что случайные числа в установленных пределах будут генерироваться по нормальному распределению. В конце эти 3 показателя перемножаются между собой, и среднее из всех этих 100 000 произведений и есть прогноз выручки на следующий квартал. 🟠Но вообще вся эта рандомизация используется не для того, чтобы найти среднее значение n-ого кол-ва воспроизведений. Это нужно для того, чтобы оценивать вероятности. В нашем случае можно задать порог выручки, например, 4 млн: print(f"{(revenue < 4000000).mean():.1%}") и получить вероятность того, что она окажется в итоге меньше 4 млн, в 53.6%. Если поставить порог в 5 млн, то можно получить вероятность 78.9%, и т.д. 🟠Также еще будет полезно узнать доверительный интервал прогноза: print(f"[{np.percentile(revenue, 5):,.0f}, {np.percentile(revenue, 95):,.0f}]") Для примера возьмем 5-ый и 95-ый перцентили. Это будет говорить о том, что в 90% случаев выручка будет находится в таком-то диапазоне значений. 🟠Но откуда нам знать, сколько именно повторений проводить, какое должно быть распределение и какие устанавливать параметры loc и scale? Число повторений должно быть как можно больше, от этого результаты становятся более точнее. Но также нужно учитывать вычислительные мощности и не брать слишком уж большие числа. Обычно выбирают 100 000, т.к. оно считается за секунду и достаточно точно определяет все остальные значения. Значения loc и scale можно брать из прошлых данных или, если таковых нет, то можно использовать сторонние наиболее релевантные источники. Что касательно типа распределения, то оно выбирается в зависимости от контекста значения. Нормальное распределение используется в случаях, когда данные распределены симметрично, в форме колокола. Это легко проверить визуально, просто построив гистограмму. В нашем случае под нормальное распределение скорее всего попадет значение кол-ва посетителей, т.к. это большое число и скорее всего имеет симметричный разброс вокруг среднего. Помимо нормального распределения используются еще: ▶️Логнормальное распределение. Используется для тех данных, которые скошены вправо. Например, в нашем случае под логнормальное распределение хорошо попадает сумма чека, т.к. часто они небольшие, но есть редкие крупные заказы. ▶️Бета распределение. Используется для процентов и долей. Например, значение конверсии (%) в нашем случае лучше было бы считать по бета распределению. ▶️Равномерное распределение. Используется по умолчанию, когда нет возможности получить информацию о форме распределения.