Изменение данных и проектирование баз

Продвинутый

UPSERT и RETURNING: вставить или обновить

К уроку

Изменение схемы: ALTER TABLE и миграции

К уроку

Продвинутый SQL

Продвинутый

Оконные функции

К уроку

CTE: WITH и рекурсивные запросы

К уроку

CASE и условная логика

К уроку

Функции для строк, дат и чисел

К уроку

Операции над множествами и сложные соединения

К уроку

Программирование внутри базы данных

Продвинутый

Триггеры

К уроку

JSON в SQL: SQLite и PostgreSQL

К уроку

Последовательности: пропуски и острова

К уроку
grp = value − ROW_NUMBER() OVER (ORDER BY value)

Где

value
значение последовательности (id, номер дня)
ROW_NUMBER()
номер строки по порядку: 1, 2, 3, …
grp
ключ острова: одинаков внутри одного острова

Затем GROUP BY grp сворачивает каждый остров в строку: MIN — начало, MAX — конец, COUNT — длина.

Базы данных на профессиональном уровне

Университет

Проектирование баз данных и нормализация

К уроку
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% значений.

За пределами одной базы: хранилища и масштабирование

Университет

Хранилища данных: схема «звезда», факты и измерения

К уроку
average_price = Σ revenue / Σ quantityaverage_price = Σ revenue / Σ quantity

Где

Σ revenue
сумма выручки по выбранным строкам
Σ quantity
сумма проданных единиц в этих строках

Средняя цена считается взвешенно; AVG(unit_price) даёт всем строкам одинаковый вес и ошибочен.

date_key = year · 10000 + month · 100 + day

Где

year
год, например 2025
month
месяц, 1–12
day
день месяца, 1–31

Такой ключ сортируется как дата и читается глазами.