0Pricing
SQL Academy · Урок

Таблицы фактов и измерений

Строительные блоки хранилища данных

«Таблицы фактов и измерений» — бесплатный урок SQL Academy на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое хранилище данных

Хранилище данных — это централизованное хранилище, предназначенное для отчетов и аналитических запросов. В отличие от транзакционной базы данных, оптимизированной для быстрых операций записи, хранилище настроено на быстрое чтение больших объемов исторических данных.

Чаще всего хранилище организуют с помощью звездной схемы, которая разделяет данные на два типа таблиц: таблицы фактов и таблицы измерений.

Определение таблиц фактов

Таблица фактов хранит измеримые количественные события — то, что вы хотите анализировать. Каждая строка представляет одно событие в рамках бизнеса, например продажу, просмотр веб-страницы или обращение в службу поддержки.

Таблицы фактов обычно содержат много строк и мало столбцов; большинство столбцов являются внешними ключами таблиц измерений или числовыми мерами, такими как quantity или revenue.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  unit_price   NUMERIC(10, 2) NOT NULL,
  total_amount NUMERIC(12, 2) NOT NULL
);

Определение таблиц измерений

Таблица измерений хранит описательные атрибуты, которые добавляют контекст каждому факту. Например, это может быть измерение продукта (название, категория, бренд) или измерение даты (день, месяц, квартал, год).

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

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_cost    NUMERIC(10, 2)
);

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  full_name    VARCHAR(200) NOT NULL,
  email        VARCHAR(200),
  country      VARCHAR(100),
  segment      VARCHAR(50)
);

Измерение даты

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

Это позволяет запросам фильтровать или группировать данные по финансовому кварталу, дню недели, признаку праздника и другим календарным атрибутам без выполнения арифметики с датами во время запроса.

CREATE TABLE dim_date (
  date_key       INT PRIMARY KEY,  -- e.g. 20240315
  full_date      DATE NOT NULL,
  day_of_week    VARCHAR(10),
  day_of_month   INT,
  month_num      INT,
  month_name     VARCHAR(20),
  quarter        INT,
  year           INT,
  is_holiday     BOOLEAN DEFAULT FALSE,
  fiscal_quarter INT
);

-- Sample row
INSERT INTO dim_date VALUES
  (20240315, '2024-03-15', 'Friday', 15, 3, 'March', 1, 2024, FALSE, 2);

Шаблон звездной схемы

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

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

-- Join fact to two dimensions to enrich a sales report
SELECT
  dp.product_name,
  dp.category,
  SUM(fs.quantity)     AS total_units_sold,
  SUM(fs.total_amount) AS total_revenue
FROM fact_sales fs
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_date     dd ON dd.date_key     = fs.date_key
WHERE dd.year = 2024
GROUP BY dp.product_name, dp.category
ORDER BY total_revenue DESC;

Суррогатные и естественные ключи

В таблицах измерений используются суррогатные ключи — искусственные целые числа, сгенерированные базой данных и не связанные с каким-либо деловым смыслом. Естественные ключи (например, SKU продукта или адрес электронной почты клиента) могут меняться со временем, а суррогатные ключи никогда не меняются.

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

-- Surrogate key approach: integer join is fast
SELECT fs.sale_id, dc.full_name, fs.total_amount
FROM fact_sales fs
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dc.country = 'Germany'
LIMIT 10;

-- Natural key approach (avoid in warehouses): slower string join
-- JOIN dim_customer dc ON dc.email = fs.customer_email

Гранулярность: уровень детализации в таблице фактов

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

Четко определенная гранулярность предотвращает неоднозначные агрегации. Если разные строки представляют разные события, результаты SUM и COUNT не будут иметь смысла.

-- Grain: one row per product per order line
-- Each row = one line item sold in one transaction
SELECT
  sale_id,
  date_key,
  product_key,
  quantity,
  unit_price,
  total_amount
FROM fact_sales
WHERE date_key = 20240315
ORDER BY sale_id;

Аддитивные, полуаддитивные и неаддитивные меры

Меры бывают трех видов в зависимости от того, как их можно агрегировать:

  • Аддитивные — их можно суммировать по всем измерениям (например, revenue, quantity).
  • Полуаддитивные — их можно суммировать по некоторым измерениям, но не по всем (например, balance счета можно суммировать по клиентам, но нельзя суммировать по времени).
  • Неаддитивные — их нельзя осмысленно суммировать (например, unit_price, ratio). Вместо этого используйте AVG или другие агрегаты.
SELECT
  dd.month_name,
  SUM(fs.total_amount)         AS total_revenue,   -- additive
  AVG(fs.unit_price)           AS avg_unit_price,   -- non-additive: use AVG
  SUM(fs.quantity)             AS total_units       -- additive
FROM fact_sales fs
JOIN dim_date dd ON dd.date_key = fs.date_key
WHERE dd.year = 2024
GROUP BY dd.month_name, dd.month_num
ORDER BY dd.month_num;

Медленно изменяющиеся измерения (SCD, типы 1 и 2)

Атрибуты измерений меняются со временем: клиент переезжает в другую страну, продукт меняет категорию. Медленно изменяющиеся измерения (SCD) обрабатывают такие изменения:

  • Тип 1 — перезаписать старое значение. Просто, но история теряется.
  • Тип 2 — добавить новую строку с новым суррогатным ключом и датами действия. Полностью сохраняет историю, поэтому исторические факты по-прежнему ссылаются на правильную версию измерения.
-- SCD Type 2: add a new version of the row
ALTER TABLE dim_customer ADD COLUMN valid_from DATE;
ALTER TABLE dim_customer ADD COLUMN valid_to   DATE;
ALTER TABLE dim_customer ADD COLUMN is_current BOOLEAN DEFAULT TRUE;

-- Expire the old row
UPDATE dim_customer
SET is_current = FALSE,
    valid_to   = CURRENT_DATE - INTERVAL '1 day'
WHERE email = 'anna@example.com' AND is_current = TRUE;

-- Insert the updated version
INSERT INTO dim_customer (full_name, email, country, segment, valid_from, valid_to, is_current)
VALUES ('Anna Muller', 'anna@example.com', 'Austria', 'Premium', CURRENT_DATE, '9999-12-31', TRUE);

Вырожденные измерения

Иногда атрибуту измерения не нужна собственная таблица. Вырожденное измерение — это ключ измерения, который хранится непосредственно в таблице фактов без соответствующей таблицы измерения.

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

-- order_number is a degenerate dimension:
-- it lives in the fact table, no dim_order table needed
CREATE TABLE fact_order_lines (
  line_id      SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL,  -- degenerate dimension
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  quantity     INT NOT NULL,
  line_total   NUMERIC(12, 2) NOT NULL
);

SELECT order_number, SUM(line_total) AS order_total
FROM fact_order_lines
GROUP BY order_number
ORDER BY order_total DESC
LIMIT 5;

Запрос ко всей звездной схеме

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

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

SELECT
  dd.year,
  dd.quarter,
  dp.category,
  dc.country,
  SUM(fs.quantity)     AS units_sold,
  SUM(fs.total_amount) AS revenue
FROM fact_sales fs
JOIN dim_date     dd ON dd.date_key     = fs.date_key
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dd.year IN (2023, 2024)
  AND dp.category = 'Electronics'
GROUP BY dd.year, dd.quarter, dp.category, dc.country
ORDER BY dd.year, dd.quarter, revenue DESC;

Быстрая проверка: факты и измерения

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

Итоги урока

В этом уроке вы изучили основные компоненты звездной схемы хранилища данных:

  • Таблицы фактов хранят измеримые события (продажи, нажатия, транзакции), числовые меры и внешние ключи.
  • Таблицы измерений предоставляют описательный контекст (кто, что, где и когда) с помощью суррогатных ключей.
  • Гранулярность точно определяет, что представляет собой одна строка фактов; ее необходимо объявить до создания хранилища.
  • Меры бывают аддитивными, полуаддитивными или неаддитивными, и это определяет способ их агрегации.
  • SCD, тип 2 сохраняет исторические значения измерений, добавляя новые строки с датами действия.
  • Вырожденные измерения хранятся в таблице фактов, если у них нет дополнительных атрибутов для описания.

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

Часто задаваемые вопросы

Урок «Таблицы фактов и измерений» бесплатный?

Да — полный текст урока «Таблицы фактов и измерений» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Таблицы фактов и измерений»?

Строительные блоки хранилища данных Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Таблицы фактов и измерений»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. OLTP и OLAP
  2. Таблицы фактов и измерений
  3. Звёздная и снежинка-схема
  4. Написание аналитических запросов
← Назад к SQL Academy