Формулы и лайфхаки
SQL12 формул17 лайфхаков
Все формулы этого курса и простые способы их запомнить, на одной странице.
Изменение данных и проектирование баз
ПродвинутыйUPSERT и RETURNING: вставить или обновить
К урокуИзменение схемы: ALTER TABLE и миграции
К урокуПродвинутый SQL
ПродвинутыйОконные функции
К урокуCTE: WITH и рекурсивные запросы
К урокуCASE и условная логика
К урокуФункции для строк, дат и чисел
К урокуОперации над множествами и сложные соединения
К урокуПрограммирование внутри базы данных
ПродвинутыйТриггеры
К урокуJSON в SQL: SQLite и PostgreSQL
К урокуПоследовательности: пропуски и острова
К урокуГде
- value
- значение последовательности (id, номер дня)
- ROW_NUMBER()
- номер строки по порядку: 1, 2, 3, …
- grp
- ключ острова: одинаков внутри одного острова
Затем GROUP BY grp сворачивает каждый остров в строку: MIN — начало, MAX — конец, COUNT — длина.
Базы данных на профессиональном уровне
УниверситетПроектирование баз данных и нормализация
К урокуГде
- X
- детерминант — множество атрибутов
- Y
- множество зависимых атрибутов
- r
- любое допустимое состояние таблицы (отношения)
- t₁, t₂
- любые две строки таблицы; t[X] — значения строки в столбцах X
Две строки, совпадающие по X, обязаны совпадать и по Y.
Где
- R₁, R₂
- множества атрибутов двух таблиц, полученных декомпозицией (R₁ ∪ R₂ = R)
- R₁ ∩ R₂
- общие атрибуты — столбцы, по которым выполняется
JOIN
Если условие выполняется, декомпозиция без потерь: R = R₁ ⋈ R₂.
Транзакции и параллельная работа
К урокуГде
- s₀
- исходный остаток, который прочитали обе транзакции
- q₁, q₂
- количество, проданное T1 и T2
- s
- результат, сохранённый последней записавшей транзакцией (T2)
Потерянное обновление: в схеме «прочитать → посчитать в приложении → записать» продажа T1 выпадает из итога.
Оптимизация запросов и индексы
К урокуГде
- h
- высота дерева — число страниц, читаемых для поиска одного ключа
- N
- число ключей (строк) в индексе
- f
- коэффициент ветвления — сколько ключей помещается на странице (обычно сотни)
Высота растёт логарифмически с числом строк: когда таблица вырастает в 500 раз, у дерева появляется всего один новый уровень.
Где
- s
- селективность условия (от 0 до 1)
- n
- число строк, удовлетворяющих условию
- N
- все строки таблицы
Малая s (условие возвращает мало строк) хороша для индекса; при большой s полный просмотр может оказаться дешевле.
PostgreSQL и MySQL на практике
К урокуАнализ данных с помощью SQL
К урокуГде
- AOV
- средний чек (average order value), манат
- R
- выручка за период: Σ количество · цена, манат
- Nₒ
- число заказов за тот же период
Где
- r
- процент удержания когорты
- C₀
- размер когорты — покупатели, пришедшие в первом периоде
- A
- покупатели, снова сделавшие заказ в последующие периоды
Где
- cᵢ
- конверсия до i-го этапа
- nᵢ
- покупатели, дошедшие до i-го этапа
- n₁
- первый этап воронки
Пошаговая конверсия — nᵢ ÷ nᵢ₋₁ · 100%; она отвечает на вопрос «где мы теряем больше всего?».
Где
- k
- позиция перцентиля в отсортированном списке (с 1)
- p
- перцентиль, например 90
- n
- число значений
Метод ближайшего ранга: p-й перцентиль — наименьшее значение, покрывающее не меньше p% значений.
За пределами одной базы: хранилища и масштабирование
УниверситетХранилища данных: схема «звезда», факты и измерения
К урокуГде
- Σ revenue
- сумма выручки по выбранным строкам
- Σ quantity
- сумма проданных единиц в этих строках
Средняя цена считается взвешенно; AVG(unit_price) даёт всем строкам одинаковый вес и ошибочен.
Где
- year
- год, например 2025
- month
- месяц, 1–12
- day
- день месяца, 1–31
Такой ключ сортируется как дата и читается глазами.