Análisis de datos - DA SQL

Как можно уменьшить количество групп в классификации Допустим, есть набор данных из миллиона строк с банковскими транзакциями, который содержит такие параметры, как: ▶️Id транзакции ▶️Id клиента ▶️Дата рождения клиента ▶️Пол клиента ▶️Локация ▶️Дата транзакции ▶️Время транзакции ▶️Баланс счета клиента ▶️Сумма транзакции 🟠Первое, что можно сделать после проверки данных на качество - это провести классификацию клиентов (как вариант). Это нужно, чтобы понять, что за данные перед нами, а именно - понять специфику той или иной группы клиентов. Можно взять основные клиентские признаки (локация, пол, возрастной диапазон (18-25, 26-30 и т.д.)) и, используя группировку и агрегатные функции, посчитать такие метрики (в разрезе каждой группы), как: ▶️Количество транзакций ▶️Сумма транзакций ▶️Средняя, медианная и модальная суммы транзакций ▶️Количество уникальных клиентов ▶️Сумма баланса на счете ▶️Средняя, медианная и модальная суммы баланса на счете ▶️Процент от общей суммы транзакций ▶️Процент от общей суммы баланса Это даст понимание того, насколько значима та или иная группа клиентов в контексте рассчитанных показателей. 🟠Но бывает, возникает ситуация, что количество групп клиентов оказывается слишком большим. Если группировать клиентов только по возрастному диапазону и полу, то кол-во строк в классификации адекватное, но если в классификацию включать еще и локацию (которая бывает излишне детализирована), то строк может стать несколько тысяч. Из-за этого значительно затрудняется анализ. На примере локаций рассмотрим несколько вариантов, как можно решить эту проблему. ▶️Можно из описания локации вытягивать только ключевые слова (например, название города) с помощью LIKE в SQL или его альтернатив на Python. Идея в том, чтобы объединить локации по типу "Москва, ул. Тверская...." и "Москва, ул. Никольская..." в один кластер "Москва", что, очевидно, уменьшит итоговый размер классификации и сделает ее более читаемой. Повезет, если это сработает. Но нужно понимать, какой именно город надо вытягивать, и прописывать название каждого из них в скрипте, это довольно долго и трудоёмко. Вдобавок, датасеты бывают разные, и ощутимое количество важных локаций нельзя будет идентифицировать так просто. ▶️Можно разделить локации на "Значимые" и "Прочие" на основании того или иного критерия. Например, если на локацию приходится больше n% от общей суммы транзакций И больше n% от общей суммы баланса И больше n% от общего кол-ва уникальных клиентов, то выводим ее как есть. Если локация не проходит эту фильтрацию, то объединяем ее вместе с такими же локациями в кластер "Прочие". Эта мера так же призвана уменьшить количество строк в финальной классификации. Опять же, повезет, если это сработает. Основная проблема здесь - найти такое значение n%, при котором одновременно и количество строк будет в итоге адекватным, и категория "Прочие" не будет слишком большой (условно, чтоб не получилось так, чтобы в "Прочее" было 10% всех клиентов, тогда как у отдельно взятой самой большой "Значимой" локации будет значение 1-2%). И опять же, повезет, если такое значение n% всё же есть. Но его легко может и не быть, всё зависит от изначального датасета. ▶️Еще один способ - это применять различные NLP-библиотеки. Идея такая же, как и в первом варианте, только вместо ручного составления списка улиц/городов этот процесс будет происходить автоматически. Из минусов - неидеальная точность определения локаций и медленная скорость работы. ▶️В заключение один достаточно интересный способ - кластеризация локаций по координатам (можно использовать k-means в python). Если координаты прописаны в датасете изначально, то это огромный плюс - можно сразу проводить кластеризацию. Если же их нет, то получить их можно, например: - через API google или яндекс карт; - через внешние БД координат городов и улиц(нужно будет скачать и присоединить к своему датасету); - через внешние БД координат по почтовым индексам

Порядок выполнения SQL-запроса Типовой SQL-запрос SELECT * FROM table WHERE -- условие GROUP BY -- условие HAVING -- условие ORDER BY -- условие LIMIT -- n имеет следующий порядок выполнения: 1️⃣ FROM. Идет первым, т.к. определяет источник данных. 🟠Что касаемо JOIN (при его наличии), то этот оператор является частью этапа FROM, но фактически выполняется после FROM. Вообще считается, что с помощью FROM + JOIN создается источник данных, который уже будет обрабатываться дальше другими операторами. 2️⃣ WHERE. После того, как данные выбрались из источника, идет фильтрация строк. WHERE идет до группировки, и связано это с тем, что нет смысла группировать данные, которые всё равно будут отброшены. 3️⃣ GROUP BY. После фильтрации происходит группировка уже "очищенных" данных от лишних строк. Это банально быстрее, чем делать наоборот. 4️⃣ HAVING. Идет после GROUP BY, т.к. фильтрует уже саму готовую группировку, чаще всего с использованием агрегатных функций. 5️⃣ SELECT. Происходит выбор столбцов, выполняется пятым. На первый взгляд это контринтуитивно, т.к. логичнее было бы выбрать столбцы до WHERE и работать только с нужными. Но не стоит забывать, что в SELECT еще присваиваются названия столбцов через AS; могут производиться разного рода математические операции (а происходят они как раз с готовыми данными после HAVING); также SQL-запросы могут работать с теми столбцами, которых нет в SELECT (например, SELECT col_1, col_2 FROM... WHERE col_3...) - без этой особенности пришлось бы выводить все столбцы, которые затрагивают другие операторы. 🟠Если есть DISTINCT, то он выполняется после SELECT, но до ORDER BY - из практически финальной выборки до сортировки нужно удалить все дубликаты. 6️⃣ ORDER BY. Практически окончательный набор данных сортируется после SELECT. Основная причина, почему ORDER BY стоит практически в самом конце - это то, что он сортирует не по каким-то промежуточным значениям, а уже по готовой выборке в зависимости от того, что было посчитано и выведено в результате SELECT-а. И поэтому ORDER BY, в отличие от того же WHERE, поддерживает псевдонимы, которые были заданы в SELECT через AS. 7️⃣ LIMIT. После всех операций финальная выборка просто ограничивается указанным количеством строк.

Выявление аномалий в данных с помощью скользящих средних В продолжение темы раскрою кейс о том, как выявлять аномалии в данных с использованием временных рядов, а конкретно - с помощью скользящих средних (MA): что это должны быть за данные и что за аномалии можно выявить таким способом. 🟠Вернемся к финансовым транзакциям. Для этого метода понадобится уже не просто размер операций, но еще дата и время каждой из этих операций. На основе этого будем применять: ▶️Простую MA (SMA) - среднее значение данных за фиксированный временной интервал. То есть, это как каждый день пересчитывать среднее значение за последние n дней. ▶️Взвешенную MA (WMA) - где более поздние значения имеют больше влияния (вес) на итоговое среднее. ▶️Экспоненциальную MA (EMA). EMA присваивает убывающие веса всем прошлым данным в зависимости от коэффициента (чем больше коэффициент, тем сильнее EMA реагирует на новые данные). 🟠Характер выявляемых аномалий на первый взгляд может быть похожим на аномалии, которые можно выявить стат методами - это отклонения от нормальных значений. Но различия есть: грубо говоря, стат методы выявляют аномалию на всей выборке (условно, платеж в 100 раз больше обычного), а временные ряды (в данном случае MA) учитывают именно нетипичные изменения в рамках "контекста", а не обособленное значение (например, резкий рост расходов). Выявим несколько аномалий: ▶️С помощью SMA, учитывая постепенно меняющиеся клиентские привычки, выявим, не было ли необычно крупного платежа за последнее время. ▶️С помощью WMA выявим, не было ли учащения кол-ва операций в последнее время. WMA из-за своих весов остро реагирует на самые последние скачки в данных. Также считается, что WMA лучше применять для дискретных данных, какими и являются данные о кол-ве транзакций. ▶️С помощью EMA можно выявлять примерно те же аномалии, как и с SMA, только EMA больше адаптирован для шумов и для реакции на последние изменения. 🟠Технически логика выявления аномалий такова: сначала считается среднее значение с помощью той или иной MA. Затем, прибавляя (и вычитая) к среднему 2 (иногда больше или меньше) стандартных отклонения, определяются верхняя и нижняя границы, значения за пределами которых - аномалии. Для начала передадим данные в df. Затем рассчитаем SMA, границы и определим значения, которые выходят за эти границы: import pandas as pd import numpy as np data = { 'date': ДАТЫ, 'amount': СУММА ТРАНЗАКЦИЙ, 'n_tr': КОЛ-ВО ТРАНЗАКЦИЙ } df = pd.DataFrame(data) df['sma'] = df['amount'].rolling(window=7).mean() df['sma_std'] = df['amount'].rolling(window=7).std() df['upper'] = df['sma'] + 2 * df['sma_std'] df['lower'] = df['sma'] - 2 * df['sma_std'] df['anomaly'] = (df['amount'] > df['upper']) | (df['amount'] < df['lower']) Для SMA считается среднее значение за последние 7 дней. Upper и lower - границы. В итоге выведутся суммы транзакций, которые в контексте ближайших 7 дней нетипичны. Для WMA присвоим веса [0.5, 0.25, 0.15, 0.07, 0.03] - последний день значим, затем с каждым днем вес снижается примерно в 2 раза. В сумме веса = 1. Применяться WMA будет для последних 5 дней: weights = np.array([0.5, 0.25, 0.15, 0.07, 0.03]) w_mean = lambda x: np.sum(weights * x) df['wma'] = df['n_tr'].rolling(window=5).apply(w_mean, raw=True) df['wma_std'] = df['n_tr'].rolling(window=5).std() df['upper'] = df['wma'] + 2 * df['wma_std'] df['lower'] = df['wma'] - 2 * df['wma_std'] df['anomaly'] = (df['n_tr'] > df['upper']) | (df['n_tr'] < df['lower']) По итогу получим актуальные нетипичные скачки в кол-ве транзакций. Для EMA используем коэф. alpha=0.3 - умеренная реакция на шумы. df['ema'] = df['amount'].ewm(alpha=0.3, adjust=False).mean() df['ema_std'] = df['amount'].ewm(alpha=0.3).std() df['upper'] = df['ema'] + 2 * df['ema_std'] df['lower'] = df['ema'] - 2 * df['ema_std'] df['anomaly'] = (df['amount'] > df['upper']) | (df['amount'] < df['lower']) По итогу получим аномалии с учетом сглаженного тренда, которые чувствительны к последним изменениям.

Apache Superset Apache Superset — один из популярных BI-инструментов для визуализации данных, разработанный для работы с большими объемами информации из баз данных. Он позволяет создавать интерактивные дашборды, отчеты и анализировать данные из разнообразных источников. Обладает типичным для BI-инструмента набором возможностей, а именно: ▶️Визуализация данных. Несколько десятков типов графиков (от круговых диаграмм до сложных карт) + поддержка библиотеки Apache ECharts. ▶️Подключение к данным. Совместим с большинством SQL- и NoSQL-баз (PostgreSQL, MySQL, ClickHouse, BigQuery и др.), а также файлами через предварительную загрузку в БД. ▶️SQL. Встроенная среда (SQL Lab) для написания и выполнения SQL-запросов с возможностью сохранения результатов как датасетов. По сравнению с другими инструментами, в Superset нет специфического диалекта в SQL, он там "оригинальный". ▶️Позволяет определять пользовательские метрики и вычисляемые поля без изменения исходных данных. 🟠Типичный процесс использования Superset выглядит так: сначала подключаемся к источнику, затем через SQL Lab достаем нужные данные, с помощью интерфейса визуализируем каждую SQL-выгрузку, собираем эти выгрузки в дашборд и в конце при необходимости еще можно сделать к этому всему публичный доступ или делиться персонально. 🟠Superset - бесплатный open-source проект, написанный на Python. И хоть он в своей базовой настройке относительно прост по сравнению с другими BI-инструментами, при должном желании его можно усовершенствовать или настроить под себя, чтобы наиболее эффективно покрывать нужные задачи. Но всё равно важно отметить, что его простота накладывает некоторые ограничения: ▶️Ограниченная возможность ETL-процессов (потребуются сторонние инструменты или нужно будет заранее подготавливать БД) ▶️Хоть Superset и совместим с файлами и со многими БД, это достаточно скромный выбор по сравнению в другими инструментами. Например, из файлов он поддерживает только CSV и Excel через предварительную загрузку, JSON поддерживает с ограничениями. По сути, можно сказать, что Superset ориентирован только на работу с базами данных, тогда как у аналогов выбор источников намного шире. ▶️Закрывает в основном базовые задачи по визуализации с помощью встроенных функций. Но для каких-то сложных, необычных визуализаций придется скачивать сторонние плагины, которые специально написаны для Superset для этих целей. ▶️Встречается еще проблема обработки сложных SQL-запросов, расчет каких-то сложных метрик порой оказывается затруднительным или даже невозможным. В некоторых случаях это ограничение можно будет обходить с помощью создания в БД отдельной таблицы, где уже всё нужное будет рассчитано. Останется только подключиться к этой таблице через Superset и визуализировать данные из нее.

Аномалии в данных Аномалии (или выбросы) — это наблюдения, которые значительно отличаются от остальных данных и могут указывать на ошибки, мошенничество, редкие события или другие интересные явления. Обычно различают 3 типа аномалий: ▶️Точечные аномалии — отдельные точки данных, сильно отличающиеся от остальных (например те, которые явно выделяются из общей массы). ▶️Контекстные аномалии — точки, которые выглядят нормально в общем контексте, но аномальны в конкретном подмножестве (например, температура +30°C зимой). ▶️Коллективные аномалии — группа точек, которая сама по себе нормальна, но её появление аномально (например, что-то однотипное, что часто повторяется). 🟠Чтобы выявить аномалии, можно использовать: ▶️Статистические методы (например, считать Z-score или разницу между перцентилями) ▶️Методы машинного обучения (алгоритмы наподобие k-means, isolation forest или метода ближайших соседей) ▶️Временные ряды (считать скользящее среднее, стандартное отклонение, использовать специальные библиотеки в python по типу Prophet и т.п.). 🟠Вообще, тема поиска аномалий в данных достаточно обширная, так что буду периодически к ней возвращаться. Сегодня затрону кейс по выявлению подозрительных транзакций в финансовых данных статистическими методами. Такие методы являются самыми базовыми и в контексте финансовых транзакций подойдут только для обнаружения значительных выбросов (например, очень большие или очень маленькие транзакции). Под другие типы аномалий, например, необычное время операции (перевод ночью, если клиент обычно активен днём) или значительная серия транзакций за маленький промежуток времени, нужны будут более сложные алгоритмы. Для начала нам нужно понять, является ли распределение нашей выборки нормальным. Для этого будем использовать тест Андерсона-Дарлинга (чем больше данных, тем качественнее будет работать): import numpy as np from scipy import stats #Предварительно надо передать в переменную data #очищенные данные только о размере транзакций, #условно 1 колонка из excel/БД result = stats.anderson(data, dist='norm') if result.statistic > result.critical_values[2]: print("Распределение не нормальное") else: print("Распределение нормальное") Стоит отметить, что result.critical_values[2] - это проверка на уровень значимости 5% (наиболее часто используемый). Если по какой-то причине нужен другой уровень значимости, то можно использовать другие индексы: [0]=15%, [1]=10%, [3]=2.5%, [4]=1%. Далее, в зависимости от полученных результатов, применим Z-score (если распределение нормальное) : z_scores = stats.zscore(data) anomalies = data[np.abs(z_scores) > 3] print(anomalies) Или же будем считать межквартильный размах (если распределение ненормальное): Q1 = np.percentile(data, 25) Q3 = np.percentile(data, 75) IQR = Q3 - Q1 k = 1.5 low = Q1 - k * IQR up = Q3 + k * IQR out = data[(data < low) | (data > up)] print(out) Оба этих метода построены на идее, что выбирают маленький процент данных, которые выделяются из "общей логики" всей выборки.

Ранжирующие функции в SQL Памятка по тому, какие бывают ранжирующие функции: ▶️Row_number() Присваивает уникальный номер каждой строке в результирующем наборе (начиная с 1). Если значения совпадают, номера всё равно будут разными. По сути обычная нумерация строк по порядку (в зависимости от заданных условий): SELECT user_id, name, age, email, row_number() OVER (ORDER BY user_id) AS r FROM users ▶️Rank() Присваивает ранг с пропусками: если значения одинаковые, они получают один ранг, а следующий ранг пропускается. Например, если у нас будут идти user_id = [1, 1, 2, 2, 2, 3, 4], то rank присвоит значения [1, 1, 3, 3, 3, 6, 7] SELECT user_id, name, age, email, rank() OVER (ORDER BY user_id) AS r FROM users ▶️Dense_rank() Присваивает ранг без пропусков: если значения одинаковые, они получают один ранг, но следующий ранг идёт без разрыва. То есть при том же наборе user_id = [1, 1, 2, 2, 2, 3, 4] dense_rank = [1, 1, 2, 2, 2, 3, 4] SELECT user_id, name, age, email, dense_rank() OVER (ORDER BY user_id) AS r FROM users ▶️Ntile(n) Разделяет данные на n примерно равных групп. Каждая группа получает свой повторяющийся ранг. SELECT user_id, name, age, email, ntile(3) OVER (ORDER BY user_id) AS n FROM users ▶️FIRST_VALUE() и LAST_VALUE() Возвращают первое и последнее значение в выборке. Учитывает и применяемую сортировку. И если с first_value всё понятно, то для last_value желательно использовать условие внутри OVER: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Это условие позволит использовать last_value по аналогии с first_value, а именно сначала получить выборку и уже из полученной выборки выбрать первое/последнее значение. SELECT user_id, name, age, email, first_value(user_id) OVER (ORDER BY user_id) AS fv, last_value(user_id) OVER (ORDER BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM users ▶️LAG() и LEAD() LAG() возвращает значение из предыдущей строки. LEAD() возвращает значение из следующей строки. Второй аргумент функции - цифра - задается как величина шага: сколько строк нужно пройти вперед/назад, чтобы вернуть значение. SELECT user_id, name, age, email, lag(user_id, 1) OVER (ORDER BY user_id) AS prev, lead(user_id, 1) OVER (ORDER BY user_id) AS next FROM users

Data Lake Data Lake (озеро данных) — это метод хранения данных, при котором информация сохраняется в исходном формате без предварительной обработки. В отличие от традиционных хранилищ данных (DWH), Data Lake может хранить в себе реляционные таблицы, файлы по типу CSV, JSON, XML, а также документы, изображения, аудио, видео и т.п. Обычно состоит из слоев: ▶️Слой приема данных. Данные просто загружаются в их первичном виде и в ранее упомянутых самых разных форматах. Загружаться данные могут пакетным способом (когда происходит периодическая загрузка), потоковым способом (непрерывное получение данных) и инкрементальным способом (когда фиксируются только изменения, но этот способ для Data Lake считается сложным). ▶️Слой хранения. Данные лежат без изменений. Из-за большого их объема и из-за разного формата для хранения обычно выбирают хранилища по типу Hadoop либо облачные хранилища. Такой способ хранения создает большие возможности для горизонтального масштабирования. ▶️Слой обработки. Хоть в концепции Data Lake и лежит принцип хранения данных без обработки, но этот принцип касается лишь их хранения. Данные, само собой, обрабатываются, но, в отличие от традиционного DWH, не "по умолчанию", а по мере необходимости. Обработка может в себя включать очистку данных, ETL/ELT процессы и аналитику. Вообще, это достаточно важный слой Data Lake, без которого собирать и хранить данные особого смысла не имеет. ▶️Использование. Готовая информация в виде отчетов/дашбордов используется по назначению. Перед этим данные еще могут каталогизироваться и защищаться. 🟠Если сравнивать DWH и Data Lake, то Data Lake может быть как и самостоятельным методом хранения данных, так и дополнять DWH, образуя таким способом гибридный подход. Во втором случае Data Lake может являться одним из слоев DWH. Сейчас считается, что использовать Data Lake обособленно - неоптимальное решение и уместно далеко не всегда. Главная проблема Data Lake - это сложность обеспечить контроль над загружаемыми данными. Из-за этого со временем появляется много лишних бесструктурных данных, которые только мешают находить и анализировать нужную информацию. Из минусов DWH можно выделить то, что они хуже масштабируются, не поддерживают так много форматов и нуждаются в сложном проектировании. Поэтому, объединив Data Lake и DWH, получаем гибридный подход - Data Lakehouse, основная цель которого - сохранить преимущества обоих методов. Такой подход решает проблемы управления данными, оптимизации, масштабирования, ограниченности форматов и надежности транзакций.

XlsxWriter: создание Excel-файлов в Python XlsxWriter — это библиотека Python, предназначенная для записи данных и excel-формул, форматирования, создания диаграмм и работы с большими объемами данных в формате Excel. Может работать в связке с классическими библиотеками по типу Pandas и с базами данных. Чаще всего используется для автоматизации создания excel-файлов на основе данных из БД. Но XlsxWriter, в отличие от тех же самых openpyxl или pandas, не умеет читать Excel-файлы, эта библиотека предназначена только для записи. Взамен у нее есть расширенный функционал по сравнению с аналогами: ▶️Может работать со всеми форматами и стилями excel ▶️Имеет продвинутые возможности для визуализации (линейные и комбинированные графики; гистограммы; круговые, кольцевые, лепестковые, кластеризированные... диаграммы; Sparklines (микрографики) и еще очень много всего) ▶️Может использоваться в контексте больших данных (оптимизированная работа с памятью, поддержка потоковой записи, быстрая обработка больших наборов данных) ▶️Также имеет полную поддержку формул excel, может создавать сводные таблицы, добавлять макросы, вставлять комментарии, изображения, гиперссылки, выпадающие списки и т.д. 🟠Используется она следующим образом. Например, одна из типовых ситуаций: у нас есть данные в БД, которые надо отобразить в экселе, прописав формулы и стили. Для начала нужно установить библиотеку через pip install. В этом примере она будет использоваться в связке с Pandas, поэтому явный импорт не потребуется, но всё равно не надо забывать, что это - отдельная библиотека, которой можно пользоваться и обособленно. Затем с помощью библиотек по типу cx_oracle/sqlalchemy подключаемся к БД, селектим нужные данные и сохраняем их в датафрейм (подробнее про подключение к БД и выборку: cx_oracle или sqlalchemy) Далее через пандас указываем конечный файл с листом и обращаемся к библиотеке через engine: import pandas as pd with pd.ExcelWriter('file.xlsx', engine='xlsxwriter') as writer: df.to_excel(writer, sheet_name='Лист_1', index=False) workbook = writer.book worksheet = writer.sheets['Лист_1'] задаем стили заголовкам и прописываем условное форматирование для столбцов С и В (если "С" > "В", то выделяем "С" красным цветом): header_format = workbook.add_format({ 'bold': True, 'bg_color': '#4F81BD', 'font_color': 'white' }) for col_num, value in enumerate(df.columns): worksheet.write(0, col_num, value, header_format) red_format = workbook.add_format({'bg_color': '#FFC7CE'}) worksheet.conditional_format( 'C2:C4', { 'type': 'formula', 'criteria': '=$C2>$B2', 'format': red_format } ) и добавляем итоговые формулы: worksheet.write('A5', 'Итого:') worksheet.write_formula('B5', '=SUM(B2:B4)') worksheet.write_formula('C5', '=SUM(C2:C4)') В результате получим скрипт, который можно будет запускать с определенной периодичностью по мере, например, обновления данных в БД, и на выходе получать стилизованную excel-таблицу с итоговыми суммирующими формулами и с подсветкой тех значений в столбце С, которые больше значений в столбце B (например, расходы > доходов).

Нормализация в базах данных Нормализация в базах данных — это организация данных в таблицах для уменьшения избыточности и улучшения целостности данных. 🟠Есть несколько форм нормализации: ▶️Первая форма нормализации: Все атрибуты должны быть атомарными: то есть каждое поле должно содержать только одно значение (не должно быть списков, множеств или составных данных). Например, есть таблица с клиентам. И если у клиента есть несколько номеров телефона, то, согласно 1НФ, будет неправильно хранить их все в одном поле и в одной строке (условно через запятую). Разделить их можно несколькими способами: - создавать столбцы "телефон 1", "телефон 2", "телефон 3" и т.д., но это тоже будет неправильно, т.к. такая таблица будет очень неудобной и всё так же будет нарушать 1НФ - а именно иметь повторяющиеся группы столбцов. - создавать под каждый номер телефона отдельную строку в таблице с клиентами. Это будет соответствовать 1НФ, но глобально всё еще будет далеко от идеала, т.к. при таком подходе будем иметь большое количество повторяющихся строк, которые будут отличаться между собой только лишь номером телефона. - и самый оптимальный вариант - создать отдельную таблицу с номерами телефонов и связать ее с основной с помощью client_id (это решение будет соответствовать 2НФ, но об этом дальше). Что касаемо составных данных в 1НФ, то будет неправильно хранить в одном столбце и в одной строке, например, информацию о товаре: "Такой-то товар такого-то цвета таких-то размеров произведенный кем-то". Для этого нужно будет выделить отдельные столбцы "Наименование", "Цвет", "Размер", "Производитель", чтобы это всё соответствовало 1НФ. Помимо всего прочего, в таблице еще не должно быть и повторяющихся строк - для этого создаются первичные ключи. ▶️Вторая форма нормализации: Таблица должна быть в 1НФ. Все неключевые атрибуты должны зависеть от всего первичного ключа. Делается это для того, обновлять данные было удобнее, быстрее и чтобы избежать ошибок. Возвращаясь к примеру с клиентами. Почему разбивка номеров телефонов на отдельные строки в рамках 1 таблицы соответствует 1НФ, а разбивка номеров телефонов и клиентов на 2 разные таблицы соответствует 2НФ? Потому что в 1 случае уникальность строки зависит не от client_id (помним про дубликаты), а от client_id + номер телефона. Из-за этого могут возникать такие ситуации, когда одни записи в таблице будут зависеть от client_id (например, ФИО), а другие от номера телефона (например, оператор). И как раз для устранения таких проблем с идентификацией используется 2 случай с разбивкой на разные таблицы, чтобы все записи в одной таблице соответствовали одному id. ▶️Третья форма нормализации: Таблица должна быть в 2НФ. Неключевые атрибуты не должны зависеть от других неключевых атрибутов. Например, в таблице с клиентами есть еще атрибуты "Город" и "Регион". Такая таблица не будет соответствовать 3НФ, если значение региона будет зависеть от города, т.к. оба этих атрибута - неключевые. Встает вопрос опять же про обновление данных - в случае, если изменится логика определения региона в зависимости от города, то будет проблематично обновлять данные по всей таблице. Для того, чтобы структура соответствовала 3НФ, нужно "Город" и "Регион" вынести в отдельную таблицу, чтобы "Регион" зависел от ключевого значения "Город". 🟠Обычно на практике останавливаются на 3НФ, остальные формы идут уже для специфических ситуаций. Бывают еще случаи, когда намеренно используется денормализация. Допускают такое, как правило, для повышения производительности запросов, т.к. степень нормализации тесно связана с тем, как много джоинов потом будет написано. Говоря об остальных НФ, чаще всего выделяют еще: ▶️Нормальную форму Бойса-Кодда (BCNF). Применяется, если при PK = (атрибут 1; атрибут 2) некоторые атрибуты зависят только либо от атр.1, либо от атр.2. ▶️Четвертую форму. Применяется, когда от PK зависит атр.1 и атр.2, но при этом, имея зависимость от одного PK, нет зависимости между атр.1 и атр.2. ▶️Пятую форму. Применяется, когда данные корректны только при соединении 3+ таблиц.