Bridge tables и date/time dimension

Star schema устроена просто: fact-таблица в центре, dimensions вокруг, между ними связь «многие к одному» — много fact-строк ссылаются на одну строку dimension. Но реальность не всегда укладывается в эту схему. У одной книги может быть несколько авторов. У одного банковского счёта — несколько держателей. У сотрудника есть начальник, у начальника — свой начальник, и глубина иерархии заранее неизвестна.

Прямой foreign key такие связи выразить не может. Решение — bridge table (таблица-мост): промежуточная таблица, которая разрывает связь «многие ко многим» и описывает иерархии переменной глубины. Во второй части урока разберём date-dimension — самую важную и почти всегда отдельную dimension любого warehouse.

Проблема many-to-many между fact и dimension

Обычная связь fact-dimension — это «многие к одному»: fact-строка ссылается ровно на одну строку dimension через один foreign key. Книга с одним автором укладывается идеально: fct_book_sales.author_key указывает на одну строку dim_author.

Но что, если у книги три автора? Положить три столбца author_key_1, author_key_2, author_key_3? Тогда книга с четырьмя авторами не поместится, а у книги с одним автором два столбца пустуют. Это нарушение принципов моделирования: фиксированное число столбцов под переменное число значений.

Правильное решение — bridge table между fact (или dimension) и многозначной dimension.

CREATE TABLE dim_book (
    book_key   INT PRIMARY KEY,
    title      VARCHAR(200),
    author_group_key INT          -- ссылка на группу авторов
);

CREATE TABLE bridge_book_authors (
    author_group_key INT,         -- идентификатор группы
    author_key       INT REFERENCES dim_author(author_key),
    weighting_factor NUMERIC(5,4),-- доля вклада автора, сумма по группе = 1.0
    PRIMARY KEY (author_group_key, author_key)
);

dim_book ссылается на author_group_key — идентификатор группы авторов. В bridge_book_authors одна группа представлена несколькими строками — по одной на автора. Книга с тремя авторами имеет группу из трёх строк bridge-таблицы.

Идея «группы» здесь — ключевой приём. Вместо того чтобы напрямую связывать книгу с каждым автором, мы вводим промежуточную сущность — группу авторов — и связываем книгу с группой, а группу с авторами. Зачем лишний уровень? Затем, что группы можно переиспользовать: если три книги написаны одним и тем же составом авторов, все три ссылаются на одну группу, и bridge-таблица не дублирует связи. Кроме того, идентификатор группы — это обычный foreign key в dim_book, то есть связь «книга -> авторы» снаружи выглядит как привычная связь «многие к одному», и только разворачивается в many-to-many внутри bridge-таблицы. Это делает схему предсказуемой: dimension по-прежнему ссылается на «один ключ», просто за этим ключом стоит группа.

Weighting factor: борьба с двойным счётом

У bridge-таблицы есть тонкая проблема. Если просто соединить fct_book_sales через dim_book и bridge с dim_author, продажи книги с тремя авторами посчитаются трижды — по разу на каждого автора. Сумма «продажи по всем авторам» окажется больше реальной выручки.

Чтобы это контролировать, в bridge-таблицу добавляют weighting factor (весовой коэффициент) — долю, с которой строка вклада относится к каждому участнику группы. Для группы из трёх авторов веса могут быть 0.5 / 0.3 / 0.2 или поровну 0.33 / 0.33 / 0.34 — сумма по группе всегда равна 1.0.

-- Allocated query: выручка по авторам, взвешенная (без двойного счёта)
SELECT a.author_name,
       SUM(s.sale_amount * b.weighting_factor) AS allocated_revenue
FROM fct_book_sales s
JOIN dim_book   bk ON bk.book_key = s.book_key
JOIN bridge_book_authors b ON b.author_group_key = bk.author_group_key
JOIN dim_author a ON a.author_key = b.author_key
GROUP BY a.author_name
ORDER BY allocated_revenue DESC;

 author_name      | allocated_revenue
------------------+------------------
 Иванов А.        |       248500.00
 Петрова М.       |       176200.00
 Сидоров К.       |        94100.00

Умножение sale_amount * weighting_factor распределяет каждую продажу между авторами по их долям. Сумма allocated_revenue по всем авторам равна общей выручке — двойного счёта нет. Это allocated query (запрос с распределением).

Иногда нужен противоположный результат — impact query: «какова суммарная выручка всех книг, в которых участвовал автор Иванов». Тут двойной счёт намеренный — игнорируем weighting factor и просто суммируем sale_amount. Bridge-таблица поддерживает оба сценария: с весами и без.

Bridge table для иерархий переменной глубины

Вторая задача bridge-таблиц — иерархии, глубина которых заранее неизвестна. Классика — оргструктура: сотрудник подчиняется руководителю, тот — своему руководителю, и так до гендиректора. У одной компании пять уровней, у другой двенадцать. Хранить иерархию столбцами (manager_level_1, manager_level_2, ...) невозможно — число уровней не фиксировано.

Решение — hierarchy bridge table: таблица, где для каждой пары «предок — потомок» хранится одна строка, включая пары «сам с собой» (расстояние 0).

bridge_org_hierarchy
 ancestor_key | descendant_key | depth_from_ancestor
--------------+----------------+--------------------
 1 (CEO)      | 1 (CEO)        | 0
 1 (CEO)      | 2 (CTO)        | 1
 1 (CEO)      | 5 (инженер)    | 2
 2 (CTO)      | 2 (CTO)        | 0
 2 (CTO)      | 5 (инженер)    | 1
 5 (инженер)  | 5 (инженер)    | 0

Эта таблица хранит не только прямые связи «начальник-подчинённый», а ВСЕ пары предок-потомок на любой глубине. Тогда запрос «все подчинённые CTO на любом уровне вложенности» — это просто фильтр по ancestor_key:

-- Суммарные продажи всех сотрудников в подчинении CTO (любая глубина)
SELECT SUM(s.sale_amount) AS team_revenue
FROM bridge_org_hierarchy h
JOIN fct_sales s ON s.employee_key = h.descendant_key
WHERE h.ancestor_key = 2;   -- 2 = CTO

 team_revenue
-------------
   1842300.00

Без bridge-таблицы такой запрос потребовал бы рекурсивного обхода дерева. Hierarchy bridge «разворачивает» дерево заранее — за счёт места (число строк растёт), но запросы «вся ветка под узлом X» становятся обычным фильтром.

Стоит понять, почему в hierarchy bridge хранят именно ВСЕ пары предок-потомок, а не только прямые связи «начальник-подчинённый». Если хранить только прямые рёбра дерева, то вопрос «все подчинённые CTO на любой глубине» снова требует рекурсии: спуститься к прямым подчинённым, к их подчинённым, и так далее, пока дерево не кончится. Глубина заранее неизвестна, число шагов рекурсии тоже. Hierarchy bridge снимает рекурсию тем, что вычисляет транзитивное замыкание дерева заранее: для CTO в таблице есть строка на КАЖДОГО потомка любого уровня — и на прямого подчинённого, и на подчинённого подчинённого. Тогда «вся ветка» — это просто все строки с нужным ancestor_key. Столбец depth_from_ancestor при этом позволяет при желании ограничить глубину (например, «только прямые подчинённые» — это depth = 1). Цена — рост числа строк: дерево из N узлов глубины d даёт в bridge порядка N*d строк вместо N-1 рёбер. Это классический размен места на скорость запроса.

Date dimension — обязательная dimension любого warehouse

Перейдём ко второй теме урока. Date dimension — отдельная dimension со строкой на каждый календарный день. Почти любая fact-таблица имеет дату, и почти всегда дата выносится в отдельную dim_date.

Зачем отдельная таблица, если в SQL есть встроенный тип date и функции EXTRACT? Затем, что date-dimension хранит то, что из самой даты не вычислить: рабочий день или выходной, государственный праздник, номер недели по корпоративному календарю, fiscal-период (финансовый год компании может начинаться не в январе), название месяца на нужном языке. Всё это заполняется заранее.

CREATE TABLE dim_date (
    date_key      INT PRIMARY KEY,   -- 'умный' ключ YYYYMMDD, напр. 20260115
    full_date     DATE,
    day_of_month  SMALLINT,
    day_name      VARCHAR(11),       -- 'Понедельник'
    month_number  SMALLINT,
    month_name    VARCHAR(10),       -- 'Январь'
    quarter       SMALLINT,
    year          SMALLINT,
    is_weekend    BOOLEAN,
    is_holiday    BOOLEAN,
    fiscal_year   SMALLINT,
    fiscal_quarter SMALLINT
);

 date_key | full_date  | day_name    | month_name | quarter | is_weekend | is_holiday
----------+------------+-------------+------------+---------+------------+-----------
 20260101 | 2026-01-01 | Четверг     | Январь     |    1    | false      | true
 20260102 | 2026-01-02 | Пятница     | Январь     |    1    | false      | true
 20260103 | 2026-01-03 | Суббота     | Январь     |    1    | true       | true

Обратите внимание на date_key: это «умный» integer вида YYYYMMDD (20260115 = 15 января 2026). Это сознательное исключение из общего правила «surrogate key без смысла». Для date-dimension осмысленный ключ удобен: fact-таблицу можно грубо отфильтровать по диапазону date_key даже без JOIN, а строки в fact-таблице естественно упорядочены по времени. Date — единственная dimension, где «умный» ключ считается хорошей практикой.

Time-of-day — отдельная dimension

Если нужен анализ не только по дням, но и по времени суток (час пик, ночные смены), время суток выносят в отдельную dimension dim_time со строкой на каждую секунду или минуту суток. Почему отдельную, а не добавить часы в dim_date?

Потому что date-dimension взорвалась бы. В сутках 86400 секунд. Если совместить дату и время в одной dimension, за 20 лет получится 7300 * 86400 ≈ 630 миллионов строк вместо 7300. Раздельные dim_date (7300 строк) и dim_time (86400 строк) дают в сумме менее ста тысяч строк и покрывают любой момент. Fact-таблица просто хранит два foreign key: date_key и time_key.

Разделение date и time — это пример общего принципа размерного моделирования: не смешивать в одной dimension сущности с разной кардинальностью и разной природой. Календарный день и момент суток — независимые понятия. День повторяется в календаре конечное число раз (7300 за 20 лет), момент суток — фиксированный набор из 86400 секунд, не зависящий от даты. Перемножать их в одной таблице — значит создавать строку на каждое сочетание, то есть взрывать объём на пустом месте. Раздельные dimensions хранят каждое понятие ровно один раз, а fact-таблица соединяет их через два независимых foreign key. Это даёт и гибкость: можно анализировать «по часам пик» через dim_time, не затрагивая dim_date, и «по будням/праздникам» через dim_date, не затрагивая время.

Заметьте также, что date dimension — это ещё и conformed dimension в чистом виде, и role-playing dimension одновременно. Conformed — потому что одна dim_date обслуживает все fact-таблицы warehouse. Role-playing — потому что внутри одной fact-таблицы она выступает в нескольких ролях (дата заказа, отгрузки, доставки). То есть dim_date сводит воедино почти все приёмы этого модуля: она и общая для процессов, и многоролевая, и иногда выступает outrigger внутри других dimensions. Если вы хорошо понимаете устройство date dimension, вы понимаете размерное моделирование в целом.

Попробуй сам

Часть 1 — bridge для many-to-many. Дана задача: у банковского счёта может быть несколько держателей, у клиента — несколько счетов.

1. Спроектируйте dim_account, dim_customer и bridge_account_holders с weighting_factor.
2. Напишите allocated query: остаток по счетам, распределённый между держателями (сумма по всем держателям равна общему остатку банка).
3. Напишите impact query: суммарный остаток всех счетов, к которым имеет доступ конкретный клиент.

Часть 2 — date dimension. Сгенерируйте мысленно dim_date на январь 2026: 31 строка. Выпишите для 1, 3, 7 января значения day_name, is_weekend, date_key. Объясните, почему date_key в формате YYYYMMDD здесь уместен, хотя в других dimensions «умные» ключи — антипаттерн.