Справочник терминов

ABS(number)

Возвращает абсолютное (положительное) значение числа.

BEGIN; ... COMMIT;  -- ACID гарантируется

Четыре свойства надёжных транзакций: Atomicity, Consistency, Isolation, Durability.

AGE(timestamp)

Возвращает интервал между двумя датами в виде лет, месяцев и дней.

ALTER TABLE table

Изменяет структуру существующей таблицы: добавляет колонки, меняет типы, добавляет ограничения.

ARPU = Общая выручка за период / Число пользователей за период

Средняя выручка на одного пользователя за период — общая выручка, делённая на число пользователей.

SELECT expression AS alias_name FROM table AS t;

Задаёт псевдоним колонке или таблице для удобочитаемости запроса.

AVG(column)

Возвращает среднее арифметическое числовых значений. NULL игнорируются.

CREATE INDEX ON table USING btree (column);  -- btree по умолчанию

Тип индекса по умолчанию в PostgreSQL. Подходит для операций =, <, >, BETWEEN, LIKE 'prefix%'.

CAC = (Расходы на маркетинг + Расходы на продажи) / Число новых клиентов за период

Стоимость привлечения одного нового платящего клиента: все расходы на маркетинг и продажи, делённые на число привлечённых клиентов.

CASE WHEN condition1 THEN result1

Условное выражение в SQL — аналог if-else. Возвращает значение в зависимости от условия.

CAST(value AS type)

Преобразует значение из одного типа данных в другой.

CEIL(number)

Округляет число вверх до ближайшего целого.

COALESCE(value1, value2, ...)

Возвращает первое не-NULL значение из списка аргументов.

CONCAT(str1, str2, ...)

Объединяет несколько строк в одну.

COUNT(*) | COUNT(column) | COUNT(DISTINCT column)

Считает количество строк или непустых значений в группе.

CREATE TABLE table_name (

Создаёт новую таблицу с определёнными колонками и ограничениями.

SELECT ... FROM t1 CROSS JOIN t2;

Декартово произведение двух таблиц: каждая строка первой соединяется с каждой строкой второй.

WITH cte_name AS (

Именованный подзапрос, объявленный через WITH. Делает сложные запросы читаемыми.

CURRENT_DATE

Возвращает текущую дату без времени.

DATE_TRUNC(field, date)

Усекает дату/время до указанной точности. Незаменима для группировки по периодам.

DAU = количество уникальных активных пользователей за календарные сутки

Число уникальных пользователей, совершивших хотя бы одно действие в продукте за календарные сутки.

DAU/MAU (stickiness) = DAU / MAU × 100%

Число уникальных активных пользователей за день (DAU), неделю (WAU) или месяц (MAU).

DELETE FROM table WHERE condition;

Удаляет строки из таблицы, подходящие под условие WHERE.

DENSE_RANK() OVER ([PARTITION BY column] ORDER BY column)

Как RANK, но без пропуска номеров при одинаковых значениях.

SELECT DISTINCT column1, column2 FROM table;

Убирает дубликаты из результата запроса.

OLTP-источники → ETL/ELT → DWH (fact + dimension таблицы) → BI-отчёты

Централизованное хранилище данных из разных источников компании, оптимизированное для аналитических запросов, а не для операционной работы приложения.

-- Защита от дедлоков: одинаковый порядок блокировок

Ситуация, когда две транзакции ждут блокировок друг друга и не могут продолжить.

SELECT ... WHERE EXISTS (SELECT 1 FROM ... WHERE ...);

Проверяет, возвращает ли подзапрос хотя бы одну строку — не важно сколько, важен сам факт.

EXPLAIN SELECT ...;

Показывает план выполнения запроса без реального выполнения — какие индексы и типы соединений выберет планировщик.

EXPLAIN ANALYZE SELECT ...;

Реально выполняет запрос и показывает фактическое время и число строк — в сравнении с оценкой планировщика.

EXTRACT(field FROM date)

Извлекает указанную часть из значения даты/времени.

FIRST_VALUE(column) OVER ([PARTITION BY column] ORDER BY column [frame_clause])

Возвращает первое значение в оконной рамке.

FLOOR(number)

Округляет число вниз до ближайшего целого.

REFERENCES parent_table(parent_col) [ON DELETE CASCADE|SET NULL|RESTRICT]

Ограничение, связывающее колонку с первичным ключом другой таблицы. Обеспечивает ссылочную целостность.

SELECT ... FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id;

Возвращает все строки из обеих таблиц. Где нет совпадения — NULL с соответствующей стороны.

SELECT col, AGG(col2) FROM table GROUP BY col;

Группирует строки с одинаковыми значениями для применения агрегатных функций.

SELECT col, AGG(col2) FROM table GROUP BY col HAVING AGG(col2) > value;

Фильтрует группы после GROUP BY. В отличие от WHERE, может использовать агрегатные функции.

CREATE INDEX [CONCURRENTLY] idx_name ON table (column);

Структура данных, ускоряющая поиск строк по значению колонки.

SELECT ... FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;

Возвращает только строки, у которых есть совпадение в обеих таблицах.

INSERT INTO table (col1, col2, ...) VALUES (val1, val2, ...);

Добавляет новые строки в таблицу.

col JSONB

Бинарное хранение JSON в PostgreSQL с поддержкой индексов и операторов поиска.

JSONB_AGG(expression [ORDER BY ...])

Агрегатная функция: собирает значения из группы строк в один JSONB-массив.

JSONB_ARRAY_ELEMENTS(jsonb_array)

Разворачивает JSONB-массив в набор строк — по одному элементу на строку. Используется в FROM, как generate_series для массивов.

JSONB_BUILD_ARRAY(value1, value2, ...)

Собирает JSONB-массив из произвольного списка аргументов.

JSONB_BUILD_OBJECT(key1, value1, key2, value2, ...)

Собирает JSONB-объект из чередующихся пар ключ-значение, переданных как аргументы функции.

JSONB_EACH(jsonb_object)

Разворачивает JSONB-объект в набор строк «ключ — значение».

JSONB_PRETTY(jsonb_value)

Форматирует JSONB для чтения человеком — с отступами и переносами строк.

JSONB_SET(target, path, new_value [, create_if_missing])

Изменяет значение по указанному пути внутри JSONB — без перезаписи всего документа.

JSONB_STRIP_NULLS(jsonb_value)

Рекурсивно удаляет из JSONB все пары ключ-значение, где значение — JSON null.

JSONB_TYPEOF(jsonb_value)

Возвращает тип JSON-значения текстом: object, array, string, number, boolean или null.

JSON_AGG(expression [ORDER BY ...])

То же самое, что JSONB_AGG, но результат — тип JSON (текст), а не JSONB (бинарный).

LAG(column [, offset [, default]]) OVER ([PARTITION BY column] ORDER BY column)

Возвращает значение из строки, стоящей на N позиций раньше в текущей секции.

LAG(col [, offset [, default]]) OVER (ORDER BY col)

LAG возвращает значение из предыдущей строки окна, LEAD — из следующей.

LAST_VALUE(column) OVER ([PARTITION BY column] ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Возвращает последнее значение в оконной рамке.

SELECT ... FROM t1, LATERAL (SELECT ... WHERE fk = t1.id LIMIT n) sub;

Подзапрос в FROM, который может ссылаться на колонки из предыдущих таблиц того же FROM.

LEAD(column [, offset [, default]]) OVER ([PARTITION BY column] ORDER BY column)

Возвращает значение из строки, стоящей на N позиций вперёд в текущей секции.

SELECT ... FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id;

Возвращает все строки из левой таблицы. Для строк без пары в правой — NULL.

LENGTH(string)

Возвращает количество символов в строке.

SELECT ... FROM table ORDER BY col LIMIT n;

Ограничивает количество строк, возвращаемых запросом.

LOWER(string)

Переводит все символы строки в нижний регистр.

LTV = ARPU × Средняя продолжительность жизни клиента (мес.) или: LTV = ARPU / Churn rate

Суммарная выручка, которую компания получает от одного клиента за всё время, пока он остаётся клиентом.

MAU = количество уникальных активных пользователей за календарный месяц (обычно 30 дней)

Число уникальных пользователей, совершивших хотя бы одно действие в продукте за календарный месяц.

MAX(column)

Возвращает максимальное значение в столбце. Работает с числами, строками и датами.

MERGE INTO target t

Стандартная SQL-команда «вставить или обновить» — сравнивает исходные данные с целевой таблицей и выполняет INSERT/UPDATE/DELETE в одном запросе.

MIN(column)

Возвращает минимальное значение в столбце. Работает с числами, строками и датами.

MRR = Σ (месячная стоимость подписки каждого активного клиента)

Регулярная ежемесячная выручка от подписок, приведённая к единому месячному эквиваленту.

SELECT ... WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...);

Проверяет, что подзапрос НЕ возвращает ни одной строки. NULL-безопасная альтернатива NOT IN.

NOW()

Возвращает текущую дату и время с часовым поясом.

NPS = (% Промоутеров) − (% Критиков)

Индекс готовности рекомендовать продукт: разница между долей сторонников и долей критиков по опросу «оцените от 0 до 10».

NTILE(n) OVER ([PARTITION BY column] ORDER BY column)

Делит строки на N примерно равных групп и возвращает номер группы.

WHERE col IS NULL

NULL означает отсутствие значения. Для проверки используется IS NULL, а не = NULL.

NULLIF(value1, value2)

Возвращает NULL если оба аргумента равны, иначе возвращает первый аргумент.

SELECT ... FROM table ORDER BY col LIMIT n OFFSET m;

Пропускает первые N строк результата — используется вместе с LIMIT для постраничного вывода.

SELECT ... FROM table ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Сортирует результат запроса по одной или нескольким колонкам.

function() OVER ([PARTITION BY col] [ORDER BY col] [frame])

Ключевое слово, превращающее агрегатную функцию в оконную — вычисляет значение без схлопывания строк.

function() OVER (PARTITION BY col1, col2 ORDER BY col3)

Делит строки на разделы для оконной функции. Аналог GROUP BY, но без схлопывания строк.

POSITION(substring IN string)

Возвращает позицию первого вхождения подстроки. 0 если не найдено.

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Уникальный идентификатор строки. Автоматически создаёт уникальный индекс, не допускает NULL.

CREATE INDEX idx_name ON table (column) WHERE condition;

Индекс с условием WHERE — индексирует только часть строк таблицы.

RANK() OVER ([PARTITION BY column] ORDER BY column)

Присваивает ранг строкам. При одинаковых значениях ранги совпадают, следующий ранг пропускается.

REGEXP_REPLACE(string, pattern, replacement [, flags])

Заменяет подстроки, соответствующие регулярному выражению.

REPLACE(string, from, to)

Заменяет все вхождения подстроки на другую строку.

INSERT INTO table (...) VALUES (...) RETURNING id;

Возвращает строки после INSERT, UPDATE или DELETE без дополнительного SELECT.

RFM-сегмент = (Recency-балл, Frequency-балл, Monetary-балл), обычно каждый 1–5

Метод сегментации клиентов по трём признакам: давность последней покупки, частота покупок и сумма трат.

SELECT ... FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id;

Возвращает все строки из правой таблицы. Для строк без пары в левой — NULL.

ROI = (Доход от инвестиции − Сумма инвестиции) / Сумма инвестиции × 100%

Коэффициент возврата инвестиций: сколько прибыли принёс вложенный рубль, в процентах.

ROUND(number)                    -- стандарт SQL

Округляет число до указанного количества знаков после запятой.

ROW_NUMBER() OVER ([PARTITION BY col] ORDER BY col)

Оконные функции нумерации строк. Различаются поведением при одинаковых значениях.

ROW_TO_JSON(record)

Преобразует строку (row) в объект JSON, где ключи — имена колонок.

Retention (день N) = (Активны в день N из когорты дня 0) / (Размер когорты дня 0) × 100%

Доля пользователей, которые вернулись и остались активными спустя определённый период после первого визита.

SAVEPOINT point_name;

Точка сохранения внутри транзакции. Позволяет откатиться к ней, не отменяя всю транзакцию.

SCD Type 2: (dimension_key, natural_id, ..., valid_from, valid_to, is_current)

Техники хранения истории изменений атрибутов в измерениях DWH — что делать с фактами, когда, например, город клиента меняется со временем.

SELECT column1, column2 FROM table WHERE condition ORDER BY column LIMIT n;

Основная команда SQL для получения данных из таблицы или нескольких таблиц.

SPLIT_PART(string, delimiter, field_number)

Разбивает строку по разделителю и возвращает указанную часть.

STRING_AGG(column, delimiter [ORDER BY ...])

Объединяет строки группы в одну с указанным разделителем.

SUBSTRING(string FROM start)

Извлекает часть строки начиная с указанной позиции.

SUM(col) | AVG(col) | MIN(col) | MAX(col)

Агрегатные функции для вычисления суммы, среднего, минимума и максимума числовых значений.

EXPLAIN SELECT * FROM table WHERE col = value;

Seq Scan — полное сканирование таблицы. Index Scan — поиск через индекс. EXPLAIN показывает какой метод выбран.

column TIMESTAMPTZ DEFAULT NOW()

Тип данных для хранения даты и времени с часовым поясом. Рекомендуется вместо TIMESTAMP.

TO_CHAR(value, format)

Преобразует дату или число в строку по заданному формату.

TO_JSONB(any_value)

Преобразует любое значение (число, строку, массив, всю строку таблицы) в JSONB.

TRIM(string)

Удаляет пробелы (или указанные символы) с начала и конца строки.

TRUNCATE TABLE table;

Мгновенно удаляет все строки таблицы, не читая их по одной — быстрее DELETE без WHERE.

SELECT col FROM t1

Объединяет результаты двух SELECT в один. UNION убирает дубли, UNION ALL оставляет все строки.

UPDATE table SET col1 = val1, col2 = val2 WHERE condition;

Изменяет значения в существующих строках таблицы.

UPPER(string)

Переводит все символы строки в верхний регистр.

INSERT INTO table (cols) VALUES (...)

Вставляет строку или обновляет её при конфликте уникального ключа. Атомарная операция.

VACUUM table_name;

Очищает «мёртвые» версии строк после UPDATE и DELETE. Необходим для корректной работы PostgreSQL.

WAU = количество уникальных активных пользователей за календарную неделю (7 дней)

Число уникальных пользователей, совершивших хотя бы одно действие в продукте за календарную неделю.

SELECT ... FROM table WHERE condition;

Фильтрует строки таблицы по заданному условию. Выполняется до GROUP BY и SELECT.

Активность когорты (месяц N) = (Активных пользователей когорты в месяц N) / (Размер когорты) × 100%

Метод анализа, при котором пользователей группируют по общему признаку (обычно — дате первого визита) и сравнивают их поведение во времени.

Конверсия = (Число совершивших действие / Число попавших на шаг) × 100%

Доля пользователей, совершивших целевое действие, от общего числа пользователей на предыдущем шаге воронки.

Churn rate = (Ушедшие клиенты за период / Клиенты на начало периода) × 100%

Доля клиентов, переставших пользоваться продуктом или отменивших подписку, за период.

-- В WHERE:

SELECT внутри другого SQL выражения. Может быть в FROM, WHERE, HAVING или SELECT.

CREATE INDEX ON table (col1, col2, col3);

Индекс по нескольким колонкам. Порядок колонок критически важен.

column_name data_type [NOT NULL] [DEFAULT value]

Определяют какие значения можно хранить в колонке и как они обрабатываются.

BEGIN;

Группа SQL операций, выполняемых как единое целое: либо все успешно, либо ни одна.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Определяет, насколько транзакция изолирована от изменений других транзакций.

Юнит-экономика сходится, если: LTV > CAC + Прочие переменные расходы на клиента

Анализ прибыльности бизнеса в расчёте на одного клиента (юнит) — сходится ли LTV с CAC и остальными расходами на клиента.