ПостДень 2793. #ЗаметкиНаПолях #SQL

День 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