Её величество Плоская таблица

Цели статьи

Понимание архитектуры таблицы позволяет лучше работать с данными. В настоящее время скорость и качество работы с данными — ключевой фактор успеха.

Каждый день нам приходится проектировать таблицы. Это может быть список продуктов, учет продаж в магазине, данные по расходам группы компаний и т.д. Правильное понимание сути таблицы позволяет правильно их проектировать.

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

Почему именно таблица? И как она может быть плоской?

Я работаю в сфере бизнес-консалтинга. Со временем мне удалось выделить три «структуры данных», которые играют ключевую роль в нашей сфере:

1. Плоская таблица — самая фундаментальная структура данных в бизнесе, по моей оценке

2. Иерархия (дерево) — по сути, это частный случай графа, но имеет много особенностей, поэтому выделяется отдельно. В области бизнеса так удобнее

3. Граф

Если вы откроете почти любой Excel файл, там будет нечто похожее на таблицы. В Word, Power Point также частенько вставляют таблицы. Реляционные БД — чистые плоские таблицы. Кубические БД — можно логически представить в виде таблицы. Даже иерархии и графы часто физически хранятся в табличном виде. Именно поэтому, в бизнесе, самый большой из трёх китов — плоская таблица. В этой статье мы подробнее рассмотрим именно её.

Термин «плоская таблица» подчеркивает разницу с просто таблицами:

Плоская таблица — это классическая таблица (а-ля SQL table) в первой нормальной форме (1NF). Однострочная шапка, атомарные типы данных. В общем красота. Когда вам дают такую таблицу, вы сразу можете приступить к интересной и полезной стадии анализа.

Таблица (в общем случае) — это всё, что человек считает таблицей. Таблицы бывают матричные, двойные матричные, с многострочными шапками, неатомарными типами данных в теле и т.д. Если вам дали такую таблицу, вы потратите от 15 минут до нескольких дней на перевод её в понятный, плоский вид. Иногда этот перевод вообще невозможен, по причине архитектурных ошибок или качества исходных данных.

Для наглядности два фото:

Это НЕ плоская таблица 🙁

А вот это плоская таблица 🙂

Для перевода вышепоказанной таблицы в плоский вид потребовалась не одна строчка кода. Код перевода можно посмотреть в статье тут.

Архитектура плоской таблицы

Введем основные термины.

Потенциальные ключи таблицы (candidate key) — это git, article). FDtool написан на python2. Видно, что библиотека не поддерживается её создателями. Что расстраивает(

Я решил написать велосипед свою версию, которая бы сделала архитектурный анализ удобнее. Код выложен на github. Ниже в кейсе я покажу применение инструмента на реальной таблице.

# базовое применение такое !git clone https://github.com/Grigory-T/FlatTableAnalysis.git from FlatTableAnalysis.FlatTableAnalysis import FlatTableAnalysis fta = FlatTableAnalysis(df) # предполагается, что уже есть плоская таблица в виде df fta.show_header_info() fta.get_candidate_keys() fta.show_fd_graph() fta.show_set_relation('колонка 1', 'колонка 2') fta...  # ряд других методов, которые применяются реже

Кейс анализа таблицы из huggingface

В качестве таблицы я взял датасет музыкальных треков из huggingface (ссылка). Он уже в хорошем плоском виде, поэтому мы сразу можем приступить к анализу (минуя скучную стадию ETL). Колаб с кодом тут. Он содержит анализ таблицы с помощью моего питоновского класса FlatTableAnalysis. Ниже я опишу основные выводы.

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

Шапка таблицы maharshipandya/spotify-tracks-dataset

Первичный ключ

что выдает нам инструмент?

Первый вопрос — что является первичным ключом. Никакая одна колонка не обеспечивает уникальность всех строк (113 550 строк). А сочетание двух колонок track_id, track_genre обеспечивает уникальность. Вообще это странно, можно было бы подумать, что track_id будет первичным ключом. Но около 17 тыс. треков имеют 2 и более жанра.

Функциональные зависимости

что выдает нам инструмент?

С этим вообще интересно. Из графов видно, что track_id функционально определяет почти все колонки! (кроме track_genre, конечно). Единственное исключение — колонка populatiry. 720 треков имеют две популярности, остальные 89 тыс. имеют строго одну. Это надо исследовать глубже. Похоже на ошибку в данных. Логично было бы предположить, что трек всегда имеет одну популярность…

Еще одна интересная ситуация с колонкой explicit. Это бинарный флаг, означающий, что трек содержит «info offensive or inappropriate for children», по крайней мере так говорит perplexity.ai. Если мы понизим порог отображения функциональных зависимостей до 0.95, то на графе будет видно, что album_name, artists достаточно сильно определяют explicit. Это, в общем-то, логично. Альбом содержит близки по сути треки, да и исполнители обычно придерживаются одного стиля. Хотя зависимость не точная.

Также можно заметить, что track_name и track_id имеют связь один-ко-многим. Есть случаи, когда одно название трека связано с разными id. Но это достаточно частая ситуация, поэтому больших вопросов не вызывает.

Выводы по архитектуре таблицы

Если забыть на минутку про колонку populatiry, то архитектуру таблицы надо менять! Таблица должна иметь первичный ключ — track_id. Все остальные колонки чётко зависят только от неё. Это позволит сократить количество строк с 133к до 89к.

Видимо один и тот же трек может относится к разным жанрам. Если это так, то можно создать отдельную таблицу, которая будет содержать отношение многое-ко-многим между track_id и track_genre. Две колонки. Эти же две колонки будут первичным ключом этой второй таблицы. В ней будет 133к строк.

С колонкой populatiry сложно. Моя гипотеза — тут ошибка в данных. Populatiry должна функционально зависеть от track_id. Если это так, то она пойдет в первую таблицу, где первичный ключ — это track_id. Мы же помним, что плоская таблица — это рассказ про первичный ключ 🙂 Но если мы убедимся, что populatiry каким-то интересным образом зависит именно от сочетания двух колонок track_id и genre, то можно добавить её во вторую таблицу.

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

Итоги

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

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

Еще некоторые полезные видео по теме есть в моем блоге на youtube.

---

UPDATE 14 апреля 2024

Для удобства добавил библиотеку python на pypi.

Добавил базовые тесты и исправил несколько багов, см. github.

!pip install flattableanalysis from flattableanalysis.flat_table_analysis import FlatTableAnalysis  df = pd.DataFrame(YOUR_DATA) fta = FlatTableAnalysis(df)  # create analysis object  fta.get_candidate_keys(2)  # check 2-cols candidates  fta.show_fd_graph()[0]  # see graph of functional dependencies (all pairs of columns)