Серия «Аналитика FM»

Представьте дерево. У него есть корень, от него отходят ветки, от больших веток - более мелкие, а затем листья.

Примерно так же устроены иерархические справочники в информационных системах.
И как же можно понять: что есть ветка, а что есть лист в этом иерархическом справочнике?

В канале Аналитика FM я часто разбираю такие ситуации - когда задача вроде решаема, но без нормальной структуры превращается в кашу.

Например:

📁 Транспорт
├── Легковой транспорт
│ ├── Седаны
│ └── Кроссоверы
└── Грузовой транспорт
 ├── Малотоннажный
 └── Тягачи

Или:

📁 Товары
├── Электроника
│ ├── Телефоны
│ └── Ноутбуки
└── Бытовая техника
 ├── Холодильники
 └── Стиральные машины

А как такое дерево хранится в базе данных?

На самом деле всё гораздо проще, чем кажется.

Обычно таблица справочника выглядит примерно так:

| id | name | parent_id |
| --- | ----------------------------------- | --------------- |
| 1 | Транспорт | NULL |
| 2 | Легковой транспорт | 1 |
| 3 | Грузовой транспорт | 1 |
| 4 | Седаны | 2 |
| 5 | Кроссоверы | 2 |
| 6 | Тягачи | 3 |

Структурно это выглядит так:
1 Транспорт
├── 2 Легковой транспорт
│ ├── 4 Седаны
│ └── 5 Кроссоверы
└── 3 Грузовой транспорт
└── 6 Тягачи

Каждая запись имеет:

• id - собственный идентификатор;

• parent_id - идентификатор родительского элемента.

Например:

id = 4
name = Седаны
parent_id = 2

Это означает:

Седаны → Легковой транспорт → Транспорт

Именно благодаря полю parent_id база понимает, что "Седаны" и "Кроссоверы" относятся к одной ветке дерева.

Если подниматься по родителям вверх, то рано или поздно мы придём к общему узлу - корню ветки.

Получается, что вся иерархия строится буквально на одном поле: parent_id

Для чего нужны иерархические справочники?

Они позволяют хранить данные не просто списком, а показывать связи между объектами.

Благодаря этому можно:

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

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

Где используются?

📌 MDM-системы (Master Data Management);
📌 каталоги товаров интернет-магазинов;
📌 банковские и страховые системы;
📌 ERP и CRM;
📌 классификаторы услуг и продуктов;
📌 организационная структура компании;
📌 государственные классификаторы и справочники.

Преимущества

✔ Гибкость. Можно добавлять новые ветки без изменения структуры данных.

✔ Удобная аналитика. Легко получить данные как по конкретному элементу, так и по всей категории.

✔ Наследование правил. Одно правило может применяться сразу к тысячам объектов.

✔ Масштабируемость. Структура может содержать десятки и сотни уровней вложенности.

Недостатки

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

❌ Производительность. Глубокие иерархии могут существенно замедлять выполнение запросов.

❌ Риск циклических ссылок. Если по ошибке сделать узел потомком самого себя, можно получить бесконечный цикл.

❌ Сложность сопровождения. Изменение структуры верхних уровней может затронуть большое количество дочерних элементов.

Как с ними работать?

При работе с иерархическими справочниками чаще всего приходится решать четыре задачи:

🔹 найти всех потомков узла;
🔹 найти всех родителей элемента;
🔹 определить, принадлежит ли элемент определённой ветке;
🔹 определить, к какому верхнему узлу относится конкретный элемент.

В Oracle для этого используются специальные иерархические запросы:

START WITH ...
CONNECT BY ...

Именно они позволяют "обходить дерево" вверх или вниз по веткам.

В канале Аналитика FM (клик :-) ) уже готов пост про конструкцию START WITH и CONNECT BY.

Подписывайся, если интересно разбираться в особенностях работы аналитика.

Иерархический справочник - это не просто список значений. Это способ описать реальные взаимосвязи между объектами и сделать систему более гибкой и управляемой.

А одна маленькая колонка parent_id превращает обычную таблицу в целое дерево данных.

Если ты когда-нибудь писал длинный SQL-запрос и в какой-то момент ловил себя на мысли:
"Я уже сам не понимаю, что здесь происходит" - поздравляю, ты подошёл к моменту, где появляются временные таблицы.

В канале Аналитика FM я часто разбираю такие ситуации - когда задача вроде решаема, но без нормальной структуры превращается в кашу.

Что такое временная таблица

Это обычная таблица…
только с одним отличием:

👉 она живёт временно и потом исчезает

Ты создаёшь её:

• чтобы сохранить промежуточный результат

• поработать с ним

• и не засорять основную базу

Тебе нужно:

• взять заказы

• отфильтровать только оплаченные

• посчитать выручку

• добавить сегментацию пользователей

• ещё пару условий сверху

Можно написать один огромный запрос.

А можно сделать по-другому:

1. Сначала собрать "чистые заказы"

2. Потом на их основе считать метрики

3. Потом добавлять бизнес-логику

И вот тут временные таблицы начинают играть.

Как это выглядит

CREATE TEMP TABLE temp_orders AS
SELECT *
FROM orders
WHERE status = 'paid';

Создаем промежуточный слой данных.

А потом работаем уже с ним.

SELECT user_id, SUM(amount)
FROM temp_orders
GROUP BY user_id;

Зачем это нужно

1️⃣ Разделить сложную логику

Вместо одного "монстра":

• ты разбиваешь задачу на шаги

• каждый шаг понятен

• легче дебажить

2️⃣ Переиспользовать результат

Если один и тот же кусок данных нужен несколько раз:

• не нужно каждый раз пересчитывать

• можно сохранить и использовать

3️⃣ Ускорить запросы

Иногда:

• тяжёлый JOIN

• сложная фильтрация

👉 выгодно посчитать один раз и сохранить результат

4️⃣ Не засорять базу

Если ты создашь обычную таблицу:

• она останется

• её надо потом удалять

• она может мешать другим

Временная таблица:

• живёт в рамках сессии

• автоматически исчезает

Когда это особенно полезно

• сложные аналитические расчёты

• многоступенчатые преобразования данных

• работа с "грязными" данными

• отладка логики

Важный нюанс

Временные таблицы - это не единственный инструмент.

Есть ещё:

• CTE (WITH)

• подзапросы

Но:

👉 CTE - это "логика в одном запросе"
👉 временные таблицы - это "разбивка на реальные шаги"

Иногда CTE читается тяжело.
А временные таблицы дают ощущение "пайплайна".

Где часто ошибаются

• создают временные таблицы без необходимости

• забывают, что они завязаны на сессию

• используют их там, где проще CTE

То есть это инструмент - не серебряная пуля.

Самая простая мысль

Временная таблица - это способ остановиться посередине запроса и зафиксировать результат

И иногда именно это спасает:

• читаемость

• производительность

• и твои нервы

В канале Аналитика FM (клик :-) )я рассказываю про продуктовые метрики в разных бизнесах. В чем особенности и нюансы. Серия постов про средний чек уже готова.

Подписывайся, если интересно интересно разбираться в особенностях работы аналитика.

Или превращение длинной таблицы в широкую

В канале Аналитика FM я часто про это пишу - потому что аналитика заканчивается не на запросе, а на том, как ты показал результат.

Подписывайся!

Представь, у тебя есть данные:

| user_id | metric | value |
| ----------- | ------------- | -------- |
| 1 | revenue | 100 |
| 1 | orders | 2 |

Это длинная таблица.

Она удобна для хранения:

• гибкая

• легко добавлять новые метрики

• хорошо ложится в модели данных

Но попробуй быстро понять:

• кто сколько потратил

• у кого сколько заказов

• и сравнить это между собой

Неудобно.

А теперь та же информация:

| user_id | revenue | orders |
| ------------ | ------------- | ---------- |
| 1 | 100 | 2 |

Это широкая таблица.

И вот тут уже:

• метрики рядом

• сравнение - в одну секунду

• можно считать новые показатели

Что значит "превратить длинную таблицу в широкую"

Это значит:

взять значения из строк и разложить их по колонкам

То есть:

• metric → становится названиями колонок

• value → становится значениями в этих колонках

Ты буквально "разворачиваешь" данные.

Причём здесь pivot table

На самом деле, это и есть pivot.

Слово pivot - это "поворот".
Ты поворачиваешь таблицу так, чтобы:

• строки стали колонками

• данные стали наглядными

Если ты работал в Excel - ты это уже делал:
сводная таблица = pivot table.

В SQL это можно реализовать так:

SELECT
user_id,
SUM(CASE WHEN metric = 'revenue' THEN value END) AS revenue,
SUM(CASE WHEN metric = 'orders' THEN value END) AS orders
FROM table
GROUP BY user_id;

Зачем это вообще нужно

Потому что аналитика - это не про "достать данные".
Это про сделать их понятными.

Широкая таблица нужна, когда ты:

• строишь отчёт

• показываешь результат бизнесу

• считаешь метрики между собой

Длинная таблица - когда:

• хранишь данные

• работаешь с событиями

• строишь модели

Где чаще всего ошибаются

Самая частая ошибка - пытаться всё делать в широком формате.

Это ломает гибкость:

• добавилась новая метрика → переписывай запрос

• данные перестают масштабироваться

Правильный подход:

• храним - в длинном виде

• показываем - в широком

Длинная таблица - это про хранение
Широкая таблица - это про понимание

И pivot - это просто инструмент, который помогает перейти от одного к другому.

И если ты хочешь не просто писать SQL, а реально чувствовать данные и понимать, как их правильно показывать,
в Аналитика FM я как раз разбираю такие вещи - простым языком, но с реальным смыслом.

Если ты только начинаешь разбираться в данных, то очень быстро сталкиваешься с кучей аббревиатур.
DDL, DML, ещё что-то… И кажется, что это какие-то сложные термины из учебника.

Но на самом деле всё гораздо проще.

И в канале Аналитика FM я как раз стараюсь разбирать такие вещи без заумных формулировок - через логику и реальные сценарии.

Представь, что база данных - это квартира

• есть стены

• есть комнаты

• есть мебель

И есть вещи, которые ты туда кладёшь.

Так вот:

👉 DDL - это про квартиру
👉 DML - это про вещи внутри неё

DDL (Data Definition Language) - это команды, которые создают и изменяют структуру базы.

То есть всё, что связано с "построить" или "переделать".

CREATE TABLE users (...);
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;

• CREATE - ты создал новую "комнату"

• ALTER - передвинул стену или добавил полку

• DROP - снёс всё к чертям

📌 Важно:
DDL не трогает сами данные.
Он создаёт условия, где эти данные будут жить.

DML (Data Manipulation Language) - это уже про работу с данными.

SELECT * FROM users;
INSERT INTO users VALUES (...);
UPDATE users SET age = 30;
DELETE FROM users WHERE id = 1;

Это то, что делает аналитик каждый день:

• посмотрел данные (SELECT)

• добавил (INSERT)

• изменил (UPDATE)

• удалил (DELETE)

📌 Здесь ты уже работаешь не с "комнатой", а с тем, что внутри неё.

В чём реальное отличие

Не в терминах, а в сути:

• DDL отвечает на вопрос: Как устроены данные?

• DML отвечает на вопрос: Что происходит с данными?

Как это используется на практике

Тебе нужно посчитать метрику.

Но:

• нет нужной таблицы

• не хватает колонок

• данные лежат неудобно

👉 Тогда сначала приходит DDL:
создают таблицу, добавляют поля, настраивают структуру.

И только потом приходит DML:
ты пишешь SELECT, считаешь, анализируешь.

DDL и DML - это не просто разные команды.
Это разные уровни ответственности.

• DDL чаще делают разработчики, инженеры, архитекторы

• DML - это зона аналитиков

Но хороший аналитик всё равно должен понимать DDL.
Иначе он не понимает, почему данные лежат именно так.

DDL обычно:

• применяется сразу

• сложнее откатывается

DML:

• можно обернуть в транзакцию

• можно отменить

Это важно, когда ты начинаешь работать не только с SELECT, но и с изменением данных.

DDL - это "построить систему"
DML - это "жить внутри неё и работать с данными"

Если ты хочешь начать действительно понимать данные, а не просто писать запросы -
такие базовые вещи дают фундамент.

В канале Аналитика FM я как раз разбираю такие темы:
без усложнений, но с пониманием того, как это работает в реальной работе аналитика.

Разбор метрик Retention и Revenue в разных сферах бизнеса уже в канале.

Подписывайся!