Формулы и лайфхаки
SQL · 20
Все формулы этого курса и простые способы их запомнить — на одной странице.
1Продвинутый SQLПродвинутый
Оконные функции
К урокуCTE: WITH и рекурсивные запросы
К урокуCASE и условная логика
К урокуФункции для строк, дат и чисел
К урокуОперации над множествами и сложные соединения
К уроку2Базы данных на профессиональном уровнеУниверситет
Проектирование баз данных и нормализация
К урокуX → Y ⇔ ∀ t₁, t₂ ∈ r : t₁[X] = t₂[X] ⇒ t₁[Y] = t₂[Y]
- Xдетерминант — множество атрибутов
- Yмножество зависимых атрибутов
- rлюбое допустимое состояние таблицы (отношения)
- t₁, t₂любые две строки таблицы; t[X] — значения строки в столбцах X
Две строки, совпадающие по X, обязаны совпадать и по Y.
(R₁ ∩ R₂) → R₁ ∨ (R₁ ∩ R₂) → R₂
- R₁, R₂множества атрибутов двух таблиц, полученных декомпозицией (R₁ ∪ R₂ = R)
- R₁ ∩ R₂общие атрибуты — столбцы, по которым выполняется
JOIN
Если условие выполняется, декомпозиция без потерь: R = R₁ ⋈ R₂.
Транзакции и параллельная работа
К урокуs = s₀ − q₂ ≠ s₀ − q₁ − q₂
- s₀исходный остаток, который прочитали обе транзакции
- q₁, q₂количество, проданное T1 и T2
- sрезультат, сохранённый последней записавшей транзакцией (T2)
Потерянное обновление: в схеме «прочитать → посчитать в приложении → записать» продажа T1 выпадает из итога.
Оптимизация запросов и индексы
К урокуh = ⌈ log N ÷ log f ⌉
- hвысота дерева — число страниц, читаемых для поиска одного ключа
- Nчисло ключей (строк) в индексе
- fкоэффициент ветвления — сколько ключей помещается на странице (обычно сотни)
Высота растёт логарифмически с числом строк: когда таблица вырастает в 500 раз, у дерева появляется всего один новый уровень.
s = n ÷ N
- sселективность условия (от 0 до 1)
- nчисло строк, удовлетворяющих условию
- Nвсе строки таблицы
Малая s (условие возвращает мало строк) хороша для индекса; при большой s полный просмотр может оказаться дешевле.
PostgreSQL и MySQL на практике
К урокуАнализ данных с помощью SQL
К урокуAOV = R ÷ Nₒ
- AOVсредний чек (average order value), манат
- Rвыручка за период: Σ количество · цена, манат
- Nₒчисло заказов за тот же период
r = A ÷ C₀ · 100%
- rпроцент удержания когорты
- C₀размер когорты — покупатели, пришедшие в первом периоде
- Aпокупатели, снова сделавшие заказ в последующие периоды
cᵢ = nᵢ ÷ n₁ · 100%
- cᵢконверсия до i-го этапа
- nᵢпокупатели, дошедшие до i-го этапа
- n₁первый этап воронки
Пошаговая конверсия — nᵢ ÷ nᵢ₋₁ · 100%; она отвечает на вопрос «где мы теряем больше всего?».
k = ⌈ p ÷ 100 · n ⌉
- kпозиция перцентиля в отсортированном списке (с 1)
- pперцентиль, например 90
- nчисло значений
Метод ближайшего ранга: p-й перцентиль — наименьшее значение, покрывающее не меньше p% значений.