14500
Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3
Руководство по SQL
Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем
Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации
Сохраняйте, чтобы не потерять
👉 @SQLPortal
Group and Sum
SELECT SUM(calories_count) OVER(partition by currval) FROM
(SELECT CASE WHEN calories_count = '' THEN 0
ELSE calories_count :: int END AS calories_count,
CASE WHEN calories_count = '' THEN nextval('aoc')
ELSE currval('aoc') END
FROM calories
) x LIMIT 10;
sum
-------
56873
56873
56873
56873
56873
56873
56873
56873
43456
43456
Функции и процедуры на самом деле очень похожи, и многие называют их как попало, но в Postgres это разные вещи.
Functions:
- Считают и возвращают значения
- Не управляют транзакциями
- Можно использовать внутри SELECT или WHERE
- SELECT function()
Procedures:
- Действия и батчи
- COMMIT и ROLLBACK
- Нельзя вызывать внутри SQL
- CALL procedure()
👉 @SQLPortal
Почему SQL считается общим языком для работы с данными
SQL настолько важен, что даже внутри Python у тебя всё равно появляется SQL.
Да, даже в pandas — по сути тот же подход.
Да, даже в PySpark — тоже SQL.
Pandas просто заимствовал базовую логику: SELECT, WHERE, GROUP BY, JOIN и завернул её в методы и функции. Синтаксис другой, мышление то же.
Если начать с SQL, потом быстрее въезжаешь в pandas и PySpark — порог входа заметно ниже.
Если уже работаешь с pandas, то выучить SQL тоже будет довольно просто.
Вывод: если ты занимаешься данными, SQL обязателен.
👉 @SQLPortal
Postgres умеет очень просто агрегировать данные по датам через date_trunc — просто передай day, week, month, quarter и сделай GROUP BY по этому же полю.
SELECT
date_trunc('month', order_date) AS month,
COUNT(*) AS total_orders,
SUM(total_amount) AS monthly_total
FROM
orders
GROUP BY
date_trunc('month', order_date)
ORDER BY
month;
Операционные накладные расходы: почему целые числа лучше
1. Эволюция схемы
Добавление новых состояний является обычным требованием в развивающихся приложениях. Рассмотрим оперативные этапы для каждого подхода:
Булевы значения:
- Добавление нового состояния требует добавление в схему нового булева поля, что влечет за собой изменение базы данных, а также тестирование в различных средах в целях безопасности. Пример:
ALTER
-- Множество булевых полей
SELECT * FROM users WHERE isActive = TRUE AND isSuspended = FALSE;
-- Единственное поле status
SELECT * FROM users WHERE status = 1; -- Активный пользователь
Популярный SQL-вопрос на собеседованиях: Напишите запрос, который находит вторую по величине зарплату в таблице employees. А что делать, если второй по величине зарплаты не существует?
Зачем его задают:
Этот вопрос проверяет не знание синтаксиса, а базовое SQL-мышление. Поиск второй зарплаты заставляет учитывать дубликаты, сортировку, фильтрацию и пограничные случаи вроде «а если значения нет». Он показывает, умеешь ли ты рассуждать о данных, а не просто вытаскивать их запросом.
Интервьюеры также смотрят, понимаешь ли ты, как SQL пошагово обрабатывает результаты, и способен ли писать устойчивые запросы, а не хрупкие. С виду задача простая, но она быстро вскрывает, реально ли человек понимает основы SQL или просто заучил пару шаблонов.
Как отвечать:
Очевидно, способов несколько. Логика простая:
Сначала определить максимальную зарплату.
Затем найти максимальное значение, которое меньше этого максимума.
Для примера возьмём таблицу employees (id, name, salary). В решении ниже используется подзапрос.
Такой подход автоматически корректно работает с дубликатами. Даже если несколько сотрудников получают максимальную зарплату, подзапрос всё равно вернёт одно значение MAX, а внешний запрос найдёт следующую отличающуюся зарплату. Никаких костылей или допущений не нужно.
Самое важное:
Решение с подзапросом надёжное. Если второй по величине зарплаты не существует, запрос спокойно вернёт NULL, а не сломается и не выдаст мусор. Для интервьюера это сигнал, что ты думаешь про edge cases и пишешь защитный SQL.
Решение:
SELECT (
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
) AS second_highest_salary;
SQL-агент с самокоррекцией и визуализацией
Этот API на базе Flask предоставляет расширенные сервисы анализа и визуализации SQL-запросов с использованием LangChain и LangGraph.
⚡️ Ссылка
👉 @SQLPortal
Целые числа: масштабируемая альтернатива
Исходная схема
Целочисленное поле обладает большей гибкостью, поскольку оно может представлять множество состояний. Вместо многочисленных булевых полей единственное поле status может кодировать все возможные состояния, например:
CREATE TABLE users (
id INT PRIMARY KEY,
status INT NOT NULL
);
const Status = {
ACTIVE: 1,
PENDING: 2,
SUSPENDED: 3,
DEACTIVATED: 4,
};
Булевы значения и целые числа в базах данных: понимание компромиссов
Булевы значения просты и рациональны, но они плохо масштабируются, когда ваша модель данных эволюционирует. Целые числа могу элегантно обрабатывать множество состояний, уменьшая сложность схемы.
При проектировании базы данных разработчики часто сталкиваются с выбором казалось бы простого решения: следует ли использовать булевы значения или целые для представления поля? Хотя на первый взгляд разница может показаться тривиальной, выбор может оказать существенное влияние на хранилище, поддержку и масштабируемость. Давайте исследуем "за" и "против" каждого подхода, сосредоточив внимание на размере хранилища, накладных операционных расходах и обслуживании в долгосрочной перспективе.
Булевы значения: простой двоичный вариант
Исходная схема:
Булево поле подходит идеально, когда значение может иметь только два состояния: true или false. Например, поля типа isActive или isAdmin - подходящий вариант. Вот типичный пример:
CREATE TABLE users (
id INT PRIMARY KEY,
isActive BOOLEAN
);
Нужна opеn-source альтернатива Airtable?
NocoDB позволяет поверх существующей базы сделать интерфейс как у таблиц, без написания бэкенда.
Подключаешь MySQL, PostgreSQL, SQL Server, SQLite и т.д., а дальше работаешь с данными через вьюхи, фильтры, формы и связи между таблицами.
Можно развернуть у себя бесплатно или взять их хостинг, если не хочется заморачиваться с инфраструктурой.
https://github.com/nocodb/nocodb
👉 @SQLPortal
Это лучшая статья про MVCC в MySQL, что я читал.
MySQL и Postgres используют разные инженерные подходы для решения одной и той же задачи: undo log против нескольких версий кортежей.
Ещё одна отличная работа от Jeremy Cole.
👉 @SQLPortal
pgcli и mycli — отличные апгрейды по сравнению со стандартными клиентами psql и mysql.
Есть автодополнение, подсветка синтаксиса и в целом куда приятнее пользоваться.
Если заходишь к базе прямо из терминала, ставь их как можно раньше.
👉 @SQLPortal
Вставки в БД всегда про компромисс по производительности: либо пишем быстро и раскладываем всё по полкам позже, либо сразу платим за организацию данных.
Хорошо видно на примере MySQL и Postgres. В MySQL с InnoDB новые строки сразу падают в кластерный индекс. Это даёт быстрые последующие выборки, но вставка сама по себе дороже из-за обхода дерева, вставки в нужное место и возможного page split.
Postgres просто пишет новые строки в файл страниц/кортежей, из-за чего вставка получается попроще, но оптимизация места (VACUUM) откладывается на потом. Плюс индексы обновляются отдельно, потому что они хранятся вне основного файла таблицы.
На картину влияет и формат хранения: B-деревья против LSM-деревьев.
В B-деревьях вставка требует больше работы сразу. LSM разнесён по уровням, и система регулярно делает фоновые проходы и слияния. Запись выглядит «быстрее» (вкинули данные в in-memory слой), но потом движок постоянно занимается оптимизацией.
Отдельная история — журнал (binlog, WAL и так далее). Логи позволяют ускорить запись, перенося обновление реальных структур на диск до чекпоинта или вытеснения страницы.
Даже с этим механизмом заметно, насколько падает или растёт производительность в зависимости от того, сколько работы откладывается «на потом».
👉 @SQLPortal
MongoDB выкатывает внезапный праздничный фичерелиз
К РАСШИРЕННОМУ РЕЛИЗУ
Пало-Альто, Калифорния — MongoDB с радостью объявляет о MongoBleed™, инновационной фиче, которая проактивно делится содержимым вашей базы данных со всем интернетом.
«Годы нам говорили: как сделать наши чувствительные данные доступнее?» — говорит вымышленный спикер. — «MongoBleed отвечает на этот запрос. Никакой авторизации. Никакого согласия. Просто свободный, беспрепятственный поток данных наружу».
Ключевые возможности:
- Zero-Click Sharing: пароли сами уходят в интернет
- Десятилетие доверия: тихая бета с 2015 года
- Релиз под праздники: ничто так не говорит «С Рождеством», как продакшен-секреты на GitHub
- Elastic Integration: писали явно люди, которые «поняли задачу» по-своему
Отзывы пользователей:
«Я ел рождественский ужин, когда прилетел пейдж. База начала делиться логинами по всему миру. Семья реально сплотилась — вокруг моего ноутбука, пока я плакал». — вполне настоящий IT-админ
Что дальше?
Планы на 2026:
- Автоматическая рассылка паролей на Shodan
- AI-добыча секретов (они тоже в AI теперь, а как же)
- Интеграция со Slack, которая шлёт ваш .env прямо в #general
О MongoDB: MongoDB — база, которая верит, что данные хотят быть свободными. Очень свободными.
👉 @SQLPortal
Дан запрос:
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC
LIMIT 5;
Data Modeling
Мы заранее загрузили данные в таблицу calories с одним текстовым столбцом calories_count. В исходном файле пустая строка использовалась как разделитель групп, и это нужно учитывать в решении.
Теперь у нас есть таблица с одной строкой на каждую строку файла. Как сгруппировать такие данные? Группировку похожих строк обычно делают через window-функции. В нашем случае нужно «складывать» строки до тех пор, пока не встретится пустая строка, после чего начинать новую группу. Создадим псевдоколонку и будем увеличивать sequence только когда значение в calories_count пустое. Также вызовем setval() для задания начального значения, чтобы функция currval() работала.
CREATE SEQUENCE aoc;
SELECT setval('aoc', 1);
SELECT calories_count,
CASE WHEN calories_count = '' THEN nextval('aoc')
ELSE currval('aoc') END
FROM calories LIMIT 10;
calories_count | currval
---------------+---------
9686 | 1
10178 | 1
3375 | 1
9638 | 1
6318 | 1
4978 | 1
5988 | 1
6712 | 1
| 2
10422 | 2
Копался во внутренностях MySQL и наткнулся на интересную штуку — doublewrite buffer. Это аккуратный хак против порчи данных. Коротко о том, как он работает.
Когда в MySQL (InnoDB) происходит запись, движок не пишет страницу сразу на диск в её финальное место. Вместо этого он делает так:
- сначала пишет страницу в doublewrite buffer на диске
- fsync — чтобы гарантировать, что данные легли
- потом копирует страницу в её финальное место в data file
Зачем этот лишний шаг? Он даёт атомарность и защиту от крэшей.
Представь, что сервер падает ровно в момент записи страницы в data file. Мы переписываем старые байты новыми, и если запись порвётся посередине — файл данных может оказаться повреждённым. Восстановиться будет уже некуда.
Но так как InnoDB сначала пишет в отдельный буфер, у нас есть прочная копия страницы с последними изменениями. После рестарта движку остаётся просто скопировать её на место.
Если порча произошла на финальном шаге, InnoDB просто восстанавливает страницу из doublewrite buffer.
Цена — небольшой дополнительный I/O, но выигрыш — высокая надёжность, crash recovery и защита от повреждений. Очень крутое решение с инженерной точки зрения.
👉 @SQLPortal
SQL Studio вышел в публичную бету
Современный кроссплатформенный SQL-клиент, сделанный с упором на скорость и простоту.
SQLite уже поддерживается. Остальные диалекты в пути.
Баги прилагаются 🪲
[https://sql.studio]
👉 @SQLPortal
в Postgres 18 появился новый возвращаемый полеcет для OLD и NEW данных. куча прикладного кода зависит от того чтобы вставить данные и сразу знать старое и новое состояние. теперь можно сразу подтверждать INSERT и UPDATE без лишних костылей.
👉 @SQLPortal
SQL-совет: понимание оператора EXCEPT в SQL
SELECT sales_person, sales_amount
FROM sales
WHERE sales_person = 'Wei Zhang'
OR sales_person = 'Yuki Nakamura'
EXCEPT -- используем EXCEPT, чтобы получить строки из первого запроса, которых нет во втором
SELECT sales_person, sales_amount
FROM sales
WHERE sales_person = 'Wei Zhang'
OR sales_person = 'Giovanni Rossi';
Сравнение хранения: булевы значения или целые числа
1. Хранилище для множества булевых полей
Скажем, мы хотим представить следующие состояния: isActive, isPending, isSuspended и isDeactivated. Использование булевых значений:
- Каждое булево значение требует 1 байт.
- Для четырех булевых полей общий объем хранилища на запись составляет 4 байта.
Если набор данных состоит из 1 миллиона пользователей, то для 4 булевых полей потребуется 4 х 1000000 = 4000000 байтов (4 Мб).
Если при развитии системе потребовалось иметь 10 состояний, представленных 10 булевыми полями, требование к хранилищу растет линейно:
10 х 1000000 = 10000000 байтов (10 Мб).
По мере роста объема данных это линейное увеличение объема памяти становится все более заметным.
2.Хранилище для единственного поля состояний
Использование целочисленного поля:
- Целое требует четырех байтов.
- Суммарный объем хранилища для 1 миллиона пользователей: 4 х 1000000 = 4000000 байтов (4 Мб).
Даже если при развитии система будет включать 10 состояний, размер хранилища на одну запись останется постоянным - 4 байта. Для 100 миллионов пользователей потребуется размер хранилища 4 х 100000000 байтов (400 Мб).
Ключевой момент
Хотя размер хранилища эквивалентен для простых случаев, целочисленное поле сохраняет постоянный размер вне зависимости от числа состояний. Напротив, хранилище для булевых полей растет линейно с ростом добавляемых полей. Для сильно масштабируемых наборов данных разница в размерах хранилища становится значительной, делая целочисленное поле более масштабируемым вариантом.
👉 @SQLPortal
Полностью бесплатная веб-книга про индексы в SQL: https://use-the-index-luke.com/
Охватывает сразу несколько СУБД: MySQL, PostgreSQL, Oracle и другие.
👉 @SQLPortal
Простая демонстрация того, насколько мощными могут быть вложенные выражения CASE для анализа данных.
👉 @SQLPortal
Полная и актуальная документация по PostgreSQL
SQL-синтаксис, индексы, транзакции, планировщик запросов, репликация, расширения и внутренняя архитектура. Здесь подробно описано не только как писать запросы, но и как база данных работает.
Забираем здесь!
👉 @SQLPortal
4,112,928 rows affected
👉 @SQLPortal
Кое-что полезное: учебное пособие по SQL для анализа данных
ЗАБИРАЕМ ЗДЕСЬ
👉 @SQLPortal
Как планировщик оценивает план выполнения запросов
Раз уж разобрались с cost’ами и статистикой, посмотрим, как PostgreSQL реально собирает и выбирает план.
Планировщик разбивает запрос на части и считает стоимость каждой отдельно.
Для каждой таблицы в запросе PostgreSQL оценивает разные варианты доступа. Пройтись по всей таблице seq scan? Использовать индекс? Задействовать несколько индексов вместе? Каждый вариант получает свою оценку стоимости на основании параметров cost’ов и статистики, о которых говорили в пред постах.
Для join’ов между таблицами PostgreSQL перебирает разные алгоритмы: nested loop, hash join, merge join. Каждый вариант также получает оценку.
Итоговая стоимость плана — это сумма всех операций: сканирование таблиц, join’ы, сортировки, фильтры. PostgreSQL генерирует несколько полноценных планов, считает итоговую стоимость каждого.
Побеждает тот, у которого минимальная оценка. Именно его PostgreSQL и исполнит.
Подход снизу вверх: оцениваем отдельные операции, собираем их в полный план, выбираем самый дешёвый. Так PostgreSQL ориентируется в огромном пространстве вариантов. Дальше посмотрим, как он оценивает варианты доступа к таблицам.
👉 @SQLPortal
Просмотр статистики
PostgreSQL держит всю эту инфу в системных каталогах, которые можно дернуть обычным запросом.
-- Базовая статистика по таблице
SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'film';
-- Результат:
-- schemaname | relname | n_live_tup | n_dead_tup
-- -----------+---------+------------+------------
-- public | film | 1000 | 0
-- Статистика по колонкам
SELECT tablename, attname, n_distinct, null_frac,
most_common_vals, most_common_freqs,
correlation
FROM pg_stats
WHERE tablename = 'film' AND attname = 'rental_duration';
-- Результат:
-- tablename | attname | n_distinct | null_frac | most_common_vals | most_common_freqs | correlation
-- ----------+-----------------+------------+-----------+-------------------+----------------------------------------+-------------
-- film | rental_duration | 5 | 0 | {6,3,4,5,7} | {0.212,0.203,0.203,0.191,0.191} | 0.163
Статистика: понимание своих данных
Формулы стоимости ничего не стоят без точного представления о данных. Сколько строк пройдёт по WHERE? Сколько уникальных значений у колонки? Какие значения встречаются чаще всего? Ответы на эти вопросы дают именно статистики.
Команда ANALYZE в PostgreSQL берёт выборку из таблиц и строит статистические сводки. Эти данные участвуют во всех решениях планировщика. Именно они отделяют нормальный план выполнения от полного провала.
Что отслеживает PostgreSQL
Для каждой колонки PostgreSQL хранит набор статистик:
Row Counts: количество живых строк в каждой таблице. Это база для всех оценок - если планировщик не знает примерный размер таблицы, он не сможет адекватно прикинуть стоимость запроса.
Null Fraction: доля NULL-значений. Когда вы пишете WHERE column IS NOT NULL, именно эта метрика помогает планировщику понять, насколько фильтр будет выборочным.
Most Common Values (MCVs): самые частые значения и их частоты. PostgreSQL хранит до ~100 таких значений на колонку.
Почему MCV важны. Представим таблицу:
-- У большинства фильмов rental_duration = 6 (21.2%), остальные распределены ровнее
SELECT * FROM film WHERE rental_duration = 6;
-- MCV показывает 21.2% → вероятнее всего последовательное сканирование
SELECT * FROM film WHERE rental_duration = 7;
-- MCV показывает 19.1% → всё ещё относительно выборочно
SELECT * FROM payment WHERE amount BETWEEN 5.00 AND 10.00;