Análisis de datos - DA SQL

Рекурсивные СТЕ в SQL Продолжая тему иерархий, напомню, что имеются такие данные: ▶️Активы ➡️ Дебиторская задолженность ▶️Активы ➡️ Денежные средства ➡️ Касса ▶️Активы ➡️ Денежные средства ➡️ Расчётные счета Если представлять это в виде таблицы, то такая таблица имела бы поля: — account_id - id статьи — name - наименование статьи (активы, ден. ср-ва, касса и т.д.) — parent_id - id родительской статьи 🟠Сегодня речь пойдет о рекурсивных СТЕ как об альтернативе упомянутому ранее методу, потому что CONNECT BY - уникальная фича Oracle, в других стандартных диалектах такое не встречается. На примере наших данных для построения иерархии можно написать такой запрос: WITH RECURSIVE tree AS ( SELECT account_id, name, parent_id, 1 AS level FROM accounts WHERE parent_id IS NULL UNION ALL SELECT a.account_id, a.name, a.parent_id, t.level + 1 FROM accounts a JOIN tree t ON a.parent_id = t.account_id ) SELECT * FROM tree Такой запрос является самым простым, можно сказать, даже шаблонным, для построения типичной иерархии таким методом. 🟠Изначально СТЕ задается с помощью WITH RECURSIVE. Главное отличие от обычной СТЕ - это то, что рекурсивная может ссылаться сама на себя. Что это значит? В этом примере мы назвали СТЕ именем tree и начали его определять с помощью дальнейшего запроса. Но в JOIN к таблице accounts присоединяется tree, которое мы еще не закончили определять. Если бы запрос был составлен с помощью обычного СТЕ, то при таком JOIN была бы ошибка. 🟠Теперь касаемо логики построения иерархии. 1️⃣В первом SELECT выбираются те строки из accounts, где parent_id IS NULL, то есть выбираются начальные строки для нашей иерархии, прямо как в START WITH parent_id IS NULL из прошлого поста. В нашем случае - это статья "Активы", т.к. у нее нет родительского id, потому что это самая "старшая" статья. На данном этапе CTE "tree" содержит только одну строку - это статью "Активы" с account_id = 1 и parent_id IS NULL. 2️⃣Во втором SELECT берется просто таблица accounts, к которой джойнится результат из первого SELECT по parent_id = account_id, то есть со смещением. Иными словами, статья "Активы" с account_id = 1 присоединяется к статьям "Дебиторская задолженность" и "Денежные средства" с parent_id = 1. 3️⃣После этого результаты селектов объединяются с помощью UNION ALL. На данном этапе CTE "tree" содержит уже 3 строки - это статью "Дебиторская задолженность" и "Денежные средства" + ранее полученные "Активы". 4️⃣И сейчас запускается рекурсия. Причем запускается она не с первого SELECT - там ей просто не от куда взяться. Запускается она со второго, потому что в нем присутствует JOIN tree, который уже содержит 3 строки. И снова выбираются данные из accounts, снова к ним со смещением присоединяются строки из "tree", и в итоге получаем уже набор из всех строк (в нашем случае) 5️⃣Заканчивается рекурсия на том моменте, когда между accounts и tree уже нет совпадений и результат ничего не выдает (потому что INNER JOIN). 🟠В итоге получаем таблицу в виде таблицы accounts, только упорядоченную по иерархии и с добавлением уровня иерархии (поле level).

Иерархии в SQL Oracle Допустим, у нас есть данные из бухгалтерского баланса, представленные иерархически: ▶️Активы ➡️ Дебиторская задолженность ▶️Активы ➡️ Денежные средства ➡️ Касса ▶️Активы ➡️ Денежные средства ➡️ Расчётные счета 🟠Если представлять это в виде таблицы, то такая таблица имела бы поля: — account_id - id статьи — name - наименование статьи (активы, ден. ср-ва, касса и т.д.) — parent_id - id родительской статьи. Например, если у статьи "Активы" account_id = 1, то у статьи "Денежные средства": parent_id = account_id ("Активы") = 1 потому что "Денежные средства" - это вид статьи "Активы", стоящий на 1 ступень ниже. Аналогично будет и со статьей "Касса". Если у "Денежные средства" account_id = 2, то у статьи "Касса": parent_id = account_id ("Денежные средства") = 2 Вообще, если брать в расчет банковский контекст, то для таких данных, связанных с фин. отчетностью, наиболее часто пишутся иерархические скрипты. 🟠На примере этих данных разберемся, как можно работать с иерархиями. Строятся они в оркале с помощью такого синтаксиса: SELECT account_id, name, parent_id, LEVEL FROM accounts START WITH parent_id IS NULL CONNECT BY PRIOR account_id = parent_id ORDER SIBLINGS BY name где: ▶️LEVEL - уровень иерархии. Для статьи "Активы" он = 1, для статьи "Денежные средства" он = 2 и т.д. ▶️START WITH parent_id IS NULL. Говорит о том, что бы иерархия начинала строиться с той статьи, у которой parent_id IS NULL. То есть выбирается статья "Активы", т.к. у нее нет родительского id, потому что это самая "старшая" статья. И далее идет parent_id =1, потом 2 и т.д. ▶️CONNECT BY PRIOR account_id = parent_id - строка, с которой начинается именно само построение иерархии. Она берет account_id из родителя и ищет строки, где parent_id равен этому значению. Возвращаясь к нашему примеру, начинается всё со статьи "Активы", которая была выбрана в прошлом шаге. У нее account_id = 1. И потом ищутся те статьи, у которых parent_id тоже равен 1. ▶️ORDER SIBLINGS BY name. Обычный ORDER BY не работает в иерархических запросах - только ORDER SIBLINGS BY. Это означает, что внутри каждой родительской группы дочерние значения сортируются по порядку/алфавиту. То есть, если брать группу "Денежные средства", то дочерние значения в ней - "Касса" и "Расчётные счета". И именно они сортируются по алфавиту. 🟠На выходе после такого скрипта получится таблица, упорядоченная по иерархии сверху-вниз + с доп. полем LEVEL 🟠Дополнительно к запросу можно добавить еще: CONNECT BY NOCYCLE PRIOR account_id = parent_id С помощью этого можно избежать зацикливания, если, например, по ошибке было указано, что "Расчётные счета" являются подкатегорией "Денежных средств", а потом "Денежные средства" - подкатегорией "Расчётных счетов". В этом случае NOCYCLE пометит такие записи и пойдет дальше. Чтобы увидеть помеченные строки, в SELECT нужно дополнительно прописать поле CONNECT_BY_ISCYCLE. Если же NOCYCLE не писать, оракл выдаст ошибку. SELECT LPAD(' ', (LEVEL - 1) * 2) || name AS tree_name, Добавление этой строки в селект позволит визуализировать иерархическое дерево, а не выводить его в виде обычной таблицы.

Кейс по созданию витрины данных В прошлом посте я упомянул уже подготовленную витрину, которая состояла из таких полей: ▶️client_id - айди клиента ▶️salary_client - был ли клиент когда-либо зарплатным (да/нет)? ▶️any_deposit - открывал ли клиент хотя бы 1 вклад (да/нет)? ▶️during_salary - открыт ли хотя бы 1 вклад в то время, когда клиент был зарплатным (да/нет)? NULL - если клиент не был зарплатным. 🟠Как ее составить из стандартной БД? Предположим, что в БД есть таблицы с такими полями (помимо других): 1️⃣clients — справочник клиентов — client_id 2️⃣salary_periods — периоды, когда клиент был зарплатным — client_id — salary_start_date — salary_end_date (может быть NULL, если статус актуален) 3️⃣deposits — информация об открытых вкладах — deposit_id — client_id — open_date Для условности пускай витрина будет в виде мат. представления. Собирать витрину будем в помощью CTE, который будет иметь такую структуру: CREATE MATERIALIZED VIEW data_mart AS WITH salary_ever AS (Запрос №1), deposit_ever AS (Запрос №2), deposit_during_salary AS (Запрос №3), all_clients AS (Запрос №4) Запрос №5 Теперь подробнее о запросах. ▶️salary_ever (Запрос №1) SELECT DISTINCT client_id FROM salary_periods Здесь просто выбираются уникальные клиенты из зарплатной таблицы. Делается это для того, чтобы вывести тех клиентов, которые хоть когда-то были (или есть) зарплатными. ▶️deposit_ever (Запрос №2) SELECT DISTINCT client_id FROM deposits То же самое - уникальные клиенты из депозитов. Цель - вывести клиентов, которые хоть раз открывали депозит. ▶️deposit_during_salary (Запрос №3) SELECT DISTINCT d.client_id FROM deposits d JOIN salary_periods sp ON d.client_id = sp.client_id AND d.open_date >= sp.salary_start_date AND d.open_date

Анализ пропорций на Python Допустим, у нас есть гипотеза: "Клиенты, получающие зарплату на счёт в банке, в n раз чаще открывают вклады, чем остальные". И есть собранная из БД аналитическая витрина с полями: ▶️client_id - айди клиента ▶️salary_client - был ли клиент когда-либо зарплатным (да/нет)? ▶️any_deposit - открывал ли клиент хотя бы 1 вклад (да/нет)? ▶️during_salary - открыт ли хотя бы 1 вклад в то время, когда клиент был зарплатным (да/нет)? NULL - если клиент не был зарплатным. Проверяются подобные гипотезы через сравнение пропорций (z-тест). Алгоритм таков: 1️⃣Сначала надо разделить клиентов на 2 группы (зарплатные и незарплатные). То есть просто считаем кол-во да/нет в поле salary_client: import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns from statsmodels.stats.proportion import proportions_ztest ###### #ИСТОЧНИК ДАННЫХ + СОЗДАНИЕ DF ###### group_counts = df['salary_client'].value_counts() print(group_counts) 2️⃣Затем найти процент тех, кто открыл вклад в каждой группе. Для зарплатных клиентов надо найти соотношение during_salary (да) /salary_client (да) Для незарплатных: any_deposit (да) / salary_client (нет) На питоне это выглядит так: salary_group = df[df['salary_client'] == 'да'] non_salary_group = df[df['salary_client'] == 'нет'] salary_deposit_rate = (salary_group['during_salary'] == 'да').mean() non_salary_deposit_rate = (non_salary_group['any_deposit'] == 'да').mean() print(salary_deposit_rate) print(non_salary_deposit_rate) 3️⃣И далее уже подключается статистика: нужно посчитать P-value, чтобы убедиться в стат значимости. salary_count = len(salary_group) salary_deposit_count = (salary_group['during_salary'] == 'да').sum() non_salary_count = len(non_salary_group) non_salary_deposit_count = (non_salary_group['any_deposit'] == 'да').sum() c1 = np.array([salary_deposit_count, non_salary_deposit_count]) c2 = np.array([salary_count, non_salary_count]) stat, pval = proportions_ztest(c1, c2, alternative='larger') print(f"p-value: {pval}") 🟠Теперь что касательно интерпретации результатов. Если мы выяснили в п.2, что доля клиентов как-то заметно отличается (например, 55% против 25%), то имеет смысл считать p-value, потому что разница ощутима и надо проверить, не случайность ли это. Если p-value > 0.05, то такая разница может быть случайной и нет смысла на этом заострять внимание. Если p-value < 0.05, то, следовательно, всё наоборот.

Фреймы оконных функций в 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) Используется в основном при расчете каких-нибудь остатков.

Аналитический кейс на 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)

Про NULL, 0 и пустоту в SQL На первый взгляд совершенно базовые вещи, но довольно часто возникают проблемы с их интерпретацией и много с чем еще. И также про это часто спрашивают на собеседованиях. 🟠Для начала, в чем различие между пустотой, NULL и 0? Разберемся на примерах. ☑️NULL - это отсутствие информации как таковой, состояние неизвестности, и отражает состояние строки, а не конкретные данные. Например, если при заключении договора с клиентом мы не спросили его номер телефона, то тогда в БД должен отразиться NULL. ☑️Пустота - это уже наличие информации о том, что значение известно и равно пустой строке. Продолжая аналогию с клиентом, это то же самое, что если бы мы спросили его номер телефона, а он отказался бы его называть (или бы нажал кнопку "пропустить" на сайте). ☑️Ноль это обычное число, которое отражает количество, еще участвует в математических операциях. Но есть еще специфические применения 0, когда он выступает не как число, а как маркер отсутствия / отрицания. Одно из таких применений - это поля с флагом, где условно 1 - это да, а 0 - это нет. 🟠Почему так важно разделять это всё? Что изменится, если, к примеру, мы везде будем записывать только пустоту или только NULL? В этом случае нарушится логика и сегментация. Возвращаясь к примеру, мы не сможем сказать, почему у нас отсутствует номер телефона клиента: потому что мы его не спросили или потому что он сам отказался его оставлять. Очевидно, что такие вещи важно различать. 🟠Но ещё не всё так просто. Важно упомянуть тот факт, что очень часто в столбцах не может храниться одновременно и NULL, и пустота, и 0. Например, если у нас поле с датой (дата первого платежа по кредиту), то оно может принимать только значения дат или NULL. Но NULL в этом случае - не неизвестность, мы можем знать, что первого платежа еще не было, но из-за особенностей формата вместо пустоты ставим NULL. В таких ситуациях используются дополнительные столбцы-флаги со значениями да/нет, чтобы с их помощью различать неизвестность и просто пустое значение. 🟠Теперь о том, как разные операторы обрабатывают пустоты, NULL и 0: ▶️COUNT. Здесь важно различать COUNT(поле) и COUNT(*). COUNT(*) считает количество строк, независимо от того, есть в них NULL или нет. COUNT(поле) работает уже как агрегатная функция и выдает кол-во именно известных значений, т.е. NULL игнорируется. ▶️AVG. Считает среднее, игнорируя NULL и учитывая 0, что, впрочем, логично. ▶️MIN и MAX точно так же игнорируют NULL и учитывают 0. Но если использовать на числовом столбце ORDER BY, то NULL будет выводиться в самом верху, если сортировать по убыванию. ▶️При сравнении чего-то с NULL (обычно в WHERE) с помощью =, всегда будет выдаваться NULL, поэтому фильтровать нужно, используя IS NULL. При этом пустоту и 0 операторы сравнения поддерживают. ▶️GROUP BY и PARTITION BY объединяет всё по разным группам: все NULL - в одну группу, пустоту - в другую, 0 - в третью. ▶️DISTINCT считает все NULL дубликатами, поэтому оставляет только одно значение NULL. Аналогично с пустотой и 0. ▶️UNION действует по такой же логике - убирает все дубли. Но UNION ALL объединяет всё как есть, ничего не убирая. ▶️JOIN не присоединяет NULL к NULL, потому что NULL = NULL это не TRUE. Но соединяет пустоты и нули. ▶️Ранжирующие функции (ROW_NUMBER(), RANK(), DENSE_RANK()) учитывают NULL и присваивают ранг.

Как хранить историю изменений в DWH Существует такая проблема, когда при обновлении строк в таблицах исторические данные пропадают. Но очень часто требуется сохранить всю хронологию изменений: хранить не только новые, но и старые данные. Это нужно как и для регуляторных требований, так и для бизнесовых (например, когда старые метрики пересчитываются задним числом с новыми данными). Говоря в контексте схем БД, прежде всего речь идет об обновлении таблиц измерений: изменившиеся данные куда-то нужно девать, и записать хронологию в этом случае сложнее, чем в таблице фактов. Как это всё осуществить архитектурно? 🟠На помощь приходит подход Slowly Changing Dimensions (SCD). Идея в том, чтобы трансформировать таблицу измерений в более информативную. Например, в таблице "Клиенты" помимо содержащихся там полей: ▶️id ▶️ФИО ▶️Сегмент и т.п. добавить еще поля: ▶️Дата начала ▶️Дата окончания ▶️Флаг текущей версии ▶️id представить как 2 ключа: бизнес ключ (старый id, например номер договора) и суррогатный ключ Отдельно прокомментирую разбивку на ключи. Это нужно для того, чтобы у строки был уникальный идентификатор. Потому что бизнес ключ остается одним и тем же - он определяет клиента Иванова И.И. как отдельного человека (например по уникальному номеру договора). Но суррогатный ключ, если можно так сказать - "технический" в рамках таблицы измерений, и постоянно меняется с каждой новой версией. Допустим, у Иванова И.И. обновился сегмент. Как это будет выглядеть для ключей: БК = 1; СК = 1; ФИО = Иванов И.И; Сегм = РБ ------------------------------- БК = 1; СК = 2; ФИО = Иванов И.И; Сегм = МСБ 🟠На фоне этого возникает вопрос: как джойнить эту таблицу с фактами? Ведь до SCD таблицы соединялись по БК, который всегда был уникальным, а теперь он стал дублироваться. Решение простое - нужно теперь джойнить по СК, а для этого его необходимо добавить еще и в таблицу фактов как дополнительный атрибут. Сделать это можно постфактум. Например, в таблице фактов отражено, что 1-ого числа была совершена продажа Иванову И. И. В клиентской таблице смотрим, какой СК был актуален у Иванова И. И. на 1-ое число, и просто заносим его в факты, предварительно добавив там новое поле. И так нужно сделать для каждого измерения, которое связывается с фактами. Если таких измерений, например, 5, то в фактах у нас будет 5 столбцов с СК для каждого из них.

MERGE в SQL Команда часто используется в ситуациях, когда данные из одной таблицы нужно перенести в другую таблицу (в т.н. ETL-процессах). Или, иначе говоря, если нужно синхронизировать данные из 2 таблиц. Допустим, у нас есть таблица customers (целевая) и new_customers (источник). И мы хотим перенести данные таким образом, что если клиент уже есть - обновить имя и дату. Если нет - добавить нового. Тогда процесс переноса будет выглядеть так: MERGE INTO customers c USING new_customers nc ON (c.id = nc.id) WHEN MATCHED THEN UPDATE SET c.name = nc.name, c.last_updated = nc.last_updated WHEN NOT MATCHED THEN INSERT (id, name, last_updated) VALUES (nc.id, nc.name, nc.last_updated) Сначала по некой аналогии с джоинами соединяем выбранные таблицы по id. Затем идет разветвление на условия со специфической командой WHEN (NOT) MATCHED (не путать с конструкцией CASE WHEN THEN - это другое): есть совпадение - делаем UPDATE. Нет - делаем INSERT 🟠Можно ли решить эту задачу без использования MERGE? Можно, но это будет не так эффективно. Например: ▶️Последовательно выполнить 2 запроса: UPDATE + INSERT или DELETE + INSERT. Но, как правило, 2 запроса всё-таки хуже одного. Также между UPDATE и INSERT могут произойти изменения в таблицах, что нарушит корректность данных. В случае с DELETE это вообще грубый способ в рамках этой задачи, т.к. это всё так же 2 запроса, еще и теряются исторические данные и нет частичного обновления (например только конкретных полей). ▶️Можно написать сложный запрос с использованием СТЕ. Но это будет банально сложнее сделать, чем просто написать конструкцию с MERGE. С точки зрения производительности неочевидно, что будет быстрее работать, так что придется сравнивать оба варианта. ▶️Еще можно попытаться решить эту задачу при помощи процедуры и цикла, но основная проблема тут будет в том, что это всё будет крайне медленно работать, особенно на больших данных.