День 2793. #ЗаметкиНаПолях #SQL
10 Редких Возможностей SQL, Которые Стоит Знать Каждому. Часть 3
1-3
4-7

8. Вычисляемые (генерируемые) столбцы
Если значение столбца всегда формируется на основе данных из других столбцов, его вычисление в коде приложения чревато ошибками, поскольку формулу приходится учитывать в каждом месте, где он используется. Использование генерируемого столбца позволяет перенести эту формулу в определение таблицы, благодаря чему БД вычисляет и сохраняет значение автоматически.

CREATE TABLE shipments.shipping_costs (
  id SERIAL PRIMARY KEY,
  shipment_id UUID NOT NULL,
  base_rate DECIMAL(10,2) NOT NULL,
  weight_kg DECIMAL(8,2) NOT NULL,
  distance_km DECIMAL(10,2) NOT NULL,
  fuel_surcharge_rate DECIMAL(5,4) NOT NULL DEFAULT 0.15,

  -- Вычисляемые столбцы
  weight_cost DECIMAL(10,2) GENERATED ALWAYS AS (weight_kg * 2.50) STORED,
  distance_cost DECIMAL(10,2) GENERATED ALWAYS AS (distance_km * 0.85) STORED,
  fuel_surcharge DECIMAL(10,2) GENERATED ALWAYS AS (base_rate * fuel_surcharge_rate) STORED,
  total_cost DECIMAL(10,2) GENERATED ALWAYS AS (
    base_rate
    + (weight_kg * 2.50)
    + (distance_km * 0.85)
    + (base_rate * fuel_surcharge_rate)
  ) STORED,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

 FOREIGN KEY (shipment_id) REFERENCES shipments(id)
);

Значение каждого столбца, определённого как GENERATED ALWAYS AS (…) STORED, вычисляется на основе других столбцов при каждой вставке или обновлении строки. Столбец total_cost суммирует базовый тариф, стоимость с учётом веса, стоимость с учётом расстояния и топливный сбор; при этом невозможно забыть пересчитать его значение, так как в этот столбец нельзя записать данные напрямую.
Ключевое слово STORED означает, что значение сохраняется физически (и может быть проиндексировано), а не вычисляется заново при каждом чтении.
Примечание: в SQL Server такие столбцы называются вычисляемыми (computed) и описываются как total_cost AS (…), а для сохранения значения используется ключевое слово PERSISTED. В MySQL для этого применяется тот же синтаксис GENERATED ALWAYS AS, что и в PostgreSQL.

9. TABLESAMPLE
Выполнение тестового запроса к огромной таблице занимает много времени, если вам нужно лишь получить общее представление о данных, а не просматривать каждую строку. Оператор TABLESAMPLE возвращает случайную выборку из таблицы, считывая лишь её часть вместо полного сканирования:

-- Простой пример выборки
SELECT carrier, COUNT(*)
FROM shipments TABLESAMPLE SYSTEM (5)
GROUP BY carrier;

-- Случайная выборка с заданным посевом для повторяемости результатов
SELECT * FROM shipments
TABLESAMPLE BERNOULLI (10)
REPEATABLE (12345);

-- Пример с WHERE
SELECT * FROM shipments
TABLESAMPLE BERNOULLI (20)
WHERE status = 'pending';

-- Пример с соединением
SELECT s.number, s.carrier, sc.total_cost
FROM shipments s
TABLESAMPLE SYSTEM (10)
JOIN shipping_costs sc ON s.id = sc.shipment_id;

Метод TABLESAMPLE SYSTEM (5) выбирает примерно 5% данных таблицы путём считывания случайных страниц: это работает быстро, но выборка осуществляется на уровне блоков. Метод BERNOULLI (10) отбирает около 10% строк по отдельности; такой подход обеспечивает более равномерную с точки зрения статистики выборку, но выполняется медленнее.
Параметр REPEATABLE (12345) фиксирует начальное значение генератора случайных чисел, благодаря чему при каждом запуске получается одна и та же выборка, что полезно для воспроизводимых тестов.
Этот механизм предназначен для быстрой проверки, профилирования и тестирования запросов к большим таблицам без затрат ресурсов на полное сканирование.
Примечание: TABLESAMPLE входит в стандарт SQL; PostgreSQL поддерживает методы SYSTEM и BERNOULLI, а SQL Server также поддерживает TABLESAMPLE SYSTEM.

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

-- Частичный индекс для отправок «в пути»/«в ожидании»
CREATE INDEX idx_shipments_pending_carrier
ON shipments (carrier, created_at)
WHERE status IN ('pending', 'in_transit');

-- Частичный индекс для поставщика
CREATE INDEX idx_shipments_fedex_status
ON shipments (status, updated_at)
WHERE carrier = 'FedEx';

-- Использует idx_shipments_pending_carrier
SELECT number, carrier, created_at
FROM shipments
WHERE status = 'pending'
  AND carrier = 'FedEx'
ORDER BY created_at DESC;

-- Использует idx_shipments_fedex_status
SELECT number, status, updated_at
FROM shipments
WHERE carrier = 'FedEx'
  AND status IN ('delivered', 'pending')
ORDER BY updated_at DESC;

Запросы, соответствующие условиям, используют нужный индекс; поскольку каждый индекс содержит лишь часть данных таблицы, операции поиска и обслуживания выполняются быстрее.
Частичные индексы особенно эффективны для работы с «горячими» подмножествами данных — например, с активными записями, данными с фильтром is_deleted = false (мягкое удаление) или записями с определённым статусом, к которым часто обращаются. В таких случаях большинство запросов затрагивает лишь небольшую, предсказуемую часть таблицы.

Примечание: в SQL Server такие индексы называются «фильтруемыми» (filtered indexes); для их создания используется тот же синтаксис CREATE INDEX … WHERE.

Источник: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know